Appending a group of players to enter a game.

[Tommy888]

Hi,

What I essentially am trying to do is to select a group that contains players, then assign that group to a game, the players will all then be assigned using a seperate entry ID to the game instead of one group entry ID, reason being is that each player has their own respective result. In a Nutshell the user chooses a group, then all the players who have that group name are automatically entered into the game.

My Database design is as follows:

tbl_players
Player_ID (pk)
Name
Surname
Preffered side

tbl_GroupMembers
Player_ID (pk)
Group Name (pk)

tbl_groups
Group Name (pk)
Description

tbl_GameRegistration
Entry_ID (pk)
Player_ID
Game_ID

tbl_Result
Entry ID (pk)
Round number (pk)
Win/Loose/Draw

So a player can be selected an enter a game, I need to do the same but essentially for a group of people all at once.

Any ideas how I would go about doing this with VBA, I know that I will have a query to Select which group I want first but from there I am confused.

Thanks,

Tom

 

[Banana]

Aircode:

PARAMETERS lGame_ID Long, sGroupName Text
INSERT INTO tbl_GameRegistration (Player_ID, Game_ID) 
SELECT Player_ID, [lGame_ID] AS Game_ID 
FROM tbl_GroupMembers 
WHERE Group_Name=[sGroupName];

(Assuming that you want Game ID to be same for all appends, which you have to supply as well the Group Name.)

HTH.

 

[Tommy888]

Thats great Banana, unfortunatley Im having a error when I come to run it though. I just made a quick query to check it out (as a append query type) and put in the code, now it has the error 'Syntax error in PARAMETER clause.'
Any ideas why it would be doing this?

 

[Banana]

I forgot two thing-

Text need length defintion... Text(255) may do.

The parameter clause should be a separate statement so it needs ; at the end.

See if it works out.

 

[Tommy888]

Thanks for your time, thats worked perfectly.

 

[Banana]

and thanks for reporting back with success.

Best of luck with your project!