MS Access 2010 VBA Integer Variable not changing in Loop

Question

I have code that is looping through an array of member numbers and retrieving records for each. Each time, I need to use the count of the records returned up to 12. However, once the variable that is to hold the count is set, it will not reset with the next call. It also "jumps" from the first to the last record rather than looping through each. In other words, if there are 4 records returned by the recordset, it will execute for the first and the last and then give an error of "No Current Record" Here is my code:

 Dim x As Integer
For i = 1 To intMembers
strGetMemberInfo = "SELECT PatientRecords.[Medication Name], PatientRecords.[First Name], PatientRecords.[Last Name],PatientRecords.[doc phone]" _
& " FROM PatientRecords WHERE member_no ='" & arrMembers(i) & "'"
Set rstMedicine = dbs.OpenRecordset(strGetMemberInfo, dbOpenSnapshot)
Dim intMedicine As Integer
    intMedicine = rstMedicine.RecordCount
    If intMedicine > 12 Then
    intMedicine = 12
    End If

Do Until rstMedicine.EOF
    For x = 1 To intMedicine
    strMedicationField = strMedication & x
    strDoctorFNameField = strDoctorFName & x
    strDoctorLNameField = strDocotrLName & x
    strDoctorPhoneField = strDoctorPhone & x
    strSQL = "UPDATE TransformationTable SET " & strMedicationField & " = '" & rstMedicine.Fields("[Medication Name]").Value & "'," & strDoctorFNameField & " = '" & rstMedicine.Fields("[First Name]").Value & "', " & strDoctorLNameField & " = '" & Replace(rstMedicine.Fields("[Last Name]"), "'", "''") & "', " & strDoctorPhoneField & " = '" & rstMedicine.Fields("[doc phone]").Value & "' WHERE member_no ='" & arrMembers(i) & "'"
    dbs.Execute strSQL


rstMedicine.MoveNext
 Next x
Loop
rstMedicine.Close
Set rstMedicine = Nothing
Next i

In the above code, intMedicinegets set by the first recordset and NEVER changes even though rstMedicine.RecordCount does change.

Any help is appreciated

How many records are in rstMedicine? If it is greater than twelve, then intMedicine will always be 12. You say it will not reset with the next call, however what value would you like it to reset to? — 0liveradam8
– 0liveradam8, Commented Aug 17, 2017 at 20:59
Check the return value of rstMedicine.RecordCount - this is not always reliable — Tim Williams
– Tim Williams, Commented Aug 17, 2017 at 21:42

Rani Radcliff · Accepted Answer · 2017-08-18 12:41:07Z

You have 2 different issues. First, use rstMedicine.MoveLast to move to the bottom of the recordset and get the full count. Second. Your limiting the number of "cycles" to 12, but you are not exiting the loop after intMedicine is 12, so it is still trying to get to the end of the recordset because your code says "Do Until rstMedicine.EOF". Change your code to this:

      Dim x As Integer
    For i = 1 To intMembers
    strGetMemberInfo = "SELECT PatientRecords.[Medication Name], PatientRecords.[First Name], PatientRecords.[Last Name],PatientRecords.[doc phone]" _
    & " FROM PatientRecords WHERE member_no ='" & arrMembers(i) & "'"
    Set rstMedicine = dbs.OpenRecordset(strGetMemberInfo, dbOpenSnapshot)
rstMedicine.MoveLast
    Dim intMedicine As Integer
        intMedicine = rstMedicine.RecordCount
        If intMedicine > 12 Then
        intMedicine = 12
        End If
    rstMedicine.MoveFirst
    Do Until rstMedicine.EOF
        For x = 1 To intMedicine
        strMedicationField = strMedication & x
        strDoctorFNameField = strDoctorFName & x
        strDoctorLNameField = strDocotrLName & x
        strDoctorPhoneField = strDoctorPhone & x
        strSQL = "UPDATE TransformationTable SET " & strMedicationField & " = '" & rstMedicine.Fields("[Medication Name]").Value & "'," & strDoctorFNameField & " = '" & rstMedicine.Fields("[First Name]").Value & "', " & strDoctorLNameField & " = '" & Replace(rstMedicine.Fields("[Last Name]"), "'", "''") & "', " & strDoctorPhoneField & " = '" & rstMedicine.Fields("[doc phone]").Value & "' WHERE member_no ='" & arrMembers(i) & "'"
        dbs.Execute strSQL


    rstMedicine.MoveNext
If x = 12 Then
Exit Do
End If
     Next x
    Loop
    rstMedicine.Close
    Set rstMedicine = Nothing
    Next i

Collectives™ on Stack Overflow

MS Access 2010 VBA Integer Variable not changing in Loop

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related