VBA: How many fields in Recordset?

Question

_{Monday, July 25, 2011 8:55 PM}

Maybe this is an unusual problem.  I would like to loop through a recordset's fields, rather than its records. 

I can easily write a loop that will go through a record set's fields, using: myRecordSet.Fields(x), if I know how many fields are in my recordset.  For this particular database, I know there are 10 fields, but if I add an extra field, to get 11, I want this code to be versatile, so I don't have to go back and update my loop to go to 11.

All replies (3)

_{Monday, July 25, 2011 9:05 PM ✅Answered}

Hi W85 try this:

Sub fields()

Dim db As Database

Dim rsData As dao.Recordset

Dim fld As dao.Field

dim i as integer

Set db = CurrentDb

Set rsData = db.OpenRecordset("Staff")

  For Each fld In rsData.fields

    i = i + 1

    Debug.Print i & ": " & fld.Name

Next fld

End Sub

_{Monday, July 25, 2011 9:24 PM ✅Answered | 1 vote}

Maybe this is an unusual problem.  I would like to loop through a recordset's fields, rather than its records. 

 

I can easily write a loop that will go through a record set's fields, using: myRecordSet.Fields(x), if I know how many fields are in my recordset.  For this particular database, I know there are 10 fields, but if I add an extra field, to get 11, I want this code to be versatile, so I don't have to go back and update my loop to go to 11.

myRecordSet.Fields.Count will give you the number of fields.  Remember that the fields are numbered from 0 to (.Count - 1).Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

_{Monday, July 25, 2011 9:15 PM}

If you need to keep adding fields it seems you are using Access as if it were a spreadsheet instead of a relational database.  If so you will have other problems later on such as revising queries, forms, and reports.