My error trapping only works once

[Keith]

I have a routine that gets all the people from a department and updates their
public holidays table to say that the date a user inputs on a form they took
that holiday on the proper day.

Unfortunately some people in the department might not have that as a holiday
because of when they started, so there will be the occasional No Current
record error. I have trapped this error and used it to report the person to
the user then continue with the other people in the department.

This works fine for the first No current record but the second one it
produced the Access error window.

Why will it only trap once?

 

[Doug Steele]

Pretty difficult to offer any advice without seeing the code that's
misbehaving.

 

[Keith]

This is the code

Sub DepartmentUpdate()
On Error GoTo ErrorHandling_Err

Dim db As Database
Dim recStaff As Recordset
Dim recEntitlement As Recordset
Dim recPtaken As Recordset
Dim recRequests As Recordset
Dim intEmpNo As Integer
Dim strStaff As String
Dim strPublic As String
Dim strName As String

'tfIsDate =
PublicDayCheck(Format([Forms]![frmDepartmentUpdate].[Combo7], "mm/dd/yyyy"))
'Debug.Print tfIsDate
Set db = CurrentDb()
strStaff = "SELECT [tblStaff Data].EmpNo, [tblStaff Data].Forenames,
[tblStaff Data].Surname FROM [tblStaff Data]WHERE ((([tblStaff
Data].Department)=" & Chr$(34) & [Forms]![frmDepartmentUpdate].[Combo2] &
Chr$(34) & "))"
Set recStaff = db.OpenRecordset(strStaff)
recStaff.MoveFirst
Do While Not recStaff.EOF
intEmpNo = recStaff("EmpNo")
strName = Trim(recStaff("Forenames")) & " " &
Trim(recStaff("Surname"))
strPublic = "SELECT tblPublicTaken.EmpNo, tblPublicTaken.Year,
tblPublicTaken.Holiday, tblPublicTaken.ActualDate, tblPublicTaken.TakenOnDay,
tblPublicTaken.Lieu FROM tblPublicTaken WHERE (((tblPublicTaken.EmpNo) =" &
intEmpNo & ") And ((tblPublicTaken.ActualDate) = #" &
Format([Forms]![frmDepartmentUpdate].[Combo7], "mm/dd/yyyy") & "#))"
Debug.Print strPublic
Set recPtaken = db.OpenRecordset(strPublic)
recPtaken.MoveFirst
'With recPtaken
' .Edit
' !TakenOnDay = True
' .Update
'End With
NoRecord:
recPtaken.Close
recStaff.MoveNext
Loop


ErrorHandling_Exit:
Exit Sub


ErrorHandling_Err:
Select Case Err.Number
Case 3021 'No Current Record
MsgBox strName & " Was not updated as this date is not on their
Public Holiday List."
GoTo NoRecord
Case Else
MsgBox "Error Detected: " & Err.Description & " - " & Err.Number
Resume ErrorHandling_Exit
End Select
End Sub

The error is when the line

recPtaken.MoveFirst

comes across an empty recordset. It works the forst time round but if a
second one is found it resorts back to the standard Access error.

I have found a workaround, but I would still like to know why it only works
once.