Link to home
Start Free TrialLog in
Avatar of mario_34
mario_34

asked on

setting RowSource of a combo box

Hi,

i have a form with some combo boxes and a subform. With those combo boxes you can filter the data displayed in the subform. The question is: Suppose you have already filtered the data with one of those combo boxes, so that only  some records are displayed. How can i set the RowSource property of another combo box so that i can choose only among those records which are already displayed on the subform and not from all the records which are available in the table?

Avatar of Paurths
Paurths

hi mario_34,

here is an example of filling a combobox with using a chosen value in another combobox (cbo2)

when the chosen value in cbo1 = numeric

Private Sub cbo1_AfterUpdate()
dim strSQL as string
strSQL = "Select Field1, Field2, Field3 from YourTable where Field4 = " & me.cbo1.value
me.cbo2.rowsource = strsql
me.cbo2.requery
End Sub




when the chosen value in cbo1 = text

Private Sub cbo1_AfterUpdate()
dim strSQL as string
strSQL = "Select Field1, Field2, Field3 from YourTable where Field4 = " & chr(34) & me.cbo1.value & chr(34)
me.cbo2.rowsource = strsql
me.cbo2.requery
End Sub



cheers
Ricky
Checkout this link:
http://www.candace-tripp.com (Select: MS Access downloads !)

There's a combo-link sample and more nice code.

Nic;o)
oops, little mistake of my part..


when the chosen value in cbo1 = numeric

Private Sub cbo1_AfterUpdate()
dim strSQL as string
strSQL = "Select Field1, Field2, Field3 from YourTable where Field4 = " & me.cbo1
me.cbo2.rowsource = strsql
me.cbo2.requery
End Sub




when the chosen value in cbo1 = text

Private Sub cbo1_AfterUpdate()
dim strSQL as string
strSQL = "Select Field1, Field2, Field3 from YourTable where Field4 = " & chr(34) & me.cbo1 & 
chr(34)
me.cbo2.rowsource = strsql
me.cbo2.requery
End Sub



u can also use other columns from the combobox.

Suppose u have this data
YourTable
----------
autoid   LastName   Firstname
1        Clinton    Bill
2        King       Stephen


now lets assume u need the column 'Lastname' to filter the next combobox.

Private Sub cbo1_AfterUpdate()
dim strSQL as string
strSQL = "Select autoid, Lastname, Firstname from YourTable where LastName = " & chr(34) & me.cbo1.column(1) & 
chr(34)
me.cbo2.rowsource = strsql
me.cbo2.requery
End Sub


Notice that the columncount starts at 0!
so, the first column is cbo.column(0),
the second column is cbo.column(1),
etc...

cheers
Ricky
Avatar of mario_34

ASKER

Hi,

the suggested code (also the one on the given link) is good, but only if you have a cascade filtering (so the first combo filters the biggest set of data, the second combo filters the remaining data and so on...). But in my case i have 7 comboboxes and the user should start filtering from any of them. Once a selection is made, the changes should be reflected in the ramaining 6 combos. Does it mean that i have to update the ramaining 6 combos every time a change is made to one of them? That would be quite a lot of code, since i should check if in the remaining 6 combos a selection is made, and if it is, i should concatenate the appropriate "filter" to the existing sql statement. Doesn't maybe exist a method like autofilter in excel, which does exactly what i'm trying to do in Access?


ASKER CERTIFIED SOLUTION
Avatar of nico5038
nico5038
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial