Export Current Record to Text File

[lmarles]

Access 97. I have a form, FORM1, and a table, TABLE1, that is the record source for the form. I have a text field in the table called TEXTFILE. TEXTFILE contains the name of a text file. I have a command button on the form that I have called 'Export Current Record to Text File'. When I click the button I would like the current record exported to TEXTFILE. How can I do this?

Lou Marles

 

[Psychpt]

Docmd.TransferText is what exports to text files
there are many options

 

[RickSpr]

So what you want to do is to write the record currently being displayed to a text file, and the name of the text file is part of the record being displayed, is that right?

There are two basic ways you could do this. One would be to write VBA code in the button's Click event to create/open the text file, write the fields to it one by one, and close the file. The other way would be to set up a linked table definition for each of the text files you want to write to, then create an Append query that will export just the current record to the file, and then set up the button to choose which Append query to run based on the setting of TEXTFILE.

The first method is quite a bit simpler, but involves VBA code. The second could be done with just macros, but is a bit complicated.

You also need to specify whether the text file should be overwritten or extended if it already exists, and the format of the data in the text file.

Here's some sample VBA code, that assumes you want to append to the file:

    Private Sub cmdExport_Click()
        Dim iFile As Integer
        
        iFile = FreeFile()
        ' If you want to overwrite the text file, uncomment the next line
        ' Kill Me![TextFile]
        Open Me![TextFile] For Append As #iFile
        Write #iFile, Me![Field1], Me![Field2], Me![Field3], ...
        ' add as many fields as you want to export
        Close #iFile
    End Sub

This is not the most robust code, since it doesn't include error handling, but it should get you started. Replace [Field1] etc. with the names of the fields you want to save in the text file. The Write statement will just write them out one after the other, separated by commas, with text data enclosed in quotes.

 

[RickSpr]

I'm re-posting the code because I forgot the forum software doesn't deal well with "[" characters.

Private Sub cmdExport_Click()
Dim iFile As Integer

iFile = FreeFile()
' If you want to overwrite the text file, uncomment the next line
' Kill Me![TextFile]
Open Me![TextFile] For Append As #iFile
Write #iFile, Me![Field1], Me![Field2], Me![Field3], ...
' add as many fields as you want to export
Close #iFile
End Sub

 

[lmarles]

Thanks everyone. I have it working using Do.Cmd TransferText. I have filed all suggestions though since I will likely need the other solutions in the future.

Lou Marles
e-mail: lmarles@trentu.ca