Syntax issues when using string variables in DLOOKUP function in VBA for Access

0
votes

I am trying to teach myself VBA for Access and am having some issues with the syntax for using text variables in a DLOOKUP function.

I am attempting to loop through a recordset one record at a time to see if two separate text fields from my recordset exist in the same two fields in a separate access table. If they do exist in the table, then I want to create an error message and display a message stating record exists already

Dim str1 As String
Set rst = db.OpenRecordset("temptbl")

Do Until rst.EOF
If Not IsNull(DLookup("Entry_Number" and "HTS_Line", "PO_Payment", 
("Entry_Number= '" & rst!Entry_Number & "'") and ("HTS_Line= '" & 
rst!HTS_Line & "'"))) Then
    str1 = MsgBox("Entry number "" & rst!Entry_Number & "" already exists in 
    the PO_Payment table.  Please investigate.", vbOKOnly)
    Exit Do
    rst.Close
    DoCmd.DeleteObject acTable, "temptbl"

All fields are data type text but I am receiving a code 13 type mismatch error and cannot understand why. Can anyone help please?

1
ms-accessvba
Are both Entry_Number and HTS_Line text columns? Entry_Number sounds an awful lot like a number. – Erik A
Yes, both columns are text columns. The entry number is a field that contains special characters. – Rich Stephan

1 Answers

0
votes
DLookup("Entry_Number" and "HTS_Line", "PO_Payment", ...

The first parameter is invalid. It must be a single string, containing a column name or an expression.

E.g.

DLookup("Entry_Number", "PO_Payment", ...

or

DLookup("[Entry_Number] & ', ' & [HTS_Line]", "PO_Payment", ...