Create a Login for a form

Question

So, I was looking for a way to prevent my users from accessing one of my forms. I found a video from a couple of years ago, here is the link - https://www.youtube.com/watch?v=bPvzneN1jyg&t=1s

I got most of it to work but for some reason when I enter the wrong user name or password, or leave the boxes blank, I get a run-time error, type-mismatch. I've tried this a couple of times over the last couple of days thinking that maybe I may of miss typed something. The author of the video does not provide a URL to his website that I can find so I am trying to re-type it from the video.

I can get it to work if I use the correct user name and password, so I know that much of it works and the quit button works fine, also.

Below is the code, maybe someone can see what is missing. Funny, the author shows it works on his computer and goes back to the VBA and it looks exactly like mine so I cannot see how his is working but mine is not but this is not the first time that I may have missed something.

Option Compare Database
Dim passok As Boolean
Dim pass As String

Private Sub cmdOK_Click()
On Error GoTo cmdOK
Dim UserN As String
Dim clogin As String
Dim stDocName As String
Dim stLinkCriteria As String

pass = DLookup("[password]", "login", "[UserName]= '" & cboUserName & "'")

If Passwords <> pass Or IsNull(Passwords) Then
MsgBox "Wrong Password", vbCrLf & "Retry!!", vbCritical, "User Login Pro!!"
Exit Sub
End If
passok = True
DoCmd.Close

stDocName = "SWITCHBOARD"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit Sub
cmdOK:
MsgBox "User doesn't exist!", vbCrLf & Err.Description, vbExclamation, "User Login Pro!!"

End Sub

Private Sub cmdQuit_Click()
On Error GoTo err_cmdQuit_click
Dim resp As String
resp = MsgBox("Do you want to quit?!", vbYesNo, "User Login Pro!")
If resp = vbNo Then
Exit Sub
End If
DoCmd.Quit
exit_cmdQuit_click:
Exit Sub
err_cmdQuit_click:
MsgBox Err.Description, vbExclamation, "User Login Pro!!"
Resume exit_cmdQuit_click

End Sub

Additionally, while watching his video and trying to make this work, I got to thinking, what keeps other users from opening the login table and changing the passwords or adding someone else that should not have access? Any help will be appreciated.

Answer 1

I would use the Windows login. Any type of security in an Access application can be broken. Collin has lots of great examples and code for locking down your applications at https://isladogs.co.uk/videos/index.html.

Answer 2

Although the answer provided by AI above did not help, I did figure out how to do this by looking at other websites and such.

Answer 3

It seems like the runtime error you are encountering may be due to the way you are trying to retrieve the password using the DLookup function. If the username entered in cboUserName does not exist in the database, DLookup will return a null value, which could lead to a type mismatch when you compare it with Passwords. Here are a few suggestions to help you troubleshoot and improve your code:

1. Check for Null Values: Before comparing the password, ensure that pass is not null. You can modify your code as follows:

      pass = DLookup("[password]", "login", "[UserName]= '" & cboUserName & "'")
      If IsNull(pass) Or Passwords <> pass Then
      MsgBox "Wrong Password", vbCrLf & "Retry!!", vbCritical, "User Login Pro!!"
      Exit Sub
      End If
   

2. Ensure User Exists: You should also check if the user exists before trying to retrieve the password. You can do this by checking if pass is null right after the DLookup call.
3. Error Handling: Your error handling is set to go to the label cmdOK, which is also the name of your subroutine. This could cause confusion. Consider renaming the label to something like ErrorHandler to avoid conflicts.
4. Protecting the Login Table: To prevent unauthorized users from accessing the login table and modifying passwords, you can implement several strategies:
   • User Permissions: Set permissions on the database so that only authorized users can access the login table.
   • Hide the Table: You can hide the table from the navigation pane to make it less accessible.
   • Use a Secure Connection: Ensure that your application uses secure methods to connect to the database, such as using encrypted connections.

By implementing these suggestions, you should be able to resolve the runtime error and enhance the security of your login system.