Looping RunSQL statement fails 1

[oticonaus]

I want to insert x records to a table based on some fields in a form, where x is a number entered by the user.

Seemed, simple, based on my code below. However, when I run the code, it writes the first record correctly, then writes the second record twice, the third record four times, the fourth record eight times....

Is there a problem using DoCmd.RunSQL in a For...Next loop? I know there are probably fancier ways using recordsets, etc, but for the purpose, RunSQL (almost!) works fine.

' Delete any existing temp records
Dim ssql As String
ssql = "DELETE [CSO Guarantee Details].*, [CSO Guarantee Details].Temp FROM [CSO Guarantee Details] WHERE ((([CSO Guarantee Details].Temp)=Yes))"
DoCmd.RunSQL (ssql)
    
' Add new temp records
Dim i As Integer, Months As Integer
Dim CSOCode As String, SMonth As String
CSOCode = Me.cmbCSO.Value
Months = Me.txtDuration
For i = 0 To Months - 2
    PDate = DateAdd("m", i, Me.txtStartDate)
    SMonth = Format(DateAdd("m", -1, PDate), "mmm")
    PaidComm = Me.txtPerAmount
    ssql = "INSERT INTO [CSO Guarantee Details] ( CSOCode, [Payment Date], [Sales Month], [Paid Commission], Temp ) SELECT '" & CSOCode & "' AS A, " & PDate & " AS B, '" & SMonth & "' AS C, " & PaidComm & " AS D, Yes AS Expr1 FROM [CSO Guarantee Details]"
    DoCmd.RunSQL ssql
Next
' Add the last record to ensure last amount is correct (rounding)
PDate = DateAdd("m", i + 1, Me.txtStartDate)
SMonth = Format(DateAdd("m", -1, PDate), "mmm")
PaidComm = Me.txtTotalValue - (Me.txtPerAmount * (Me.txtDuration - 1))
ssql = "INSERT INTO [CSO Guarantee Details] ( CSOCode, [Payment Date], [Sales Month], [Paid Commission], Temp ) SELECT '" & CSOCode & "' AS A, " & PDate & " AS B, '" & SMonth & "' AS C, " & PaidComm & " AS D, Yes AS Expr1 FROM [CSO Guarantee Details]"
DoCmd.RunSQL ssql

 

[TheAceMan1]

How are ya oticonaus . . .

My first stop was in the delete query:

[blue]WHERE ((([CSO Guarantee Details].Temp)=[purple][b]Yes[/b][/purple]))[/blue]

If Yes is a string then [blue]'Yes'[/blue] is proper.

If Yes is a yes/no field then [blue]True[/blue] is proper.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[oticonaus]

Fair point. Although the DELETE query runs fine with both options - changing it to true does not help the problem of the looping issue.

 

[TheAceMan1]

oticonaus . . .

I'm just taking it as you've presented it and no matter how you look at it the Yes syntax is wrong! At least correct it!

What is the source of [blue]CSOCode, PDate, SMonth, PaidComm[/blue], Yes (you already know how to correct Yes)?

See Ya! . . . . . .

 

[oticonaus]

I have corrected the code for the Yes/No field so that all three SQL statement use the correct True value instead of Yes.

ssql = "DELETE [CSO Guarantee Details].*, [CSO Guarantee Details].Temp FROM [CSO Guarantee Details] WHERE ((([CSO Guarantee Details].Temp)=true))"

ssql = "INSERT INTO [CSO Guarantee Details] ( CSOCode, [Payment Date], [Sales Month], [Paid Commission], Temp ) SELECT '" & CSOCode & "' AS A, " & PDate & " AS B, '" & SMonth & "' AS C, " & PaidComm & " AS D, " & True & " AS Expr1 FROM [CSO Guarantee Details]"

ssql = "INSERT INTO [CSO Guarantee Details] ( CSOCode, [Payment Date], [Sales Month], [Paid Commission], Temp ) SELECT '" & CSOCode & "' AS A, " & PDate & " AS B, '" & SMonth & "' AS C, " & PaidComm & " AS D, True AS Expr1 FROM [CSO Guarantee Details]"

The fields CSOCode, PDate, SMonth, PaidComm, Yes all come from a table called [CSO Guarantee Details]. The values for these fields come from a form

CSOCode : dropdown box (Me.cmbCSO.Value)
PDate : text box (Me.txtStartDate)
Smonth : calculated (Format(DateAdd("m", -1, PDate), "mmm"))
PaidComm : left empty
Yes : set value - True

 

[TheAceMan1]

Roger That oticonaus . . .

Replace the [blue]ssql[/blue] SQL in your [blue]For/Next[/blue] loop with the following:

[blue]   ssql = "INSERT INTO [CSO Guarantee Details] (" & _
                       "CSOCode, " & _
                      "[Payment Date], " & _
                      "[Sales Month], " & _
                      "[Paid Commission], " & _
                       "Temp) " & _
          "Values ('" & CSOCode & "', " & _
                        PDate & ", " & _
                  "'" & SMonth & "', " & _
                        PaidComm & ", " & _
                       "True);"[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .