Outputting Data to a Text File

[thefonz37]

Here's a big of code I have written so far:

Private Sub Form_Close()
Dim rsOutExceptions As Recordset
Set rsOutExceptions = CurrentDb().OpenRecordset("SELECT * FROM
tbl_Exceptions WHERE change_flag = TRUE;")
If rsOutExceptions.RecordCount > 0 Then
outFile = "M:\FTPTransfer\FE_Schedule_Exceptions\" & fOSUserName() &
"_" & Format(Now(), "yyyymmddhhhnnss") & ".txt"
' Open outFile For Output As #1
' Close #1
DoCmd.TransferText acExportDelim, "Modified_Exceptions_Export",
"qry_Modified_Exceptions", outFile
DoCmd.SetWarnings False
DoCmd.RunSQL ("UPDATE tbl_Exceptions SET change_flag = False;")
DoCmd.SetWarnings True
End If
rsOutExceptions.Close
End Sub

The idea is that when this form closes, it writes all the changed records to
a text file (which is named on the user's lan ID and the current date/time).

However, when this code executes, it returns the message: "Run-Time error
'3011': The Microsoft Office Access database engine could not find the object
<filename>. Make sure the object exists and that you spell its name and the
path name correctly."

As you can see, I even tried to add two lines of code (commented out above)
that manually create the text file, but even this doesn't work and it returns
the same error message that it can't find the file.

 

[Ken Snell [MVP]]

My initial thought is that the path you have specified in the outFile
variable is not valid. Double-check what is being built in that path by
having a MsgBox pop up with the string in outFile after you set it.

 

[thefonz37]

I did copy/paste the folder address back and forth from the file folder
window, and that's correct. I stepped through this sub too and it looked
like the outFile variable name was being built correctly.

That aside, though, I discovered another way to accomplish this, but I have
a new question. Here's my code:

Private Sub Form_Close()
Dim rsOutExceptions As Recordset
Set rsOutExceptions = CurrentDb().OpenRecordset("SELECT * FROM
tbl_Exceptions WHERE change_flag = TRUE;")
If rsOutExceptions.RecordCount > 0 Then
outFile = "M:\FTPTransfer\FE_Schedule_Exceptions\" & fOSUserName() &
"_" & Format(Now(), "yyyymmddhhhnnss") & ".txt"
Open outFile For Output As #1
Dim outText
While Not rsOutExceptions.EOF
outText = outText & rsOutExceptions!empid & "|" &
rsOutExceptions!date & "|" & rsOutExceptions!start_time & "|" &
rsOutExceptions!end_time & "|" & rsOutExceptions!excep_code & _
"|" & rsOutExceptions!approval_flag & "|" &
rsOutExceptions!retracted_flag & "|" & rsOutExceptions![timestamp] & "|" &
rsOutExceptions!comments & Chr(13) & Chr(10)
rsOutExceptions.MoveNext
Wend
Write #1, outText
DoCmd.SetWarnings False
DoCmd.RunSQL ("UPDATE tbl_Exceptions SET change_flag = False;")
DoCmd.SetWarnings True
End If
Close #1
rsOutExceptions.Close
End Sub

My new question is this method works great, but it puts quotes before and
after the output text. Is there a way to remove those quotes?

 

[Ken Snell [MVP]]

Change this code step:

Write #1, outText


to this:

Print #1, outText

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I did copy/paste the folder address back and forth from the file folder
window, and that's correct. I stepped through this sub too and it looked
like the outFile variable name was being built correctly.

That aside, though, I discovered another way to accomplish this, but I
have
a new question. Here's my code:

Private Sub Form_Close()
Dim rsOutExceptions As Recordset
Set rsOutExceptions = CurrentDb().OpenRecordset("SELECT * FROM
tbl_Exceptions WHERE change_flag = TRUE;")
If rsOutExceptions.RecordCount > 0 Then
outFile = "M:\FTPTransfer\FE_Schedule_Exceptions\" & fOSUserName()
&
"_" & Format(Now(), "yyyymmddhhhnnss") & ".txt"
Open outFile For Output As #1
Dim outText
While Not rsOutExceptions.EOF
outText = outText & rsOutExceptions!empid & "|" &
rsOutExceptions!date & "|" & rsOutExceptions!start_time & "|" &
rsOutExceptions!end_time & "|" & rsOutExceptions!excep_code & _
"|" & rsOutExceptions!approval_flag & "|" &
rsOutExceptions!retracted_flag & "|" & rsOutExceptions![timestamp] & "|" &
rsOutExceptions!comments & Chr(13) & Chr(10)
rsOutExceptions.MoveNext
Wend
Write #1, outText
DoCmd.SetWarnings False
DoCmd.RunSQL ("UPDATE tbl_Exceptions SET change_flag = False;")
DoCmd.SetWarnings True
End If
Close #1
rsOutExceptions.Close
End Sub

My new question is this method works great, but it puts quotes before and
after the output text. Is there a way to remove those quotes?

My initial thought is that the path you have specified in the outFile
variable is not valid. Double-check what is being built in that path by
having a MsgBox pop up with the string in outFile after you set it.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/