Subform rows not independent of one another 1

[StevenB]

I'm working on a database for tracking ice hockey statistics. I have
created two forms for entering the results of a game. One allows me to
select a specific game from the database and enter some generic (non-
player-specific) data, such as the date, time, and location of the game.

The second form I created as a sub-form. The purpose is to allow me to
enter all of the player-specific information for a given game. For
example, who scored, at what time, of what period.

The subform essentially looks like this:

Choose Team Choose Player Choose Action Period Time
(dropdown) (dropdown) (dropdown)

The form is created such that when you use Choose Player, you can only
see players who belong to the team you chose in Choose Team. You create
one row for each action that happens in a game.

Both Choose Team and Choose Player are dropdown controls that are bound
to fields in the database and use queries as their Row Source.

My problem is this: let's say I enter one record, as follows:

Choose Team Choose Player Choose Action Period Time
Sharks Smith Goal 2 10:05

No problem. Next, I start a new row for the next action:

Choose Team Choose Player Choose Action Period Time
Devils Davis Goal 3 8:15

However, the moment I choose "Devils" from the Choose Team dropdown,
the Player value for Record # 1 changes to blank. In other words, for
some reason, the Choose Team dropdown is driving the values of not only
the current record for Choose Player, but all records.

Does anyone have any suggestions of where I could look to resolve this
issue? Is there something I need to specify in one of the queries for
these two controls? Or could there be some underlying problem in the
database relationships?

I'm having a really hard time with this. Let me know if you have any
ideas!

Thanks,

Steve Battisti

 

[RickSpr]

This behavior is not what I'd expect from what you described. How about providing the following:
Name of the table the subform is bound to, and its field names.
SQL of the queries used for the combo boxes.
Relationships among any of the tables in the main form, subform, and underlying the queries.

 

[StevenB]

Thanks for offering your help. Here's the information you requested:

Record Source for the subform is the Player_History table, (I assume that's the table the subform is "bound" to; I'm not great with Access terminology yet). The table looks like this:

Name Type Size
Player_Action_I Number (Long) 4
Game_ID Number (Long) 4
Player_ID Number (Long) 4
Action_Type Text 50
Start_Time Text 50
End_Time Text 50
Period Text 50
Penalty_Type Text 50
Penalty_Minutes Text 50
Penalty_Result Text 50
Scratch Yes/No 1
team Number (Long) 4

SQL from the combo boxes:

Choose Team Box:
SELECT Team_Info.Team_ID, Team_Info.Team_Name
FROM Team_Info
ORDER BY Team_Info.Team_ID;

Choose Team also has an after update event:
Private Sub Team1_AfterUpdate()
DoCmd.Requery "Player_ID1"
End Sub

Choose Player Box:
SELECT Player_Info.Player_ID, Player_Info.Jersey_No, Player_Info.Last_Name, Player_Info.First_Name, Player_Info.Team_ID
FROM Player_Info
WHERE (((Player_Info.Team_ID)=[Forms]![frmUpdateGameStatistics]![Player_History subform1].[Form]![Team1]) AND ((Player_Info.Player_Status)="active&quot)
ORDER BY Player_Info.Jersey_No;

What sort of information do you need about the relationships? I'm not sure how to present it. I could always e-mail you an Access analysis if you want. (Heck, I could e-mail you the whole database, it's only 150K zipped. (^_^)

Thanks!
Steve Battisti

 

[RickSpr]

Wow! I think I found your problem. The good news is, your table updates are just fine! They just don't show properly in the subform.

I actually built a copy of your database from the info you provided (which was nice and complete, thank you!).

Here's what's happening. You enter the first player event and everything is fine. When you enter the team for the second player event, you requery the Choose Player combo box. That combo box's internal list now has only players from the second team. It no longer has players from the first team.

But you see, the Choose Player combo box for the first player event is the same combo box! It's repeated, but it's still the same control. So when you requeried it, you changed the list underneath the first player's combo box, too.

Well, now the value in the first player's combo box (the Player_ID is the value, because it's Control Source property is bound to Player_ID) is not in the combo box's list. When this happens, the combo box blanks out its text box part. The data underneath (and in the Player_History table) hasn't changed, but the combo box no longer knows what name to display.

This only happens when what you display in the combo box is something other than the bound column. I'm assuming you set the Column Widths property to 0 (or 0;0 etc., just so the first number is 0). This keeps the Player_ID from displaying, which is what you wanted. But what actually is displayed comes from the combo box's list. If you change the list, the text box part gets updated, and if the Player_ID value is no longer in the list, the text box will display blank.

Ok, so how do you fix it? Unfortunately, you can't make it do what you want, unless you leave all the players in the combo box list. This is just a result of showing multiple records at once, but only having one combo box control and hence only one list with which to translate Player_IDs to names.

I can think of one thing that would work, though. It's kind of tricky.

First, create a query with both your Player_History and Player_Info tables. Access should automatically join them on Player_ID fields; if it doesn't, create that join yourself. Drop the "*" from Player_History into the grid, and drop Last_Name from Player_Info into the grid. Save the query as Player_History_WNames.

Next, go to your subform. Change its RecordSource to Player_History_WNames. Then draw a text box on the form, delete its label if it has one, and set its properties as follows:
Control Source: Last_Name
Enabled: No
Locked: Yes
Back Style: Normal
Back Color: 16777215 (White)
Special Effect: Flat
Border Style: Transparent
Finally, move and resize the text box so that it exactly overlaps the text part of the Choose Player combo box.

Now you won't see what's actually in the combo box any more, you'll see the text box on top of it--and that contains the player name from the query.

Except: When the combo box has the focus, Access brings it to the top temporarily, which means the text box will be hidden. So, if you select a team, etc., then move to a row in the subform with a different team, then click the player combo's dropdown arrow, the combo's text part will go blank. But we can fix that, too.

The problem here is that the combo's list is out of sync with the current record. To make sure they always stay in sync, you need to requery the combo box whenever you change rows in the subform. Fortunately, Access fires a Current event whenever you do this. All you have to do is build an event procedure for On Current in the subform, and DoCmd.Requery "Player_ID1" in it.

I tried this all out, and it seems to work. Hope it works for you, too. Good luck. And hey, if it does, I wouldn't object to getting a vote for my effort (hint, hint ;-)). Rick Sprague

 

[StevenB]

Hey, I'll vote for you whether it worked or not, just for taking the time to give me such a detailed response. I haven't had a chance to play around with it yet (new baby due any day now...)

But, if the workaround you provided is the only way to manage it, I may just remove some of the intelligence and just get rid of the combo box altogether! (^_-)

Steve