count fields in recordset

[brad78]

Hi everyone,
I hope someone can give me a hand with a problem I'm having. How can check to see what field number I'm in of a recordset and add additional formatting as needed? I am using the following code (found in the forms here) to write the table to a text file but in the for each loop, I need to format each of my 5 fields differently.

Private Sub exp_2_notepad_Click()
Dim rst As DAO.Recordset
Dim db As DAO.database
Dim fld As DAO.Field
Dim strDelimeter As String
Dim strText As String
Dim blnColumnHeaders As Boolean
Dim FilePartOne As String
Dim mdbpath As String
Set db = CurrentDb
blnColumnHeaders = False
Set rst = db.OpenRecordset("select * from Intranet_Search_Directory")
Open "C:\exportfile.txt" For Output As #1
Do While Not rst.EOF
        For Each fld In rst.Fields
           strText = strText & fld.Value
        Next
    rst.MoveNext
Loop
'removes the extra line
strText = Left(strText, Len(strText) - Len(vbNewLine))
Print #1, strText
Close #1
End Sub

My fields all need to be wrapped in " " and I can't figure out how to add that in the For Each line in the code above.
Any help is appreciated
Brad

 

[ByteMyzer]

I would recommend the following revised code:

[COLOR="Navy"]Public Sub[/COLOR] exp_2_notepad_Click()

    [COLOR="navy"]Dim[/COLOR] db [COLOR="navy"]As[/COLOR] DAO.Database
    [COLOR="navy"]Dim[/COLOR] fld [COLOR="navy"]As[/COLOR] DAO.Field
    [COLOR="navy"]Dim[/COLOR] fVar() [COLOR="navy"]As String
    Dim[/COLOR] rst [COLOR="navy"]As[/COLOR] DAO.Recordset
    [COLOR="navy"]Dim[/COLOR] strText [COLOR="navy"]As String

    Set[/COLOR] db = CurrentDb

    [COLOR="navy"]Set[/COLOR] rst = db.OpenRecordset("select * from Intranet_Search_Directory")
    [COLOR="navy"]Open[/COLOR] "C:\exportfile.txt" [COLOR="navy"]For Output As[/COLOR] #1

    [COLOR="navy"]ReDim[/COLOR] fVar(rst.Fields.Count - 1)

    [COLOR="navy"]Do While Not[/COLOR] rst.EOF
        [COLOR="navy"]For Each[/COLOR] fld [COLOR="navy"]In[/COLOR] rst.Fields
            fVar(fld.OrdinalPosition) = _
                Replace([COLOR="navy"]CStr([/COLOR]Nz(fld.Value, "")[COLOR="navy"])[/COLOR], """", """""")
        [COLOR="navy"]Next[/COLOR]
        strText = strText & """" & Join(fVar, """,""") & """" & vbNewLine
        rst.MoveNext
    [COLOR="navy"]Loop

    Print[/COLOR] #1, strText;
    [COLOR="navy"]Close[/COLOR] #1

    rst.Close
    [COLOR="navy"]Set[/COLOR] rst = [COLOR="navy"]Nothing[/COLOR]

    db.Close
    [COLOR="navy"]Set[/COLOR] db = [COLOR="navy"]Nothing

End Sub[/COLOR]