combo box - run query - query

[papa]

Hi Guys,

Beware Access Beginner****
I need help with the following query:

I have a form with a drop down box with different teams
in them. What I need is that when the user clicks on
their team, this will popultale a list box with all
different members of the selected team.

There are ten different teams team1, team2, etc. these
teams are all stored in one table.

I have different queries running which select each team.
(queries are called Qu_Team1, Qu_Team2, etc.)

Is there an easy way to get the teams in the combo box to
run the queries to fil the list box?

many thanks,
Papa

 

[Sandra Daigle]

Hi Papa,

What I would recommend doing is get rid of the queries Qu_Team1 . . .
Qu_teamN and instead use one query which has a reference to the Team combo.
You do not want to build an application that requires you to make design
changes when new data (a new team for instance) is added. By using a
parameter in your query, you eliminate this design overhead.

Say your Team combo is named TeamId (assuming the bound column of the combo
is a numeric id field). Then the query for your listbox would be something
like:

Select * from tblTeamPlayers where Teamid=forms!frmMyForm!TeamId;

If you are using the query designer, add TeamID from tblTeamPlayers to the
list of fields, then in the criteria cell under TeamID put:

forms!frmMyForm!TeamId

Replace 'frmMyForm' with the name of your form, and replace 'TeamId' with
the name of your combo.

There is one more thing you have to do - the listbox needs to be requeried
everytime the combo box changes. Just add an AfterUpdate event on the combo
and in it put:

me.lstPlayerId.requery

Replace 'lstPlayerId' with the name of the listbox.

Here are a couple of articles that describe this method and another similar
method to synchronize two combo controls. The principle is the same and
works on listboxes and combos:

How to Synchronize Two Combo Boxes on a Form
http://support.microsoft.com/default.aspx?scid=kb;EN-US;289670

ACC: How to Synchronize Two Combo Boxes on a Form (97624)
http://support.microsoft.com/default.aspx?scid=kb;[LN];97624