Multiple Look-up

[The Dad]

I am currently in the process of creating a database using Access 2007 and in the need of help. The database will be tracking employee production.



I have two tables of look-up information that is related to my main table. Basically what I have is a table which lists "Work Items" and a sub table which lists "Work Item Descriptions". (i.e., some work items have only one associated description, whereas some have several, up to 20).

What I would like to do is when in the main table, by selecting a "Work Item", only the associated "Work Item Descriptions" will populate in the next field.

I am stumped. I know it is possible, but can't find out how because I do not know what this would be called. Any help?

[pbaldy]

It sounds like this type of thing:

Baldy-Cascading Combos

To my knowledge, it can't be done directly in a table. Most of us don't let users anywhere near the tables anyway.

[The Dad]

Paul, thank you for the direction you have provided. I am stumped though. I have the row source for the first drop down to correctly display, but I can't get the second to populate.

I used the following for the first:

[SELECT Work Item Description].[Work Item Description] FROM Work Item Description WHERE (([Work Item Description]

I used the following for the second:

[SELECT Work Item Description].[Work Item Description] FROM Work Item Description WHERE (([Work Item Description;]

What I did was basically reverse engineer your example from Baldy Web (which is great by the way). There is a string in your example which is ![cboState1] and I don't understand it, as that "cbo" part does not show in the form nor the table.

Could you explain it to me?

Thank you in advance.

[pbaldy]

cboState1 is a reference to the first combo box on the form (that's the name of the combo). A WHERE clause has to have some value to compare the field to, like

WHERE Parent = 'Dad'

You just have

WHERE Parent

You also have brackets around the SELECT and such. You generally don't need them at all, but you will because of the inadvisable spaces in your names. The brackets just go around the table or field name though: [Work Item Description].

[The Dad]

Thanks Paul. I try that.

[The Dad]

I can't seem to make it work. Will you take a look and see where I am going wrong?

The data all points to "Main Table" but I made copies with "1" after them.

I sent you a copy of the database via g-mail since it is too big to put here.

Any help would be greatly appreciated.

[pbaldy]

I only have a minute, but I see a couple of things right off. You have SQL in the Control Source properties, which won't work. Those either need to be the name of a field in the table or blank. Second, you don't have a requery of the second combo in the after update event of the first combo (that's what makes it refresh its selections). I see the row source of the combo is invalid, as there's no such table as "Work Item". I'll let you fix that.

I have to go pick up my wife, so let me know if you're still stuck.