Debug issue with Print #file

[eklin1]

Hi there, I am having an issue with the below code. The debug error keeps highliting the "Print #fFile, strString" section of the code, but i can't figure out what's wrong with it. All I'm attempting is to create a recordset with a SQL string and export it as a text file..

can you help?? thanks. a million.

Function testme()
Dim fFile As Long
Dim strFile As String
Dim strString As String
Dim Rs As Recordset
Dim RsSql As String
fFile = FreeFile
'Nominate the output text file path and name
strFile = "C:\Documents and Settings\eklinkbe\desktop\testme2.Txt"
'***You may want to put a check in here to test if the file
'***already exists, and as the user if they want to overwrite it first
'***Use the Kill strFile to delete it first
'Create a snapshot of data from your table with the desired fields and data
RsSql = "Select Messages, Minutes, Charges From 201205Summary"

'Open the recordset and test for EOF
Set Rs = CurrentDb.OpenRecordset(RsSql)
If Not Rs.EOF And Not Rs.BOF Then

Open strFile For Output As #fFile

Do Until Rs.EOF
'Loop across all fields in recordset delimiting them with a tab key
For i = 0 To Rs.Fields.Count - 1
strString = strString & Rs(i) & vbTab
Next
'Output the resulting string to the text file
Print #fFile, strString
'Reset the string to empty for the next record
strString = ""
'Move to the next record in the recordset
Rs.MoveNext
Loop
'Close the recordset and the text file
Rs.Close
Close #fFile
End If
'Destroy the instance of the recordset from memory
Set Rs = Nothing

End Function

 

[AccessMSSQL]

Try using Append or Write instead of Output?

 

[AccessMSSQL]

Also I think you need to remove the # from your fFile

 

[DJkarl]

Try changing fFile variable to a double data type.

 

[jdraw]

I added
an error handler and used one of my tables and a new txt file name
to your code.

'---------------------------------------------------------------------------------------
' Procedure : testme
' Author    : Jack
' Date      : 05/06/2012
' Purpose   :
'---------------------------------------------------------------------------------------
'
Function testme()
Dim fFile As Long
Dim strFile As String
Dim strString As String
Dim Rs As Recordset
Dim RsSql As String
   On Error GoTo testme_Error

fFile = FreeFile
'Nominate the output text file path and name
'strFile = "C:\Documents and Settings\eklinkbe\desktop\testme2.Txt"
strFile = "C:\a2k\testfile.txt"
'***You may want to put a check in here to test if the file
'***already exists, and as the user if they want to overwrite it first
'***Use the Kill strFile to delete it first
'Create a snapshot of data from your table with the desired fields and data
'RsSql = "Select Messages, Minutes, Charges From 201205Summary"
RsSql = "Select animalid, aname,barcode from animal"
'Open the recordset and test for EOF
Set Rs = CurrentDb.OpenRecordset(RsSql)
If Not Rs.EOF And Not Rs.BOF Then

Open strFile For Output As #fFile

Do Until Rs.EOF
'Loop across all fields in recordset delimiting them with a tab key
For i = 0 To Rs.Fields.Count - 1
strString = strString & Rs(i) & vbTab
Next
'Output the resulting string to the text file
Print #fFile, strString
'Reset the string to empty for the next record
strString = ""
'Move to the next record in the recordset
Rs.MoveNext
Loop
'Close the recordset and the text file
Rs.Close
Close #fFile
End If
'Destroy the instance of the recordset from memory
Set Rs = Nothing

   On Error GoTo 0
   Exit Function

testme_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure testme of Module AWF_Related"

End Function

Everything worked fine.

Attached jpg show my table fields and values.

Must be something with your table, your fields??????

Print results in testfile.txt

1 Spot 000000000001
2 Jim 000000000002
3 Sam 000000000003
4 Dave 000000000004
5 BlueEyes 000000000005
6 Capitan 000000000006
7 Johnny 000000000007

 

[eklin1]

awesome.. thanks for all the ideas.. i made a few adjustments but ultimately i needed to reset and then it ran smooth..

thanks all.