Dlookup - Using two form fields (number & drop down text) to query table for data

[samh4567]

Need help with dlookup function. (this is for a stamp collection database).

On my form ("InventoryInput") I have a text box called "Catalog" for a numeric entry and a text combo box for selecting a "Country" in drop down list.

I want to query a table called "CatNameList" to get the "StampName" of the item (based on the entries of Catalog and Country) and populate that name in the text box. The fields in this table are called "StampName", "CatNumb" & "CName" respectively.

I have successfully placed the following expression in the control source of that textbox and able to populate the StampName I need based solely on the catalog number alone.
That express is :
=DLookUp("StampName", "CatNameList", "CatNumb = Form![Catalog]")
So it will populate the "StampName" data to match the "Catalog" number entry just fine.

However, I need to add a second layer to incorporate the Country.
Example : There is a catalog "1" for "USA", and a catalog "1" for "Canada" but both have different "StampName".

I have been attempting to get that second piece added with no success.
Here is the expression I have been trying to get to work :
=DLookUp("StampName", "CatNameList", "[CatNumb] = " & [Catalog] & " And CName = '" & [Country] & "'")

Right now, the text box is just blank with the above expression. I thought it may be because there was no match found, but I have triple checked to ensure I have the spelling correct on the country name in both places.

Basically, I just need the dlookup to take the "catalog" and "country" off the form and match it to the "CatNameList" table fields of "CatNumb" and "CName" to give me "StampName" field back on the form.

Please help!! I have been trying for hours. Is there something else other than the expression I need to check ?

Thanks for any help or guidance.

 

[JHB]

Re: Dlookup - Using two form fields (number & drop down text) to query table for data

Is the CName field in the table a normally text box or is it a List-/Combobox?
What is the name of the combobox on the form form where you select the country?

 

[samh4567]

Re: Dlookup - Using two form fields (number & drop down text) to query table for data

Hi JHB,
CName is the name of the field in the table called CatNameList.

The field on the form is called Country, it is a combo list text drop down list from the table called Country.

Form Fields are : Catalog (numeric entry), Country (the combo box), and Title (the field I want the dlookup to put the retrieved data)

The fields on the CatNameList table are CatNumb (numeric), CName (text of country names) and StampName (the text data I am trying to pull over to the form field).

As I was mentioning, I can pull over the StampName data to the form just fine using a dlookup using only the catalog field. The problem begins when I try to incorporate using the Country field data off the forms combo box in conjunction with the catalog data, I just get a blank return. I have checked to ensure the country name in the country table which feeds the country combo box on the form matches the list on the CatNameList table (I just cut / pasted the entries).

Any help or pointers is appreciated.

Thanks!!

 

[JHB]

Re: Dlookup - Using two form fields (number & drop down text) to query table for data

But is the CName field in the table a normally text box or is it a List-/Combobox?
If it is a List-/Combobox it stores an index number and not the actually country name.
To find out what fieldtype it is, open the table in design view, goto the field CName, and look at the field's property.
In the Lookup sheet, what Display control does the field have?

 

[evega6686]

I'm searching for a similar solution except that I have 3 Combo boxes (independentof each other, not cascading) for the user to select from on the form. I want that to be, essentially, the filter for a list box on the Form showing the ID associated with the 3 Combo boxes combination.

Any ideas would be helpful.

Thanks