Creating linked list boxes.. 1

[secretcrowds]

Hi.

I am trying to create list box's on a form that can link to a combo box already created on the same page.
So say if i select some data from the drop down box, i want it to bring up the other data stored in the same table as the data from the drop down menu, but appear in the list box.

for example..
I have a table called tblControl, with the fields:
ControlID,ControlObjectiveID,ControlName,Description,Procedures and FrequencyOfAudit.

In the drop down menu, it looks up the data of the ControlObjectiveID, and once that is selected, i would like a list box underneath that will then display the data for Description and Procedures, corresponding with what is selected in the drop down menu for ControlObjectiveID

Thank you for any help!

 

[MajP]

faq702-681

 

[fneily]

First your rowsource of your combobox should look like:
SELECT DISTINCT [ControlObjectiveID] FROM tblControl ORDER BY [ControlObjectiveID];

Then on the AfterUpdate event of the combobox put:
Me![List2].RowSource = "select [Description], [Procedures] from [tblControl] where [ControlObjectiveID] = '" & Me![Combo0].Value & "'"
Also for the listbox, make column count 2.
If ControlObjectiveID is numeric, take out the 's.

Note if Description is a memo field, you'll only see the first 255 characters. If you want the full memo field, you'd use the Dlookup function.


Also, your table is not normalized. See:
Fundamentals of Relational Database Design

http://r937.com/relational.html

You should have a table for Controlname, one for Procedures (if a procedure can be used by more then one Control) and then a junction table for frequencyofAudit(depending on what you're auditing).