Populating multiple combo boxes

[Guest]

Hello all. Here's my dilemma:
In form "fOrder", a basic order form with customer information and product
ordering information, I want to have three combo boxes. When you select from
the first combo box I want that result to populate the second box then third.
Each selection will narrow down the results of the next combo box. This
database is to keep track of shipping orders from our butcher shop. The
first box is "Type" and it allows us to select whether customers want steaks,
marinades or roasts. The next combo box, "Product", allows us to say what
type of steak, marinade or roast they want (sirloin, ribeye, etc...). The
last box is "Size" and it will allow us to select sizing options (16oz, 24oz,
etc...).

I have used one combo box to populate another before. I set up an SQL
statement in one combo box's control source to base its fields on the results
of another combo box selection in the form. Then I went in and added some
VBA code. That was fairly easy...there was a nice Microsoft help article
about how to do it. I'm running into problems on how to work with three
combo boxes, though. Maybe you guys can offer some help?!

Here are the particulars of my database:
The order form is based on the table "tOrder". "tOrder" contains customer
fields ("Last", "First", "Phone") and product fields ("Type", "Product",
"Size" and "Price"). I have table "tCustomer" to store all my customer
information. I have tables "tType", "tProduct" and "tSize" to store all my
product information...these tables have a one-to-many relationship with each
other. All control sources on my form are bound.

I just don't have the Access knowledge and the VBA coding ability to do
this. Any suggestions are greatly appreciated!!! This is getting to be our
busy season (summer=grilling season=meat=$$$)... : ) Thank you
very much!!!

 

[Steve Schapel]

Ben,

The basic concept here is to make queries for the Row Sources of the
Product and Size comboboxes. The query for the Product combobox will be
based on the tProduct table, of course, and in the Criteria of the Type
field in this query, put a reference to the Type combobox, probably it
will look like this...
[Forms]![fOrder]![Type]
Similarly, the query for the Size combobox will be based on the tSize
table, of course, and in the Criteria of the Product field in this
query, put a reference to the Product combobox, probably it will look
like this...
[Forms]![fOrder]![Product]

 

[Guest]

Worked like a champ! Not sure what I was doing wrong before, but this works.
Now another question. Now that my boxes are basing themselves on each other
nicely I need to enter some VBA code (something I'm not very good at yet):
When my Form (fOrder) opens I want all combo boxes to display the first item
in the field. Then I want "Product" and "Size" to requery themselves
everytime "Type" is updated...so that they stay updated to the "Type" combo
box. Here's what I entered to keep "Product" and "Size" updated:

Private Sub TypeAfterUpdate()
Me.Product = Null
Me.Product.Requery
Me.Product = Me.Product.ItemData(0)
Me.Size = Null
Me.Size.Requery
Me.Size = Me.Size.ItemData(0)
End Sub

Obviously, this isn't right because neither "Product" or "Size" are
requerying. Any suggestions?

Ben,

The basic concept here is to make queries for the Row Sources of the
Product and Size comboboxes. The query for the Product combobox will be
based on the tProduct table, of course, and in the Criteria of the Type
field in this query, put a reference to the Type combobox, probably it
will look like this...
[Forms]![fOrder]![Type]
Similarly, the query for the Size combobox will be based on the tSize
table, of course, and in the Criteria of the Product field in this
query, put a reference to the Product combobox, probably it will look
like this...
[Forms]![fOrder]![Product]

--
Steve Schapel, Microsoft Access MVP

Hello all. Here's my dilemma:
In form "fOrder", a basic order form with customer information and product
ordering information, I want to have three combo boxes. When you select from
the first combo box I want that result to populate the second box then third.
Each selection will narrow down the results of the next combo box. This
database is to keep track of shipping orders from our butcher shop. The
first box is "Type" and it allows us to select whether customers want steaks,
marinades or roasts. The next combo box, "Product", allows us to say what
type of steak, marinade or roast they want (sirloin, ribeye, etc...). The
last box is "Size" and it will allow us to select sizing options (16oz, 24oz,
etc...).

I have used one combo box to populate another before. I set up an SQL
statement in one combo box's control source to base its fields on the results
of another combo box selection in the form. Then I went in and added some
VBA code. That was fairly easy...there was a nice Microsoft help article
about how to do it. I'm running into problems on how to work with three
combo boxes, though. Maybe you guys can offer some help?!

Here are the particulars of my database:
The order form is based on the table "tOrder". "tOrder" contains customer
fields ("Last", "First", "Phone") and product fields ("Type", "Product",
"Size" and "Price"). I have table "tCustomer" to store all my customer
information. I have tables "tType", "tProduct" and "tSize" to store all my
product information...these tables have a one-to-many relationship with each
other. All control sources on my form are bound.

I just don't have the Access knowledge and the VBA coding ability to do
this. Any suggestions are greatly appreciated!!! This is getting to be our
busy season (summer=grilling season=meat=$$$)... : ) Thank you
very much!!!

 

[Steve Schapel]

Ben,

First of all, I don't think the "= Null" lines in your code are
necessary... though I don't expect that is causong the problem.

So let's go one step at a time here. If you simply have this code...
Private Sub TypeAfterUpdate()
Me.Product.Requery
End Sub
.... and you change the entry in the Type combobox, does the drop-down
list in the Product combobox get adjusted accordingly?

 

[AccessVandal via AccessMonster.com]

Ben,

Size and Type are reserve names in VBA.
Rename your controls.



"in the field. Then I want "Product" and "Size" to requery themselves "
"everytime "Type" is updated...so that they stay updated to the "Type" combo
"
"box. Here's what I entered to keep "Product" and "Size" updated:"

"Obviously, this isn't right because neither "Product" or "Size" are "
"requerying. Any suggestions?"

 

[Guest]

And it's as easy as that. I renamed the controls and it solved everything.
Thanks. You guys are the best.