Dlookup issue

[Kaybaj]

please i have an issue with my dlookup, i want my dlookup to run through a table and check if a name exist in the table before carrying out and action, but it seems to be working on one name at a time and it won't work on another name except i remove the first name from the table, then another name will work.

If Forms!PASSWORDS!Text43.Value = DLookup("USERNAME", "AUTHORISATION") Then
DoCmd.SetWarnings True
DoCmd.RunSQL thequery
DoCmd.SetWarnings False
DoCmd.RunSQL thequery2
Forms![PAYTEMP2].Requery
Else
MsgBox "You have no permission for this", vbOKOnly, _
"mindBASE!"
End If

 

[Insane_ai]

strUsername = Nz(Dlookup ("USERNAME","AUTHORISATION","AUTHORISATION.USERNAME= " & Forms!PASSWORDS.[Text43]),"NoAuth")

If strUserName = "NoAuth" then
msgbox "You have no permission for this"
else
'do stuff
End if

Th nz function looks for spaces, nulls and otherwise blank data and enables you to program an alternative return value. This is useful if the username does not exist so you have somehting to work with.
Your dlookup did not have any criteria listed so it just pulls the first value it finds.

There are a few posts related to Dlookup and other Domain Aggregate functions you can review to get a better understanding of how the function works.

 

[Kaybaj]

Thanks for that, anytime a user logs in the username is displayed in Forms!PASSWORDS!Text43.Value, and i want the username to be checked if it exists in the Authorisation table before allowing the user to do a certain thing. thanks and i appreciate your response.

 

[John Big Booty]

...

Th nz function looks for spaces, nulls and otherwise blank data and enables you to program an alternative return value.

...

Not quiet correct the Nz() function will only fire for a Null value it will not fire for a ZLS (Zero Length String) or a space, in fact it's default is to return a ZLS if the parameter is omitted.