Not figuring out dlookup (RunTime 2465)

[Gilligan]

Ok, trying to do what I think is a simple dlookup to verify a UPC code is in the db before I do an append. If not then I'll add it but I'm not even there yet.

Private Sub theUPC_Enter()
    Dim A, B As Variant
    
    A = theUPC
    B = DLookup("[UPC]", "tblItems", [UPC] = A)
    
    If IsNull(B) = True Then
    MsgBox "Null!"
    Else
    MsgBox "Not Null!"
    End If
End Sub

My form has a text box for UPC... I'd like for this vba code to happen after the scan from my scanner (which ends with an ENTER command). That's not really happening but that's not really the point.

When I run the code as is I get a Runtime error on the dlookup line. I THINK it is related to the field section.

"tblItems" has several fields one is titled "UPC" so I thought this was the right way to do it.



Basically, it checks to see if the UPC code is in the table or not. I also tried DCount with the same results.

I'm just trying to learn how to use Access and I keep hitting brick walls.

[RuralGuy]

All of the arguments for the Domain functions are strings: http://www.mvps.org/access/general/gen0018.htm

[Gilligan]

Ok... so I got it to work with this:

Private Sub theUPC_Enter()
    Dim A, B As Variant
    
    A = theUPC
    B = DLookup("UPC", "tblItems", "UPC = [theUPC]")
    
    If IsNull(B) = True Then
    MsgBox "Null!"
    Else
    MsgBox "Not Null!"
    End If
End Sub

Basically eliminating my variable. Which is fine here but I would like to better understand how I would use the variable instead of explicitly defining what I'm looking for in the criteria area. "UPC =[theUPC]"

[Sciencefair]

Ok... so I got it to work with this:

Which is fine here but I would like to better understand how I would use the variable instead of explicitly defining what I'm looking for in the criteria area. "UPC =[theUPC]"

Use concatonation <sic> B = DLookup("UPC", "tblItems", "UPC = " & A)

[Gilligan]

That's giving me a "type mismatch" but I know that isn't exactly the code's fault.

Not sure what "type" it wants... but at least I know the syntax.

[RuralGuy]

If it is a string then enclose the value with single quotes:
B = DLookup("UPC", "tblItems", "UPC = '" & A & "'")

[Gilligan]

That worked... not sure I completely understand it... but sometimes that's just how things are.

Thanks!

[RuralGuy]

The link I posted shows how to deal with the 3 different types of values.

[Gilligan]

You have extra double quotes... I'm sure that has to do with the concatenation but that isn't covered in that link you posted. Trust me, I tried the variations on that link.

Thanks again!

[RuralGuy]

It is the 2nd item:

Refering to a form control
For numerical values:
        DLookup("FieldName", "TableName", "Criteria = " & forms!FormName!ControlName)
For strings: (note the apostrophe before and after the value)
        DLookup("FieldName", "TableName", "Criteria = '" & forms!FormName!ControlName & "'")
For dates:
        DLookup("FieldName", "TableName", "Criteria = #" & forms!FormName!ControlName & "#")

[Gilligan]

Ah ha! I guess it was the formatting, I think I looked at those single quotes as double quotes.

My bad.