String Variable for DLookup Criteria

[LiziM]

Hi all I have learned that the syntax for passing a string variable to dlookup criteria argument is the following:
DLookup("[field-to-return]","table","[criteria-field]=' " & string-variable & " ' ") . I have tried to use this syntax to obtain "maxID" in the code below, however it returns an error: "Compile error: Syntax error". The second line for getting "maxID" does work, so I can use that, but I would prefer to use the input variable passed to the function.

"varProductSerialNumber" is taken from a form control, however, it is saved to the variable, "varProductSerialNumber", before it is passed as an input to the below function- maybe that could contribute to the error?

Public Function CheckScanOut(varProductSerialNumber As String) As Boolean 'receive ProductSerial as input and output=true/false (true if OUT_employee exisits, false if not or
Dim maxID As Long
Dim varOUTEmployee As Variant

'maxID = DMax("[Transaction_ID]", "tbl_Transaction_Master", "[ProductSerialNumber]='"&varProductSerialNumber&"'") **THIS IS THE LINE I'D LIKE TO USE THAT PRODUCES AN ERROR
maxID = DMax("Transaction_ID", "tbl_Transaction_Master", "ProductSerialNumber=[Forms]![frm_scan_IN]![tb_ProductSerialNumber].Value")
varOUTEmployee = (DLookup("[OUT_Employee]", "tbl_Transaction_Master", "[Transaction_ID]=" & maxID))
'Debug.Print (varOUTEmployee)

    If IsNull(varOUTEmployee) = True Then 'if out_employee is a null value return false
    CheckScanOut = False
    Else
    CheckScanOut = True 'elseif out_employee is not a null value return true
    End If
   
End Function

Hope you can help. Let me know if I haven't given enough info!

 

[CJ_London]

no spaces either side of the &'s

'"&varProductSerialNumber&"'"

when you get this sort of error, remove some of the code until you don't get a syntax error, then rebuild

also this code

If IsNull(varOUTEmployee) = True Then 'if out_employee is a null value return false
CheckScanOut = False
Else
CheckScanOut = True 'elseif out_employee is not a null value return true
End If

can just be replaced with
CheckScanOut= not IsNull(varOUTEmployee)

 

[Gasman]

Create a string variable and put the "[ProductSerialNumber]='"&varProductSerialNumber&"'" into that, and use that instead.
Before using it, Debug.Print the variable.
That should show you the error, if you can't see it, post back the results of the debug.print

 

[LiziM]

no spaces either side of the &'s

'"&varProductSerialNumber&"'"

when you get this sort of error, remove some of the code until you don't get a syntax error, then rebuild

also this code

If IsNull(varOUTEmployee) = True Then 'if out_employee is a null value return false
CheckScanOut = False
Else
CheckScanOut = True 'elseif out_employee is not a null value return true
End If

can just be replaced with
CheckScanOut= not IsNull(varOUTEmployee)

Unfortunately still battling with the syntax error (I have no spaces), but that you for that other line of code- so much quicker!

 

[LiziM]

Create a string variable and put the "[ProductSerialNumber]='"&varProductSerialNumber&"'" into that, and use that instead.
Before using it, Debug.Print the variable.
That should show you the error, if you can't see it, post back the results of the debug.print

I can see the erro... Will give this a go anyway, thanks.

 

[CJ_London]

still battling with the syntax error (I have no spaces)

point is you should have spaces

'" & varProductSerialNumber & "'"

 

[LiziM]

So

point is you should have spaces

'" & varProductSerialNumber & "'"

Sorry for my misunderstanding. It works now. Thank you so much for your help!