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

> 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 

COLLECTION.



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

 .MoveNext

 Loop

End With



'Clean up!

rsMyQuery.Close

Set rsMyQuery = Nothing



dbMe.Close

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







--------------------------------

FIELD COLLECTIONS - TEXT FROM ACCESS HELP FILE



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 = _

		dbsNorthwind.OpenRecordset("Employees")



	' Assign a Field object from different Fields 

	' collections to object variables.



Set fldTableDef = _

		dbsNorthwind.TableDefs(0).Fields(0)

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

	Set fldRecordset = rstEmployees.Fields(0)

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

	Set fldIndex = _

		dbsNorthwind.TableDefs(0).Indexes(0).Fields(0)



	' Print report.

	FieldOutput "TableDef", fldTableDef

	FieldOutput "QueryDef", fldQueryDef

	FieldOutput "Recordset", fldRecordset

	FieldOutput "Relation", fldRelation



FieldOutput "Index", fldIndex



	rstEmployees.Close

	dbsNorthwind.Close



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 & " = " & _

			prpLoop.Value

		On Error GoTo 0

	Next prpLoop



End Sub

This should work - use the 'For Each <item> in <Collection>' statement.



E.g.



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>



	Next



-BDS



-----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