login form (1 Viewer)

Danny · Jun 23, 2017

Greetings,

I’ve a login form that users have to input username/password to access the DB.

However, here is some scenarios that I’d like to enhance the code to avoid the following error:

1. If an invalid username is entered, I get the following error:
Run-time error ‘3021’
No current record.

2. An attempt to click on password w/o entering a user name will result in the following error:
Run-time error ’94:
Invalid use of Null

3. If password entered is incorrect,
The logon button and password text will grey out. To successfully login with a valid password, one must click ‘enter’ on keyboard.

Code:

Private Sub txtUserNm_BeforeUpdate(Cancel As Integer)
'Check To See If User Are Valid. Look Into The Table To Get User Status
Dim strUser As String
Dim strStatus As String
Dim strSQL As String
Dim db As DAO.Database
Dim rstStatus As DAO.Recordset

Set db = OpenDatabase("C:\DB folder path")
Set rstStatus = db.OpenRecordset("tblLOGIN")
strUser = Me.txtUserNm
rstStatus.Index = "USERNM"
rstStatus.Seek "=", strUser

If rstStatus!STATUS = "A" Then
    strStatus = rstStatus!STATUS
    Me.txtStatus = strStatus
End If

    If Me.txtStatus = "A" Then
        Me.txtPWD.Enabled = True
     '  Me.txtPWD.SetFocus
        Me.txtValidUser = "Y"
    Else
        MsgBox "Invalid User Name Try Again" & vbCrLf & "          GOOD-BYE", vbCritical + vbOKOnly, "TRY AGAIN LATER"
        Me.txtValidUser = "N"
        Me.txtPWD.Enabled = False
        Me.cmdLOGIN.Enabled = False
        DoCmd.Quit

    End If

End Sub


Private Sub cmdLOGIN_Click()

'Check to see if data is entered into the UserName combo box
    If IsNull(Me.txtUserNm) Or Me.txtUserNm = "" Then
      MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
      Me.txtUserNm.SetFocus
        Exit Sub
    End If

    'Check to see if data is entered into the password box

    If IsNull(Me.txtPWD) Or Me.txtPWD = "" Then
      MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
        Me.txtPWD.SetFocus
        Exit Sub
    End If

'Enable certain options on the main menu screen depending on access level
  DoCmd.OpenForm "frmMainMenu"
  If Me.txtPWD Like "SU*" Then
        Forms!frmMainMenu!optMaintenance.Enabled = False
    Else
        If Me.txtPWD Like "US*" Then
            Forms!frmMainMenu!optReports.Enabled = False
            Forms!frmMainMenu!optSuspCase.Enabled = False
            Forms!frmMainMenu!optMaintenance.Enabled = False
        End If
    End If

TIA

Regards,

jdraw · Jun 23, 2017

You may get some ideas from this free youtube video by Steve Bishop

Good luck

isladogs · Jun 23, 2017

I'd also recommend the video listed by jdraw - one of many by him.
There are also many other useful examples online

With regard to your code the first & most important thing is to add decent error trapping to each procedure.
For example with error trapping code added in RED:

Code:

Private Sub txtUserNm_BeforeUpdate(Cancel As Integer)
'Check To See If User Are Valid. Look Into The Table To Get User Status

[COLOR="Red"]On Error GoTo Err_Handler[/COLOR]

Dim strUser As String
Dim strStatus As String
Dim strSQL As String
Dim db As DAO.Database
Dim rstStatus As DAO.Recordset

Set db = OpenDatabase("C:\DB folder path")
Set rstStatus = db.OpenRecordset("tblLOGIN")
strUser = Me.txtUserNm
rstStatus.Index = "USERNM"
rstStatus.Seek "=", strUser

If rstStatus!STATUS = "A" Then
    strStatus = rstStatus!STATUS
    Me.txtStatus = strStatus
End If

    If Me.txtStatus = "A" Then
        Me.txtPWD.Enabled = True
     '  Me.txtPWD.SetFocus
        Me.txtValidUser = "Y"
    Else
        MsgBox "Invalid User Name Try Again" & vbCrLf & "          GOOD-BYE", vbCritical + vbOKOnly, "TRY AGAIN LATER"
        Me.txtValidUser = "N"
        Me.txtPWD.Enabled = False
        Me.cmdLOGIN.Enabled = False
        DoCmd.Quit

    End If

[COLOR="red"]Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " in txtUserNm_BeforeUpdate procedure : " & Err.Description, vbCritical, "Program error"
    Resume Exit_Handler[/COLOR]

End Sub

Do this for ALL procedures - not just the 2 you listed

Next run the login form & see where the errors occur so you can fix them.
If you need to do it step by step you can add breakpoints to the code or step into the code using the VBE Debug menu

Hopefully you will able to fix error 3021 and 94 by doing this.

One way of fixing the null error is to use the Nz function e.g.

Code:

If Nz(Me.txtUserNm,"")="" Then
      MsgBox "You MUST enter a user name",vbCritical, "Login error"
      Exit Sub
End If

However if its impossible to fix an error & if the program would run correctly by 'ignoring ' it - you can do something like this in the Err_Handler section

Code:

Err_Handler:
    If Err.Number = 3078 Then 'no connection
        MsgBox "Error " & Err.Number & " : " & _
            "You are not currently connected to a datafile." & vbNewLine & vbNewLine & _
            "Please contact the program administrator" & vbNewLine & _
            "The program will now close", vbCritical, "Required database connection missing"
            Application.Quit
    Else
         MsgBox "Error " & Err.Number & " in txtUserNm_BeforeUpdate procedure : " & Err.Description, vbCritical, "Program error"
        Resume Exit_Handler
    End If
    
End Sub

Or possibly just tell the code to 'bypass the error' e.g.

Code:

[CODE]Err_Handler:
    If Err.Number = 3021 Then Resume Next

However be careful you know what you are doing if you choose to ignore errors

Hopefully the error trapping will also help you understand why the login button and password text are disabled

One other suggestion:
Give users 3 attempts to enter a correct user name / password combination & if they fail to do so, close the program

Use something like this:

Code:

  'Show error messages if incorrect password entered
        'If User enters incorrect password 3 times database will shutdown
        Dim intLogOnattempts As Integer
        intLogonAttempts = intLogonAttempts + 1
        If intLogonAttempts = 1 Then
            MsgBox "Incorrect Password.  Please Try Again", vbOKOnly, "Incorrect Password!"
            Me.txtPassword.SetFocus
        End If
            
        If intLogonAttempts = 2 Then
            MsgBox "You have entered an incorrect password twice. You have one more chance before the database closes.", vbCritical, "Final chance!"
        End If
        
        If intLogonAttempts = 3 Then
            MsgBox "You have entered an incorrect password 3 times. This database will now close.", vbCritical, "Restricted Access!"
            'v443w - Close the hidden form frmLogoutTimer so that it logs that we have exited
            DoCmd.Close acForm, "frmLogoutTimer"
            Application.Quit
        End If

As the login form is users first experience of a program, it needs to run absolutely smoothly,.
So the time invested in getting it right will be worthwhile

Good luck

Danny · Jun 26, 2017

Thank you both for the reply post along with very helpful links.
ridders: I tried your modified code w/ error trapping and tested the login form and I got:
Compiler error: Sub or Function not defined (screenshot attached)

The error occurs even if the username is valid etc. and the only way to quit the db is via Ctr + Alt + Del

TIA

Regards,

isladogs · Jun 26, 2017

Hi

Apologies - I used a special FormattedMsgBox function which I use to add bold text in a message box.

I've just edited the original post to replace it with a standard message box

The replacement code is:

Code:

Err_Handler:
MsgBox "Error " & Err.Number & " in txtUserNm_BeforeUpdate procedure :             " & Err.Description, vbCritical, "Program error"
    Resume Exit_Handler

End Sub

Danny · Jun 27, 2017

Thanks Colin,

I do not get the error now. But, is it possible to enhance the syntax just a bit?

Please see my comments along w/ screenshot summary of my test result in the attached document.

TIA,

isladogs · Jun 27, 2017

Referring to the 3 points in your Word doc:

1.

I would like the error to read: “Invalid User Name, Please enter a valid User Name’
After 2 or 3 attempts to exit the db

No problem - just edit the error handler:

Code:

If err=3021 Then
   MsgBox "Invalid User Name" & vbNewLine & _
"Please enter a valid User Name", vbExclamation, "User name incorrect"
Else
... other error handling code goes here

I already gave you code to force exit after 3 incorrect entries

2. You say you like that message ....!

3a/b -it doesn't get disabled in my form (or others you'll find online)
In other words, the cause is in your own code - in fact you mentioned it in post 1.
Step through your code to find out why

Danny · Jun 29, 2017

Hi,

Many thanks, it’s working as planned now...

Regards,

login form (1 Viewer)

Danny

Registered User.

jdraw

Super Moderator

isladogs

MVP / VIP

Danny

Registered User.

Attachments

isladogs

MVP / VIP

Danny

Registered User.

Attachments

isladogs

MVP / VIP

Danny

Registered User.

Similar threads

Users who are viewing this thread