Using SQL in Do Until Loop

[dbrother]

Access 2003 Win XP Pro SP3
Using SQL /ADO Recordsets in a Do Loop

Hello,
I'm using a random number generator based on an integer input from a user from a form that will get X number of random records from an external Oracle source using a SQL statement. The SQL statement works as expected when the loop code is commented out, but I receive an error "SQL command not properly ended" when the loop is active.

Do Until intLoop = UserNum

    RandNum = Int((HighNum - LowNum + 1) * Rnd + LowNum)
    strRandNum = RandNum
    strSQL_LOC = DLookup("LOC", "tblALL_RACK_LOCS", "[ID] = '" & strRandNum & "'")
    
   
'SQL = "INSERT INTO tblExport_Data "
SQL = SQL & "SELECT DISTINCT ORAINV.MTL_ITEM_LOCATIONS.SEGMENT1 as ""LOC"", APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.SEGMENT1 as ""ITEM"", "
'SQL = SQL & "(SELECT DISTINCT ORAINV.MTL_ITEM_LOCATIONS.SEGMENT1 as ""LOC"", APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.SEGMENT1 as ""ITEM"", "
SQL = SQL & "APPS_ORAFND.MTL_ITEM_QUANTITIES_VIEW.QUANTITY as ""QTY"", APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.PRIMARY_UNIT_OF_MEASURE as ""UOM"", "
SQL = SQL & "APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.DESCRIPTION AS ""DESC"" "
SQL = SQL & "FROM APPS_ORAFND.MTL_SYSTEM_ITEMS_VL, "
SQL = SQL & "ORAINV.MTL_ITEM_LOCATIONS, APPS_ORAFND.MTL_ITEM_QUANTITIES_VIEW "
SQL = SQL & "WHERE (APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.ORGANIZATION_ID=5609 "
SQL = SQL & "AND ORAINV.MTL_ITEM_LOCATIONS.SEGMENT1='" & strSQL_LOC & "') "
'SQL = SQL & "  AND ORAINV.MTL_ITEM_LOCATIONS.SUBINVENTORY_CODE IN('STORES','FG STAGE','WIRE','TOOL','RECEIVING','MRB','CONSIGN','PACK','10','11','12','13','14','15','16','20','21','24','25','26','27','28','29','30','31','86','87','90','99'))"
'SQL = SQL & "  AND ORAINV.MTL_ITEM_LOCATIONS.SUBINVENTORY_CODE LIKE('%STAGE%')"
'SQL = SQL & "   OR ORAINV.MTL_ITEM_LOCATIONS.SUBINVENTORY_CODE='FG STAGE'"
SQL = SQL & "AND  ((APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.INVENTORY_ITEM_ID=APPS_ORAFND.MTL_ITEM_QUANTITIES_VIEW.INVENTORY_ITEM_ID) "
SQL = SQL & "AND (APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.ORGANIZATION_ID=APPS_ORAFND.MTL_ITEM_QUANTITIES_VIEW.ORGANIZATION_ID) "
SQL = SQL & "AND (APPS_ORAFND.MTL_ITEM_QUANTITIES_VIEW.ORGANIZATION_ID=ORAINV.MTL_ITEM_LOCATIONS.ORGANIZATION_ID) "
SQL = SQL & "AND (APPS_ORAFND.MTL_ITEM_QUANTITIES_VIEW.LOCATOR_ID=ORAINV.MTL_ITEM_LOCATIONS.INVENTORY_LOCATION_ID)) "
SQL = SQL & "ORDER BY ORAINV.MTL_ITEM_LOCATIONS.SEGMENT1 ASC"

Set RS1 = Cn.Execute(SQL)
    
 With RS0
        .Source = "tblExport_Data"
        Set .ActiveConnection = Cn0
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        
End With

RS0.Open
      Do While Not RS1.EOF
         RS0.AddNew
         RS0![Loc] = RS1![Loc]
         RS0![Item] = RS1![Item]
         RS0![QTY] = RS1![QTY]
         RS0![UOM] = RS1![UOM]
         RS0![DESC] = RS1![DESC]
       RS0.Update
       RS1.MoveNext

    Debug.Print SQL
    Loop

     
   
        'Debug.Print "RS1"
     If RS1.RecordCount = 0 Then
          MsgBox "1 EMPTY LOCATION", vbExclamation, "RACK AUDIT"
          Me.lblSTATUS.Caption = "READY"
          Me.Repaint
          End
     End If
     
     Set RS1 = Nothing
Loop

Once the recordset is updated to the table, it exports out into Excel. The problem I'm having is that if it tries to go back and generate the > than 1st loop, I receive the error message.

Any help appreciated,
dbrother

[NeoPa]

Does it run fine first time through the loop?

If so, it's because you've commented out your original first line (The one which resets the SQL variable from the previous iteration through the loop).

PS. Just read your trailing explanation (after the code), so you can bet that's the problem.

[dbrother]

I'm not seeing the commented line to which you are referring...
There are 3 lines commented in the code that I posted.
2 lines of SQL statement that aren't/shouldn't be used and 1 Debug.Print

If you could reference line number or paste the code line, I would be grateful.

Thanks for the quick response.

[NeoPa]

No worries (and you're absolutely right I should have posted the line #).

The line is #8. Notice the different format used. That format is necessary for the first line (of the SQL building code) only.

[dbrother]

Doh! I new that I was overlooking something easy.
Thanks for the reply.

Working code for random number generator using SQL to get recordset:

Randomize
DoCmd.SetWarnings False
 
Do Until intLoop = UserNum

    RandNum = Int((HighNum - LowNum + 1) * Rnd + LowNum)
    strRandNum = RandNum
    strSQL_LOC = DLookup("LOC", "tblALL_RACK_LOCS", "[ID] = '" & strRandNum & "'")
    
   
'SQL = "INSERT INTO tblExport_Data "
SQL = "SELECT DISTINCT ORAINV.MTL_ITEM_LOCATIONS.SEGMENT1 as ""LOC"", APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.SEGMENT1 as ""ITEM"", "
'SQL = SQL & "(SELECT DISTINCT ORAINV.MTL_ITEM_LOCATIONS.SEGMENT1 as ""LOC"", APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.SEGMENT1 as ""ITEM"", "
SQL = SQL & "APPS_ORAFND.MTL_ITEM_QUANTITIES_VIEW.QUANTITY as ""QTY"", APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.PRIMARY_UNIT_OF_MEASURE as ""UOM"", "
SQL = SQL & "APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.DESCRIPTION AS ""DESC"" "
SQL = SQL & "FROM APPS_ORAFND.MTL_SYSTEM_ITEMS_VL, "
SQL = SQL & "ORAINV.MTL_ITEM_LOCATIONS, APPS_ORAFND.MTL_ITEM_QUANTITIES_VIEW "
SQL = SQL & "WHERE (APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.ORGANIZATION_ID=5609 "
SQL = SQL & "AND ORAINV.MTL_ITEM_LOCATIONS.SEGMENT1='" & strSQL_LOC & "') "
'SQL = SQL & "  AND ORAINV.MTL_ITEM_LOCATIONS.SUBINVENTORY_CODE IN('STORES','FG STAGE','WIRE','TOOL','RECEIVING','MRB','CONSIGN','PACK','10','11','12','13','14','15','16','20','21','24','25','26','27','28','29','30','31','86','87','90','99'))"
'SQL = SQL & "  AND ORAINV.MTL_ITEM_LOCATIONS.SUBINVENTORY_CODE LIKE('%STAGE%')"
'SQL = SQL & "   OR ORAINV.MTL_ITEM_LOCATIONS.SUBINVENTORY_CODE='FG STAGE'"
SQL = SQL & "AND  ((APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.INVENTORY_ITEM_ID=APPS_ORAFND.MTL_ITEM_QUANTITIES_VIEW.INVENTORY_ITEM_ID) "
SQL = SQL & "AND (APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.ORGANIZATION_ID=APPS_ORAFND.MTL_ITEM_QUANTITIES_VIEW.ORGANIZATION_ID) "
SQL = SQL & "AND (APPS_ORAFND.MTL_ITEM_QUANTITIES_VIEW.ORGANIZATION_ID=ORAINV.MTL_ITEM_LOCATIONS.ORGANIZATION_ID) "
SQL = SQL & "AND (APPS_ORAFND.MTL_ITEM_QUANTITIES_VIEW.LOCATOR_ID=ORAINV.MTL_ITEM_LOCATIONS.INVENTORY_LOCATION_ID)) "
SQL = SQL & "ORDER BY ORAINV.MTL_ITEM_LOCATIONS.SEGMENT1 ASC"

Set RS1 = Cn.Execute(SQL)
    
 With RS0
        .Source = "tblExport_Data"
        Set .ActiveConnection = Cn0
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        
End With

RS0.Open
      Do While Not RS1.EOF
         RS0.AddNew
         RS0![Loc] = RS1![Loc]
         RS0![Item] = RS1![Item]
         RS0![QTY] = RS1![QTY]
         RS0![UOM] = RS1![UOM]
         RS0![DESC] = RS1![DESC]
       RS0.Update
       RS1.MoveNext

    Debug.Print SQL
    Loop

     
   
        'Debug.Print "RS1"
     If RS1.RecordCount = 0 Then
          MsgBox "1 EMPTY LOCATION", vbExclamation, "RACK AUDIT"
          Me.lblSTATUS.Caption = "READY"
          Me.Repaint
          End
     End If
     
     intLoop = intLoop + 1
     Set RS1 = Nothing
     RS0.Close
Loop

[NeoPa]

No worries. I'm actually glad it was that simple. I prefer the easy life ;)

Welcome to Bytes!