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
5 9245 NeoPa 32,556
Expert Mod 16PB
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.
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 32,556
Expert Mod 16PB
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.
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 32,556
Expert Mod 16PB
No worries. I'm actually glad it was that simple. I prefer the easy life ;)
Welcome to Bytes!
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
by: minguskhan |
last post by:
Does anyone know how to reverse a string using a loop?
|
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...
|
by: Madhavi |
last post by:
Hi
Is there any Do Until Loop in C#
Maadhavi
|
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...
|
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
|
by: HARISHAHI |
last post by:
How to find first 3 and least rows of a table using row id( using for loop)?
|
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
|
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...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| |