473,327 Members | 2,065 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,327 developers and data experts.

Accessing Field Values in Recordsets

ADezii
8,834 Expert 8TB
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 #1
9 75819
NeoPa
32,556 Expert Mod 16PB
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 #2
ADezii
8,834 Expert 8TB
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 #3
@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 #4
ADezii
8,834 Expert 8TB
@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 #5
I've been using MyRS("LastName"). Is that a valid 5th method?
Oct 2 '16 #6
Oralloy
985 Expert 512MB
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 #7
ADezii
8,834 Expert 8TB
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 #8
Oralloy
985 Expert 512MB
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 #9
Method #3 is the only one that works with multi-value fields. Thanks, that was a lifesaver!
Apr 3 '19 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Wishing I was skiing mom | last post by:
In form1, which is an item master maintenance screen, I have a button which launches form2. Form2 contains an item master datagrid. After the user double-clicks the desired item record in form2 I...
1
by: Megan | last post by:
I have 2 tables, CASE and ISSUE. My table, CASE, stores the issues pertaining to that particular case. The table, CASE, was designed with 2 fields, IssueID and IssueDesc. Right now, all of the...
1
by: Richard Dixson | last post by:
Under ASP.NET (C#) I want to create a page that people can use to submit questions. This page will consist of a table with several rows of information, like: Name: Bill Smith Job title:...
2
by: Richard Dixson | last post by:
Under ASP.NET (C#) I want to create a page that people can use to submit questions. This page will consist of a table with several rows of information, like: Name: Bill Smith Job title:...
0
by: rlueneberg | last post by:
I am having difficulty with preserving field values in a DetailsView control after clicking the insert button event. protected void DetailsView1_ItemInserted(object sender,...
5
by: mehere | last post by:
Please excuse me if I am posting in wrong NG. Can someone explain how I can use javascript (I am assuming this is the best way to do it) to add two field values together. I.e. field 1 plus field...
5
by: sklett | last post by:
I'm not real experienced with asp.net so this may be obvious. I've got a situation where some of my client side javascript is causing my posted form's controls to lose their values. In other...
3
by: Bjorn Sagbakken | last post by:
What is the best way to read single field values from a dataset? The dataset is populated with a customer record, showing well in a gridview. But I also want to read the single field values, like...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.