How to use DLookup for a String with spaces

[Jeroen3131]

I'm having difficulties with using the DLookup function.

I have a form with two textboxes (txtSAP and txtDP) which are bound to a table (tblParts). In these boxes the user can type the partnumber and the corresponding description.

If I press the Create Button the data is saved to tblParts. Now I want to add a functionality: I want to check if a part already exists using the DLookup function.

This is my code for the On-Click event:

If DLookup("SAPnumber", "tblParts", "SAPnumber = '" & Me.txtSAP & "'") Then
    MsgBox "SAP number already exists!", vbInformation, ""
    Exit Sub
End If
 
If DLookup("PartDescription", "tblParts", "PartDescription = '" & Me.txtDP & "'") Then
    MsgBox "Part description already exists!", vbInformation, ""
    Exit Sub
End If

The first IF works flawlessly. The second IF returns Error 13' : Type Mismatch.

Both Fields "PartDescription" and "SAPnumber" are Text fields. The only difference is that the PartDescription sometimes contains one or multiple spaces. If

It seems that the criteria from the DLookup function ignores the spaces in the String. Is there a solution/workaround for this?

[jforbes]

You can use spaces in a Where Clause for a Dlookup. I would guess that you really are running into a typo. Try adding "Option Explicit" to the top of your code module, then compiling the code.

Also, as a best practice, it's a good idea to use Me.txtDP.Value instead of me.txtDP. You probably wont have trouble with it the way you have, but if you ever get into development with vb.NET vs VBA it will throw an error.

[twinnyfo]

I have found that I will get errors using DLookup when my search criteria includes an apostrophe (').

If the SAPNumber is a text field but holds numeric characters, it may return a numeric value, which will work in the Boolean world of the If...Then construction. However, if your second DLookup is returning a true text value, then it may be causing the Type Mismatch, as the If...Then is looking for a Boolean result.

Also, It seems odd that you are looking up the same field in your table that you already have. Is this just for demonstration purposes or is there a reason for this? Perhaps a more error free method would be:

If Nz(DLookup("SAPnumber", "tblParts", _
    "SAPnumber = '" & Me.txtSAP & "'"), "") <> "" Then
    MsgBox "SAP number already exists!", vbInformation, ""
End If
 
If Nz(DLookup("PartDescription", "tblParts", _
    "PartDescription = '" & Me.txtDP & "'"), "") <> "" Then
    MsgBox "Part description already exists!", vbInformation, ""
End If

Also, note that I removed the Exit Sub lines from Lines 3 & 8, as your second block would never run if the part was already found--BUT, I don't know how your tables are set up, so you may want to have those lines in there.

Hope this hepps!

[Jeroen3131]

To clearify:

The SAPnumber always consist of two letters and 8 numbers. For instance: ZP12345678. But it never contains a space.

If i already have a part with SAPnumber ZP12345678 in my tblParts and I type the same number (ZP12345678) into my form a message will display: SAP number already exists! If it doesn't exist in the tblParts, the new part will be created.

I also want to check if the description already exists in the tblParts. This description can contain spaces, numbers and letters.

The first part of my code works. If I type an existing SAP number into my form, the message "SAP number already exists!" pops up.

My tblParts is contains:

PartID: Primary Key
SAPnumber: Text
PartDescription: Text
PartPicture: Text (link to the image, nut not relevant in this matter).

Twinny with your code everything works! When I press the Create Button both textfields are checked to see if their values already exist in the tblParts. However it still adds the values to tblParts after prompting the messageboxes. This is why I added the Exit Sub again for each If ... Then clause. This way the user has to retype the SAPnumber and/or description.

Jforbes, Twinnyfo Thanks for the fast responses!

[twinnyfo]

Jeroen3131,

Glad we could help! Your explanation of why the Exit Sub was helpful, too. Keep plugin' away and let us know if we can help with anything else.