Login Difficulties

I have created a ms access front end to SQL database.

I have used the below vb to create the user login function, but it is
not working, i believe that this is because the 'password' field in the sql database
is set to the password format, is there some extra code i need to enter to get the
login script to work.

** also i am using a different systems user table (so i don';t have to keep it up to date!
but users can change their password, this seeem to then be stored in several fields Password, Password#2, password#3 etc.
so when i need to authenticate users, i will have to check all these password fields to make sure....

any help would be greatly appreciated...

Private Sub cmdLogin_Click()
If IsNull(Me.username) Or Me.username = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
Me.username.SetFocus
Exit Sub
End If
If IsNull(Me.pword) Or Me.pword = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.pword.SetFocus
Exit Sub
End If
If Me.pword.Value = DLookup("[Password]", "tblUser", "[PayrollNo]=" & Me.username.Value) Then
MyPayrollNo = Me.username.Value
DoCmd.Close acForm, "LogOn", acSaveNo
DoCmd.OpenForm "Welcome"
Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.pword.SetFocus
End If
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

thanks

Hi mezza1,

You said it wasn't working, but didn't indicate where in the code it was failing. One thing I did notice was your DLookup function. Is [PayrollNo] a number or the name of the user? Me.username.Value appears to be a text (users name) and if you are trying to match a number to a text it will obviously fail at this point.

You may want to step through the code and see where it is failing, but I have a hunch it has to do with your DLookup function that has the wrong "Where" criteria or syntax. Keep in mind when using a text in your Where clause, you will need to include the quotes marks. Example:

Dim strUser As String
strUser = username
Me.pword.Value = DLookup("[Password]", "tblUser", "[PayrollNo]='" & strUser & "'")

The above syntax assumes that [PayrollNo] is the column/field in your table "tblUser" that contains the user name.

lwells

Thanks IWells,

the payrollno and the username are the same data type..both are text..so this presumably should work?

I have a feeling it is the password field in sql, when you view this field is shows up as 'TT6UF37XXUHAUCX8Y6>6;64PSAF6VO8@S;TXPYHF7LX0U6IQ@B2;NIC321295694209.. ...etc etc'

when i tried this as a simple login with actual password in a field in the local table is worked, so i think the way the password is stored may be affecting the lookup..

any ideas?

Cheers

Mezza1

Did you place your quotation marks in your DLookup function for text?

lwells

Hi,

yes, i had forgotton that the MyPayRollNo was declared as long in a public module, as soon as i changed this to a string it worked.

Thanks very very much for your help.

Cheers

Mez

Hi Mezza1,

Excellant, how in the world did you find that one? I would have never thought to look there. Is is possible that you could share that portion of your module with me. I would love to make a simulation for debugging purposes. If you don't want to post here, send it to me via my email or if you don't want to disclose this module for security purposes, I would certainly understand. I was just intrigued with this particular problem and how it was solved.

Thanks,
lwells

It was just the following

Option Compare Database

Public MyPayrollNo As String

save in a module (any name). as soon as i changed it to 'As String' it worked!


I have deceideD that the way the other team change/admin the passwords is not great, and so i am hoping to use the 'sysusers' table that SQL server security applies to the database, the only problem is that passwords are stored as binary as ideas on how to get this to work in that situation?


Mez

Hi Mez,

Afraid I can't help you with that one. But I will look around and see if I can find something that might be helpful. Possibly someone else has an answer and will post here for you.

lwells