Accessing Field Values in Recordsets

[ADezii]

There are basically 4 Methods that you can use to access the values of Fields in Recordsets. For the purposes of this demonstration, I'll be using a Table called tblEmployees, a Field named [LastName], and a DAO Recordset although these Methods are equally applicable to ADO Recordsets. The following code will print all the Last Name ([LastName]) values in the Employees (tblEmployees) Table using all 4 of these Methods.

Dim MyDB As DAO.Database, MyRS As DAO.Recordset
Dim strFieldName As String
 
strFieldName = "LastName"
 
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("tblEmployees", dbOpenForwardOnly)
 
Do While Not MyRS.EOF
  'Method 1 - the Bang or Exclamation Method
  Debug.Print MyRS![LastName]
 
  'Method 2 - the String Method
  Debug.Print MyRS.Fields("LastName")
 
  'Method 3 - the Index Method (not recommended - see Note #2)
  Debug.Print MyRS.Fields(2)
 
  'Method 4 - the Variable Method
  Debug.Print MyRS.Fields(strFieldName)
  MyRS.MoveNext
Loop
 
MyRS.Close 
 
NOTE: All 4 Methods will produce the same results.

NOTE #2:
By using an Index to retrieve a Field's value in a Recordset, you are referring to a position number (Index), of an Object (Field), in its Collection (Fields). This Index number starts at 0 and ends with the number of items in the Collection - 1. Since [LastName] is the 3rd Field defined in tblEmployees, an Index of 2 (zero based) will be used to retrieve its value. As Objects are added to, or removed from a Collection, the position of other Objects may be affected because Access automatically updates Index numbers when a Collection changes. For this reason, the Index Method should not be used for retrieving Field values in Recordsets.

[NeoPa]

Accessing a field via its index can be a very important concept nevertheless (hence its inclusion).

Consider the situation where you have to output all fields from a record, but you don't want to tie your code down to specific field names for reasons of reusability of code, or even simply that the record layout for you table is prone to change over time.
In this case, a loop incrementing the index number (or even a For Each {variable} In {collection) type loop) would give you the required results.

[ADezii]

Accessing a field via its index can be a very important concept nevertheless (hence its inclusion).

Consider the situation where you have to output all fields from a record, but you don't want to tie your code down to specific field names for reasons of reusability of code, or even simply that the record layout for you table is prone to change over time.
In this case, a loop incrementing the index number (or even a For Each {variable} In {collection) type loop) would give you the required results.

Good point, Neo.

[RisoSystems]

@ADezii - thanks for the article. I, too, have a use for the Index method. When you have a query which results in Sums, Averages, Counts or other calculated values, it can be challenging to retrieve the values. The index syntax is fast and easy in these situations.

[ADezii]

@RisoSystems:
Good point. It also comes in handy when you have a Generic, Portable, Routine that will accept a Recordset Object and iterate the Fields in some manner. To be Portable, you must use the Index Method.

[Bluegoo]

I've been using MyRS("LastName"). Is that a valid 5th method?

[Oralloy]

This may sound lame, but the "String" and "Variable" methods appear to me as fundamentally identical; the only difference is the source of the string. What am I missing?

[ADezii]

but the "String" and "Variable" methods appear to me as fundamentally identical; the only difference is the source of the string.

Agree with you wholeheartedly.

[Oralloy]

As an observation, looking up fields by index is highly useful, when you have large Data-Sets to work through. This can provide a noticeable throughput improvement (time savings).

Another by index lookup is when you have to perform a process against a query that you know nothing about, for example converting a query result into table in a web page. Or, for the brave, inserting a table into a uSoft-Word document.

The recipe is something of the sort:

1. Execute your Query.
2. Retrieve the RecordSet.
3. Get indices for your required fields.
4. Use indices instead of string-match look-up.
5. Do something with the data.

[respinosa]

Method #3 is the only one that works with multi-value fields. Thanks, that was a lifesaver!