DLookUp Help

[ThaGreenMoose]

Hello,



I have having trouble using the DLookUp function in Access.

I have a form: "frm - Finish Specs"
A subform within this form "qry - Finish Specs - Final Product Chemicals subform1"

Within this subform I have a bound set of values in a text box called "tblMasterChemicals.1_CAS_#".

I would like to take this bound value and lookup a value in a separate table titled "tblHS14722".

It's matching value will be found in the field "CAS#" in table "tblHS14722." When it finds the same value I would like it to return in the text box "Text94" the value in "tblHS14722" in the "Category" field.

Here is what I am typing into the Control Source of the Text98:

=DLookUp("[Category]","tblHS14722","tblMasterChemicals.3_CAS_# = '" & "CAS#") & "'"

Any help would be greatly appreciated. Or if you need more information. I have also attached a screenshot in hopes of clarifying too.

Thanks you.

[Bob Fitz]

You may like to try this:

=Nz(DLookUp("[Category]","tblHS14722","CAS# = '"& tblMasterChemicals.3_CAS_# & "')","")

[boblarson]

You can't check using the table:
"& tblMasterChemicals.3_CAS_# &

you need to reference the control on the form, whatever that is named.

[boblarson]

Looks like it is Text94 (please rename your controls to something meaningful. This is horrible naming and makes it difficult for anyone coming in after you or even you a year down the line to know what is what.)

It would be

=Nz(DLookUp("[Category]","tblHS14722","CAS# = '"& Me.Text94 & "')","")

[ThaGreenMoose]

Bob and Bob,

Sorry for the late response and thank you for your responses. Unfortunately I am now getting the error message "The expression you entered has a function containing the wrong number of arguments." When I plug the statements which you gave into the textbox control source. Any ideas?

Bob L, I will definitely rename my controls to something more meaningful, I was just trying to figure this out first.

Thank you.

[boblarson]

Ah, there was a quote misplaced. It should be:

=Nz(DLookUp("[Category]","tblHS14722","CAS# = '" & Me.Text94 & "'"),"")

[ThaGreenMoose]

Bob,

Now I am getting a #Name? error in the box. However it is letting me input it now. Thanks for the speedy response!

[boblarson]

Oops, I guess we'll need the brackets around the CAS# part as well. This is a good illustration why you shouldn't use spaces or special characters (any of them except underscores) in field or object names.

=Nz(DLookUp("[Category]","tblHS14722","[CAS#] = '" & Me.Text94 & "'"),"")

and if that doesn't work then make sure all of the names are correct, including the field names, table name, and text box name.

[ThaGreenMoose]

I looked over it many times and it everything seems to be spelled correctly. To the point where I went into datasheet view of the tables and copy and pasted the field names and the table and textbox names I am getting from the naviagation column on the left and the property sheet on the right (under Other Tab, "Name" input) respectively.

Is it possible that there would be an issue because the data which is being loaded into Text.94 is being pulled from a Query?

I have attached the database. The form where I am having the trouble is "qry - Finish Specs - Final Product Chemicals subform 1" If you look all the way to the right of this form (the fourth column of data on the form) the first three chemicals on it have this code applied to it and only yield #Name?.

[boblarson]

Okay, I'm a bit confused (also having some sample data would make it easier to connect the dots) as I am not seeing any DLookups, nor do I think you would want them. It looks to me that the controls are bound to fields in the query. So, can you explain a bit more of where I need to look? (if you can provide some data with the sample it would also be helpful. It just needs to be made up data but I don't know what goes where and what you would expect to see so anything you can do to help in that regard would be potentially useful).

[ThaGreenMoose]

Sorry for the confusion.

I have a form in my database called "frm - Paints and Primers" when you open that you see a specification, which chemicals are in in (which is a multivalued lookup field with each selected chemical getting a unique "ChemicalID". The chemicals can either be on the final product or process chemicals. Therefore I made subforms where the users can see what chemicals are found in the final products and which are found in the process. These two subforms are called "subFRM - Paints and Primers Specs - Final Product Chemicals" and "subFRM - Paints and Primers Specs - Process Chemicals."

I have not finished the second subform to include a column for this DLookup command yet. However in the subform titled "subFRM - Paints and Primers Specs - Final Product Chemicals" if you put it in form view it should list 4 records of Paints and Primers which I have uploaded. 44-HN-7 (Part A) and (Part B) and 44-HN-24 (Part A) and (Part B). All of these chemicals have a CAS number associated with it, name, constituent %, and the far right is "Category of HS14722:" where the DLookUp command is being placed.

So in the DLookUp I have is looking up values in the "tblHS14722" table which appears to be named correctly. The "CAS#" and "Category" fields both appear to be spelled correctly. Lastly the text box it calls for is the one on the same line as it on the "subFRM - Paints and Primers Specs - Final Product Chemicals" form.

Sorry I changed some of the names around however now that I am sharing the DB I should've known that the bad naming schemes I am using wouldn't hold up.