Union Query loop

[Earthmover]

Hi All,

Basically what I'm trying to do is raise a number of orders on my inventory system.
I have about 5000 lines that need to be put onto orders but each order can't have more than 200 lines.

I've got a list of SKU's and quantities in a table (PNS) and the following queries made up.

Basically, I want the bold part in the append query to repeat itself 200 times before it continues, then start at the top and do it again for the next 200 lines.

Append
SELECT PNS.ID,
"WaitForCursorPos (22, 20)" AS AA,
"TypeString (16)" AS AB,
"FunctionKey (ENTER)" AS AC,
"" AS AD,
"WaitFor (UNLOCK)" AS AE,
"" AS AF,


"WaitForCursorPos (2, 1)" AS AG,
"FunctionKey (ENTER)" AS AH,
"" AS AI,
"WaitFor (UNLOCK)" AS AJ,
"" AS AK,
"WaitForCursorPos (2, 1)" AS AL,
"FunctionKey (ENTER)" AS AM,
"" AS AN,
"WaitFor (UNLOCK)" AS AO,
"" AS AP,
"WaitForCursorPos (22, 37)" AS AQ,
"TYPESTRING (""" & [PN] & """)" AS AR,
"FunctionKey (TAB)" AS [AS],
"TYPESTRING (""" & [QTY] & """)" AS [AT],
"FunctionKey (ENTER)" AS AU,
"" AS AV,
"WaitFor (UNLOCK)" AS AW,
"" AS AX,
"WaitForCursorPos (11, 15)" AS AY,
"FunctionKey (ENTER)" AS AZ,
"WaitForCursorPos (9, 3)" AS BA,
"FunctionKey (PF4)" AS BB,
"" AS BC,
"WaitFor (UNLOCK)" AS BD,
"" AS BE,
"WaitForCursorPos (11, 21)" AS BF,
"FunctionKey (TAB)" AS BG,
"FunctionKey (TAB)" AS BH,
"TypeString (pwktest)" AS BI,
"FunctionKey (ENTER)" AS BJ,
"" AS BK,
"WaitFor (UNLOCK)" AS BL,
"" AS BM,
"WaitForCursorPos (7, 42)" AS BN,
"FunctionKey (TAB)" AS BO,
"TypeString (05test)" AS BP,
"FunctionKey (ENTER)" AS BQ,
"" AS BR,
"WaitFor (UNLOCK)" AS BS,
"" AS BT,
"WaitForCursorPos (11, 21)" AS BU,
"FunctionKey (PF4)" AS BV,
"" AS BW,
"WaitFor (UNLOCK)" AS BX
FROM PNS;


Union
SELECT ALL ID,
"AA" As SEQ, A As Action FROM BT1 UNION ALL SELECT ID,
"AB", AB FROM BT1 UNION ALL SELECT ID,
"AC", AB FROM BT1 UNION ALL SELECT ID,
"AD", AB FROM BT1 UNION ALL SELECT ID,
"AE", AB FROM BT1 UNION ALL SELECT ID,
"AF", AB FROM BT1 UNION ALL SELECT ID,
"AG", AB FROM BT1 UNION ALL SELECT ID,
"AH", AB FROM BT1 UNION ALL SELECT ID,
"AI", AB FROM BT1 UNION ALL SELECT ID,
"AJ", AB FROM BT1 UNION ALL SELECT ID,
"AK", AB FROM BT1 UNION ALL SELECT ID,
"AL", AB FROM BT1 UNION ALL SELECT ID,
Loop this bit 200 times
"AM", AB FROM BT1 UNION ALL SELECT ID,
"AN", AB FROM BT1 UNION ALL SELECT ID,
"AO", AB FROM BT1 UNION ALL SELECT ID,
"AP", AB FROM BT1 UNION ALL SELECT ID,
"AQ", AB FROM BT1 UNION ALL SELECT ID,
"AR", AB FROM BT1 UNION ALL SELECT ID,
"AS", AB FROM BT1 UNION ALL SELECT ID,
"AT", AB FROM BT1 UNION ALL SELECT ID,
"AU", AB FROM BT1 UNION ALL SELECT ID,
"AV", AB FROM BT1 UNION ALL SELECT ID,
"AW", AB FROM BT1 UNION ALL SELECT ID,
"AX", AB FROM BT1 UNION ALL SELECT ID,
"AY", AB FROM BT1 UNION ALL SELECT ID,
"AZ", AB FROM BT1 UNION ALL SELECT ID,
"BA", AB FROM BT1 UNION ALL SELECT ID,
Loop this bit 200 times
"BB", AB FROM BT1 UNION ALL SELECT ID,
"BC", AB FROM BT1 UNION ALL SELECT ID,
"BD", AB FROM BT1 UNION ALL SELECT ID,
"BE", AB FROM BT1 UNION ALL SELECT ID,
"BF", AB FROM BT1 UNION ALL SELECT ID,
"BG", AB FROM BT1 UNION ALL SELECT ID,
"BH", AB FROM BT1 UNION ALL SELECT ID,
"BI", AB FROM BT1 UNION ALL SELECT ID,
"BJ", AB FROM BT1 UNION ALL SELECT ID,
"BK", AB FROM BT1 UNION ALL SELECT ID,
"BL", AB FROM BT1 UNION ALL SELECT ID,
"BM", AB FROM BT1 UNION ALL SELECT ID,
"BN", AB FROM BT1 UNION ALL SELECT ID,
"BO", AB FROM BT1 UNION ALL SELECT ID,
"BP", AB FROM BT1 UNION ALL SELECT ID,
"BQ", AB FROM BT1 UNION ALL SELECT ID,
"BR", AB FROM BT1 UNION ALL SELECT ID,
"BS", AB FROM BT1 UNION ALL SELECT ID,
"BT", AB FROM BT1 UNION ALL SELECT ID,
"BU", AB FROM BT1 UNION ALL SELECT ID,
"BV", AB FROM BT1 UNION ALL SELECT ID,
"BW", AB FROM BT1 UNION ALL SELECT ID,
"BX", AB FROM BT1 UNION ALL SELECT ID,
ORDER BY ID, SEQ;

Hoping this makes sense, any guidance would be greatly appreciated.

Cheers,
Earthmover.

[June7]

Access query objects cannot be coded to 'loop'. You need VBA.

My experience with UNION is that there is a limit of 50 lines. Also, VBA recordsets did not like working with UNION queries. I had to redesign a process to eliminate UNION query.