Dlookup function is not working 1

[ChrisHaynes]

Hi. I have a register form, which is unbound. When I click the command button to create a new user, it is supposed to use the Dlookup function to check that there aren't any records including the first name and surname in the User table.

However, this doesn't work and the form still add's the record to the table. I have set the field 'username' to the primary key and no duplicates but the form still add the record but just doesn' add a duplicate to the table.

The code I am using is shown below:

If DLookup("[First Name]", "tblUsers") = Me![First Name] And DLookup("[Surname]", "tblUsers") = Me![Surname] Then
MsgBox "You Are Already A User", vbOKOnly & vbCritical, "User Already Exists"
DoCmd.Close

Can anyone help?

Thanks,
Chris.

 

[IanAble]

DLookup function are of the form:
DLookup(expr, domain[, criteria])
If you don't specify criteria they will return random values of expr in the domain.

You should be using the criteria to look for a specific field value. If no matching records are found in the domain (tblUsers) is not found a DLookUp function will return Null. So, the code should be:

If Not IsNull(DLookup("[First Name]", "tblUsers","[First Name]='" & Me![First Name] & "'")) And Not IsNull(DLookup("[Surname]", "tblUsers", "[Surname]='" & Me![Surname] & "'")) Then
MsgBox "You Are Already A User", vbOKOnly & vbCritical, "User Already Exists"
DoCmd.Close

 

[PHV]

you may try this:[tt]
If DCount("*", "tblUsers", "[First Name]='" & Me![First Name] & "' AND Surname='" & Me!Surname & "'") > 0 Then[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ChrisHaynes]

Thanks for your help IanAble. That works perfect!

Cheers,
Chris.

 

[IanAble]

That was elegant PHV.