Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Subform rows not independent of one another

Subform rows not independent of one another

Subform rows not independent of one another


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


Steve Battisti

RE: Subform rows not independent of one another

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.

RE: Subform rows not independent of one another

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"))
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. (^_^)

Steve Battisti

RE: Subform rows not independent of one another

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

RE: Subform rows not independent of one another

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! (^_-)


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close