DatabaseLessons.com

'Serving the Microsoft® Access
Community since 1997'

News/Blog
Tables
Queries
Forms
Reports
Modules
Miscellaneous
Subscribe
Free Samples
Videos
Services
Links
About Us

 

 

Referring to Fields and Form Objects

In my article on Looping Through Fields In Unbound Forms I used VBA constructs like this in my form:
   me(fld.name) = rst(fld.name)
This allowed easier looping compared to:
   me.txtCompany = rst!CustCompany
Let's look at the various ways of referring to fields in a recordset or objects on a form.

(article continues after sponsor spot)



First, several ways to refer to fields from records in a recordset

  • rst!CustCompany
  • rst![Cust Company]   (when there is a space in the field name)
  • rst("CustCompany")
  • rst(strFieldName)   (where strFieldName is a string variable containing the name of the field)

Next, several ways to refer to objects on a form (or report)

  • Me.CustCompany
  • Me.[Cust Company]   (when there is a space in the field name)
  • Me("CustCompany")
  • Me(strTextBoxName)   (where strTextBoxName is a string variable containing the name of the textbox)

What I love about the final choice in each case is the ability to create the variable's contents "on-the-fly".

I created a form that had a 15 by 15 grid of text boxes. The names were like this example, a0712, which was the name of the textbox in the 7th column, 12th row. You could easily write some code that would determine the value of the row and column and then do something like this ...

Dim iR as integer
Dim iC as integer
'--- determine which row and column using whatever logic applies
'--- for this article I will just define some constant values
iC = 7
iR = 12
'--- now assign a value to the appropriate textbox
Me("a" & Format(iC, "00") & Format(iR, "00")) = "abc123"

That final VBA statement starting with "Me" will determine the name of the textbox "on-the-fly", such that the VBA interpreter sees
   Me("a0712")
when the code executes.

NOTE: All the VBA code segments on the Database Lessons site assume that you have DAO references active. If you are not sure what this means, and you are using Microsoft Access 2000 or higher, click here.

Happy Coding

 
Note: This web site dedicated to MS Access database users is an independent publication of Richard W. Killey and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.
 

 

© 2006, 2007, 2008 Richard W. Killey. All Rights Reserved. - Privacy Policy