Add multiple (2) rows to same table from one form

[Bob Quintal]

@r1g2000hsg.googlegroups.com
:

Is it possible to create a form in such a way that I can enter
data on the form and create two rows in the same table?

I'm creating a database for my school's chess club and I have a
table for playerResults.

I get a little slip of paper with the names of the black and white
player and the result of the game.

I would like to enter a record for both the players into the
PlayerResults table with the number of the player, the color they
were and the result.

At the moment the only way I can see to do this is to create a
different table with a single row that captures the number for
each player and their scores. Then I can do a union of the
results, first for the white and then for the black player, from
data extracted from the joint table.

Thanks for any help.

Would it not be more efficient to have a table which contains the
date of the match, the two player ID numbers, the duration of the
match (if playing speed chess) number of moves and and a code to
indicate the winner or a draw, optionally comments?

Then use a union query to get an individual player's statistics at
report time.

 

[MikeB]

Is it possible to create a form in such a way that I can enter data on
the form and create two rows in the same table?

I'm creating a database for my school's chess club and I have a table
for playerResults.

I get a little slip of paper with the names of the black and white
player and the result of the game.

I would like to enter a record for both the players into the
PlayerResults table with the number of the player, the color they were
and the result.

At the moment the only way I can see to do this is to create a
different table with a single row that captures the number for each
player and their scores. Then I can do a union of the results, first
for the white and then for the black player, from data extracted from
the joint table.

Thanks for any help.

 

[MikeB]

@r1g2000hsg.googlegroups.com
:









Would it not be more efficient to have a table which contains the
date of the match, the two player ID numbers, the duration of the
match (if playing speed chess) number of moves and and a code to
indicate the winner or a draw, optionally comments?

Then use a union query to get an individual player's statistics at
report time.

As I said in the last paragraph, that's more or less the way I see it
possible as well. It's just that I was hoping to directly create the
table from the data entry, rather than running the Union query.

 

[Bob Quintal]

@o42g2000hsc.googlegroups.co
m:

As I said in the last paragraph, that's more or less the way I see
it possible as well. It's just that I was hoping to directly
create the table from the data entry, rather than running the
Union query.

In which case, just write an SQL INSERT INTO statement in the code
that runs on the after_Insert event of the form, that grabs the
textbox values you want and puts them in the table

something like

Dim strSQL as String
strSQL = "INSERT INTO Scores (playerID,score) " _
& "VALUES (" & me.blackID & ", " me.score & ");"
docmd.runsql

 

[David Portwood]

You could use an unbound form with a Submit button to enter the game
results. The OnClick event of the Submit button would open a recordset var
to programmatically add a row to the PlayerResults table and enter the White
player's result, then add another row and enter the Black player's result,
then the recordset var would close itself.

 

[MikeB]

@o42g2000hsc.googlegroups.co
m:





In which case, just write an SQL INSERT INTO statement in the code
that runs on the after_Insert event of the form, that grabs the
textbox values you want and puts them in the table

something like

Dim strSQL as String
strSQL = "INSERT INTO Scores (playerID,score) " _
& "VALUES (" & me.blackID & ", " me.score & ");"
docmd.runsql

Thanks. In the end it looked easier to just go with the Union table,
so I went that route for now.