docmd.runsql doesnt execute in 2nd iteration of for loop

[rhigg]

Hello --

New to the forum and fairly new to vba. I am running a for loop with a run sql command as part of it. It runs correctly the first time, updates the proper table and populates the form. When it comes back again, the value of the counter (i) is correctly incremented, but the sql statement doesn't run. I do NOT get an error message, it just sort of hangs. I check to see if the table are open, thinking that might be it (they are not) so can't figure what I am missing. The msgboxes are simply to check values and let me see where the problem may be. DeleteRecordsSQL function merely deletes the old records in the target table before adding the new ones.

partial code:

For i = 1 To splits

MsgBox "Value of i in Splits =: " & i

DeleteRecordsSQL

strSQL = "SELECT SplitTable.FirstName, SplitTable.lastName, SplitTable.School INTO SparTable FROM SplitTable WHERE splitTable.Split = " & i & ""

IsObjectOpen ("SparTable")
IsObjectOpen ("SplitTable")

DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True

MsgBox "SparTable updated"

Set rsteventsplit = dbsTournament.OpenRecordset("SparTable")

MsgBox "passing value of i - number of splits -- to cmdpopulategrid function: " & i
DoCmd.OpenForm "SparringSplits", acNormal

cmdPopulateGrid (i)

Next

On Error GoTo Error_Handler
Dim Rpt As Report

Error_Handler_Exit:
On Error Resume Next
Set Rpt = Nothing
Exit Sub

Error_Handler:
MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
" Error Number: " & Err.Number & vbCrLf & _
" Error Source: RedefRptSQL" & vbCrLf & _
" Error Description: " & Err.Description, _
vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit

(END partial Code)

Any help is appreciated

 

[MarkK]

Hey, welcome to the forum. You don't need a loop here, you can just do . . .

SQL = _
   "SELECT SplitTable.FirstName, SplitTable.lastName, SplitTable.School " & _
   "INTO SparTable  " & _
   "FROM SplitTable " & _
   "WHERE splitTable.Split > 0 " & _
      "AND splitTable.Split <= " & splits
CurrentDb.Execute SQL, dbFailOnError

But there are a number if irregularities in the code too, like you don't need parenthesis around parameters when you don't return a value from a call to a subroutine, so . . .

IsObjectOpen "SparTable"
IsObjectOpen "SplitTable"
...
DoCmd.RunSQL strSQL
...
cmdPopulateGrid i

As opposed to here, where you do return a value, so you do enclose the parameter in parenthesis . . .

Set rsteventsplit = dbsTournament.OpenRecordset[COLOR="Red"]("SparTable")[/COLOR]

Hope this helps,

 

[rhigg]

In this case I believe I do need a loop as I am taking a large number of competitors in an event and splitting them into separate groups. Initially, I take a group of (ex 16 people in an event and split it 3 ways (6, 5, 5) Each of the original records is updated to show if the competitor is part of split 1, 2 or 3.

I then want to loop through, grab each from split 1, populate a form, print the form, then close it, so I can physically release that sub(split) group to a competition ring. Thus I want to loop through and grab split 2, then 3, etc

If I use your suggested sql of >0 or <= splits, I'll get all records.

I should have been more specific as to why I am using the For loop.

Is there some oddity of running the SQL again that I am missing?

 

[MarkK]

SELECT...INTO...FROM creates a table. An error occurs if a table with the same name already exists, which is probably what happens in your second loop.

 

[rhigg]

I am a complete dunce.

I found my problem in that I opened the recordset in the subroutine, and had an additional rstable = open recordset in the function (2 different variable names). Once corrected, the loop executes just like I always knew it should

Done in again by cut and paste...

MarkK -- thanks for your kind assistance