By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,640 Members | 2,118 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Accessing Field Values in Recordsets

ADezii
Expert 5K+
P: 8,634
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.
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database, MyRS As DAO.Recordset
  2. Dim strFieldName As String
  3.  
  4. strFieldName = "LastName"
  5.  
  6. Set MyDB = CurrentDb()
  7. Set MyRS = MyDB.OpenRecordset("tblEmployees", dbOpenForwardOnly)
  8.  
  9. Do While Not MyRS.EOF
  10.   'Method 1 - the Bang or Exclamation Method
  11.   Debug.Print MyRS![LastName]
  12.  
  13.   'Method 2 - the String Method
  14.   Debug.Print MyRS.Fields("LastName")
  15.  
  16.   'Method 3 - the Index Method (not recommended - see Note #2)
  17.   Debug.Print MyRS.Fields(2)
  18.  
  19.   'Method 4 - the Variable Method
  20.   Debug.Print MyRS.Fields(strFieldName)
  21.   MyRS.MoveNext
  22. Loop
  23.  
  24. MyRS.Close 
  25.  
  26. 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.
Apr 20 '07
Share this Article
Share on Google+
9 Comments


NeoPa
Expert Mod 15k+
P: 31,487
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.
Apr 26 '07

ADezii
Expert 5K+
P: 8,634
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.
Apr 26 '07

P: 11
@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.
Oct 21 '11

ADezii
Expert 5K+
P: 8,634
@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.
Oct 21 '11

P: 1
I've been using MyRS("LastName"). Is that a valid 5th method?
Oct 2 '16

Oralloy
Expert 100+
P: 983
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?
Oct 2 '16

ADezii
Expert 5K+
P: 8,634
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.
Oct 2 '16

Oralloy
Expert 100+
P: 983
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.
Oct 3 '16

P: 2
Method #3 is the only one that works with multi-value fields. Thanks, that was a lifesaver!
Apr 3 '19