473,840 Members | 1,420 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Using SQL in Do Until Loop

19 New Member
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
5 9293
NeoPa
32,584 Recognized Expert Moderator MVP
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
dbrother
19 New Member
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
32,584 Recognized Expert Moderator MVP
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
dbrother
19 New Member
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
32,584 Recognized Expert Moderator MVP
No worries. I'm actually glad it was that simple. I prefer the easy life ;)

Welcome to Bytes!
Feb 18 '09 #6

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

Similar topics

8
15686
by: Eric | last post by:
Let me start off by saying I have VB working model which means no help. I have searched MSDN. It seems that I get "Read the help" instead of answers which is totally useless to me. With that said can someone please help me understand why this does not work? Dim x As String Private Sub cmdlogin_Click() Do Until x = "Corey" If txtlogin = "Corey" Then
5
5996
by: John Dumais | last post by:
Hello, I have been trying to figure out how to write an array of doubles (in this specific case) to a binary stream without using a loop. What I have been doing is... foreach(double d in TraceData) { instanceOfBinaryWriter.Write(d); }
3
10165
by: minguskhan | last post by:
Does anyone know how to reverse a string using a loop?
15
51864
by: shannon | last post by:
Hello, I am wondering if a Do until loop can be used in Javascript. I have an array and want to fill the array 10 times with the users details until it reaches 10 or if they press cancel. I'm using window.prompt to get user info. Any suggestions. Do { //ask user to input their details } while (myairline !=10);
4
144025
by: Madhavi | last post by:
Hi Is there any Do Until Loop in C# Maadhavi
1
4340
khalidbaloch
by: khalidbaloch | last post by:
hi every one, how are you folf , hope fine dear Friends i want to get values of multi-dimensional arrays using foreach loop and after that print out the html using an other while loop , i tried alot but did not successed accutly i want to create an yahoo api websearch application i got a sample from developer.yahoo.com for this purpose ,this sample uses unserialize/serialize php here is the code exapmle <?php // Parsing Yahoo! REST...
6
28137
by: apking | last post by:
please write the programe in c language for this Accept 5 Employee details to find highest salary employe name using for loop and arrays Thanks in advance
0
1212
by: HARISHAHI | last post by:
How to find first 3 and least rows of a table using row id( using for loop)?
1
1562
by: vit159 | last post by:
I have Data Recive it from stordProceder and i want to Get this data using foreach loop to equal it with local variable
13
4420
by: Sarath | last post by:
What's the advantage of using for_each than the normal iteration using for loop? Is there any 'tweak' to use stream objects as 'Function' parameter of for_each loop. Seems copy function can do the same.
0
9856
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9698
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10657
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9436
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7836
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7022
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5684
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4495
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3136
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.