create a txt file by reading data from 3 different table and writing

[Neera]

I need create a txt file by reading 3 tables. There is a header table, detail table, and trailer table. Each set of records should have one header, one or more detail records and one trailer. Some data items are similar for each type of record. I started coding the module but having problem:
Here is what I want to code:


Option Compare Database
Option Explicit

Sub ReadHeader()
Dim db As Database
Dim recH As Recordset
Dim RECD As Recordset
Dim RECT As Recordset
Dim strSQL As String

'OPEN A TXT FILE
Open "c:misfile.dat" For Output As #1

strSQL = "select * from MISHEADER,MISDETAIL WHERE MISHEADER.DOC_ID = MISDETAIL.DOC_ID"
Set db = CurrentDb()
Set recH = db.OpenRecordset(strSQL)
rec.FindFirst "DOC_ID"
'WRITE THE HEADER RECORD TO MISFILE
'DO WHILE END OF HEADER TABLE
'SET RECD = DB.OPENRECORDSET(MISDETAIL)
'DO WHILE MISDETAIL.DOC_ID = MISHEADER.DOC_ID
'WRITE THE DETAIL RECORD TO MISFILE
'READ NEXT MISDETAIL RECORD
'LOOP
'READ THE TRAILER RECORD
'Do While TRAILER.DOC_ID = MISHEADER.DOC_ID
'WRITE THE TRAILER RECORD
'READ NEXT TRAILER
'LOOP
'READ NEXT HEADER RECORD
'LOOP
Close #1

End Sub

 

[Durkin]

Try putting an 'INNER JOIN' in your SQL statement like this:

select * from MISHEADER INNER JOIN MISDETAIL ON MISHEADER.DOC_ID = MISDETAIL.DOC_ID

If that's not the solution, please post up the error you are getting or the problem you are having. Durkin
alandurkin@bigpond.com