usrname + passwd verification (match to a tbl)

[DRathbone]

Hello

I'm trying to write a procedure on a form that checks If txtUsername = Username from the Users table & txtPassword = Password from Users table, if true then continue to admin form, if false msgbox"Username/Password not found".

I know it would involve some kind of loop but I'm unsure of were to even start, would anyone be able to help me with the syntax.

thanks in advance

 

[pbaldy]

No loop. Searching here should turn up lots of threads, but basically either use a recordset or DLookup to find the password in the table for the user on the form and compare it against the password on the form.

 

[DRathbone]

would something along the following lines suffice?

Private Sub btnlogin_Click()
Dim User as String
Dim pass as String


User= DLookup("username", "Users", "username = '" & forms!login!txtusername & "' and password = '" & forms!login!password & "'")

If User = forms!login!Username Then
DoCmd.OpenForm "Admin", acNormal
DoCmd.Close acForm, "Login", acSaveNo
Else
msgbox"Invalid Username/Password combination, please try again"
End If
End Sub

 

[DRathbone]

it seems to be returning
"Invalid use of Null"

I can confirm theres no null values in the user table, any suggestions?

 

[boblarson]

Your DLookup syntax is a bit off. Change

User= DLookup("username", "Users", "username = '" & forms!login!txtusername & "' and password = '" & forms!login!password & "'")

to

User= DLookup("[username]", "Users", "[username]= '" & forms!login.txtusername & "'")
pass= DLookup("[password]", "Users", "[username]='" & Forms!login.txtusername & "'")

 

[DRathbone]

Thanks Bob!

Working better now, its verifying the users but when the user is not found its returning

run time error '94'
invalid use of Null

 

[boblarson]

use the NZ function
User= NZ(DLookup("[username]", "Users", "[username]= '" & forms!login.txtusername & "'"),"")
pass= NZ(DLookup("[password]", "Users", "[username]='" & Forms!login.txtusername & "'"),"")

 

[DRathbone]

Great! thanks Bob

Out of interest what is the NZ function used for? I've never used it before...

thanks again

 

[boblarson]

NZ replaces nulls with whatever you want, so for Text you can use

NZ([YourFieldNameHere],"WhateverYouWantIfNullHere")

and for numbers

NZ([YourFieldNameHere, WhateverYouWantIfNullHere)

 

[DRathbone]

thanks

(If someone has helped you, please add to their reputation by clicking on the icon above that looks like the scales)

will do thanks again!

 

[WayneRyan]

D,

If you use the DLookUp function (and the user/password is not found), it will
return a Null value. You can use the Nz function, to assign something else
to the string (like an empty string).

You are not trying to retrieve their username/password, only check if it exists.
I prefer to use the DCount function (roughly the same syntax). It will return
either a 0 or a 1 in your case. Much easier to deal with.

Wayne

 

[DRathbone]

Whislt under going testing I have found that this code is only matching a username and password thats the first record in the users table..... how would I loop the dlookup?

 

[boblarson]

A DLookup, if it's working correctly will return the first record that exists that matches the criteria you have set. If you don't include criteria then it will do exactly as you say and return the first record.

 

[DRathbone]

I thought the criteria was part of the DLookup?
ie.
User= NZ(DLookup("[username]", "Users", where "[username]= '" & forms!login.txtusername & "'"),"")

/open users table , look at username field and find a username that matches the value in txtusername???

 

[boblarson]

You don't use the word where in the criteria, just

User= NZ(DLookup("[username]", "Users", "[username]= '" & forms!login.txtusername & "'"),"")

 

[DRathbone]

Sorry Bob I just input the 'where' on that quote to signify where I thought the search citeria was defined.

I use this exact line

user = Nz(DLookup("[username]", "Users", "[username]= '" & Forms!login.txtusername & "'"), "")
pass = Nz(DLookup("[password]", "Users", "[password]='" & Forms!login.txtpassword & "'"), "")

and its only matching againest the first record in the user table

 

[boblarson]

Then something isn't grabbing the form's text box value. The only way I could be sure of what's happening is to see it myself. Any chance you can strip down your database, compact and repair it, zip it and post it here?

 

[DRathbone]

Not a problem, thanks Bob - give me 2 minutes

 

[DRathbone]

As request - thanks for taking your time to look at this

 

[boblarson]

Okay, this was an easy fix.

You had:

If user = Forms!login!Username And pass = Forms!login!Password Then

Change it to

If user = Me.txtusername And pass = Me.txtpassword Then

The reason why it wouldn't work the way you had it is that there is no field named Username or Password in the form as it is unbound. You would need to refer to the control name and not the field. Also, you don't have to use the full form name qualifier if the code is on the same form it is referring to, so you can use Me. and then also intellisense will kick in and you would find the control you want because the other name would not be available in the list.