Security using passwords in a table

[cxi]

Could anyone given me some (relatively) simple code that would compare a password on a form with a password in a table and if correct allow another form to be opened?

 

[Malcy]

Hi
Here is simple code I use. It seems to provide enough security and logging for most of my applications but could probably be improved upon

    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim stDocName As String
    Dim stLinkCriteria As String
    
'Check to see if data is entered into the UserName combo box
    If IsNull(Me.cboStaff) Or Me.cboStaff = "" Then
            MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
            Me.cboStaff.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

    Set cnn = CurrentProject.Connection
    Set rst = New ADODB.Recordset
        
'Check value of password in tblEmployees to see if this matches value chosen in combo box
    If Me.txtPassword.Value = DLookup("strPassword", "tblStaff", "[lngStaff]=" & Me.cboStaff.Value) Then

' Record the ID of the person logging onto the system
        rst.Open "tblStaffLogon", cnn, adOpenDynamic, adLockOptimistic
            With rst
                .AddNew
                    .Fields("lngStaff") = Me.cboStaff
                    .Update
                .Close
            End With
            
' Log the event in the database log
        rst.Open "tblDbLog", cnn, adOpenDynamic, adLockOptimistic
            With rst
                .AddNew
                    .Fields("lngLogOp") = Me.cboStaff
                    .Fields("dtmLogD") = Now()
                    .Fields("lngLogAct") = 1
                    .Update
                .Close
            End With
                    
'Close logon form and open data entry screen
        stDocName = "frmIp1"
            DoCmd.Close acForm, stDocName, acSaveNo
        stDocName = "frmLogon"
            DoCmd.Close acForm, stDocName, acSaveNo
        
        stDocName = "frmControlPanel"
            DoCmd.OpenForm stDocName, , , stLinkCriteria

        Else
        
        rst.Open "tblDbLog", cnn, adOpenDynamic, adLockOptimistic
            With rst
                .AddNew
                    .Fields("lngLogOp") = Me.cboStaff
                    .Fields("dtmLogD") = Now()
                    .Fields("lngLogAct") = 3
                    .Update
                .Close
            End With
                    
        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
    
        rst.Open "tblDbLog", cnn, adOpenDynamic, adLockOptimistic
            With rst
                .AddNew
                    .Fields("lngLogOp") = Me.cboStaff
                    .Fields("dtmLogD") = Now()
                    .Fields("lngLogAct") = 4
                    .Update
                .Close
            End With
                    
        MsgBox "You do not have access to this database.  Please contact your system administrator.", vbCritical, "Restricted Access!"
        Application.Quit
    End If
    
    Set rst = Nothing
    Set cnn = Nothing

Hope it helps
Good luck

 

[cxi]

Security

When I put in the following code:

If Me.txtPassword.Value = DLookup("strpassword", "tblnames", "username=" & Me.cmbUserName.Value) Then


it states that I have canceled the previous operation.

Any ideas?

 

[Malcy]

My code refers to specific tables and field names. You will either have to re-create in your db or modify the references to suit your db.
Does that help?

 

[cxi]

Security

I have done this.

txtpassword is a text box on my form
password is a field in my table
tblnames is the name of my table
username is a field in my table
cmbusername is a combo box on my form

It looks as if it should work but it doesn't!

 

[Malcy]

OK
Pehaps worth commenting out the lines of code under the sections that record the user and log the event since you probably don't have these tables set up - simply put a ' at the beginning of each line
If still a problem then put a breakpoint on the "If then" line and work through the code using F8. That will tell you the line that is falling over.
Correction may be obvious (I find it usually isn't) but if not come back.

Actually, I wonder if your combo box value is numeric? If not, then need to adapt the SQL statement since it is set for numbers not text value
Shout if needed

 

[cxi]

Still no joy.

I have re-set the combo box to a number and when it gets to that line of code it still states that I have cancelled the previous opertation.

Am I right in thinking that the "strpassword" is looking through the password field in the table to see if any match what is in the text box on the form, or have I got that wrong?

I did not put the code in regarding the ID of the person logging onto the system or any code after that. I have only put in to check if combo box and text box are empty (and that works) and to check value of password in the table to see if it matches value in the combo box.

 

[Malcy]

Hi
What the DLookup is doing is seeing if the password entered on your form matches the password recorded for the user (also named on the form).
i.e. password on form = password on database where user on form = user on record.
Can you post the actual code you have put up so I can see?

 

[cxi]

Code

The code is:

Private Sub cmdOpen3_Click()
' Dim cnn As ADODB.Connection
' Dim rst As ADODB.Recordset

'Check to see if data is entered into the UserName combo box
If IsNull(Me.cboUserName) Or Me.cboUserName = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
Me.cboUserName.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

' Set cnn = CurrentProject.Connection
' Set rst = New ADODB.Recordset

If Me.txtPassword.Value = DLookup("strpassword", "tblnames", "[username]=" & Me.cboUserName.Value) Then
DoCmd.OpenForm "frmEnter"
Else
MsgBox ("Incorrect password")
DoCmd.Close
End If
End Sub

 

[john471]

PMFJI
Is your problem still current - a quick skim of your posts revealed the following :

When I put in the following code:

If Me.txtPassword.Value = DLookup("strpassword", "tblnames", "username=" & Me.cmbUserName.Value) Then

txtpassword is a text box on my form
password is a field in my table
tblnames is the name of my table
username is a field in my table
cmbusername is a combo box on my form

Also - if username is a field of type text, you will need to enclose it in quotes - best to use doublequotes if there is any chance the field may contain an apostrophe e.g username = "O'Malley"
therefore
DLookup("password", "tblnames", "username=" & """" & Me.cmbUserName.Value & """")

Also beware that if the username is not actually in the table, the Dlookup will return Null, which may cause further problems. You can prevent this by using the nz() function which will, by default, change a null to a zero length string (""). Therefore...

if nz(Me.txtPassword.Value) = nz(DLookup("password", "tblnames", "username=" & Me.cmbUserName.Value))

HTH

Regards

John