Jan 05 2024 04:54 AM
Hi
I have successfully been able to create the necessary VBA code to restrict access to one particular form. This works by having a table of authorised windows usernames and then the code for the On Open event of the form checks to see if the user is in the table and blocks access if not. There's just one hiccup I can't get to the bottom of. The opening of the form is via a control on the Switchboard and if the user is in the authorised users table then the form opens fine. If the user is not in the table, then the message box shows fine but when I click OK on the message box I get the run-time error 2501 with the message saying 'The OpenForm action was cancelled'. When I choose Debug, it takes me to the DoCmd.OpenForm.... code for the control on the switchboard. So I think it is still trying to run the OpenForm command and I can't figure out how to stop that. I'm sure it's something simple but I'm just not getting it. The code is as follows:
Private Sub Form_Open(Cancel As Integer)
On Error GoTo Error_Handler
If Nz(DLookup("UserName", "tblUsers", _
"[ObjectType]='Form' AND [ObjectName]='" & Me.Name & "' AND [UserName]='" & fOSUserName() & "'"), "") = "" Then
Cancel = True
MsgBox "You are not authorized to open this form", vbInformation Or vbOKOnly, "Operation cancelled"
End If
Error_Handler_Exit:
On Error Resume Next
Exit Sub
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: Form_Open" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Sub
Can anybody give me any clues please?
Many thanks
Jan 05 2024 07:51 AM
In the sub which attempts to open the form, you need to handle the 2501 error when it is returned to that form.
Jan 06 2024 02:45 PM
SolutionJan 09 2024 06:46 AM - edited Jan 09 2024 06:47 AM
Many thanks for the responses. I hadn't considered using a class module for error handing before, whilst in hindsight it makes perfect sense. On this occasion I chose to handle the error on the DoCmd.OpenForm action rather than the class module. Now you have highlighted this to me I will certainly be looking at this in the future so thanks for that.
Jan 06 2024 02:45 PM
Solution