Problems with looping?

[mayonace]

Hi All,

Need help with the following code. What I am trying to accomplish is to have:

Record1
Record2
Record2
Record5
Record9
Record1..etc.

With the following code my data is coming out as:

Record1
Record2
Record5
Record9
Record2
Record5
Record9
Record1..etc.

Any suggestions on how to have this loop as I like?

Option Compare Database
Option Explicit

Sub ExportTextFile2()

Dim rst As ADODB.Recordset ' Used to open record1
Dim rst2 As ADODB.Recordset ' Used to open record2
Dim rst3 As ADODB.Recordset ' Used to open record5
Dim rst4 As ADODB.Recordset ' Used to open record9
Dim cnn As ADODB.Connection

Dim lngFile As Long

lngFile = FreeFile ' Find next available file

Open "\test.txt" For Output As #lngFile ' Open file to export data to

Set rst = New ADODB.Recordset
Set rst2 = New ADODB.Recordset
Set rst3 = New ADODB.Recordset
Set rst4 = New ADODB.Recordset
Set cnn = CurrentProject.Connection

With rst
.ActiveConnection = cnn
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Source = "Record1"
.Open Options:=adCmdTableDirect
Do Until .EOF
' Export the header
Print #lngFile, !Recordtype & !PorH & !ProvClaimNumber & !Auth & !ProvID & !ProvID & !MemberID & !POS

With rst2
.ActiveConnection = cnn
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Source = "SELECT * FROM Record2 WHERE claimno='" & rst!claimno & "'"
.Open Options:=adCmdText
Do Until .EOF
'Export detail
Print #lngFile, !Recordtype & !PorH & Format(!DateFrom, "yyyyMMdd") & Format(!DateTo, "yyyyMMdd") & !ServiceCodeP

With rst3
.ActiveConnection = cnn
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Source = "SELECT * FROM Record5 WHERE claimno='" & rst!claimno & "'"
.Open Options:=adCmdText
Do Until .EOF
'Export detail
Print #lngFile, !Recordtype & !DXRef & !DXCode

With rst4
.ActiveConnection = cnn
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Source = "SELECT * FROM Record9 WHERE claimno='" & rst!claimno & "'"
.Open Options:=adCmdText
Do Until .EOF
'Export detail
Print #lngFile, !Recordtype & !Filler & !ProviderName & !Filler2 & !Clearinghousename & _
!Filler3

.MoveNext
Loop
.Close
End With
.MoveNext
Loop
.Close
End With
.MoveNext
Loop
.Close
End With
.MoveNext
Loop
.Close
End With

Close #lngFile
Set rst = Nothing
Set rst2 = Nothing
Set rst3 = Nothing
Set rst4 = Nothing
Set cnn = Nothing

End Sub

TIA,
mayonace