Linking two Combo-boxes within a subform

[ScottyBoy]

I'm creating a database to assist with the sales operations of a hat shop. I have a form for creating new orders called 'Invoice' where the order number and date is set.
Within this form is a subform called 'Invoice_Order' (Continuous layout), this form is used for assigning order items to an order number.
Within the subform there are two combo boxes which search the Stock table.

1st ComboBox- diplays hat styles (E.g- v1/v2/v3/v4...)

Problem is; different hat styles are available in different colours based on the material used. I would like the second CoboBox to display a list of colours depending on the style of hat chosen.
At present i can only get it to display a list of colours for all hat styles.

Does that make sense?
Any help would be most appreciated

 

[TinkerMan]

Try using events

This is a crude suggestion, but it should work.

If you put code on your first combobox's afterUpdate event, and put this code in it:
me.combo2.rowsource = "Brown;Black"

You can of course do some more intelligent on which colours to pick to which hat.

Hope this helps

 

[ScottyBoy]

Re: Linking Two Comboboxes within a subform

Thanks,
Though I don't mean to 'look a gift horse in the mouth', but i do agree that is rather a crude solution, as there are many hat styles and these will change/update regularly based on details held within a stock query (StyleNo, Colour, HatsInStock, HatMaterial).

My aim is:
to have the first ComboBox(Style) to show the Hat Styles in this query which currently works- using the 'Group by' option.
Then I would like the second combobox (colour) to be restricted in order to show all colours from this query where the Hat Style equals the style selected in the first combo box.

I appologise if i'm repeating myself- but i would prefer the soloution to be based on the query allowing the stock related data (Hat style and colour) to be easily mainatinable rather than hardcoding the solution.
Is this possible?

 

[Mile-O]

Do a search on Cascading Comboboxes - I put a tutorial in the FAQ forum ages ago.

 

[ScottyBoy]

Re:Combobox FAQ

BINGO!
This works in a round about way which is cool.
Though after selecting the frst combobox (Hat Style), Access pops up with a box requiring the input of a paramater, so the same hat style then ahs to be typed. I presume this will have some thing to do with my SQL statement, but once this has been entered, the correct list of colours appear.

Problem though, when i go to enter the next record- the colour in the combo box of the first record disappears, but the database still records the correct data.

Anyway, i shall have a bash at sorting it out.
Thanks