Looped Integer Variable will not Pass into Query (1 Viewer)

[ss279577]

Hi All,

I'm working on a bit of an odd project that requires me to loop through a range of values specified by two textboxes within a form. The reason for the loop is that the field that needs updating is a multivalue field that I can only get to update one at a time. I've succesfully put together the following code - the issue I am running into is that the "PRange" value becomes a 0 within the SQL string. The correct value does show up in the first message box I set up for testing purposes, but the query in the subsequent box shows a 0 value.

Any thoughts as to why this would be? I believe my syntax is correct for including the variable within the SQL string. Am I missing something else? Any insight is much appreciated!

Private Sub Command13_Click()
Dim PRange As Integer
Dim strSQL As String

strSQL = "INSERT INTO Tasks ( Projects.[Value] )" & _
        "VALUES (" & PRange & ")" & _
        "WHERE (((Tasks.ID) <4 and (Tasks.ID) >1 ));"

For PRange = Forms("Update Tasks with new Projects").Begin_Project_ID To Forms("Update Tasks with new Projects").End_Project_ID
  MsgBox PRange
  MsgBox strSQL
  DoCmd.RunSQL strSQL
    
   Next PRange

 End Sub

 

[ss279577]

Wow, scratching my head for not doing this earlier, but moving the SQL string into the FOR loop fixed it. Hope this helps someone else!

 

[pbaldy]

Thanks for posting your solution and welcome to the site!

As you've found, the variable isn't "dynamic". The SQL string gets whatever the value of the variable is at the time the SQL string is set, so in your case it has to be set inside the loop.

 

[MarkK]

I can't see how the WHERE clause in this SQL does anything. You are inserting a row, right? And there is no SELECT clause? So what does the WHERE apply to?

INSERT INTO Tasks 
   ( Projects.Value )
VALUES 
   ( 1 )
WHERE Tasks.ID < 4 and Tasks.ID > 1

Does that query actually run?