[Hockeyball]

I have a module which I loop through and create a comma separated text file. It works for the most part but 1 line of text I write I cannot get in the right spot, it is a trailer record.
Here is my code and after that is a sample table and then my output and then how I would like the output. I cannot get the TRL,MOAA, line to print after each ID, it prints after each SEQ.
Basically it is a trailer record for the ID.

Code
Private Sub BuildFile()
Dim db As Database, rst As Recordset, ID, Item
Dim lngFile As Long
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * " & "FROM InputTable ORDER BY ID, Seq", dbOpenDynaset)
'/open text file
lngFile = FreeFile
Open "C:\FileLocation\Testfile.txt" For Output Access Write As #lngFile
rst.MoveFirst
Do Until rst.EOF
Print #lngFile, "This is ID " & Format(rst!ID, "000000000") & "HDR," & rst![Test] & ",END"
ID = rst!ID
Do While rst!ID = ID
Item = rst!Item
Print #lngFile, "This is ID " & Format(rst!ID, "000000000") & "DTL," & rst![Seq] & ",END"
Print #lngFile, "This is ID " & Format(rst!ID, "000000000") & "DTLTX," & rst![Seq] & ",TSQ1" & rst![ID] & ",TLN1,END"
Print #lngFile, "This is ID " & Format(rst!ID, "000000000") & "DTLTX," & rst![Seq] & ",TSQ2" & rst![MainLine] & ",TLN2,END"
Print #lngFile, "This is ID " & Format(rst!ID, "000000000") & "TRL,MOAA,NDTL" & rst![SeqCount] & ",NMS0,NBS1,NTX" & rst![DetailLines] & ",END"
rst.MoveNext
If rst.EOF Then Exit Do
Do While rst!Item = Item
rst.MoveNext
If rst.EOF Then Exit Do
Loop
If rst.EOF Then Exit Do
Loop
Loop
rst.Close
db.Close
Close lngFile
Set rst = Nothing
End Sub

Table
ID SEQ ITEM MAINLINE TEST SEQCOUNT DETAILLINES
100 1 Item1 John Y 2 2
100 2 Item2 George Y 2 2
101 1 Item5 Deb N 1 2
102 1 Item1 Jewel Y 2 2
102 2 Item3 Lisa Y 2 2

Current Output
This is ID 000000100HDR,Y,END
This is ID 000000100DTL,1,Item1,END
This is ID 000000100DTLTX,1,TSQ1100,TLN1,END
This is ID 000000100DTLTX,1,TSQ2John,TLN2,END
This is ID 000000100TRL,MOAA,NDTL2,NMS0,NBS1,NTX2,END
This is ID 000000100DTL,2,Item2,END
This is ID 000000100DTLTX,2,TSQ1101,TLN1,END
This is ID 000000100DTLTX,2,TSQ2George,TLN1,END
This is ID 000000100TRL,MOAA,NDTL2,NMS0,NBS1,NTX2,END
This is ID 000000101HDR,N,END
This is ID 000000101DTL,1,Item5,END
This is ID 000000101DTLTX,1,TSQ1101,TLN1,END
This is ID 000000101DTLTX,1,TSQ2Deb,TLN2,END
This is ID 000000101TRL,MOAA,NDTL1,NMS0,NBS1,NTX2,END
This is ID 000000102HDR,Y,END
This is ID 000000102DTL,1,Item1,END
This is ID 000000102DTLTX,1,TSQ1102,TLN1,END
This is ID 000000102DTLTX,1,TSQ2Jewel,TLN2,END
This is ID 000000102TRL,MOAA,NDTL2,NMS0,NBS1,NTX2,END
This is ID 000000102DTL,2,Item3,END
This is ID 000000102DTLTX,2,TSQ1102,TLN1,END
This is ID 000000102DTLTX,2,TSQ2Lisa,TLN2,END
This is ID 000000102TRL,MOAA,NDTL2,NMS0,NBS1,NTX2,END

Expected Output
This is ID 000000100HDR,Y,END
This is ID 000000100DTL,1,Item1,END
This is ID 000000100DTLTX,1,TSQ1100,TLN1,END
This is ID 000000100DTLTX,1,TSQ2John,TLN2,END
This is ID 000000100DTL,2,Item2,END
This is ID 000000100DTLTX,2,TSQ1101,TLN1,END
This is ID 000000100DTLTX,2,TSQ2George,TLN2,END
This is ID 000000100TRL,MOAA,NDTL2,NMS0,NBS1,NTX2,END
This is ID 000000101HDR,N,END
This is ID 000000101DTL,1,Item5,END
This is ID 000000101DTLTX,1,TSQ1101,TLN1,END
This is ID 000000101DTLTX,1,TSQ2Deb,TLN2,END
This is ID 000000101TRL,MOAA,NDTL1,NMS0,NBS1,NTX2,END
This is ID 000000102HDR,Y,END
This is ID 000000102DTL,1,Item1,END
This is ID 000000102DTLTX,1,TSQ1102,TLN1,END
This is ID 000000102DTLTX,1,TSQ2Jewel,TLN2,END
This is ID 000000102DTL,2,Item3,END
This is ID 000000102DTLTX,2,TSQ1102,TLN1,END
This is ID 000000102DTLTX,2,TSQ2Lisa,TLN2,END
This is ID 000000102TRL,MOAA,NDTL2,NMS0,NBS1,NTX2,END

[OBP]

The End line needs to be outside the Loop.

[Hockeyball]

As soon as I do that I get Run-time error '3021' No current record.
I believe I still need it as part of the loop as I need to print after each ID,

[OBP]

Did you put outside the Inner Loop?

[Hockeyball]

I thought so but maybe I did something wrong.
Can you show me what you mean?

[OBP]

Well I would try it here
rst.MoveNext
If rst.EOF Then Exit Do
Loop
Code Here
If rst.EOF Then Exit Do
Loop
Loop

However as you are using the Items for the "Current ID" perhaps it would be easier to assign the values you want to use to Variables, you can then use them anywhere in your code.

Can you explain what each Loop does?

[Hockeyball]

No luck I get the same error.
I am a novice at this and will do my best to explain the loops.
I am first looping through the ID's and then looping the items and printing the lines for each item under an ID. I then need to print a trailer at the end of the ID.
So I may have 2 items so what I need is
ID1 Header
ID1 Item 1 line 1
ID1 Item 1 line 2
ID1 Item 2 Line 1
ID1 Item 2 Line 2
ID1 Trailer
ID2 Header
ID2 Item 2 line 1
ID2 Trailer
ID3 Header
ID3 Item 3 line 1
ID3 Item 3 line 2
ID3 Item 3 line 3
ID3 Trailer

Does that make sense?

[OBP]

You obviously know how to use Variables as you used one for the File number.
So you could do something like this
Dim currentID as String
currentID = Format(ID, "000000000") 'add this after your line ID = rst!ID
Now try printing that currentID to the file between each of the Loops,
so that you should end up with 3 of them. That should show you where it needs to go.
Once you have that you can just set the other Items that you want to print to their own variables.

[Hockeyball]

That got it.
Set the Variable and printed it after the second loop and it is working perfectly.
Thanks for your help!

[OBP]

Great job.