Dlookup Problem

[Researcher]

Can someone please help me with this syntax issue?

I am using the following code to check usernames and passwords on a form,
and am getting the following error:

Application-Defined or Object-Defined error

My Table Name is tblUsers and the field names are UserName and Password
The form name is: SignIn

Here is my code:

Private Sub SignIn_Click()
Dim User As String
Dim pass As String

User = Nz(DLookup("[UserName]", "tblUsers", "[UserName]= '" & Forms!SignIn.txtusername & "'"), "")
pass = Nz(DLookup("[Password]", "tblUsers", "[Password]= '" & Forms!SignIn.txtusername & "'"), "")


If User = Forms!SignIn!UserName Then
DoCmd.OpenForm "frmSignIn", acNormal
DoCmd.Close acForm, "SignIn", acSaveNo
Else
MsgBox "Invalid Username/Password combination, please try again"
End If
End Sub


Thank You for any help...

 

[spasticus]

im not an expert and its late so i could be wrong..

im pretty sure its the Forms!SignIn... bit - are u lookin at text fields on another form or on the same form as the code you gave?

anyway, i think you should have forms("SignIn").txt... insead of Forms!SignIn
if its on the same form you can just do the name of the text box .. so "[Username]='" & txtusername

 

[boblarson]

im not an expert and its late so i could be wrong..

im pretty sure its the Forms!SignIn... bit - are u lookin at text fields on another form or on the same form as the code you gave?

anyway, i think you should have forms("SignIn").txt... insead of Forms!SignIn
if its on the same form you can just do the name of the text box .. so "[Username]='" & txtusername

No, spasticus you are wrong, the DLookup syntax is correct... sorry

But, my question is why are you trying to use the click event of the form and not of a command button? Also, did you manually type in
Private Sub SignIn_Click()
or did you select the event from the form's properties or VBA code IDE?

You can't just type it in. Access won't let you add your own events, you need to select them and if it is giving you that particular error it makes me believe that you did type it in. Also, you can't have a control with the same name as the form, on that form, and have it work as Access won't know what object you are using.

 

[Researcher]

Dlookup problem

No, spasticus you are wrong, the DLookup syntax is correct... sorry

But, my question is why are you trying to use the click event of the form and not of a command button? Also, did you manually type in
Private Sub SignIn_Click()
or did you select the event from the form's properties or VBA code IDE?

You can't just type it in. Access won't let you add your own events, you need to select them and if it is giving you that particular error it makes me believe that you did type it in. Also, you can't have a control with the same name as the form, on that form, and have it work as Access won't know what object you are using.

You are right Bob, I did type in the Private Sub SignIn_Click() statement, that certainly makes sense, it has to be generated thru an event, Im new to VBA Bob, so still a bit iqnorant with methods/procedures etc. However , I am using the click event from the command button. I aquired pieces here and there of code written by others and have been trying to adapt.

I also have changed the name of the form, the form name is now Login and command button name remains the same "SignIn".

Im going back and review all your suggestions on my form and make the changes as suggested. Thank you very much Bob for pointing me in the right direction, and I"ll keep you posted on the outcome.

Regards

 

[Researcher]

DLookup

Im still getting the original error Bob after making changes. Im not as experienced as I'd like to be, maybe this can help:

The form name is Login

The Table name is tblUsers

The two fields on the login form are username and password

The command button on the form is called SignIn and is derived from the on-click event of the button.

If you could take a look at the MDB file, perhaps you can see the problem I am unable to identify due to experience. I have attached the MDB file.

I was attempting to add password security for this little messenger program I downloaded, and was not comfortable using the security in Access as I thought it was more than I needed.

Thank you for anything you can do...

 

[boblarson]

I keep getting a CRC error when trying to extract it. Can you zip it again and post again?

 

[Researcher]

DLookup

Here is the Zip file again Bob..

 

[boblarson]

Okay, here you go:
User = Nz(DLookup("[username]", "tblUsers", "[username]='" & Me.UserName & "'"), "")
pass = Nz(DLookup("[password]", "tblUsers", "[password]='" & Me.Password & "'"), "")

You had the textboxes named differently (UserName/Password) and not txtUserName/txtPassword, so the code couldn't find the thing. Also, since you are checking the boxes on the form that has the code, you can just use Me.UserName to refer to the text box and don't have to use Forms!Login.UserName

 

[Researcher]

DLookup

Thank You, Thank You Bob!

It works beautifully, I can't thank you enough.

John

 

[Researcher]

Dlookup

Hi Bob,

After using the code you supplied me with, everything works except when I select any user from the combo box and put in any password(s) from the same table, it still authenticates the username. How can that be modified?

Here is my code again:


Thank You Bob for any help....BTW, I added a RecordID field to the tblusers table hoping to have the issuse resolved.



Private Sub SignIn_Click()
Dim User As String
Dim Pass As String
User = Nz(DLookup("[Username]", "tblUsers", "[Username]='" & Me.UserName & "'"), "")
Pass = Nz(DLookup("[Password]", "tblUsers", "[Password]='" & Me.Password & "'"), "")

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.Password) Or Me.Password = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.Password.SetFocus
Exit Sub
End If


If User = Me.UserName And Pass = Me.Password Then
'If Pass = Me.Password Then

DoCmd.OpenForm "frmUserInfo", , , "[Username] = '" & Me.UserName & "'", , acHidden
DoCmd.Close acForm, "frmSignIn", acSaveNo
DoCmd.OpenForm "frmMainMenu"
DoCmd.OpenForm "frmMessageCount"

Exit Sub

Else

MsgBox "Invalid Password. Please Try Again KnuckleHead!", vbOKOnly, "Invalid Entry!"
Me.Password.SetFocus

intLogonAttempts = intLogonAttempts + 1
If intLogonAttempts > 2 Then
MsgBox "You do not have access to this database.", vbCritical, "Restricted Access!"
Application.Quit
End If
End If
End Sub

 

[Researcher]

Dlookup Problem? **Resolved**

Here is the code I substituted to correct the problem of logging in with any username in the table and entering any password from the table that did not belong to the username. P.S. - Bob Larson was instrumental in helping me solve this issue in the beginning. - Thanks Bob :.)

This was added to a command button on click event.

''Private Sub SignIn_Click()
''To create Login Security and message confidentiality

Private Sub SignIn_Click()
On Error GoTo Err_Login_Click
Dim stDocName As String
Dim stLinkCriteria As String
Dim UserName As String
Dim Password As String
Dim validate As String
Dim strname As String

If IsNull(Me.UserName) Then
MsgBox "Empty Username", vbInformation, "No Username"
DoCmd.GoToControl Me.UserName
Else
If IsNull(Me.Password) Then
MsgBox "Empty Password", vbInformation, "No Password"
DoCmd.GoToControl Me.Password
Else
UserName = Me.UserName
Password = Me.Password
validate = DLookup("[Password]", "tblUsers", "[UserName] = '" & UserName & "'")

If validate = Password Then
strname = DLookup("[UserName]", "tblUsers", "[UserName] = '" & UserName & "'")
Me.Visible = False
stDocName = "frmMainMenu"

DoCmd.OpenForm "frmUserInfo", , , "[Username] = '" & Me.UserName & "'", , acHidden
DoCmd.Close acForm, "frmSignIn", acSaveNo
MsgBox "Hello " & strname, vbInformation, "Access Granted"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.OpenForm "frmMessageCount"
Exit Sub
Else
MsgBox " Wrong Password - Please Re-Enter"
End If
End If
End If
Exit_Login_Click:
Exit Sub

Err_Login_Click:
MsgBox Err.Description
Resume Exit_Login_Click
End Sub

 

[pbaldy]

I'm curious what the point of this DLookup is:

strname = DLookup("[UserName]", "tblUsers", "[UserName] = '" & UserName & "'")