[SOLVED] DLOOKUP for filling a text box using 2 combo box selections

DLOOKUP for filling a text box using 2 combo box selections

=DLookUp([GTIN],"tblGTIN","VarietyCode = " & [Forms]![frmCaseLabel]![VarietySelected] & "AND PackType =" & [Forms]![frmCaseLabel]![PackInput])

Returns: #Error

=DLookUp([GTIN],"tblGTIN","VarietyCode = " & [Forms]![frmCaseLabel]![VarietySelected].[Text] & "" & "AND PackType =" & [Forms]![frmCaseLabel]![PackInput].[Text] & "")

Returns: #Type!

I am having issues populating a text box, using above DLookUP however I can't find what's wrong. GTIN is a number, varietyselected is a string, packinput is string.

I am sure the same can be achieved with SELECT, which I am willing to use but I can't figure that out.

Solution

There are several things that you are doing wrong here:

If fields are text, then they need to be wrapped in single quotes.
You need to have a space before the AND, otherwise this gets joined into the value that you are looking up;
The .Text property of a control is only available when the control has the focus, and allows you to grab the uncommitted value. Normally you should use the default property, which is .Value (and can be omitted).

Putting this all together, it should look like:

=DLookup("GTIN","tblGTIN","VarietyCode='" & Forms!frmCaseLabel!VarietySelected & "' AND PackType='" & Forms!frmCaseLabel!PackInput & "'")

Regards,