Smart Combo Box?

[Zacharyjuniorman]

I have a combo box that draws from a table called tblFailureModes. In this table I have 9 columns. Column 1 is the ComplaintName and each column after is FailureMode1, FailureMode2, FailureMode3 and so on.

I want combo box 1 to select the ComplaintName and combo box #2 to show only the choices for that complaintName.

Any suggestions?

 

[charityg]

In the source query for the failuremode combobox you would enter the criteria to only show those records where the first field is equal to the complaintname combobox selection.

If you've created the failuremode combobox with the wizard, just go to the properties for the combobox and select the rowsource property. Add the complaintname field to the grid and enter "=forms!formname!cmbname" in the criteria where formname is the name of the form where your complaintname combobox is located and cmbname is the name of your complaintname combobox. Make sure you uncheck the show property for this field.

The last thing you need to do is requery the failurecode combobox after the complaintname box is updated. Using the properties window for the complaintname combo and select the afterupdate event, code builder. Then enter cmbfailcode.requery where cmbfailcode is the name of the failcode combo box.

Repeat this for all of your failcode combo boxes.

 

[Pat Hartman]

You are not going to be able to populate the second combobox except by creating the recordsource on the fly with code due to your table design. You really have a one to many relationship (perhaps even many-to-many). This requires at least two tables (three if it is many-to-many). tbl1 should define the Complaint and tbl2 should relate the Complaint to a FailureMode.

tbl1:
ComplaintID (primary key)
ComplaintDesc

tbl2:
ComplaintID (primary key part1)
FaliureModeID (primary key part2)
FailureModeDesc

The query for the second combobox should use a where clause that refers to the selected ComplaintID from the first combobox. See northwinds.mdb or solutions.mdb for examples of how this works.