User Login - Enable / Disable Account

[karl009]

Hi,

I have been playing around with some Access tutorials on the Internet, am interested in how I would go about changing a User Login to also have enabled and disabled accounts.


Here is the site; http://www.databasedev.co.uk/login.html

On the cmdLogin button I have added the following code;

If (chkAccStatus) = False Then
        MsgBox "Your account is disabled see admin.", vbOKOnly, "Disabled Account"
        Exit Sub
    End If

Here is the main cmdLogin code;

Private Sub cmdLogin_Click()

'Check to see if data is entered into the UserName combo box

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

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

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

    'Check value of password in tblEmployees to see if this
    'matches value chosen in combo box

    If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", _
            "[lngEmpID]=" & Me.cboEmployee.Value) Then

        lngMyEmpID = Me.cboEmployee.Value

        'Close logon form and open splash screen

        DoCmd.Close acForm, "frmLogon", acSaveNo
        DoCmd.OpenForm "frmSplash_Screen"

    Else
      MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
            "Invalid Entry!"
        Me.txtPassword.SetFocus
    End If

    'If User Enters incorrect password 3 times database will shutdown

    intLogonAttempts = intLogonAttempts + 1
    If intLogonAttempts > 3 Then
      MsgBox "You do not have access to this database.Please contact admin.", _
               vbCritical, "Restricted Access!"
        Application.Quit
    End If

End Sub

Also on the table I have added chkAccStatus, which is Yes/No data type.

Thanks for any pointers...

 

[JamesMcS]

What errors are you getting?

 

[karl009]

Hi,

There are no errors, When I login as either a Enable/Disabled user I get the disbaled message, the user is login into the database.

I can only think that am missing something somewhere.

Thanks
Karl

 

[JamesMcS]

Try going through the variables while you're running the form, using "print" in the VBA immediate window, see if any of the values are blank, that might get you started. Apart from not having Dim'd your variables at the start of the code, I can't really see anything wrong right now... but it is early

 

[karl009]

Hi,

I've had a play, the "Print" didnt seem to work too well, so I used a MSGBOX and found that I wasnt getting the data from the table to compare.

After playing aroung this is what I have;

chkAccStatus = DLookup("chkAccStatus", "tbl_System_Users", "lngEmpID=" & lngEmpID)

    If chkAccStatus = False Then
        MsgBox "Your account is disabled see admin.", vbOKOnly, "Disabled Account"
        MsgBox (chkAccStatus)
        Exit Sub
    End If

It only seems to take the data from the first line, when the username is changed in the Combo Box the DLookup dose not update with the data from the other lines in the table.

For testing I have placed on the from a text box in the control source I have put the same DLookup above, and on the Combo Box for the users I have the Me.UserCombo.Requery as an After_Update event, but the value dose not change.

Where am I going wrong.

 

[JamesMcS]

Sorry I should have explained - you can put break points in your code, say at the chkaccstatus line, then when it stops on the break, you'd type "print chkaccstatus" and it would tell you the value in it. But a msgbox is just as good.

So then - if you've established that the dlookup is pulling back a value, that's good. Something else to check, is the combo box's bound column property set to whichever column your lngEmpID is in?

I would put the code above in the afterupdate of the combo box. That way every time the value in the CB changes, it'll rerun the dlookup and repopoulate chkaccstatus.

Also something to bear in mind is you're trying to populate the value of a variable chkaccstatus, with the data from the field chkaccstatus. Try and avoid duplicate names like this, it sometimes causes problems - so just call the variable "EmpIDNumber" or something.

 

[karl009]

Hello,

This is part of the code, I have placed the DLookup in the AfterUpdate, but when it is there when I come to logon and click the button their is no value getting to the IF statement in the cmdLogin_Click.

If I where to place it all in the AfterUpdate it would not work how its ment to as it would sitll allow the user to login after the msgbox had been displayed.
However I did try it out and found that for every user the DLookup will only return the value of the first list in the database.

Why is the DLookup not updating?

First try;

Private Sub cboEmployee_AfterUpdate()
    'After selecting user name set focus to password field
    Me.txtPassword.SetFocus
    chkAccStatus = DLookup("chkAccStatus", "tbl_System_Users", "lngEmpID=" & lngEmpID)
End Sub

Private Sub cmdLogin_Click()

    If chkAccStatus = False Then
        MsgBox "Your account is disabled see admin.", vbOKOnly, "Disabled Account"
        MsgBox (chkAccStatus)
        Exit Sub
    End If

Second try;

Private Sub cboEmployee_AfterUpdate()
    'After selecting user name set focus to password field
    Me.txtPassword.SetFocus
    chkAccStatus = DLookup("chkAccStatus", "tbl_System_Users", "lngEmpID=" & lngEmpID)
    
    If chkAccStatus = False Then
        MsgBox "Your account is disabled see admin.", vbOKOnly, "Disabled Account"
        MsgBox (chkAccStatus)
        Exit Sub
    End If
End Sub

Thanks...

 

[JamesMcS]

Are they in the same module/form? You might have to declare chkaccstatus as a public variable. Populate the variable in the afterupdate of the combo box, and if the variable is public the command button will definitely be able to see it.

Dlookup will only pull back one record - you should only have one lngEmpID per user, no duplicates.

The other thing you could do is to have a field in the user table to say "account disabled yes/no", and have that as a zero width column in the combo box. Then, you could put an if in the command button to say "if combobox.column(x)=yes, msgbox "Account Disabled...."

 

[karl009]

Hi,

Thanks for all your help, after a bit of playing and with your recommendation I have been able to get it to work.

Here is the change to the code;

chkAccStatus = Me.cboEmployee.Column(2)
    
    If chkAccStatus = False Then
        MsgBox "Your account is disabled see admin.", vbOKOnly, "Disabled Account"
        Exit Sub
    End If

Thank You....
Karl

 

[JamesMcS]

Wicked - enjoy the BH weekend if you're in the UK! Lie-ins and barbecues for me.... whatever the weather!

 

[rexmorgan]

Good Morning,
I have taken some of the code you have posted and tried to use it. I am having an error that is related to the DLookUp value. I have a table called Users. There are 3 columns in the Users table; ID, User, & Passcode.
The Login form contains a textbox called txtUserName and another called txtPassword.

Private Sub Login_Click()
    txtUserName.SetFocus
    
    'Check to see if data is entered into UserName textbox
    If IsNull(Me.txtUserName) Or Me.txtUserName = "" Then
        MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
            Me.txtUserName.SetFocus
            Exit Sub
    End If
    
    'Check to see if data is entered into Password textbox
    If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
        MsgBox "You must enter a password.", vbOKOnly, "Required Data"
            Me.txtPassword.SetFocus
            Exit Sub
    End If
    
    'Check value of username textbox in the table Users matches value of password
    'chosen in Password textbox
    If Me.txtPassword.Value = DLookup("Passcode", "Users", "[User]=" & Me.txtUserName.Value) Then
        
        'User = Me.txtUserName.Value
        
        'close logon form and open splash screen
        DoCmd.Close acForm, "Login", acSaveNo
        DoCmd.OpenForm "SplashScreen"
        
    Else
        MsgBox "Password Invalid. Please Try Again!", vbOKOnly, "Invalid Entry!"
            Me.txtUserName.SetFocus
    End If


End Sub

The error i get is
*******************************************************
* Run-time error '2471': *
* *
* The expression you entered as a query parameter produced this error: *
* 'rexmorgan'. *
*******************************************************
Now the 'rexmorgan' error is the txtUserName value that I have entered. Any help, advice, or insight.

 

[JamesMcS]

I'd say first of all, start a new thread.

Next, which line is highlighted in the VBA editor when you get this error message?

The first problem I can see is that you're not surrounding txtusername.value in apostrophes. Try

If Me.txtPassword.Value = DLookup("Passcode", "Users", "[User]='" & Me.txtUserName.Value & "'") Then

Notice the apostrophe after [user]=

 

[rexmorgan]

Thanks for the quick reply! The line that is highlighted is the

If Me.txtPassword.Value = DLookup("Passcode", "Users", "[User]=" & Me.txtUserName.Value) Then

line.

I tried your suggestion and it worked. Just out of curiosity what does the apostrophe do? What I mean is why is the apostrophe necessary? Thanks again for the quick reply.

 

[JamesMcS]

Well, it means that Access treats whatever is inside the quotes as a string (which I presumed user name was). Without the quotes it would treat it as a number and you'd get a problem.

 

[rexmorgan]

Interestingly enough it was not quotes that seem to have made a difference. It was the apostrophe that you suggested. Any clues?

Private Sub Login_Click()
    txtUserName.SetFocus
    
    'Check to see if data is entered into UserName textbox
    If IsNull(Me.txtUserName) Or Me.txtUserName = "" Then
        MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
            Me.txtUserName.SetFocus
            Exit Sub
    End If
    
    'Check to see if data is entered into Password textbox
    If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
        MsgBox "You must enter a password.", vbOKOnly, "Required Data"
            Me.txtPassword.SetFocus
            Exit Sub
    End If
    
    'Check value of username textbox in the table Users matches value of password
    'chosen in Password textbox
    
    If Me.txtPassword.Value = DLookup("Passcode", "Users", "[User]='" & Me.txtUserName.Value & "'") Then
                  
        'close logon form and open splash screen
        DoCmd.Close acForm, "Login", acSaveNo
        DoCmd.OpenForm "SplashScreen"
        
    Else
        MsgBox "Password Invalid. Please Try Again!", vbOKOnly, "Invalid Entry!"
            Me.txtUserName.SetFocus
    End If


End Sub

Thanks for your patience. I am just really learning Access or relearning it anyway. It has been quite some time since I have fooled around with it. I just don't use it enough to be proficient. But I am still puzzled as to what difference the apostrophe makes. I understand the concept of anything inside quotes is treated as a text string. Regardless I greatly appreciate your help in this matter. Cheers!

 

[JamesMcS]

Sorry, you use apostrphes in the place of quotes in an expression like this - the other option is a double quote. Basically if you used a single quote Access would think that's the end of the expression and it wouldn't work