How do I change combo box options as the user makes selections?

[Guest]

I have 3 combo box fields in a "Case" form that represent a categorization
for a help desk case. Each combo box queries the "Categories" table to get
the content to list. I want to create the following behavior:

when the user selects a line from Category1, the combo box in Category2 gets
re-populated with the narrowed-down options (i.e. only the categoy2 from the
categories table where category1 equals my case form category1).
Then the same goes when the user selects from category2 to re-populate the
Category3 combo box.

I have tried macros running on "after update" on Category1 that was changing
the RowSource for Category1 to my new query but I get a message that says
that this component does not allow this change.

So I tried to keep the RawSource constant, it being a "Avalable Categories"
table. Then the "after update" on Category1 macro runs a make table query
that overwrites the "Available Categories" table. But I get a message that
the table can't be accessed because it's already locked by another user.

I believe this is called a dynamic menu system. Any suggestions on how to
implement using Access?

 

[Steve Schapel]

Splatre,

Make a query for the Row Source of the Category2 combobox, which refers
to the Category1 combobox in its criteria, using syntax such as...
[Forms]![Case]![Category1]
.... and then on the After Update event of Category1, use a macro with a
Requery action applied to the Category2 combobox.