Access VBA Dlookup Run-time Error - I thought I understood Dlookups lol-CodePudding

I am getting the following error when trying to run my code:

My code is this:

    
    Dim ID As Long
    Dim frm As Access.Form
    Set frm = Forms!Frm_JobTicket

    'Look up Customer Name and insert corresponding ID from Qry_CustomerID
    ID = DLookup("Customer_ID", "Qry_CustomerID", "Customer_Name = " & frm("Customer_Name"))
    
    Syteline_Customer_ID = ID

End Sub

I originally was trying to set a form control [Syteline_Customer_ID] = to the Dlookup, but it was giving me this same error, so this was my attempt at working around it. [Syteline_Customer_ID] is a text box on Frm_JobTicket. Qry_CustomerID pulls in 2 fields from Tbl_MasterCustomerList. That table's structure is as follows:

Customer_ID - Number (Integer)

Customer_Name - Short Text

Billing_Address - Short Text

Contact_Person - Short Text

CP_Email - Short Text

CP_Phone - Short Text

I would appreciate the help, honestly I have no idea why I'm getting this error. I have already checked the Qry_CustomerID is not spelled wrong, and is not missing an underscore, that's just my naming convention. I even tried changing it to just Query1 to call it and it didn't work.

CodePudding user response：

Text fields require quote enclosure around literal values. Without quotes, the Access engine assumes you are referencing another field by that literal value. And in your case the space in the name value raised a syntax error. Therefore, in concatenating the form control value, simply enclose it with single quotes:

"Customer_Name = '" & frm("Customer_Name") & "'"

Actually, you do not even need any concatenation as DLookUp can read open form fields with absolute referencing:

"Customer_Name = Forms!Frm_JobTicket!Customer_Name"