Combo box question!

[Bachfile]

I have two cascading combo boxes. They work - you select one Category from the first box and the SubCategory boxes update so you can only select the related subcatories.

There is a small problem with this however - the subcategory box appears empty when I select a different record (e.g going from record 2303 to 2304) when the category in record 2303 is different from record in 2304.

When I click on the category again, the subcategory selection I had previously selected and saved shows up.

Is there a way to have the form update the category box somehow so when I select a new record, it shows the related subcategory?

I can post all relevant code if needed.

Thanks a lot guys!

Bachfile

 

[DBL]

if the combos are in form view you need to put a requery on the combos on the forms On Current event so that they refresh themselves as you move through the records. If the cascading combos are in datasheet view, you need to deal with them differently. Here's a sample for both types

 

[Bachfile]

Thanks!

Thanks a lot for your suggestion - it works perfectly now

One more question however - it would be EVEN better if all of the subcategory combo boxes would show their contents in the datasheet view as well - I know you submitted an example -

Would you mind explaining what I might do and then I could cross-reference with your example?

That would be great!

Thanks,


Bachfile

 

[DBL]

I'll try to explain this but when I read it back it never makes sense - hence the demo! When you select an item from a second combo that is filtered base on a first combo it's the item's autonumber that is stored to the base table. The recordsource for the second query is dynamic - depending on the selection on the first category so if you have three rows in your datasheet and two are for Shoe Shops and then you select a Clothes Shop in the first category then the shoe shops aren't in the query for the second combo any more. To get round this, you can have the second form show the ID field (which is normally hidden) and the description field for the second combo so that the user can select the shop by name but once you've made the selection it's the combos bound field that you view. You can then have a text field that shows the name of the shop separately.

Hope that makes sense.

 

[Bachfile]

Thanks DBL, I'll give it an honest go at it -

Just one quick question though - changes you make to the form in datasheet view - will they have an impact on the form view?

Just wondering....


Thanks,

Bachfile

 

[Bachfile]

Hey guys!

Does anybody know how I could get all of the fields to update using VBA or some other method other than the one that DBL has suggested. Adding that extra box would be too much of a pain. There must be a way using programming to fix this somehow.

Thanks for any help you can offer guys!

Bachfile