Using combo to control list box selection

  • Thread starter Thread starter Shiner452
  • Start date Start date
S

Shiner452

How do I make it so that when I make a selection from a combo box the
list box displays certain choices. Example. I have a combo box wit
every variety of plant that I grow and then a list box with every siz
pot that is used. Not all plants are grown in so many different size
pots so i want only the size pots that the plant selected in the comb
box is grown in to be displayed in the list box. Is there any way
can set this up
 
How do I make it so that when I make a selection from a combo box the a
list box displays certain choices. Example. I have a combo box with
every variety of plant that I grow and then a list box with every size
pot that is used. Not all plants are grown in so many different sized
pots so i want only the size pots that the plant selected in the combo
box is grown in to be displayed in the list box. Is there any way I
can set this up?
Click to expand...

You can do this with a little bit of VBA code, actually a couple of
different ways.

Probably the best is to construct the SQL string for the listbox's
RowSource in the AfterUpdate event of the Pot combo box. Not knowing
the structure of your tables I can't give specific instructions, but
it might be something like:

Private Sub cboPotSize_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT PlantID FROM StockTable WHERE"
strSQL = strSQL & " [PotSize] = " & cboPotSize
strSQL = strSQL & " ORDER BY PlantID"
Me!lstPlants.RowSource = strSQL
End Sub

You'll need to adapt to your table and fieldnames of course.
 
Back
Top