Rookie: Broken loop

fluidmind

Registered User.
Local time
Today, 17:47
Joined
Jun 30, 2006
Messages
66
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

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
 
Loops come in many varieties with Until or While at the beginning or end of the loop. Developers have different preferences in various situations.

Until rst.EOF means do up to and including the condition which is why it processes on the EOF.
While Not rst.EOF will stop processing when it finds EOF.

The OK is because Warnings are on. You can turn them off with:
DoCmd.SetWarnings False (note there is no equal sign before the False)

However make sure they are turned back on or ALL warnings in the database will remain off. Appropriate error handling is absolutely essential to ensure they are turned back on even if the sub fails.

Better to use the Execute Method which has no warnings.

However I'm assuming you are creating the records in preparation for filling out the stats later. You could avoid this by structuring your form with subforms.

The MasterLinkField and ChildLinkFields of the subformcontrol synchronise the subform to the current record of the main form recordset. Just add your stats as new records in the subform. Note that the child link field value is automatically added when you insert a new record in the subform.
 
Thank you very much for your help and the thorough explanations. That helped me a lot.

It now works perfectly!
 
Or at least, so I thought.... :-)

It acts very strange! It only inserts the first seven lines into the table. Sometimes it is 8 or 9 lines, but never all of them.

After hours of testing, I have found out, that the problem is related to the number of posts in the "stats" table. When there are plenty of test-entries in there, everything works fine, but when I clear the table of test-data it all of a sudden acts out as discribed above.

It makes absolutely no sense to me...!

Please help!

John


My currernt code is:

Code:
' Go to first record in form

DoCmd.GoToRecord , , acFirst

'Insert every record into table one at a time

DoCmd.SetWarnings False
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 While Not rst.EOF

If spillerID <> "" Then
strSQL1 = ("INSERT INTO stats (spiller, kamp) VALUES (spillerID, kampID)")
DoCmd.RunSQL (strSQL1)
DoCmd.GoToRecord , , acNext
End If
    .MoveNext
  Loop
End With

DoCmd.SetWarnings True

' Open Form called fm_statsheet

  Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "fm_statsheet"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Last edited:
Access is probably guessing at spollerID and kampID since you have not specified where they come from. Surprising it works at all. Never let Access guess anything.

Code:
strSQL1 = "INSERT INTO stats (spiller, kamp) VALUES (" & rst.spillerID & ", " & rst.kampID & ")"

You might also try rst.MoveFirst before you enter the loop to make sure it is at the top of the recordset.

However, why not avoid the whole problem and simply perform the whole task with a single query? I should have noticed this before but I only answered the specific question about the loop last time rather than looking at the context of what you were actually trying to do.
 

Users who are viewing this thread

Back
Top Bottom