usrname + passwd verification (match to a tbl)

DRathbone · Feb 24, 2007

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 · Feb 24, 2007

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 · Feb 25, 2007

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 · Feb 25, 2007

it seems to be returning
"Invalid use of Null"

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

boblarson · Feb 25, 2007

Your DLookup syntax is a bit off. Change

Code:

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

to

Code:

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

DRathbone · Feb 25, 2007

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 · Feb 25, 2007

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

DRathbone · Feb 25, 2007

Great! thanks Bob

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

thanks again

boblarson · Feb 25, 2007

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

NZ([YourFieldNameHere],"WhateverYouWantIfNullHere")

and for numbers

NZ([YourFieldNameHere, WhateverYouWantIfNullHere)

DRathbone · Feb 25, 2007

thanks

bobs signature said:
(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 · Feb 25, 2007

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 · Mar 22, 2007

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 · Mar 22, 2007

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 · Mar 22, 2007

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 · Mar 22, 2007

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

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

DRathbone · Mar 22, 2007

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 · Mar 22, 2007

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 · Mar 22, 2007

Not a problem, thanks Bob - give me 2 minutes

DRathbone · Mar 22, 2007

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

boblarson · Mar 22, 2007

Okay, this was an easy fix.

You had:

Code:

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

Change it to

Code:

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.

usrname + passwd verification (match to a tbl)

Registered User.

Wino Moderator

Registered User.

Registered User.

Smeghead

Registered User.

Smeghead

Registered User.

Smeghead

Registered User.

AWF VIP

Registered User.

Smeghead

Registered User.

Smeghead

Registered User.

Smeghead

Registered User.

Registered User.

Attachments

Smeghead

Similar threads

Users who are viewing this thread