By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,446 Members | 2,998 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,446 IT Pros & Developers. It's quick & easy.

Using SQL in Do Until Loop

P: 19
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.

Expand|Select|Wrap|Line Numbers
  1. Do Until intLoop = UserNum
  2.  
  3.     RandNum = Int((HighNum - LowNum + 1) * Rnd + LowNum)
  4.     strRandNum = RandNum
  5.     strSQL_LOC = DLookup("LOC", "tblALL_RACK_LOCS", "[ID] = '" & strRandNum & "'")
  6.  
  7.  
  8. 'SQL = "INSERT INTO tblExport_Data "
  9. SQL = SQL & "SELECT DISTINCT ORAINV.MTL_ITEM_LOCATIONS.SEGMENT1 as ""LOC"", APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.SEGMENT1 as ""ITEM"", "
  10. 'SQL = SQL & "(SELECT DISTINCT ORAINV.MTL_ITEM_LOCATIONS.SEGMENT1 as ""LOC"", APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.SEGMENT1 as ""ITEM"", "
  11. SQL = SQL & "APPS_ORAFND.MTL_ITEM_QUANTITIES_VIEW.QUANTITY as ""QTY"", APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.PRIMARY_UNIT_OF_MEASURE as ""UOM"", "
  12. SQL = SQL & "APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.DESCRIPTION AS ""DESC"" "
  13. SQL = SQL & "FROM APPS_ORAFND.MTL_SYSTEM_ITEMS_VL, "
  14. SQL = SQL & "ORAINV.MTL_ITEM_LOCATIONS, APPS_ORAFND.MTL_ITEM_QUANTITIES_VIEW "
  15. SQL = SQL & "WHERE (APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.ORGANIZATION_ID=5609 "
  16. SQL = SQL & "AND ORAINV.MTL_ITEM_LOCATIONS.SEGMENT1='" & strSQL_LOC & "') "
  17. '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'))"
  18. 'SQL = SQL & "  AND ORAINV.MTL_ITEM_LOCATIONS.SUBINVENTORY_CODE LIKE('%STAGE%')"
  19. 'SQL = SQL & "   OR ORAINV.MTL_ITEM_LOCATIONS.SUBINVENTORY_CODE='FG STAGE'"
  20. SQL = SQL & "AND  ((APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.INVENTORY_ITEM_ID=APPS_ORAFND.MTL_ITEM_QUANTITIES_VIEW.INVENTORY_ITEM_ID) "
  21. SQL = SQL & "AND (APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.ORGANIZATION_ID=APPS_ORAFND.MTL_ITEM_QUANTITIES_VIEW.ORGANIZATION_ID) "
  22. SQL = SQL & "AND (APPS_ORAFND.MTL_ITEM_QUANTITIES_VIEW.ORGANIZATION_ID=ORAINV.MTL_ITEM_LOCATIONS.ORGANIZATION_ID) "
  23. SQL = SQL & "AND (APPS_ORAFND.MTL_ITEM_QUANTITIES_VIEW.LOCATOR_ID=ORAINV.MTL_ITEM_LOCATIONS.INVENTORY_LOCATION_ID)) "
  24. SQL = SQL & "ORDER BY ORAINV.MTL_ITEM_LOCATIONS.SEGMENT1 ASC"
  25.  
  26. Set RS1 = Cn.Execute(SQL)
  27.  
  28.  With RS0
  29.         .Source = "tblExport_Data"
  30.         Set .ActiveConnection = Cn0
  31.         .CursorType = adOpenKeyset
  32.         .LockType = adLockOptimistic
  33.  
  34. End With
  35.  
  36. RS0.Open
  37.       Do While Not RS1.EOF
  38.          RS0.AddNew
  39.          RS0![Loc] = RS1![Loc]
  40.          RS0![Item] = RS1![Item]
  41.          RS0![QTY] = RS1![QTY]
  42.          RS0![UOM] = RS1![UOM]
  43.          RS0![DESC] = RS1![DESC]
  44.        RS0.Update
  45.        RS1.MoveNext
  46.  
  47.     Debug.Print SQL
  48.     Loop
  49.  
  50.  
  51.  
  52.         'Debug.Print "RS1"
  53.      If RS1.RecordCount = 0 Then
  54.           MsgBox "1 EMPTY LOCATION", vbExclamation, "RACK AUDIT"
  55.           Me.lblSTATUS.Caption = "READY"
  56.           Me.Repaint
  57.           End
  58.      End If
  59.  
  60.      Set RS1 = Nothing
  61. Loop
  62.  
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
Feb 17 '09 #1
Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 31,494
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.
Feb 17 '09 #2

P: 19
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.
Feb 17 '09 #3

NeoPa
Expert Mod 15k+
P: 31,494
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.
Feb 17 '09 #4

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

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


Expand|Select|Wrap|Line Numbers
  1. Randomize
  2. DoCmd.SetWarnings False
  3.  
  4. Do Until intLoop = UserNum
  5.  
  6.     RandNum = Int((HighNum - LowNum + 1) * Rnd + LowNum)
  7.     strRandNum = RandNum
  8.     strSQL_LOC = DLookup("LOC", "tblALL_RACK_LOCS", "[ID] = '" & strRandNum & "'")
  9.  
  10.  
  11. 'SQL = "INSERT INTO tblExport_Data "
  12. SQL = "SELECT DISTINCT ORAINV.MTL_ITEM_LOCATIONS.SEGMENT1 as ""LOC"", APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.SEGMENT1 as ""ITEM"", "
  13. 'SQL = SQL & "(SELECT DISTINCT ORAINV.MTL_ITEM_LOCATIONS.SEGMENT1 as ""LOC"", APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.SEGMENT1 as ""ITEM"", "
  14. SQL = SQL & "APPS_ORAFND.MTL_ITEM_QUANTITIES_VIEW.QUANTITY as ""QTY"", APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.PRIMARY_UNIT_OF_MEASURE as ""UOM"", "
  15. SQL = SQL & "APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.DESCRIPTION AS ""DESC"" "
  16. SQL = SQL & "FROM APPS_ORAFND.MTL_SYSTEM_ITEMS_VL, "
  17. SQL = SQL & "ORAINV.MTL_ITEM_LOCATIONS, APPS_ORAFND.MTL_ITEM_QUANTITIES_VIEW "
  18. SQL = SQL & "WHERE (APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.ORGANIZATION_ID=5609 "
  19. SQL = SQL & "AND ORAINV.MTL_ITEM_LOCATIONS.SEGMENT1='" & strSQL_LOC & "') "
  20. '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'))"
  21. 'SQL = SQL & "  AND ORAINV.MTL_ITEM_LOCATIONS.SUBINVENTORY_CODE LIKE('%STAGE%')"
  22. 'SQL = SQL & "   OR ORAINV.MTL_ITEM_LOCATIONS.SUBINVENTORY_CODE='FG STAGE'"
  23. SQL = SQL & "AND  ((APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.INVENTORY_ITEM_ID=APPS_ORAFND.MTL_ITEM_QUANTITIES_VIEW.INVENTORY_ITEM_ID) "
  24. SQL = SQL & "AND (APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.ORGANIZATION_ID=APPS_ORAFND.MTL_ITEM_QUANTITIES_VIEW.ORGANIZATION_ID) "
  25. SQL = SQL & "AND (APPS_ORAFND.MTL_ITEM_QUANTITIES_VIEW.ORGANIZATION_ID=ORAINV.MTL_ITEM_LOCATIONS.ORGANIZATION_ID) "
  26. SQL = SQL & "AND (APPS_ORAFND.MTL_ITEM_QUANTITIES_VIEW.LOCATOR_ID=ORAINV.MTL_ITEM_LOCATIONS.INVENTORY_LOCATION_ID)) "
  27. SQL = SQL & "ORDER BY ORAINV.MTL_ITEM_LOCATIONS.SEGMENT1 ASC"
  28.  
  29. Set RS1 = Cn.Execute(SQL)
  30.  
  31.  With RS0
  32.         .Source = "tblExport_Data"
  33.         Set .ActiveConnection = Cn0
  34.         .CursorType = adOpenKeyset
  35.         .LockType = adLockOptimistic
  36.  
  37. End With
  38.  
  39. RS0.Open
  40.       Do While Not RS1.EOF
  41.          RS0.AddNew
  42.          RS0![Loc] = RS1![Loc]
  43.          RS0![Item] = RS1![Item]
  44.          RS0![QTY] = RS1![QTY]
  45.          RS0![UOM] = RS1![UOM]
  46.          RS0![DESC] = RS1![DESC]
  47.        RS0.Update
  48.        RS1.MoveNext
  49.  
  50.     Debug.Print SQL
  51.     Loop
  52.  
  53.  
  54.  
  55.         'Debug.Print "RS1"
  56.      If RS1.RecordCount = 0 Then
  57.           MsgBox "1 EMPTY LOCATION", vbExclamation, "RACK AUDIT"
  58.           Me.lblSTATUS.Caption = "READY"
  59.           Me.Repaint
  60.           End
  61.      End If
  62.  
  63.      intLoop = intLoop + 1
  64.      Set RS1 = Nothing
  65.      RS0.Close
  66. Loop
Feb 18 '09 #5

NeoPa
Expert Mod 15k+
P: 31,494
No worries. I'm actually glad it was that simple. I prefer the easy life ;)

Welcome to Bytes!
Feb 18 '09 #6

Post your reply

Sign in to post your reply or Sign up for a free account.