DLookup problem

[foxy]

Hi,

I have a DLookup that has multiple criteria in order to check whether a person already exists in the database when a user enter theyre details. The code I have is as follows:

Private Sub cmdSave_Click()
 
Dim SaveResponse As Integer
 
SaveResponse = MsgBox("Are you sure you wish to save this professional record?", vbYesNo, "Save?")
   
If SaveResponse = vbNo Then
End If
If SaveResponse = vbYes Then
 
If Nz(DLookup("lead_prof_ID", "tblLead_Professional", "tblLead_Professional.forename=" & txtforename And "tblLead_Professional.surname=" & txtsurname), 0) <> 0 Then
        MsgBox "This professional already exists in the database. Please search for this professional in order to make ammendments.", vbOKOnly, "Duplicate Person"
        txtForename.SetFocus
        Exit Sub
Else
    DoCmd.RunSQL "INSERT INTO tblLead_Professional (forename, surname, address_line_1, address_line_2, address_line_3, address_line_4, address_line_5, postcode, agency_id, tel, email) " & _
            "VALUES (txtforename, txtsurname, txtadd1, txtadd2, txtadd3, txtadd4, txtadd5, txtpostcode, DLookup(""agency_ID"", ""tblAgency"", ""tblAgency.agency_desc = txtagency""), txttel, txtemail)"
 
MsgBox "Record Saved", vbOKOnly
DoCmd.Close acForm, "frmAddNewProf"
DoCmd.OpenForm "frmMainScreen"
End If
End If
End Sub

When I run this code I get an error message of 'Type Mismatch' and the 'Nz(DLookup(' line is highlighted yellow.

Both forename and surname fields are 'text' data type and both of the text boxes are set to the default format.

Any suggestions?

Cheers

Foxy

 

[DCrake]

If Nz(DLookup("lead_prof_ID", "tblLead_Professional", "forename=[B][COLOR="Red"]'[/COLOR][/B]" & txtforename & "[B][COLOR="red"]'[/COLOR][/B] And surname=[B][COLOR="red"]'[/COLOR][/B]" & txtsurname & "[B][COLOR="red"]'[/COLOR][/B]"), 0) <> 0 Then

Take a look at the changes I have made. As you can see I have removed the table name from the field prefixes (not needed as you have already established which domain you are searching). Also I have encompassed your criteria fileds in single quotes to actually pass the contents of the controls to the look up statement.

What is is actually saying now is

If Nz(DLookup("Lead_Prof_ID","TblLead_Professional","Forename='John' And Surname = 'Doe',0) <> 0 Then

David

 

[foxy]

That works perfectly thanks very much! I've only used a dlookup for an ID number before so didnt even think to put string quotes around it.

Cheers

 

[DCrake]

For future reference

Strings need quotes
Number do not
Dates need #'s


David