Searching Records - Error Message

J

Janice W.

Please help, I have very little experience creating VBA.

The staffing office form has a field called Filled_By_Combo_Box with
tblRestrictedStaff attached with Restricted Staff Name field. The
tblRestrictedStaff lists staff who cannot work at our facility. Below I have
tried to create a search to determine if the restricted staff name is in the
tblRestrictedStaff. When the clerical staff start to type a name of anyone in
the tblRestrictedStaff I want an error message telling them that the staff
are restricted. The clerical staff must also be able to add in the names of
staff who are not restricted.

I tried to originally do this by changing the colour of the restricted
staff, however, this did not work.

Private Sub Filled_By_Combo_Box_AfterUpdate()

'to find restricted staff
If Not IsNull(Filled_By_Combo_Box) Then
DoCmd.GoToControl "Filled_By_Combo_Box"
DoCmd.FindRecord tblRestrictedStaff, acEntire, 0, acSearchAll, , acAll
DoCmd.GoToControl "tblRestrictedStaff.Agency"

End If

Filled_By_Combo_Box = Not Null

End Sub

Private Sub Form_Error(DataErr As Integer, Response As Integer)

'Display custom error message for restricted staff
If DataErr = 2019 Then
MsgBox "Staff are restricted to work at HRRH, press End to continue"
Respone = acDataErrContinue
DoCmd.GoToControl "Filled_By_Combo_Box"
Else
Response = acDataErrDisplay
End If
End Sub
 
Top