Automating Microsoft Access with VBA

As you've seen, each of the objects in any of the object collections is represented by an AccessObject object. This object has a set of built-in properties that tell you a few things about the object:

• CurrentView A constant that tells you the current view of an object (for example, Design view or Datasheet view) if the object is open.

• DateCreated The date that the object was created.

• DateModified The date that the object was last changed.

• FullName The full name (including the path) of the object.

• IsLoaded True if the object is currently open, False if the object is currently closed.

• Name The name of the object.

• Parent The collection that contains this object.

• Properties A collection of the properties of the object.

• Type A constant representing the type of the object, such as form, report, or table.

NOTE

The FullName property applies only to data access pages, which are stored as external files. It's empty for all other objects.

The AccessObject object offers an additional, advanced capability: you can create your own properties for these objects. This is useful in much the same way as the Tag property is on a form; you can use your own properties to store custom information that is used in your code.

For more information on the Tag property, see "Working with the Tag Property," p. 202.

Take another look at the FormList form. One problem with it as you initially built it is that it lists all the forms, even ones that you probably don't want to open all by themselves. For example, showing the various sample forms, switchboards, and subforms in the list is more confusing than anything else. To deal with this problem, you can use a custom property to tell the FormList form whether to display a particular form.

To start, you need a way to add your own custom property to the forms that you want to display. Typically, this is the sort of thing that you want to do when you're designing a database; there's no need to let the users adjust this property. So you can add a procedure to the sample database to handle the task, like so:

Public Sub ShowInFormList(strFormName As String) ' Mark the specified form so that it ' will be displayed on the FormList ' form ' Get the AccessObject corresponding to ' the specified form Dim AO As AccessObject Set AO = CurrentProject.AllForms(strFormName) ' Create a new property on the object AO.Properties.Add "ShowInFormList", True Debug.Print "Property set" End Sub

Each AccessObject object has its own collection of custom properties named (not surprisingly) Properties. This code starts by retrieving the AccessObject object that corresponds to the specified form. It then uses the Add method of the Properties collection to add a new custom property to the AccessObject. The two arguments to this method are the name of the property (which must be a string) and the initial value of the property (which can be any variant). So the code as shown adds a new property named ShowInFormList and sets its initial value to True. Figure 15.3 shows how you might call this procedure from the Immediate window to add the ShowInFormList property to the Clients form.

TIP

The Properties collection of an AccessObject object contains only the custom properties that you've added to the object. It doesn't contain any of the standard properties such as Name or DateCreated.

In the sample database, we used the ShowInFormList procedure to add the custom property to the Clients, Employees, Projects, and Timeslips forms.

The next step is to modify the code behind FormList to display only the forms with the custom property present and set to True. Here's the revised code:

Private Sub Form_Load() ' Stock the listbox with the names of ' all marked forms in the database Dim AO As AccessObject ' Keep going if the custom property is missing On Error Resume Next For Each AO In CurrentProject.AllForms If AO.Properties("ShowInFormList").Value = True Then If Err = 0 Then ' Only add the form if the property is ' actually present and set to True lbForms.AddItem (AO.Name) End If Err.Clear End If Next AO End Sub

You need to exercise some care when working with custom properties. If you try to retrieve a value for a property that doesn't exist, VBA will raise an error. This code shows one way to deal with the situation. First, it sets the error handler to On Error Resume Next to make sure that any errors are not fatal. Next, for each form, it tries to retrieve the value of the custom property. There are three possibilities for what happens here:

• If the property doesn't exist, the code proceeds to the next line, but the built-in Err variable (which holds the number of the most recent error) is set to some number other than zero. In this case, the line to add the form name is skipped.

• If the property exists and its value is False, the check for True fails and the line to add the name to the form is also skipped.

• If the property exists and its value is True, the check for an error returns zero and the form's name is added to the listbox.

Figure 15.4 shows the FormList form after making these code changes. As you can see, it only displays the forms that have the custom ShowInFormList property set to True.