Iterator not changing when used in SQL Statement

[dbonin1599]

I have the following code, which is going through a FOR loop to run an INSERT statement with a nested SELECT statement. The issue is that I am trying to use the interator in the SQL statement, but the number never changes, even though the process is running through the proper number of times. What am I missing? Why is the number not appearing to be changing?

 Sub SetLEAs()
    Dim MyCnt As Integer
    Dim i As Integer
    MYSQL2 = "INSERT INTO DGBTestVariations2 
                      (ID, 
                       FeeSchedule, 
                       LEA, 
                      JURISDICTION, 
                      PROSECUTOR) 
        SELECT ID, 
               FeeSchedule, 
               (SELECT LEA_Agency_Name 
                  FROM LEA_NAMES 
                 WHERE ID= " & MyCnt & ") , 
               JURISDICTION, 
               PROSECUTOR 
        FROM DGBTestVariations 
        WHERE LEA='MuniPD';"

             For MyCnt = 1 To 5
                     Application.SetOption "Confirm Action Queries", 0
                     DoCmd.RunSQL (MYSQL2)
              Next MyCnt
     Application.SetOption "Confirm Action Queries", 1
End Sub

[ajalwaysus]

Your problem is that you are assigning the value to the query, the you are incrementing it, but you don't reassign it.

This is what it should look like to do what you want.

Sub SetLEAs()
    Dim MyCnt As Integer
    Dim i As Integer

    For MyCnt = 1 To 5
        Application.SetOption "Confirm Action Queries", 0

        MYSQL2 = "INSERT INTO DGBTestVariations2 "
                             (ID,
                              FeeSchedule,
                              LEA,
                              JURISDICTION,
                              PROSECUTOR)
                  SELECT ID,
                         FeeSchedule,
                         (SELECT LEA_Agency_Name
                                 FROM LEA_NAMES
                                 WHERE ID= " & MyCnt & ") ,
                                 JURISDICTION,
                                 PROSECUTOR
                           FROM  DGBTestVariations
                          WHERE  LEA='MuniPD';"

        DoCmd.RunSQL (MYSQL2)
    Next MyCnt
    Application.SetOption "Confirm Action Queries", 1
End Sub

You HAVE to reassign a value once you change part of it.

-AJ

[dbonin1599]

Brillant

Thanks you so much!

I was working on this for hours, and just couldn't figure out the issue, I finally found this site and decided to submit the question.

I highly recommend starting at this point from now on, it makes life so much easier.

Of course, you need to get to a point, to demostrate that you have put some thought into the problem and not just use these experts to solve your issues.

That is for the other newbies like me.

AJ, you saved me! and I still have some of my hair left.

Dan

[ajalwaysus]

Glad to help, and welcome to Bytes.

-AJ

[ADezii]

Nice pick up, ajalwaysus. Just a small note to mention, shouldn't

Application.SetOption "Confirm Action Queries", 0

be removed outside of the Loop?

[NeoPa]

You're right ADezii. Line #6 could better be placed before line #5.

This will not cause any serious problems in the execution. Logically it is a little cleaner though.

[dbonin1599]

Thanks for the help I have one more issue

I have one more issue, everytime I run this code it asks me for CountString and MyCnt, as you can see I am assigning them, I got it to work once, but than it quit working correctly. I am sure I made some change that messed it up, but I can't seem to see it.

Ultimately the CountString value will be filled from a select statement, but for testing I just put the value in. What I need to do is parse the individual values from the delimited list create a new record with the individual value and I will then delete the delimited string row. Do I need to assign the ParseWord to a Column name (i.e.- As JURISDICTION)? I think that might complicate things more, since I am assigning the values.

AMENDMENT: I got it to apply the values to CountString and MyCnt, but it still is not working, I get an error "Syntax Error - Missing Operator" with the ParseWord code in the view. Do I need to somehow assign the value of the result of the ParseWord?


Thanks,

Dan

Sub TESTINGDGB()
    Dim AddToSQL As String
    Dim CountString As String
    Dim MyCnt As Integer
    For MyCnt = 1 To 19
    CountString = "19APVAL~19BUR~19EAG~19FARM~19HAST~19IGH~19LAK~19MENHGT~19ROS~19SSP~19WSP~19LILY~19MEN~19SUNLK~19COAT~19HAMP~19MIES~19NTR~19RAN~19VER"
          AddToSQL = "INSERT INTO DGBTestVariations (ID, [Fee Schedule], LEA, JURISDICTION, PROSECUTOR) SELECT ID, [Fee Schedule], LEA, ParseWord(" & CountString & " , " & MyCnt & "  ," & Chr(34) & Chr(126) & Chr(34) & " ) as JURISDICTION , PROSECUTOR FROM DGBTestVariations WHERE JURISDICTION like (" & Chr(34) & Chr(42) & Chr(126) & Chr(42) & Chr(34) & ")"
          DoCmd.RunSQL (AddToSQL)
    Next MyCnt
End Sub

[dbonin1599]

Got it

Thanks for all of the help, I finally figured it out, I need to surround the ParseWord with Chr(34)s to make it a string value.

Sometimes, if you bang your head hard enough on your desk, the answer just appears, along with stars and a few birds. :-)

Cheers,

Dan