Password Protect a Form

[billgyrotech]

I have the following code to protect a form. Either 'Houston' or 'Austin' will allow the form to open. The code is in the On Open event of the form.

************************

Private Sub Form_Open(Cancel As Integer)
Dim PassWord As String

PassWord = InputBox("Enter Password")
Cancel = (PassWord <> "Houston" And PassWord <> "Austin")

If Cancel Then MsgBox ("You are not authorized for access.")
End Sub

************************

My question is it possible to have a Password Table that this code gets the passwords from? This will be for different security levels to open forms. I have a Main Switchboard sectioned Users and Administrators. The Administrators area has forms that have this code for password protection.

Thanks for any help,
Bill

 

[nanscombe]

You might want to reconsider some of your names. With a table, field and input all called "Password" it's going to get confusing to say the least.

Private Sub Form_Open(Cancel as Integer)
Dim PassWord As String, varSecurityLevel as Variant
 

  PassWord = InputBox("Enter Password")

  If Len(Password)

    varSecurityLevel = Nz(DLookup("SecurityLevel", "Password", "Password = '" & Password & "'") , 0) & ""

  End if

  Cancel = (Instr("12", varSecurityLevel) = 0) ' Or whatever combinations of Security level

  If Cancel Then MsgBox ("You are not authorized for access.")

End Sub

 

[sefa3333]

Good afternoon. how do you get the inputbox to cancel if null value is entered or user clicks OK or Cancel buttons. when using the code from Nigel above on Form Open.

 

[theDBguy]

Good afternoon. how do you get the inputbox to cancel if null value is entered or user clicks OK or Cancel buttons. when using the code from Nigel above on Form Open.

Hi. You're replying to an old thread. The Inputbox() function returns (I think) an empty string when the user clicks on the Cancel button. If so, you can check for that. Hope that helps...

 

[sefa3333]

Thanks for your reply. i am just starting to learn VBA. on the open form function it work if text is put in the input box, however if input box is left blank and they click OK, Cancel or hit the ESC or enter key it opens the form.
the user has to enter a WO id # from the form Query to open certain record, the password input box opens based on the code, but my problem Listed above still opens the form to the record requested in the 1st input box.
I need the open form event to cancel if the user clicks OK, Cancel, hits Enter Key or ESC if password input box is blank, just as if they entered the wrong password.
here is the code i am using with a table that has the security level for each employee.

Private Sub Form_Open(Cancel As Integer)
Dim Password As String, varSecurityLevel As Variant
Password = InputBox("Enter Password")
If Len(Password) Then
varSecurityLevel = Nz(DLookup("SecurityLevel", "tblEMPLOYEELIST", "Authorization='" & Password & "'"), 0) & ""

End If
Cancel = (InStr("1 or 2 or 10", varSecurityLevel) = 0) ' Or whatever combinations of Security level
If Cancel Then MsgBox ("You are not authorized for access.")
End Sub

thanks for your help in advance.

 

[theDBguy]

Thanks for your reply. i am just starting to learn VBA. on the open form function it work if text is put in the input box, however if input box is left blank and they click OK, Cancel or hit the ESC or enter key it opens the form.
the user has to enter a WO id # from the form Query to open certain record, the password input box opens based on the code, but my problem Listed above still opens the form to the record requested in the 1st input box.
I need the open form event to cancel if the user clicks OK, Cancel, hits Enter Key or ESC if password input box is blank, just as if they entered the wrong password.
here is the code i am using with a table that has the security level for each employee.

Private Sub Form_Open(Cancel As Integer)
Dim Password As String, varSecurityLevel As Variant
Password = InputBox("Enter Password")
If Len(Password) Then
varSecurityLevel = Nz(DLookup("SecurityLevel", "tblEMPLOYEELIST", "Authorization='" & Password & "'"), 0) & ""

End If
Cancel = (InStr("1 or 2 or 10", varSecurityLevel) = 0) ' Or whatever combinations of Security level
If Cancel Then MsgBox ("You are not authorized for access.")
End Sub

thanks for your help in advance.

Okay, just a guess, but try this:

Private Sub Form_Open(Cancel As Integer)
Dim Password As String, varSecurityLevel As Variant
Password = InputBox("Enter Password")
If Len(Password) Then
  varSecurityLevel = Nz(DLookup("SecurityLevel", "tblEMPLOYEELIST", "Authorization='" & Password & "'"), 0) & ""
  Cancel = (InStr("1 or 2 or 10", varSecurityLevel) = 0) ' Or whatever combinations of Security level
  If Cancel Then MsgBox ("You are not authorized for access.")
Else
  Cancel = True
End If
End Sub

Hope that helps...

 

[sefa3333]

WORKS GREAT!! Thank you so much for your help.

 

[theDBguy]

WORKS GREAT!! Thank you so much for your help.

Hi. You're very welcome. Glad we could assist. Good luck with your project.