p2p.wrox.com Forums

Need to download code?

View our list of code downloads.

  Return to Index  

access thread: Re: Cycling through fields collection

Message #1 by "Mavin Specimen" <mspecimen@h...> on Thu, 28 Jun 2001 17:07:04
How do I cycle through the fields collection. Don't have any books with me now
Message #2 by "Susan Henesy" <susan.henesy@w...> on Thu, 28 Jun 2001 21:38:00
> How do I cycle through the fields collection. Don't have any books with 

me now


Hi Marvin,

When you are bereft of book, there is another place to turn for immediate 

answers.  The Help Files that are built in Access will tell you most of 

what you want to know.

For example, to respond to your question, I simply went into one of my 

Access modules and pressed F1 for Help; then I searched for FIELDS 


I got a nice explanation of fields, as well as a hefty example on how to 

use them.  Using the example from the help files, it is easy to see that 

your use of cycling through fields is dependent on what you want to do 

with the field.  Edit them ?  View their properties?  Etc.

You will need to declare a variable for a Database and a Recordset -- 

sometimes this is all you need (it's all I ever use for obtaining the 

values of fields). 

An example of getting field values is:

Dim dbMe as Database

Dim rsMyQuery as Recordset

Dim strFieldValue as String

Set dbMe = CurrentDB

Set rsMyQuery = dbMe.OpenRecordset("rsMyQuery")

With rsMyQuery

 Do Until .EOF

   strFieldValue = ![FieldName]

   Debug Print strFieldValue



End With

'Clean up!


Set rsMyQuery = Nothing


Set dbMe = Nothing


For a few other examples, here is a copy - n - paste from the Access Help 

File I told you I found.  Enjoy! -- Susan



This example shows what properties are valid for a Field object depending 

on where the Field resides (for example, the Fields collection of a 

TableDef, the Fields collection of a QueryDef, and so forth). The 

FieldOutput procedure is required for this procedure to run.

Sub FieldX()

	Dim dbsNorthwind As Database

	Dim rstEmployees As Recordset

	Dim fldTableDef As Field

	Dim fldQueryDef As Field

	Dim fldRecordset As Field

	Dim fldRelation As Field

	Dim fldIndex As Field

	Dim prpLoop As Property

	Set dbsNorthwind = OpenDatabase("Northwind.mdb")

	Set rstEmployees = _


	' Assign a Field object from different Fields 

	' collections to object variables.

Set fldTableDef = _


	Set fldQueryDef =dbsNorthwind.QueryDefs(0).Fields(0)

	Set fldRecordset = rstEmployees.Fields(0)

	Set fldRelation =dbsNorthwind.Relations(0).Fields(0)

	Set fldIndex = _


	' Print report.

	FieldOutput "TableDef", fldTableDef

	FieldOutput "QueryDef", fldQueryDef

	FieldOutput "Recordset", fldRecordset

	FieldOutput "Relation", fldRelation

FieldOutput "Index", fldIndex



End Sub

Sub FieldOutput(strTemp As String, fldTemp As Field)

	' Report function for FieldX.

	Dim prpLoop As Property

	Debug.Print "Valid Field properties in " & strTemp

	' Enumerate Properties collection of passed Field

	' object.

	For Each prpLoop In fldTemp.Properties

		' Some properties are invalid in certain 

		' contexts (the Value property in the Fields 

' collection of a TableDef for example). Any 

		' attempt to use an invalid property will 

		' trigger an error.

		On Error Resume Next

		Debug.Print "    " & prpLoop.Name & " = " & _


		On Error GoTo 0

	Next prpLoop

End Sub
Message #3 by Brian Skelton <brian.skelton@b...> on Thu, 28 Jun 2001 22:50:36 +0100
This should work - use the 'For Each <item> in <Collection>' statement.


Dim rst as Recordset

Dim fld as Field

Dim myDB as Database

	Set myDB = CurrentDB

	Set rst = myDB.OpenRecordset("tablename")

	For each fld in rst.Fields

		<Your Code>



-----Original Message-----

From:	Mavin Specimen [SMTP:mspecimen@h...]

Sent:	28 June 2001 18:07

To:	Access

Subject:	[access] Re: Cycling through fields collection

How do I cycle through the fields collection. Don't have any books with me now

  Return to Index