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

[samh4567]

Having problems getting dlookup to work in the control source field of a text box.


My form has fields : Catalog # (numeric value) and Country (drop down text selection).

I would like to query a table CatNameList for a name (text) if the catalog # and country find a match on the table.
My field names on the CatNameList table are : Name, Number (to validate against the Catalog # entered on the form) and CName (to validate against the Country drop down on the form).

I am successfully able to populate the name from the CatNameList table on my form using lookup of the catalog # using this :
=DLookUp("Name","CatNameList","Number = Form![Catalog #]")

However, I will eventually have several catalog numbers that will be identical in the table CatNameList, thus why the country is important as the second criteria to be added into the dlookup.

I have tried for a few hours unsuccessfully to add the second portion to my dlookup. Any help greatly appreciated.

This is what I have currently (not working) that I have been playing with, I'm sure I'm missing a quote mark, & or something simple.

=DLookUp("Name", "CatNameList", “Number = Form![Catalog #] And CName = ‘”& Form![Country] & ”’”)

Thank you !!!!!

[June7]

What does 'not working' mean, what happens - error message, wrong results, nothing?

Why does the expression show two different styles of quote and apostrophe marks? Access won't like the italic versions.

I am surprised the first expression works with the field reference within quote marks especially since it doesn't have the form name. The references are incomplete.

Advise not to use spaces and special characters/punctuation (underscore is exception) in naming convention.

[samh4567]

The text box in the form just says "#Name?" using the second longer express above that I mentioned does not work. I can rename the field for Catalog and take out the "#".

[June7]

Try:

=DLookUp("[Name]", "CatNameList", "[Number] = " & [Catalog] & " And CName = '" & [Country] & "'")


Also should not use reserved words as names. Doing so can cause unexpected results. Name and Number are reserved words.

[samh4567]

Some progress, at least the field is just blank now instead of "#Name?". Do you think I need to reference the Form!Title in the expression ? Thanks for the advice on the name and number titles.

[June7]

Should not need the form name in the references.

This assumes the expression is on the form that has Catalog and Country fields/textboxes.

Blank means no record meets the criteria.

[samh4567]

Thank you for the help so far, but I still cannot populate that text box field with the expression to try. It is just blank (no errors). I have triple checked to ensure that the spelling between my form entry and the table entry are identical.
This does work to populate the name using the catalog field alone : =DLookUp("StampName", "CatNameList", "CatNumb = Form![Catalog]")

This does not seem to work to when incorporating the country name : =DLookUp("StampName", "CatNameList", "[CatNumb] = " & [Catalog] & " And CName = '" & [Country] & "'")

I have attached a photo of what I am looking at to see if that may help.

Thanks all for any help !!

Is there something else that could be contributing that may not be set correctly ? Just a thought....

[June7]

I don't see how the first expression can be working properly.

What data type is the CName field?

DLookup is really the least preferred method to do what you want. Why is Catalog a textbox and not a combobox? Why is country a combobox and not a textbox? What is purpose of this form? What table is it bound to?

If you want to provide db for analysis, follow instructions at bottom of my post.

[samh4567]

The CName field in the table CatNameList is set as Text for data type.

The Catalog field on my form is for entering the catalog number (all numeric) of the item I wish to log as having possession of.

The Country field on my form is a combo box list of country names that feeds off the table called Country. I did this so all my country names would be consistent.

The purpose of the form is to enter records for items that I have in my collection.
This is for a stamp collection, so I have a list of all the countries that ever issues stamps - this is stored in the country table and linked to the form combo box called country.
For example, as I get a stamp, I want to be able to log on the form that I have stamp number 2345 (catalog number field) from country Canada.
I enter 2345 in the catalog field and select Canada from the combo box (country) on the form.
I have a field for the name of the stamp on the form, but instead of typing the name, I want it to lookup or query or something this table called CatNameList where I intend to import data from excel of all the stamp names for each country found on the country table.
The reason I want to do this instead of just typing the name of the stamp is because there are different varieties of the stamp, so I may have 3 of the same stamp but different varieties (which is another combo list selection on my form). I want the names to be consistent just like country name.

The form is bound to a table called Catalog, which has all the fields of my form. So the forms table is called Catalog and I have a field on the form called catalog as well for the number entry of the catalog number of the stamp.


DB Attached

I left the working dlookup expression in the control source for the field "Title" on the form "Inventory Input". This is the one that works pulling the StampName from the table CatNameList using only the catalog field. I need to incorporate the field Country as well with that Dlookup expression to pull the StampName data off the table CatNameList that matches both the catalog and the country fields on the form InventoryInput.

Thanks for any help !!!!!!

Thanks

[Micron]

One thing I have found wonky about DLOOKUP expressions is the use of [ ]'s. I would add them around CName and try that.

[June7]

Can each catalog number be associated with more than one country? More than one stamp name? The example records imply no for both.

[samh4567]

Yes, there could be a catalog "2345" for "United States of America" and a catalog "2345" for Canada. Each country starts with 1 and depending on how many stamps they issued will dictate how high the catalog number will be. I have 3 test one just listed in the table CatNameList to play with right now. That table will be quite large once I'm done placing all the StampNames in there for each catalog number for each country.

OK, I added a text box to my form and titled it "Test". I am using this expression now :
=DLookUp("StampName","CatNameList","[CatNumb] = " & [Catalog] & " And [CName] = '" & [Test] & "'")

AND IT WORKS

So, for some reason, the expression does not like my combo box titled "Country" where I select the country name from the drop down list (reading from table Country).
If I freely type "Canada" in the new "Test" text box, it populates my StampName I need from the table CatNameList.

So what would cause it not to like selecting "Canada" from the drop down list in my combo box and use it to read the table CatNameList ??? But it likes it if I manually type out Canada in my "Test" text box ????

Thoughts appreciated

[Micron]

I got it to work with a combo box on a test form using
DLookup("StampName", "CatNameList", "CatNumb =[Text1] AND CName = '" & Text4 & "'")
If that doesn't help, post the field names and control names. Not sure if you are using Form!Catalog as shown in your picture, but you cannot refer to the control that way. I realize the control references make it look as though they are both textboxes, but I came back to post and saw your note about the combo box so I went back to change the control type but did not change the name.

[samh4567]

Thanks all..... It's working.
Final working expression : =DLookUp("StampName","CatNameList","CatNumb =Catalog AND CName = '" & [Country] & "'")

[June7]

Because the actual value of the combobox is ID. This is the first field in the combobox RowSource and that is the Bound Column. Do you want to save the ID or the actual country name? If you want to save the ID then CName field in CatNameList must be a number type. Right now it is text and holds the actual country name. If you want to save the name then remove ID from the combobox. The ID field then serves no purpose.