export data to txt file

[barra47]

I have 4 field
ID, Supplier, Qty, Item

I want to output the data to a text file ( comma or Tab seperated)

I want the output to keep the same Id numbers together
with linefeed between each group

eg.
1 blackwood 3 drill bits
1 blackwood 5 spanners
1 blackwood 20 pins

2 universal 10 bolts
2 universal 10 washers

3 evans safety 4 safety glasses
3 evans safety 12 gloves

the linefeed is needed by MYOB when the data is imported to distinguish between records if there is no line feed MYOB would import the data as 1 record instead of 3 records.


Thanks in Advance

 

[Remou]

I think the best bet is to use either the OpenTextFile method of the FileSystemObject or the Open Statement, either of which can be used to write a file, and a recordset.

 

[barra47]

Hi Remou
Thanks for your reply
Im not familiar with these methods
could you possibly write a sample script that I can develope from

 

[Remou]

Something like:

Dim strContact As String
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblPersons ORDER BY ContactType")
Open "C:\Docs\TextFile.txt" For Output As #1
Do While Not rs.EOF
    strContact = rs!ContactType
    Do While rs!ContactType = strContact
        Print #1, rs!ContactType & " " & rs!Surname
        If Not rs.EOF Then
            rs.MoveNext
        Else
            Exit Do
        End If
    Loop
    Print #1,
    rs.MoveNext
Loop
Close #1
rs.Close
Set rs = Nothing

You will find other examples in these fora.

 

[barra47]

Thanks once again Remou
To run your sample script
I created a table tblPersons
with 2 fields ContactType and Surname
i populated this table with

1 Ian
1 Kerst
1 Robs
2 Kerry
3 Hall
4 Jerry
4 Hanna

I placed your code in the onclick event of a button and ran it

It created the file Ok but them bombed out at
Do While rs!ContactType = strContact

the error was "No Current Record"

Ok where am I going wrong here

 

[PHV]

You may try this:
Dim strContact As String
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblPersons ORDER BY ContactType")
If Not (rs.BOF Or rs.EOF) Then
strContact = rs!ContactType
Open "C:\Docs\TextFile.txt" For Output As #1
Do While Not rs.EOF
If strContact <> rs!ContactType Then
Print #1,
strContact = rs!ContactType
End If
Print #1, rs!ContactType & " " & rs!Surname
rs.MoveNext
Loop
Close #1
End If
rs.Close
Set rs = Nothing

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[barra47]

Hi PHV
Thank you very much
That code worked a treat
I will now adapt it to my working table

One extra thing can I print the Field Names ContactType and Surname as the first line of the Text File

 

[PHV]

Dim strContact As String
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblPersons ORDER BY ContactType")
If Not (rs.BOF Or rs.EOF) Then
Open "C:\Docs\TextFile.txt" For Output As #1
Print #1, "ContactType Surname"
Do While Not rs.EOF
If strContact <> rs!ContactType Then
Print #1,
strContact = rs!ContactType
End If
Print #1, rs!ContactType & " " & rs!Surname
rs.MoveNext
Loop
Close #1
End If
rs.Close
Set rs = Nothing

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[barra47]

Excelent Thank you PHV