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.
