Hi
I have a do while loop which seems to keep falling over when it gets to the last record.
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
I have a do while loop which seems to keep falling over when it gets to the last record.
Code:
' 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