VBA DLookup with multiple criteria not functioning

[Brendan Wolf]

Happy Halloween all,

I have been struggling at work with a DLookup using multiple criteria. I would like a text box to display the results of a DLookup based on the values selected in three comboboxes and the table that runs them all. Here is my code:

 

Private Sub Subverbs_GotFocus()
'When the Subverbs gets focus it looks up proper subverbs from Authority_Lookup table
Me.Subverbs = DLookup("[Subverbs]", "AUTHORITY_LOOKUP", "[Sub_Line]= '" & "Me.Employee_LOB.Value &" And "AUTHORITY_LOOKUP", "[Band_name]= '" & "& Me.Change_Title.Value &" And "AUTHORITY_LOOKUP", "[InsideOutside]= '" & "& Me.Clm_Rep_Type.Value &" "'")
End Sub

It works perfect with just the one criteria, but not with two or three more comboboxes as criteria.

The table is AUTHORITY_LOOKU P and the fields are sub_line, band_name, InsideOutside, and I wand me.subverbs to display the Subverbs field result based on the value in the comboboxes.

I would love any help! This is my first post and this looks like a great community.

Thank you so much!

[gnawoncents]

Try:

Me.Subverbs = DLookup("[Subverbs]", "AUTHORITY_LOOKUP", "[Sub_Line] = '" & [Employee_LOB] & _
            "' AND [Band_name] = '" & [Change_Title] & _
            "' AND [InsideOutside] = '" & [Clm_Rep_Type] & "'")

Of course, you will need to filter for valid inputs since you could easily have an invalid combination selected with multiple combo boxes feeding into one DLookup.

[Brendan Wolf]

Thank you so much!

Awesome Awesome! Thank you gnawoncents! It worked like a charm. I will of course have restrictions in place so there is not null combination.

Have a great weekend!

[gnawoncents]

I'm glad it worked for you. You were close, just needed some minor adjustments.