Combo-box linking?

[witney]

I am attempting to set up my first Access database, and I have chosen to base it on my local football (soccer) team. The basic structure of the database is based around a "Match" table. This includes date, opponents, venue, competition, etc. I then have tables linked in with this main table. One of these is a "MatchPlayers" table. This tells the "Match" table which players played in that match, and in what position (ie; Goalkeeper, shirt 2, shirt 3, sub 1, etc.). A "Players" table is then linked to the "MatchPlayers" table. Also linked to the "MatchPlayers" table is a "Goals" table. This states how many goals each particular player scored in that game. I have set all these tables up and linked them in a way that I am confident in their working. However, I now what to create a form for entering all this match information.
I have done the easy bit of creating drop-down boxes for the opponents and the venue, etc. but what I am having trouble with is selecting the players that played in that match. I have a vision of my form being set out like a pitch, with the positions being drop-down boxes, in which I can select the player that played in that position. How can I set up a drop-down box to link a player with a certain position for a certain match? In a way, the box result links to two fields. How can this be done?
I would appreciate any suggestions, but please bear in mind that I am a bit of a MS Access novice. Hope someone can help.

Thanks
Mark

 

[D-Fresh]

You can set that up pretty easily... Build your form with a recordsource of "MatchPlayers". Then position the fields on your form(You mentioned you want it to resemble the field.) and then change them to combo boxes. Then all you have to do is set each combo boxes control Source to your "Players" table. Go to the properties sheets and set the "Limit to List" property to yes. You should be able to go from here. Let me know if you can't. Hope this helps.

Doug

 

[witney]

Thanks for the reply. However, I have tried your answer and got stuck again with the same problem. I am not sure if you have fully understood my question, or whether I haven't fully understood your answer. I'll try to make things a bit clearer. My "Matches" table consists of the fields MatchID (Autonumber), Date, Venue, Opponents, etc. My "MatchPlayers" table has the fields MatchID (linked with "Matches" table), PlayerID (linked with a "Players" table) and Position. The trouble is getting a form where I can select the players (which I can pick up from the "Players" table okay) into the various positions. How does Access know what the positions are? Do I need another table with the positions in? If so, how do I incorporate the "Position" table into the relationships and then how do I create a form with a field for Position 1, to be able to select the player which played in that position for that match from the list? The "MatchPLayers" table would then store the Match number, the player, and what position they played in, automatically.
Looking forward to your response.

Thanks for your help.
Mark