Access VBA Macro Programming

automatic list boxes and providing a full hierarchy in every line of code. This example works

with the Northwind database Employees table:

Dim Db As Database

Dim MyTable As TableDef, MyField As Field

Set Db = CurrentDb

Set MyTable = Db.TableDefs("Employees")

Set MyField = MyTable.Fields("Company")

MsgBox MyField.Size

TheDimstatements create three variables:Dbas Database,MyTableas TableDef, andMyField

as Field. The firstSetstatement setsDbto point to the CurrentDb. Why can you not use the

CurrentDbobject directly here instead of going through an intermediate object? This is because

the way in which the DAO object model is set up does not allow you to use theTableDefs

collection directly on the CurrentDb object.

The secondSetstatement setsMyTableto point at theEmployeestable within theTableDefs

collection of database, and the thirdSetstatement setsMyFieldto point to the field “Company”

within the Employees table.

Now you can useMyFieldas the field object for “Company” and display the size

property of the field. This has the added advantage that all the list boxes of properties and

methods will automatically appear as you write your code to show the options available for

that particular object. TypeMyFieldand then a dot in the procedure, and the list box will

appear next to your code. You need only click the item required in the list box to complete

your code.

Hierarchy

Within the object models is a hierarchy of objects. It is important to understand how this

hierarchy works because of the implications in referring to objects. In most organizations

there exists a hierarchy of jobs, for example. In the armed forces, you have generals of varying

grades at the top of the hierarchy, with four-star generals at the very top. The structure then

cascades down to colonels, majors, captains, and lieutenants. Orders are sent down from the

top via the command structure. The object models work in a very similar way.

Consider theApplicationobject as the four-star general in charge; a structure that radiates

out from the general. TheCurrentDbcollection object could be considered a colonel, with

theTableDefscollection object below it a major. Field objects would be the captain level.

The hierarchy is very important for issuing commands, and the order in which they are

issued must go down the hierarchy. For example, a major cannot give an order to a general or

a colonel. The major must accept orders from generals and colonels, but the major can give

orders to captains and lieutenants. In the same way in the Excel object model, aFieldobject

190 Microsoft Access 2010 VBA Macro Programming