Sourcing Drop Down options from results of other drop down

[boprat]

To help everyone understand, I will use a simple example of what I'm attempting to do (plz keep in mind that my actual table is more complex and requires access as the solution).

Imagine a table that contains two fields: 1) Division 2) Team

I have a form that has two drop downs. The first allows the user to select the division (let's use the NFL here): AFC EAST, AFC WEST, AFC NORTH, AFC SOUTH, NFC EAST, NFC WEST, NFC NORTH, & NFC SOUTH.

Based upon user selection of one of those divisions (let's assume "NFC NORTH" was selected, I would like to have the second drop down show those teams only associated w/the NFC NORTH (Packers, Lions, Bears, Vikings).

Can someone give me a detailed description of what type of queries I need to create ( and I'm not too swift, so feel free to be as detailed as you'd care too ) and where I need to hook them into my user form.

I run on Access 97 fyi.

Thanks in advance for any help you can provide.

 

[Dr_Worm]

Re: Sourcing Drop Down options from results of other drop do

OK…This is how I have solved this problem on one of my Access 97 databases, but there may be a better way, I just haven’t found it.

Create a query for the first dropdown from the table that holds the “Division” and “Team” fields. The query only needs the division information. Go to view and select totals, and then in the sort row select Ascending, this will give you a list of all the divisions in alphabetical order. Save this Query. Create a combo box on your form using the query you just created as the values for the combo box.

Now create another query this time with the “Team” field and “Division” field, in the criteria for “Division” type – [Forms]![Your_Form]![Your_Division_ComboBox]

On the your form create another combo box using the second query as the values.

When you select the Division on you first combo box, Access will hold the value in the combo box, when the second combo box is activated it will run the second query it will use the Division you selected as the criteria for the query, giving you only the teams in that division.

Hope this helps

 

[Dugantrain]

Re: Sourcing Drop Down options from results of other drop do

There is a host of information on cascading combo boxes on http://www.access-programmers.co.uk/forums. Just have a Search on 'Cascading Combo'. I also found this link here.

 

[Dr_Worm]

Re: Sourcing Drop Down options from results of other drop do

The second link has some very useful stuff

 

[boprat]

Re: Sourcing Drop Down options from results of other drop do

great tips guys... thx!