Filling two fields with a single lookup

[cman26]

I am using Access 2010 and I have a table containing a list of chemicals (Analytes) that I am working with and their Chemical Abstract Service number (CAS). I am trying to use this table as a basis for generating other tables that will be used to create quality control criteria for specific analysis methods. As such, I will need both fields populated for each subsequent table. I have been able to successfully create a lookup that will pull the Analyte name, but I want the same lookup to fill the CAS number. How do I make this work?

[June7]

Simplest would be to use another DLookup.

Could open a recordset and pull values from record.

Why do you need both fields populated as opposed to doing a query that joins on the CAS fields? Why are you generating tables as opposed to just queries?

[cman26]

I have been able to put a second lookup in for CAS number, but I need the number to match the Analyte name. There will be hundreds of Analytes and CAS numbers when all is said and done and there is too much opportunity for error if there are two separate lookups.


How do you pull values from a record?

[June7]

Use the Analyte name as criteria in the second DLookup. Provide sample data.

Open a recordset object in VBA and pull values from it.

[cman26]

Let me state here. I am a novice user. I do not know how to open a recordset object in VBA. Please elaborate.

[John_G]

If you are trying to use DLookup to get the Analyte Name AND the CAS Number, what are you using as the criteria in the DLookup?

But as June7 pointed out, why are you keeping both values in all the tables? That violates the rules of normalization, and worse, will leads to all sorts of problems if you ever have to correct or change an analyte name. I suggest you just keep the CAS Number in all the tables and use queries when you need the name as well.

[cman26]

I will never have to change an analyte name. The names and CAS numbers are permanent. I guess an analogous comparison would be if I had a lookup for an employee ID number and I wanted the fields Last Name and First Name to also be populated. Eventually I am going to pull in external data that will use the CAS numbers as a primary key, but I want the Analyte names there for my own use since it is much easier for me to look at Acetone and know what I'm looking at instead of its CAS number 67-64-1

[June7]

That's what queries are for - to view related data.

[cman26]

I realize Queries are used to view related data. I need to be able to build tables from that related data for different analysis types. I don't want to just view it. This is why I want to use a drop box from a lookup, choose an analyte name, and have both the analyte name AND CAS number appear in separate fields. The subsequent tables will then have different fields with different values corresponding to different analysis types.

[June7]

Still haven't seen anything in that description indicating saving both data is necessary.

However, if you provide the requested samples, maybe we can advise how to construct the DLookup expressions.

[John_G]

I don't think you need a DLookup at all. If you use a combo box with two columns - CAS Number and analyte name - but hide the CAS Number column (set the width to 0 ), then whenever you select an analyte name you are automatically selecting its CAS Number as well. All you need to do is copy the CAS Number from the hidden combo box column to a text box that you can see.

One line of VBA code in the After Update event of the combo box will do it: me![textboxname] = me![comboboxname].column(0)

replace the italics with the actual names of your form controls.

[cman26]

John_G, I think I am almost there, Where do I get to the After Update event in Access 2010?

[June7]

Form in design view, select the combobox. On the Event tab of Properties Sheet, select [Event Procedure] in the AfterUpdate event. Click the ellipsis (...) to open the VBA editor. Type code.

[cman26]

My properties sheet is only showing a General tab and not an Event tab.

[June7]

Of what object - a table or query?

Form or report in design view and Property Sheet will have 5 tabs.