Using one field on a form to limit values displayed in a combo box

[Michael V]

This has to be fairly easy, but I'm just not getting it. I am trying to
create a database for a friend to keep track of his high school basketball
assignments. My specific problem is as follows:

I have created a table called games. Columns in include the key, date, time,
level home team and visiting team. The level designates whether it is
Freshman, JV or Varsity. I have created acombo box for that and it works fine.

I'd like to display only those teams that are relavant to the level (e.g. if
it is a Freshman game, then the choices for the home team combo box should be
limited to Freshman teams).

I've tried creating a query that uses the "level" entry on the form as part
of the where clause, but I'm not doing something right . I either get a blank
result, or a list that seems to be casually related (perhaps from a prior
entry) or the primary key values from the team (rather than the team name).

Any suggestions?

 

[KARL DEWEY]

In your WHERE clause put --
[YourTableName].[level home team] = [Forms].[YourFormName].[YourComboBoxName]

If this does not work then post your SQL for the query.

 

[Michael V]

Here is the SQL. I think it reflects your suggestion.

SELECT School.[School Name], [Playing Levels].[Playing Level Code],
Team.[Team ID], [Playing Levels].[Playing LevelID]
FROM [Playing Levels] INNER JOIN (Team INNER JOIN School ON Team.[School
ID]=School.SchoolID) ON [Playing Levels].[Playing LevelID]=Team.[Playing
Level]
WHERE ((([Playing Levels].[Playing LevelID])=Forms!Game![Playing Level ID]));


When I run the query, I get prompted for a Playing Level ID value and the
results are just what I expect.

The problem still seems to be that the query doesn't recognize the value I
selected from the actual combo box when I use the form. I thought recognition
of the current entry would be inherent, which is why I'm probably messing
something simple up.

Anyway, thanks for any suggestion you may have.

Michael.
--
Michael

In your WHERE clause put --
[YourTableName].[level home team] = [Forms].[YourFormName].[YourComboBoxName]

If this does not work then post your SQL for the query.

This has to be fairly easy, but I'm just not getting it. I am trying to
create a database for a friend to keep track of his high school basketball
assignments. My specific problem is as follows:

I have created a table called games. Columns in include the key, date, time,
level home team and visiting team. The level designates whether it is
Freshman, JV or Varsity. I have created acombo box for that and it works fine.

I'd like to display only those teams that are relavant to the level (e.g. if
it is a Freshman game, then the choices for the home team combo box should be
limited to Freshman teams).

I've tried creating a query that uses the "level" entry on the form as part
of the where clause, but I'm not doing something right . I either get a blank
result, or a list that seems to be casually related (perhaps from a prior
entry) or the primary key values from the team (rather than the team name).

Any suggestions?

 

[KARL DEWEY]

Add this to the FROM part of the SQL --
[Forms]![Game]![Playing Level ID] AS [Form entry]

This way you will see what the query is getting for criteria.

Here is the SQL. I think it reflects your suggestion.

SELECT School.[School Name], [Playing Levels].[Playing Level Code],
Team.[Team ID], [Playing Levels].[Playing LevelID]
FROM [Playing Levels] INNER JOIN (Team INNER JOIN School ON Team.[School
ID]=School.SchoolID) ON [Playing Levels].[Playing LevelID]=Team.[Playing
Level]
WHERE ((([Playing Levels].[Playing LevelID])=Forms!Game![Playing Level ID]));


When I run the query, I get prompted for a Playing Level ID value and the
results are just what I expect.

The problem still seems to be that the query doesn't recognize the value I
selected from the actual combo box when I use the form. I thought recognition
of the current entry would be inherent, which is why I'm probably messing
something simple up.

Anyway, thanks for any suggestion you may have.

Michael.
--
Michael

In your WHERE clause put --
[YourTableName].[level home team] = [Forms].[YourFormName].[YourComboBoxName]

If this does not work then post your SQL for the query.

This has to be fairly easy, but I'm just not getting it. I am trying to
create a database for a friend to keep track of his high school basketball
assignments. My specific problem is as follows:

I have created a table called games. Columns in include the key, date, time,
level home team and visiting team. The level designates whether it is
Freshman, JV or Varsity. I have created acombo box for that and it works fine.

I'd like to display only those teams that are relavant to the level (e.g. if
it is a Freshman game, then the choices for the home team combo box should be
limited to Freshman teams).

I've tried creating a query that uses the "level" entry on the form as part
of the where clause, but I'm not doing something right . I either get a blank
result, or a list that seems to be casually related (perhaps from a prior
entry) or the primary key values from the team (rather than the team name).

Any suggestions?