Combobox filtered rowsource?

[TerraEarth]

I have two unbound comboboxes, one on a main form and the other on a subform attached to the mainform. The comboboxes are used to select records from their respective forms. They are populated with simple SQL queries for the rowsource, no controlsource. I have set up their "After Update" events to lookup the relevant record chosen in the dropbox and to populate their respective forms with the recordset.



I'm trying to set it up so that the user first has to select an entry from the main combobox (or add a new entry into the form entirely) before the second combobox/form is unlocked.

However, I want the second combobox to be populated with a filtered list, filtered depending on what was selected in the main combobox. For example, Albums and songs. If Albums was the mainform, and Songs was the subform, I would want the Songs combobox to be locked until the user chooses an Album. Then I want the user to be able to either:

a) Choose a song from that album
b) Enter in a new song not in the database for the album.

What do you guys think is the best way to set up the filtered rowsource for the second combobox and the locking feature?

[June7]

This is called cascading or dependent comboboxes and is a common topic. There are numerous threads here and elsewhere.

Usually the comboboxes are on the same form.

What data is the main form bound to - albums? Is subform bound to dependent table of songs associated with albums? Does Master/Child Links on subform synchronize display of only the songs associated with respective album? If this is the case, then the only need for the second combobox would be to select a song from a master table of songs filtered to exclude songs already associated with the album. The means the second combobox would be bound.


There are also numerous discussions of music collection database structure.

[ArviLaanemets]

To put it simply - a event of 1st combo (AfterUpdate/OnChange) changes the RowSource of 2nd combo.

[TerraEarth]

This is called cascading or dependent comboboxes and is a common topic. There are numerous threads here and elsewhere.
There are also numerous discussions of music collection database structure.

Hey June. I apologize for asking a somewhat redundant question, in truth I was not able to locate what the topic for this subject was. The best I can do is google phrases that seem related and look for hits.

Yes, the main form would be bound to albums. The subform in this example would be bound to a table called songs which is linked to albums with a master/foreign key relationship. I'm not sure what you mean by "dependent table" though. The Master field is set to "Album ID" on the main Album form, and the Child field is set to "Album ID" on the Songs subform.

How the form currently works is, I put in an album and it lists the first song entry for that album in the subform (though I would like it to be blank until a specific song is selected from the combo box). So essentially the purpose of these forms would be to pinpoint a song from an album and make edits accordingly. This structure might seem a bit silly, but I am actually trying to implement a 3 part form, main form with a subform and that subform has a subform.

So an example for this would be a customers orders database for example. A customer database is the main form, a "orders" form would be the subform, and the subform of that form would be the "items" form. I was debating whether I wanted it to be all on one form sheet or perhaps have two different separate forms, one for strictly customers, and the other for orders and items.

To put it simply - a event of 1st combo (AfterUpdate/OnChange) changes the RowSource of 2nd combo.

This... I'm not sure why I didn't think of that. Is there a command that can lock the subform until the combo box is queried for a value?

################################################## ###########################################

While we're on the topic gentlemen, how do you guys feel about one of the subforms being a table? For example in the Customers(mainform)/Orders(subform)/Items(subform of subform) example, since there will likely be many items per order per customer, it might be useful to make the last subform into a table format and users could edit/add items through that table. Is this a dumb idea? Is there a smart way of doing this?

[June7]

Subform container control has a Locked property which can be changed by code. The real trick is figuring out which event(s) to use.