Do while loop not playing the game

[Malcy]

Hi
I have a do while loop which seems to keep falling over when it gets to the last record.

' Set up loop to append new records to tblStaffDays
        If rst.EOF Or rst.BOF Then
            .Close
        End If
        
        Do While Not rst.EOF And Not rst.BOF
        
' Set the [qcboStaff].[bUpd] back to No and set the next record to Yes
            .Fields("bUpd") = 0
            .Update
            .MoveNext
            .Fields("bUpd") = -1
            .Update
    
' Append new set of dates to tblStaffDays
    DoCmd.SetWarnings False
        strQryDef = "qappSdCalDates"
            DoCmd.OpenQuery strQryDef, acViewNormal, acAdd
    DoCmd.SetWarnings True
    
' Update [lngEmpId] from 0 for the member of staff where [tblStaff].[bUpd] = Yes
    DoCmd.SetWarnings False
        strQryDef = "qupdStaffDaysEmpNo"
            DoCmd.OpenQuery strQryDef, acViewNormal, acAdd
    DoCmd.SetWarnings True
    
' Loop back to create next set of records
    Loop

What seems to happen is that first time through the If/Endif bit works but after that the loop goes back to the do while line.
I have played it through line by line and it falls over having added the final record.
It gets to the do while, doesn't seem to recognise it is the last record, updates the field bUpd to 0, then moves to the next record (there isn't one) and falls over when it tries to update the bUpd field to -1

What I am trying to do is walk through the recordset adding records to an archive table for each active member of staff. So flag staff member 1 then process, unflag staff member 1 and move onto staff member 2, flag them ,process untill all members added.

Can anyone tell me which bit I have got wrong or what I am missing?
Any help greatly appreciated
Best wishes

 

[Mile-O]

You might want to start by changing:

If rst.EOF Or rst.BOF Then
    .Close
End If

to

If .BOF And .EOF Then
    .Close
    Exit Sub
End If

 

[DALeffler]

The EOF property doesn't signify the last record position: When true the EOF property signifies the record position is past the last record (mirroring the BOF property which when true means the record position is before the first record, not at the first record).

If BOF and EOF are both true, there are no records in the recordset.

With rst
' Set up loop to append new records to tblStaffDays
        If .EOF And .BOF Then
           .Close     'No records...
            Exit Sub  'or something else
        End If
        
        rst.MoveFirst

        Do While Not .EOF
        
' Flag record to Yes
            .Fields("bUpd") = -1
            .Update
    
' Append new set of dates to tblStaffDays
    DoCmd.SetWarnings False
        strQryDef = "qappSdCalDates"
            DoCmd.OpenQuery strQryDef, acViewNormal, acAdd
    DoCmd.SetWarnings True
    
' Update [lngEmpId] from 0 for the member of staff where [tblStaff].[bUpd] = Yes
    DoCmd.SetWarnings False
        strQryDef = "qupdStaffDaysEmpNo"
            DoCmd.OpenQuery strQryDef, acViewNormal, acAdd
    DoCmd.SetWarnings True
 
' Flag record to No
            .Fields("bUpd") = 0
            .Update
            .MoveNext

' Loop back for next record
    Loop

End With

But wouldn't it more efficient to mark all the records in rst as false using an update query, process all the records at once using the querys in the two DoCmd methods above, and "unmark" all the rst records using the same update query but with a different update value?

 

[Pat Hartman]

The problem is one of logic:

Do While Not rst.EOF And Not rst.BOF

As long as the recordset is not empty both of these will ONLY be true at the same time while you are within the bounds of the file. At EOF, they will fail to both be true. Normally when looping through a recordset, you would rely on only the EOF indicator.

Do While Not rst.EOF

 

[Malcy]

Thanks for the ideas
I have gone through comments and finally changed code to that given below and it works. It seems my real problem was moving to the next record after I had gone through the Do While test.
I am putting the code in just incase anyone hits a similar problem
Thanks again for all your help
Best wishes

' If there are no records in qcboStaff then exit
        If .EOF And .BOF Then
            .Close
            Exit Sub
        End If
            
' Update bUpd for the first record in the recordset to -1
            .MoveLast
            .MoveFirst
            .Fields("bUpd") = -1
            .Update
            
' Append dates to tblStaffDays'
    DoCmd.SetWarnings False
        strQryDef = "qappSdCalDates"
            DoCmd.OpenQuery strQryDef, acViewNormal, acAdd
    DoCmd.SetWarnings True
    
' Update [lngEmpId] from 0 for the first member of staff where [tblStaff].[bUpd] = Yes
    DoCmd.SetWarnings False
        strQryDef = "qupdStaffDaysEmpNo"
            DoCmd.OpenQuery strQryDef, acViewNormal, acAdd
    DoCmd.SetWarnings True

' Set the [qcboStaff].[bUpd] back to No and move to the next record
            .Fields("bUpd") = 0
            .Update
            .MoveNext

' Set up loop to append new records to tblStaffDays
' Set the [qcboStaff].[bUpd] to Yes
        Do While Not .EOF
            .Fields("bUpd") = -1
            .Update
    
' Append new set of dates to tblStaffDays
    DoCmd.SetWarnings False
        strQryDef = "qappSdCalDates"
            DoCmd.OpenQuery strQryDef, acViewNormal, acAdd
    DoCmd.SetWarnings True
    
' Update [lngEmpId] from 0 for the member of staff where [tblStaff].[bUpd] = Yes
    DoCmd.SetWarnings False
        strQryDef = "qupdStaffDaysEmpNo"
            DoCmd.OpenQuery strQryDef, acViewNormal, acAdd
    DoCmd.SetWarnings True

' Set the [qcboStaff].[bUpd] back to No and set the next record to Yes
            .Fields("bUpd") = 0
            .Update
            .MoveNext
    
' Loop back to create next set of records
    Loop

 

[modest]

I usually use Do Until rs.EOF

and if you're closing the recordset... you should also release the pointer.
rs.Close
Set rs = nothing