Using Listbox to narrow selection for other Listbox

[loki1049]

I am trying to make a form in design view to enter data into a transactions table. I have the following set of tables with these relationships:

<EquipmentTypeTable>
[EquipmentTypeID] PK
[EquipmentTypeName]

<EquipmentTable>
[EquipmentID] PK
[EquipmentName]
[EquipmentTypeID] FK

<TransactionsTable>
[TransactionID] PK
[EquipmentID] FK

I would like to use a form to insert data into this table. I already have it so that whatever I send to a bunch of text boxes can be added as a new record to the TransactionsTable, but I can't figure out how to get a first list box to pick a EquipmentType, then using that EquipmentType Value look through the EquipmentTable and only display the equipment that is of that type.

Please no Visual Basic response, it means nothing to me at the moment..

Ideally I would like to use a query in the "Row Source" field of the properties table for the second Listbox, and use a where clause saying that the EquipmentTypeID must equal that which is currently selected in the first Listbox.

 

[pbaldy]

The concept is known as cascading combos:

http://www.baldyweb.com/CascadingCombo.htm

As you can see one of the techniques uses your proposed method. You just have to requery the second one.

 

[loki1049]

There is no way to do this in the Properties Sheet of the design form? I don't have time to learn VB nor do I choose to for this project, that can be another day.

 

[boblarson]

Check out my sample here. It has only one line of VBA code which requeries the combo box. The other part is in the other combo box's row source (query that is the rowsource) and it references the other combo box which then limits the records based on the selection. See if that helps.

 

[loki1049]

Nevermind, after I figured out that this type of operation had a specific name I just searched the internet and came up with this:

Answer
Ok lets look at this via an example. Lets refer to 4 levels: Level1-Region, Level2-Country, Level3-Province, Level4-City. Lets further assume that you have 4 tables, one for each of these levels and the format of these tables is Code, Description, Category. For example, your countries table might have a record of
Code: 1
Description: USA
Category: 2 (2 is the code for North America in the Region table)

Now you have the 4 comboboxes on your form. The Rowsource for cboRegion (level1) would be:

SELECT Code, Description FROM tblRegions ORDER BY Description

The bound column would be column 1 (the code) and the column width would be set to 0" so only the description would show.

In the second combo (cboCountry), the RowSource would be:

SELECT Code, Description, Category FROM tblCountries WHERE Category = Forms!formname!cboRegion ORDER BY Description

Again the bound column is column 1 and the Columnd widths would be 0",x",0" where x is the however wide you want that control to be. Only the second column would display.

That should give you the idea. If not check out the Northwinds sample database for examples.

Hope this helps,
Scott<>

Thanks for the help on terminology Paul!

 

[loki1049]

Check out my sample here. It has only one line of VBA code which requeries the combo box. The other part is in the other combo box's row source (query that is the rowsource) and it references the other combo box which then limits the records based on the selection. See if that helps.

Thanks for that, I'll take a look!

 

[pbaldy]

Either VBA or a macro. One way or the other the second combo/list box has to update when a selection is made in the first. You don't have to learn much, as the code is provided. Here's how to get it in:

www.baldyweb.com/FirstVBA.htm

 

[pbaldy]

I'll be interested to see if that sample is fundamentally different that the one already posted.

 

[boblarson]

Nope, pretty much the same without the dynamic SQL one. Didn't see that there was a sample on the page until your statement just now.

 

[boblarson]

Although mine has bright colors and more words