For the benefit of others, let me explain that the bitmap showed:
1. A Dream_Team table with key "pk" and field "Team_Name"
2. A Player table with key "pk" and field "Player_Name"
3. A DT_Player table with foreign key "DT_fk" referring to the "pk" field of Dream_Team, and foreign key "Player_fk" referring to the "pk" field of Player_Name. The combination of DT_fk and Player_fk are the key of DT_Player.
4. The relationship from Dream_Team to DT_Player is one-to-many.
5. The relationship from Player_Name to DT_Player is also one-to-many.
Thus, DT_Player is an associative table which resolves and implements the many-to-many relationship between Dream_Team and Player_Name.
Darrylle added the following comments to the diagram:
------------------
"On the form where I want to add PLAYERs to a DREAM_TEAM, I draw (or paint) 11 combo's - 1 for each player.
I can't use a continuos form because I want the player combo's to be in formation as on a football field.
I pull ALL fields from all tables into the orm.
When I choose the first player from a combo, it displays ok, but when a choose a 2nd or 3rd or 4th etc it changes ALL combo's to that value."
------------------
Darrylle, the table structure is exactly what I had gathered from your description. The problem is that you want to have each combo box bound to a different record from the DT_Player table. Forms just don't do that--a form shows only one record, unless it's in datasheet view (in which case you can't control the positions of the controls) or continuous form view (in which case you have multiple copies of the whole form, not just of one control).
All is not lost, though. The combos are all getting set to the same player name because they are bound to the same field. If you make them unbound, they'll be independent of one another. Of course, that means you'll have to update the DT_Player table in code, but that's an unavoidable consequence of what you're trying to do (display data from multiple records on one copy of the form).
However, this is going to be somewhat complicated. For one thing, you have no way (in the tables as given) to indicate which player goes in which position. I should think your DT_Player table needs to have a PositionPlayed field, as a start. That would indicate which combo box a particular DT_Player row should be displayed in. Also, since each position can only be filled once for each team, you should probably make DT_Player's key be the combination of Team_fk and PositionPlayed. (Note that this means DT_Player will no longer be a standard many-to-many associative table, but it will continue to support that relationship.)
When a team is selected, you'll have to use code to look up each position for that team from DT_Player joined to Player, and insert the player's name into the corresponding combo box. Keep in mind that you may not find any row for one or more of the positions. It depends on whether it's a new team being composed, and whether you let them save an incompletely built team.
While the user is selecting players for each position from the combos, you'll have to check for duplicates (can't have one player playing more than one position). How you handle a duplicate is up to you; you might erase the previously existing combo, or give them a MsgBox.
When they're done selecting, they'll have to click a button to save changes (or you can do this automatically when they select a different team or close the form). You'll then use code to add or update records in the DT_Player table, using the Player.pk saved in the combo, the Team.pk from the team selection, and the Position associated with each combo. If you allow them to delete an existing player, you may also have to be prepared to delete the corresponding row from DT_Player.
This all takes a good bit of code, I'm afraid. If you're not up to speed on VBA programming, and DAO for accessing records from code, you've got quite a learning curve ahead of you. You may want to check into a third-party book to learn VBA programming. Unfortunately, I don't think there's any other way you can arrange data from all these separate rows on one form, except by using code. Rick Sprague
