Exporting current record as a .txt file 1

[jw5107]

Below is code I am working with. I would like to change the code to work with a command button & export the current record on a form to a text file (with header information) INSTEAD of exporting a query to a text file. Can this be done? I sure it can, just not sure how to construct it....
Thanks in advance!!
jw

Sub ExportTextFile()
On Error GoTo ExportTextFile_Err

Dim cnn As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim Directory As String
Dim MyString As String, strSQL As String
Dim strDS As String

Set cnn = CurrentProject.Connection
strDS = cnn.Properties("data source")
Directory = (Mid(strDS, 1, Len(strDS) - Len(Dir(strDS))))

Open Directory & "\Timeline.txt" For Output As #1

rst.Open "qryExportFormatted", cnn, adOpenForwardOnly, adLockReadOnly

Print #1, "This is where header information would go if needed"
rst.MoveFirst
Do While Not rst.EOF
MyString = rst!formattedNumber & _
rst!FormattedAmount & _
rst!FormattedCheckdate & _
rst!FormattedAcctNbr & _
rst!Fill_Field & rst!test
Print #1, MyString
rst.MoveNext
Loop


ExportTextFile_Exit:
' Close text file.
Close #1
rst.Close
Set cnn = Nothing
Exit Sub

ExportTextFile_Err:

MsgBox Err.Description
Resume ExportTextFile_Exit

End Sub

 

[hymn]

for an example of code for a button


stDocName = "Cats"
DoCmd.OpenReport stDocName, acPreview, , "stay.stay_id = " & stay_id
DoCmd.OutputTo acOutputReport, stDocName, acFormattxt, stOutput, True
DoCmd.OutputTo acOutputReport, stDocName, acFormatTXT, stOutput, True

Cats is the name of a report
stay is the name of a form
stay_id is the name of a control on the form
the Output will send the report to My Docs??? as a txt file
you need the report to exist



Hope this helps
Hymn

 

[hymn]

Oooops put the output line in twice

Hope this helps
Hymn

 

[PaulF]

I haven't tried this code, but it should work... if you want to output a record to a file and maintain a format such as a fixed length then you can do something like this... this example uses 20 spaces for each field plus a filler field of 20 spaces at the end, it prints two lines to the text file, the first being the header, the second being the current record when the command button is clicked.

Sub ExportTextFile()
On Error GoTo ExportTextFile_Err

Dim cnn As ADODB.Connection
Dim Directory As String
Dim MyString As String, strSQL As String
Dim strDS As String

Set cnn = CurrentProject.Connection
strDS = cnn.Properties("data source")
Directory = (Mid(strDS, 1, Len(strDS) - Len(Dir(strDS))))

Open Directory & "\Timeline.txt" For Output As #1
‘Print Header
MyString = space(14) & “Number” & space(14) & “Amount” & space(10) & “Check Date” & space(12) & “Acct Nbr” & Space(20)

Print #1, MyString

‘Print Record
MyString =Space(20 – len(Me.Number) & Me.Number &_
Space(20 – len(Me.Amount ) & Me.Amount &_
Space(20 – len(Me.CheckDate) & Me.CheckDate & _
Space(20 – len(Me.AcctNbr) & Me.AcctNbr & _
Space(20)

Print #1, MyString

ExportTextFile_Exit:
' Close text file.
Close #1
rst.Close
Set cnn = Nothing
Exit Sub

ExportTextFile_Err:

MsgBox Err.Description
Resume ExportTextFile_Exit

End Sub


HTH
PaulF

 

[jw5107]

Thanks ALL!!

I have modified the code that I originally posted - It came out like this:

Command Button:
Private Sub cmdExportFixed_Click()
On Error GoTo Err_cmdExportFixed_Click

Call ExportTextFile

MsgBox "File exported. Go to the directory where this program exists " & vbCrLf & _
"and look at the file called 'Timeline.txt'."

Exit_cmdExportFixed_Click:
Exit Sub

Err_cmdExportFixed_Click:
MsgBox Err.DESCRIPTION
Resume Exit_cmdExportFixed_Click

End Sub

Module:
Sub ExportTextFile()
On Error GoTo ExportTextFile_Err

Dim cnn As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim Directory As String
Dim MyString As String, strSQL As String
Dim strDS As String

Set cnn = CurrentProject.Connection
strDS = cnn.Properties("data source")
Directory = (Mid(strDS, 1, Len(strDS) - Len(Dir(strDS))))

Open Directory & "\Timeline.txt" For Output As #1

rst.Open "qryTimeline", cnn, adOpenForwardOnly, adLockReadOnly

rst.MoveFirst
Do While Not rst.EOF
MyString = rst!DateTime & ";" & _
rst!Fact
Print #1, MyString
rst.MoveNext
Loop


ExportTextFile_Exit:
' Close text file.
Close #1
rst.Close
Set cnn = Nothing
Exit Sub

ExportTextFile_Err:

MsgBox Err.DESCRIPTION
Resume ExportTextFile_Exit

End Sub