Help Writing Field Names to a String

[birdDBheadache]

I am trying to concatenate field names (separated by a comma or some of the delimiter) and write them to record in my database, or store them as a string that I can use in an expression later on. So far I have code that pulls all the field names from a query and writes them to the debug window. But I am stuck after that. I can't figure out how to get the output into another format. Any suggestions would be appreciated.

Sub Fieldnames()
    Dim Rst As Recordset
    Dim db As Database
    Dim f As Field
    Dim qdfParmQry As QueryDef
        Set db = CurrentDb()
        Set qdfParmQry = db.QueryDefs("qry_1test")
        qdfParmQry("Forms!Occu_formatting!Species") = [Forms]![Occu_formatting]![Species]
        Set Rst = qdfParmQry.OpenRecordset()
    For Each f In Rst.Fields
    Debug.Print f.Name
    Next
    Rst.Close
End Sub

[Seth Schrock]

You would need a string variable to store the field names.

Dim strFields As String

Now, just below your Debug.Print line, put this

strFields = strFields & f.Name & ", "

This will leave you with a comma and a space at then end of the string when you are done, so after your loop, you will probably want to drop those.

strFields = Left(strFields, Len(strFields) - 2)

You might have to play with the number that you are subtracting to make sure it works, but otherwise it should work.

[birdDBheadache]

That worked, thanks!

[NeoPa]

That works Seth, but an alternative approach is to use :

strFields = strFields & ", " & f.Name

This has the benefit of being easier to tidy up afterwards using :

strFields = Mid(strFields, 3)