Reference in subform help! Criteria not getting desired results.

[StevenB]

Howdy folks,

I'm working on a form (frmUpdateGameStatistics) with a subform (Player_History subform), and I'm having some trouble with a criteria I've built within the subform. Let's see if I can explain this:

The database is for hockey statistics. The "master" form is for entering game details, such as which teams are playing, and what the final score was. The subform is for entering specific actions that a player does, such as goal, assist, etc.

In the sub-form, you first choose a team. This is a drop-down choosing the following fields:

Team_Info.Team_ID (not displayed)
Team_Info.Team_Name

(Apologies to those of you who hate the underscores. (^_^) )

However, we are not actually writing this field to the subform, so it has no control source. This control is called Team.

Once you've chosen a team, you need to choose a player from a drop-down control (called Player) that is based on a query of the following fields:

Player_Info.Player_ID (not displayed)
Player_Info.Jersey_No
Player_Info.Last_Name
Player_Info.First_Name

and the problem field:
Player_Info.Team_ID (not displayed)

The intention of the last field there is this: we only want to display players whose Team_ID is equal to the Team_ID we just selected in the Team control in the subform. So, in order to do this, I gave the last field I listed the following criteria:

Forms![frmUpdateGameStatistics]![Player_History subform].Form!Team.Column(1)

I thought this should result in only the selected team's players being visible in the Player drop-down, but no matter which team I choose in the initial drop-down, I get nothing when I try to select a player.

Any suggestions would be greatly appreciated!

Thanks!

Steve Battisti

 

[Robertd]

Hi Steve,

I think you need to do something like this


Team_ID After update event


' "Player_Info.Team_ID" = combobox ie the team member cbo

me.ComboBox.requery


Ok what we are doing (i think) is selecting a cbo that selects the team name, so the next control is the team player and what you want is after the "team" has been selected is that the user then selects from an cbo the team member form a list of members of the previously selected team. so when the user selects the team in the team cbo, the team member cbo then has a list of the team membes for the user to select from,...?

hth


Robert Dwyer
rdwyer@orion-online.com.au