Using DLookup for a Login Form? (1 Viewer)

[colklink]

Hey yall,

Quite stumped and need a pick me up. My expertise is building but still somewhat limited...but I AM LEARNING!

I have a query, "QRYLOGINEXT" and my login form "FRMLOGIN" and my login button "cmdLogin".

In my query i only have two fields, "Login Name" and "Password".
In my Login form, my two boxes are "cboUser" and "txtPassword".

After that I'm lost. Here is what I have for my "On Click" event...

Private Sub Login_Click()

If IsNull(cboUser) Then
MsgBox "Please type in a valid User Name", vbOKOnly, "Invalid Entry"
End If

If IsNull(txtPassword) Then
MsgBox "Please type in a valid Password", vbOKOnly, "Invalid Entry"
End If

cboUser = Nz(DLookup("Login Name", "QRYLOGINEXT", "Login Name= '" & cboUser & "'"), "")
txtPassword = Nz(DLookup("Password", "QRYLOGINEXT", "Password= '" & txtPassword & "'"), "")

'Validate the User Name and Password exists in the Tbl_Representatives

If UserNamePassword = (cboUser & txtPassword) Then
DoCmd.OpenForm "FrmHome"
DoCmd.Close acForm, "FrmLogin"
Else
MsgBox "Invalid User Name/Password. Please try again.", vbOKOnly, "Invalid Entry"
End If
End Sub


Everytime I click on my Login button, I get an error message. I try to fix what it highlights and whatever I do it doesnt change anything. Any bit of advice can certainly help. Thanks guys.:banghead:

 

[jzwp22]

Is the login form an unbound form? I assume that it is.

I assume that since your control name is cboUser, that the user selects their user name from the combo box list of only valid users. I further assume that the bound field of this combo box is the [Login Name]. (It is generally not recommended to have spaces or special characters in your table or field names otherwise you have to enclose the field or table name in square brackets).

Once a user selects their name, I assume that you want them to enter their password. You will then need to verify the password they entered to that stored in the table for the particular user via the query you mentioned.


As to your code, I'm not sure why you need this statement since the user is selecting their user name from the combo box. You would not need to look it up.

cboUser = Nz(DLookup("Login Name", "QRYLOGINEXT", "Login Name= '" & cboUser & "'")

As to validating the password, you are looking up the password and assigning to the control. You should be putting the looked up password in another variable and comparing it to that which the user entered in the form control. The code should look more like this:

Private Sub Login_Click()
'define a variable to hold the looked up password for the user
Dim pswdlookup as string

If IsNull(cboUser) Then
MsgBox "Please type in a valid User Name", vbOKOnly, "Invalid Entry"
me.cboUser.setfocus
End If

IF IsNull(txtPassword) Then
  MsgBox "Please type in a valid Password", vbOKOnly, "Invalid Entry"
  me.txtPassword.setfocus

ELSE

pswdlookup = DLookup("Password", "QRYLOGINEXT", "[Login Name]= '" & cboUser & "'")

'Validate the User Name and Password exists in the Tbl_Representatives

  IF pswdlookup<>me.txtPassword THEN
      MsgBox "Invalid User Name/Password. Please try again.", vbOKOnly, "Invalid Entry"
      me.txtPassword.Setfocus

  ELSE
    DoCmd.OpenForm "FrmHome"
    DoCmd.Close acForm, "FrmLogin"  
  END IF
END IF

End Sub

 

[colklink]

THANK YOU!! Works like a charm and was easy like Sunday mornin!

I didnt know about the whole brackets when having spaces or special characters. Thanks for the info. Like I said, I learn something new almost everyday as Im working on this database.

Again, much appreciated!

 

[jzwp22]

You're welcome.

 

[colklink]

I hope this will be one of my last posts. This is getting me more aggravated than a redneck in a spelling bee....

I used your advice and like I said works like a charm. Now Im running into a problem after my user would log in. After I copied and pasted your advice, I deleted the close form code and replaced with:

Me.Form.Visible = False

to hide the Login form and but still let code run to grab information (the current user logged in) to show "Welcome, John Smith") on my Home Page (frmHome). But my problem is inside the text box where the current user should be has "#Error.

The code I have for the Control Source for the 'Current User' in the home page after the successful login or as I have it 'txtCurrentUser' is:

=DLookUp("[Login Name]","QRYLOGINEXT","[Login ID]=" & [Forms]![frmlogin]![cboUser])

Is the problem in my Control Source for the 'Current User' text box or is there something else Im missing? I hope my question and problems are clear. Thanks for any additional advice.

 

[pr2-eugin]

colklink, is your ComboBox returning a String or Number? The DLookUp you have suggests that you are returning a number... but the error says a string.. try..

=DLookUp("[Login Name]","QRYLOGINEXT","[Login ID]='" & [Forms]![frmlogin]![cboUser] & "'")

 

[colklink]

I will try when I get to my computer. And ill let you know the details. Much appreciated.

 

[jzwp22]

Since you left the login form open, you can reference the user combo box when you open frmHome, assuming that the user combo box's bound field holds the name you want to show on frmHome. See red text in the code below

Private Sub Login_Click()
'define a variable to hold the looked up password for the user
Dim pswdlookup as string

If IsNull(cboUser) Then
MsgBox "Please type in a valid User Name", vbOKOnly, "Invalid Entry"
me.cboUser.setfocus
End If

IF IsNull(txtPassword) Then
  MsgBox "Please type in a valid Password", vbOKOnly, "Invalid Entry"
  me.txtPassword.setfocus

ELSE

pswdlookup = DLookup("Password", "QRYLOGINEXT", "[Login Name]= '" & cboUser & "'")

'Validate the User Name and Password exists in the Tbl_Representatives

  IF pswdlookup<>me.txtPassword THEN
      MsgBox "Invalid User Name/Password. Please try again.", vbOKOnly, "Invalid Entry"
      me.txtPassword.Setfocus

  ELSE
    DoCmd.OpenForm "FrmHome"
    [COLOR="Red"]forms!frmHome!controlname=me.cboUser
    me.visible=false [/COLOR]
  END IF
END IF

End Sub

 

[colklink]

Alrighty fellas...I'm back and bare with me here. I took both of your inputs and heres what Im getting...

Again, Im no pro or even intermediate, but no novice at access. My question to JZWP22 is: I assume the purpose of the "pswdlookup" is to verify the password entered by the user. Where do I need to put it? Do I need to put it anywhere?

What does "Dim" mean and how/why is it used?

I also assume that the "Control Name" of the following code:
forms!frmHome!controlname=me.cboUser
Should be the name of the text box I use to show the current user after login (txtCurrentUser).

After I login, my home page pops up like it should, but an error message pops up
"Run -time error 2448. You can't assign a value to his object." and when I click on "Debug" it highlights in yellow "forms!frmHome!txtCurrentUser=me.cboUser" Why is it doing that?

 

[colklink]

Also, my combo box for the available users is unbound. The row source is qryloginext; row source type is table/query; and bound column 1

 

[jzwp22]

What does "Dim" mean and how/why is it used?

Dim (dimension) is the VBA statement used to define a variable: its name and datatype. You can put in anywhere in the code prior to when you use the variable. I generally put it at the beginning of the code.

I also assume that the "Control Name" of the following code:
forms!frmHome!controlname=me.cboUser
Should be the name of the text box I use to show the current user after login (txtCurrentUser).

That is correct.



I generally use variables so I do not have to repeat the DLookup() function since invoking the DLookup() multiple times to get the same information is inefficient.


I do see a couple of other issues with your code that I missed earlier. See red text below. The Exit Sub command does just that, exits the program and transfers control back to the form.

Private Sub Login_Click()
'define a variable to hold the looked up password for the user
Dim pswdlookup as string

If IsNull(cboUser) Then
MsgBox "Please type in a valid User Name", vbOKOnly, "Invalid Entry"
me.cboUser.setfocus
End If

IF IsNull(txtPassword) Then
  MsgBox "Please type in a valid Password", vbOKOnly, "Invalid Entry"
  me.txtPassword.setfocus
  [COLOR="Red"]Exit Sub[/COLOR]

ELSE

pswdlookup = DLookup("Password", "QRYLOGINEXT", "[Login Name]= '" & cboUser & "'")

'Validate the User Name and Password exists in the Tbl_Representatives

  IF pswdlookup<>me.txtPassword THEN
      MsgBox "Invalid User Name/Password. Please try again.", vbOKOnly, "Invalid Entry"
      me.txtPassword.Setfocus
      [COLOR="red"]Exit Sub[/COLOR]

  ELSE
    DoCmd.OpenForm "FrmHome"
    forms!frmHome!txtCurrentUser=me.cboUser
    me.visible=false 
  END IF
END IF

End Sub

After I login, my home page pops up like it should, but an error message pops up
"Run -time error 2448. You can't assign a value to his object." and when I click on "Debug" it highlights in yellow "forms!frmHome!txtCurrentUser=me.cboUser" Why is it doing that?

I'm not sure why it is doing that but we could use a different approach. Let's start by removing this line from your code: forms!frmHome!txtCurrentUser=me.cboUser

Now, in the on open event of frmHome, you can use the following code to assign the value in cboUser to your textbox

me.txtCurrentUser=forms!frmLogin!cboUser

 

[colklink]

I will try it all. Ill get back to you when done and see where we're at....again thanks for helping. Id give you a good hearty handshake and offer a beer or a few... Im more of a Excel person and just basically starting in Acces. Made a few very minor and simple databases that required little effort on my part.

 

[jzwp22]

Let us know how it works out.

 

[anishkgt]

Hi guys,

I've got a similar problem. since I use macros am not able copy paste the code but here is the code I use to validate

DLookUp("Password","tblEmployee","[Password]='" & [txtPassword] & "'")

The above code is placed in cmdSubmit as a click event. The above validates as needed but user1 is able to login with user2 password and vice versa.

I have a table tblEmployee where EmployeeName and Password is stored. Then I have form with cmbEmployee (a combo box with list of Employees from tblEmployee), a txtPassword field (where the user types his Password from the table tblEmployee) and finally submit button named as cmdSubmit

 

[pr2-eugin]

Hi guys,

I've got a similar problem.

You need to redesign your DLookUp. Since you are using the UserName to identify the user and use the password for that particular user needs to be looked into.. Change this..

DLookUp("Password", "tblEmployee", "[EmployeeName] = '" & [cmbEmployee] & "'")