Loop within loop

[SteveE]

I think I need a second loop on a recordset to produce my output file:
the rs consists of 5-600 rows of data against 6 OrderNo's and I need to start a new output when 1 field "OrderNo" changes.
Below is the function I currently have but I,m not sure if or where I can put the loop to give me the result:


Function ExportO2Pipe()
On Error GoTo Err_ExpO2Pipe

Dim O2DB As DAO.Database
Dim rstO2 As DAO.Recordset
Dim intO2 As Integer
intO2 = FreeFile()
Open "C:\ImportFiles\DEL" & Format(Now, "yymmddhhmm") & ".txt" For Output As #intO2
Set O2DB = CurrentDb()
Set rstO2 = O2DB.OpenRecordset("O2Export", dbOpenForwardOnly)

With rstO2
Print #intO2, "IBO" & "|" & "S" & ![OrderNo] & "|" & ![Sver]
Print #intO2, "IBD" & "|" & ![O2PO] & "|" & ![O2ProdC] & "|" & ![CountOfIMEI] & "|" & "U" & "|" & "S" & ![OrderNo] & "|"
Do While Not .EOF
Print #intO2, "SRN" & "|" & ![O2PO] & "|" & "S" & ![OrderNo] & "|" & ![IMEI] & "|||" & ![O2ProdC]
.MoveNext
Loop

End With
Exit_O2Pipe:
Close #intO2
rstO2.Close
Set rstO2 = Nothing
Exit Function
Err_ExpO2Pipe:
MsgBox "Error on Output Verify Data " & Err.Number & ": " & Err.Description
GoTo Exit_O2Pipe
End Function

The results I need in one output file are:

IBO|S121345|3.60
IBD|4010101010|1DELL5XN |400|U|S121345|
SRN|4010101010|S121345|354203038293595|||1DELL5XN
SRN|4010101010|S121345|354203038293603|||1DELL5XN
SRN|4010101010|S121345|354203038293785|||1DELL5XN
...............

IBO|S121346|3.60
IBD|4010101010|1DELL5XN |400|U|S121345|
SRN|4010101010|S121346|354203038293595|||1DELL5XN
SRN|4010101010|S121346|354203038293603|||1DELL5XN
SRN|4010101010|S121346|354203038293785|||1DELL5XN
................

IBO|S121347|3.60
IBD|4010101010|1DELL5XN |400|U|S121345|
SRN|4010101010|S121347|354203038293595|||1DELL5XN
SRN|4010101010|S121347|354203038293603|||1DELL5XN
SRN|4010101010|S1213475|354203038293785|||1DELL5XN
................

Steve

 

[jdraw]

What tables are you dealing with?
Are the 6 Order Numbers in a list?
The repeating data is the same for each order number?

Suppose you had the 6 order numbers in an array.
OrderNos(5) -- array is 0 based

Open your output file
For i = 0 to 5
Print the stuff related to Order ...OrderNos(i)
Open you data file
Print he data as necessary and
loop thru entire dataset.
close dataset
Next i
Close the output file

 

[SteveE]

First thanks for the reply
No the data is not the same, its contained in one table imported that can have 1200+ unique records, covered by any amount of OrderNo's. Sorry for confusing but I put the sample there to show mainly the format of each output for each OrderNo I didnt change the other data in the sample only the OrderNo

I am faced with either producing 1 output per order number or consolidated into a single file as example.

I am struggling with how within the existing code to EOF per OrderNo and start the next

Steve

 

[boblarson]

Is O2Export a table or query?

 

[SteveE]

O2Export is a query


Steve

 

[gemma-the-husky]

get the query sorted by order no

then you can do this sort of thing (assume your recordset is open)

dim lastorder as string (say)
lastorder=""

'clear any data structures ready for this order
while not rst.eof
   if rst!orderno<>lastorder then
       'deal with last order totals etc, if you need to
       'clear any data structures ready for this order
       lastorder = rst!orderno
   end if
   'deal with this item
   rst.movenext
wend
'deal with last order details etc, if you need to - otherwise the last item gets missed
rst.close
set rst=nothing

 

[SteveE]

Dave thanks for your suggestions I,m sure it will work if I can work ouk out where in the code it should go ! I have inserted it as below but it dosent catch the OrderNo change and insert the 2 headder lines and then the data lines for the next OrderNo

Function O2Pipe()
On Error Resume Next

Dim O2DB As DAO.Database
Dim rstO2 As DAO.Recordset
Dim intO2 As Integer
Dim lastorder As String

lastorder = ""

intO2 = FreeFile()
Open "C:\ImportFiles\DEL" & Format(Now, "yymmddhhmm") & ".txt" For Output As #intO2
Set O2DB = CurrentDb()
Set rstO2 = O2DB.OpenRecordset("O2Export", dbOpenForwardOnly)

'clear any data structures ready for this order

While Not rstO2.EOF
If rstO2!OrderNo <> lastorder Then
With rstO2
Print #intO2, "IBO" & "|" & "S" & ![OrderNo] & "|" & ![Sver]
Print #intO2, "IBD" & "|" & ![O2PO] & "|" & ![O2ProdC] & "|" & ![CountOfIMEI] & "|" & "U" & "|" & "S" & ![OrderNo] & "|"
Do While Not .EOF
Print #intO2, "SRN" & "|" & ![O2PO] & "|" & "S" & ![OrderNo] & "|" & ![IMEI] & "|||" & ![O2ProdC]
.MoveNext
Loop
End With
'deal with last order totals etc, if you need to
'clear any data structures ready for this order
lastorder = rstO2!OrderNo
rstO2.MoveNext
End If
'deal with this item

Wend'deal with last order details etc, if you need to - otherwise the last item gets missed

Exit_O2Pipe:
Close #intO2
rstO2.Close
Set rstO2 = Nothing
Exit Function

Any suggestions welcomend
thanks for your response
Steve