Change Record source of a combo box on a form based on another combo box

[littlecool]

I have a form that currently uses a "catch all" table for listing available equipment to choose from for an equipment field. I call it tblEquipment. What I want to do is to make it so when I type a name in (1 of 35) in one field of the current record, the record source for the equipment field immediately looks at a different table that has equipment available only for that name. To do this I plan on making 35 different tables with limited data originally found in tblEquipment. I would call these tblEquipment1, tblEquipment2, etc. I do not use a sub form, nor do I want to.

So my questions are:

1) can this be done

2)If it can be done, how can I do it?

Any help is greatly appreciated.

-LC

 

[pbaldy]

Your first question should have been "Should it be done", and the answer for 35 tables is "no". Is this the type of thing you're trying to do?

http://www.baldyweb.com/CascadingCombo.htm

 

[littlecool]

exactly like that, except (using there example) each city may be associated with 1 or up to 35 names.

 

[pbaldy]

That would be a matter of repeating the city with each appropriate state. You want to come up with a similar table that works with your data. You don't want a table for each; it would turn into a maintenance nightmare, and is not dynamic like a relational database should be.