To see an example of this, open or create a new report (the example assumes this has been
saved with the name MyReport). While in Design View, open the Property Sheet by pressing
ALT+ENTER(assuming that you do not already have the property sheet open) and scroll down
to the propertyCaption. This will allow you to enter a text string that will appear in the
control bar at the top of your report. However, you can change these properties using code, in
response to user actions.
An example of the syntax for reading properties is shown here:
MsgBox Reports(“MyReport”).Caption
Bear in mind that the report has to be open for it to be in theReportscollection.
All collections have indexes that define individual objects within the collection. The title
"MyReport"shown in parentheses defines that it is MyReport within theReportscollection
that the code is referring to. There could be several reports loaded at once, and this is how
VBA distinguishes between them.
Some objects are grouped together into other objects, or collections. For example, Access
can have many forms or reports open at once. Each individual form or report is an object. All
currently open Reports in the Access application are grouped together into the Reports object
or collection. Accessing an individual item or member in a collection involves either specifying
its numeric position in the collection or accessing its name (if it has one). The preceding code
example accesses the report namedMyReportin theReportscollection and displays the
caption property.
Note that a dot is used as a separator between the object and the property. This is a bit like
using a backslash () when defining pathnames for files. You can have more than one dot
separator because objects can have sub-objects and properties can have subproperties. For
example, aTableDefis a collection ofTableDefs(table definitions), so one of the properties
of theTableDefobject is aFieldscollection. If you want to refer to one field out of the
collection, it would look like this:
MsgBox CurrentDb.TableDefs("MyTable").Fields( 0 ).Name
This will display Name of the first field in the table MyTable. This demonstrates how the
property is part of the overall picture of the object that is the Table. This can also be used to
display other field properties such as Type and Size.
You can also change properties if they are not read-only. For example, you can change the
name of a field by using the following code:
CurrentDb.TableDefs("MyTable").Fields( 1 ).Name = "NewTest"
This will change the name of the second field in the table (the Fields index starts at zero) to
NewTest. This is an example of needing to know what you are doing when writing to properties
so that you can preserve the integrity of your application. The danger is that if you change the
name of field names from within VBA code, it will almost certainly have a direct effect on
queries, forms, and reports that use that table.
Just because the field name has changed does not mean Access automatically changes
every object that is dependent on that table. So you would need to write extensive code to
ensure that all the relevant changes are made throughout the database.
Chapter 14: The Access Object Models 179