Hi guys!
Hope you can help me with this one. I have a form listing a number of players from a query. When clicking the okay button I wish to create a line in the table "stats" for each player listed in the form. In the player table, the playerID and the gameID (taken from a text-box in the form header) is inserted into the "player" and "game" columns.
I have not created a loop before, but my guess is, that it is needed to do this. What I've tried to do is this: Insert the current line, skip to next form post, go to next line in the table, repeat until end of file.
The code underneath gives me two problems: 1) It does not stop when the last form post is reached. It tries to add a new and empty post, which results in an error stopping the entire button action process. 2) I have to press "OK" for each line that is added to the table.
Here is my code. Thank you in advance!
J
Hope you can help me with this one. I have a form listing a number of players from a query. When clicking the okay button I wish to create a line in the table "stats" for each player listed in the form. In the player table, the playerID and the gameID (taken from a text-box in the form header) is inserted into the "player" and "game" columns.
I have not created a loop before, but my guess is, that it is needed to do this. What I've tried to do is this: Insert the current line, skip to next form post, go to next line in the table, repeat until end of file.
The code underneath gives me two problems: 1) It does not stop when the last form post is reached. It tries to add a new and empty post, which results in an error stopping the entire button action process. 2) I have to press "OK" for each line that is added to the table.
Here is my code. Thank you in advance!
J
Code:
Dim dbs As DAO.database
Dim rst As DAO.Recordset
Dim strSQL As String
Set dbs = CurrentDb()
strSQL = "SELECT * FROM stats"
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
With rst
Do Until rst.EOF
strSQL1 = ("INSERT INTO stats (player, game) VALUES (playerID, gameID)")
DoCmd.RunSQL (strSQL1)
DoCmd.GoToRecord , , acNext
.MoveNext
Loop
End With