CODEWITHSUNDEEP
ms-access
Zack
Zack

Reputation: 121

Filter Access Combo Boxes

I have an access database with a form that contains two combo boxes. One combo box filters its options based on the selection of the first combo box. This works perfectly but - I have a command button that changes the record source of the form. I only know how to populate the combo boxes based on one table though. So when the form changes record sources the combo boxes are still filled with options from the first record source. How can I create a query that populates the combo box options not on one table in general but based on whatever the record source of the form is at the time?

Upvotes: 2

Views: 2503

Answers (1)

RolandTumble
RolandTumble

Reputation: 4703

In the same command button handler that changes the RecordSource, assign a new RowSource to each combo box. Better yet, do this in a separate sub that's called by the button handler. Your code will have to know, or be able to figure out, what the new RowSource should be to correspond to the new RecordSource.

Edit in reply to comments

The RowSource doesn't have to be a QueryDef object, it can just be an SQL statement:

Dim sSQL As String

sSQL = "select whatever from wherever"
comboBox.RowSource = sSQL

I can't see the comments at the same time I'm editing, but I didn't understand what you were asking in the second.... On a second, separate, look at the comments, I think you're asking it you can set the RowSource to the form's RecordSource property. This strikes me as a bad idea--you've probably got many more columns in the RecordSource than you'll want to deal with in the RowSource, and a basic principle of query construction is to only request the columns you want.

Upvotes: 1

Related Questions