Recordset loop not working

[iglobalusa]

I'm trying to edit fields in a recordset by looping through it using an ID number to match the record to edit. All records with a matching ID number are successfully edited except for the record prior to the last matching record. Can anyone help me resolve this problem?

Private Sub txtl_name_AfterUpdate()
On Error GoTo Error_txtl_name_AfterUpdate
    'look for all records with the same stateno then update the same field
    Dim strstateno As String
    strstateno = Trim(Me.stateno)
    strSQL = "Select * from labdata Where stateno = '" & strstateno & "'"
    Set DAO_DB = CurrentDb
    Set DAO_RS = DAO_DB.OpenRecordset(strSQL, dbOpenDynaset)
 
    'prevent write conflict
    If Me.Form.Dirty Then Dirty = False
 
    With DAO_RS
        'find the first stateno match to edit
        .FindFirst "[stateno] = '" & strstateno & "'"
        'search for stateno match in labdata
        If Not DAO_RS.NoMatch Then
            'edit l_name field
            .Edit
                !l_name = Me.l_name
                !transfer = 0
            'commit the edit
            .Update
        End If
 
        Do While Not DAO_RS.EOF
            'keep finding the next match until EOF
            .FindNext "[stateno] = '" & strstateno & "'"
            If Not DAO_RS.NoMatch Then
                   'edit l_name field
                   .Edit
                        !l_name = Me.l_name
                        !transfer = 0
                    'commit the edit
                    .Update
            End If
            'move to next record if not EOF
            .MoveNext
        Loop
    End With
    'close recordset
    DAO_RS.Close
    Set DAO_RS = Nothing
Exit_txtl_name_AfterUpdate:
     Exit Sub
Error_txtl_name_AfterUpdate:
    MsgBox "Error Number: " & Err.Number & vbCrLf & "Error Description: " & _
    Err.Description, vbInformation, "Error:"
    Resume Exit_txtl_name_AfterUpdate
End Sub

 

[DCrake]

At the top of your code your sql is creating a recordset for a matching states. What you need to then is to check for EOF

Dim Rs as DAO.Recordset
Set Rs = currentdb.Openrecordset("Select * where ...")

If Not Rs.EOF and Not Rs.BOF Then

Do Until Rs.EOF

Rs. Edit
....
Rs.Update
Rs.MoveNext

Loop
Rs.Close
End If

Set Rs = Nothing

Its seems to me that the findnext stuff is not needed as all records found in the recordset match the criteria you have requested.

 

[iglobalusa]

Thanks, David. You resolved the problem! And thanks for your advice on writing efficient code. I got rid of the "findnext stuff" since they were obviously not needed. What was I thinking!

Here's the modification:

Private Sub txtl_name_AfterUpdate()
On Error GoTo Error_txtl_name_AfterUpdate
    'look for all records with the same stateno then update the same edited field
    Dim strstateno As String
    strstateno = Trim(Me.stateno)
    strSQL = "Select * from labdata Where stateno = '" & strstateno & "'"
    Set DAO_DB = CurrentDb
    Set DAO_RS = DAO_DB.OpenRecordset(strSQL, dbOpenDynaset)
 
    'prevent write conflict
    If Me.Form.Dirty Then Dirty = False
 
    With DAO_RS
        If Not .EOF And Not .BOF Then
            Do While Not .EOF
                'edit l_name field
                .Edit
                    !l_name = Me.l_name
                    !transfer = 0
                'commit the edit
                .Update
                'move to next record if not EOF
                .MoveNext
            Loop
        End If
    End With
    'close recordset
    DAO_RS.Close
    Set DAO_RS = Nothing
Exit_txtl_name_AfterUpdate:
    Exit Sub
Error_txtl_name_AfterUpdate:
    MsgBox "Error Number: " & Err.Number & vbCrLf & "Error Description: " & _
    Err.Description, vbInformation, "Error:"
    Resume Exit_txtl_name_AfterUpdate
End Sub