DLookup with multiple criteria (1 Viewer)

dale_j1992 · Jul 8, 2019

Morning All

i am having some trouble with the following code.

Code:

Private Sub CRANK_1_Change()
Dim Answer As Variant

Answer = DLookup("[Barcode]", "tbllogfurnaces", "[Barcode] = '" & Me.CRANK_1 & "'" And "[FURNACELOADNUM]", "tbllogfurnaces", "[furnaceloadnum] = '" & Me.FURNACENUM & "'")

If IsNull(Answer) Then

    MsgBox "This crankshaft was not in this furnace load" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Incorrect Part"
    
    Cancel = True
    Me.CRANK_1.Undo
    
End Sub

Basically when the user types in a barcode it should lookup to ensure the barcode is logged for this cycle.

i have tried changing it to notisnull and it still has no effect.

Any help would be greatly appreciated.

Kind regards

Gasman · Jul 8, 2019

Syntax for criteria looks wrong to me.
Text is generally surrounded by single quotes and numerics not.?
So what is FurnaceNum?

Build the criteria into a string, then you can Debug.Print it to see if correct.?

My guess from what you have

Code:

Answer = DLookup("[Barcode]", "tbllogfurnaces", "[Barcode] = '" & Me.CRANK_1 & "' And [furnaceloadnum] = '" & Me.FURNACENUM & "'")

Not sure why you are returning barcode for a barcode you are supplying?

https://support.office.com/en-us/article/dlookup-function-8896cb03-e31f-45d1-86db-bed10dca5937

dale_j1992 · Jul 8, 2019

Afternoon Gasman,

Thanks that worked perfectly.

All sorted now, i was just not sure how to lay out the code with multiple criteria.

Kind regards

DLookup with multiple criteria (1 Viewer)

dale_j1992

Registered User.

Gasman

Enthusiastic Amateur

dale_j1992

Registered User.

Similar threads

Users who are viewing this thread