Recipe 4.3 Verify That Objects Use Consistent Settings

4.3.1 Problem

You've finished your application and you're ready to deliver it, but you notice that your use of color, fonts, alignment, and other layout properties isn't consistent across all your forms or reports. You know you can manually check the values of all the properties of all the controls on your forms and reports, but there's got to be a faster way. Is there some method you can use to compare similar properties for all the objects in your application?

4.3.2 Solution

Access doesn't provide a "cross-section" of your properties, which is really what you need—some way to look at properties not listed by item, but by property name, across all objects. Building on the technology introduced in the Solution in Recipe 4.2, this solution creates a group of tables containing information about all the properties on any forms or reports you select. Once it builds those tables, it constructs a query that will allow you, using the Quick Sort menu items, to view all the property settings for various objects, sorted any way you'd like. Once you've sorted the output by property name, for example, you'll quickly be able to see which objects have incorrect settings for that particular property.

The 04-03.MDB sample database includes a single form, zsfrmVerifySettings. Figure 4-5 shows the form after it has done its cataloging in Northwind.MDB, ready to present property information on three different forms. Figure 4-6 shows the output data, sorted by property name, showing that several controls have different background colors.

To use zsfrmVerifySettings to catalog properties in your own applications, follow these steps:

Figure 4-5. zsfrmVerifySettings is ready to catalog all controls on three selected forms

Figure 4-6. zsqryProperties allows you to sort by any categories to view your property settings

For example, you might want to ensure that all command buttons on all your forms have their ControlTipText properties set. To do that, follow these steps (assuming you've followed the previous steps):

Figure 4-7. This filter limits rows to the ControlTipText property of command buttons

Figure 4-8. The result query shows which buttons don't have their ControlTipText properties set

4.3.3 Discussion

To build the list of forms and reports, zsfrmVerifySettings borrows code from the example in the Solution in Recipe 4.2. Instead of looping through all the collections, however, it works only with the Forms and Reports collections. Otherwise, the mechanics of creating the list of objects are the same as in the Solution in Recipe 4.2; investigate that topic if you'd like more information on building the object inventory.

4.3.3.1 Creating the temporary tables and query

The Solution in Recipe 4.2 created a single table, zstblInventory, to hold the list of objects. In this case, however, you need three tables (zstblInventory for main objects, zstblSubObjects for objects on those forms or reports, and zstblProperties for property information). You also need a query (zsqryProperties) to join the three tables and display the output. The CreateTables function, shown here, uses DDL queries to create each of the necessary tables (see the Solution in Recipe 1.15 for more information on DDL queries) and DAO to create the query (see Chapter 6 for more information on using DAO):

Private Function CreateTables( ) As Boolean

    ' Return True on success, False otherwise.
    
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    
    On Error GoTo HandleErr
    
    Set db = CurrentDb
    
    db.Execute "DROP TABLE zstblInventory"
    db.Execute "DROP TABLE zstblSubObjects"
    db.Execute "DROP TABLE zstblProperties"
    
    ' Create zstblInventory.
    strSQL = "CREATE TABLE zstblInventory (Name Text (255), " & _
     "Container Text (50), DateCreated DateTime, " & _
     "LastUpdated DateTime, Owner Text (50), " & _
     "ID AutoIncrement Constraint PrimaryKey PRIMARY KEY)"
    db.Execute strSQL
    
    ' Create zstblSubObjects.
    strSQL = "CREATE TABLE zstblSubObjects (ParentID Long, " & _
     "ObjectName Text (50), ObjectType Text (50), " & _
     "ObjectID AutoIncrement Constraint PrimaryKey PRIMARY KEY)"
    db.Execute strSQL

    ' Create zstblProperties.
    strSQL = "CREATE TABLE zstblProperties (ObjectID Long, " & _
     "PropName Text (50), PropType Short, " & "PropValue Text (255), " & _
     "PropertyID AutoIncrement Constraint PrimaryKey PRIMARY KEY)"
    db.Execute strSQL
    
    ' Create zsqryProperties.
    strSQL = "SELECT zstblInventory.Name AS Parent, " & _
     "zstblInventory.Container, zstblSubObjects.ObjectName, " & _
     "zstblSubObjects.ObjectType, zstblProperties.PropName, " & _
     "zstblProperties.PropValue FROM zstblInventory " & _
     "INNER JOIN (zstblSubObjects INNER JOIN zstblProperties " & _
     "ON zstblSubObjects.ObjectID = zstblProperties.ObjectID) " & _
     "ON zstblInventory.ID = zstblSubObjects.ParentID;"
    
    db.CreateQueryDef ("zsqryProperties")
    Set qdf = db.QueryDefs("zsqryProperties")
    qdf.SQL = strSQL

    ' If you got here, you succeeded!
    CurrentDb.TableDefs.Refresh
    CreateTables = True
    
ExitHere:
    Exit Function
    
HandleErr:
    Select Case Err
        Case acbErrTableNotFound, acbErrObjectNotFound, _
          acbErrAlreadyExists
            Resume Next
        Case Else
            CreateTables = False
    End Select
    Resume ExitHere
End Function

4.3.3.2 Getting ready to document items

When you click on the Document Selected Items button, the form walks through the list of selected items and then documents the object. The code in cmdDocumentSelected_Click does the work: it looks through the ItemsSelected collection of the list box and, for each selected item, calls either DocumentForm or DocumentReport, depending on the value in the second column of the list box. Each of those procedures requires the ID of the parent object (the form or report in question) and the name of the object. The source code for the cmdDocumentSelected_Click event procedure is:

Private Sub cmdDocumentSelected_Click( )

    ' In the list box:
    ' ParentID == Column(0)
    ' Container == Column(1)
    ' Name == Column(2)
    
    Static fInHere As Boolean
    Dim varItem As Variant
    Dim strName As String
    Dim lngParentID As Long
    
    On Error GoTo HandleErr
    ' Don't allow recursive entry. If this routine is doing
    ' its thing, don't allow more button clicks to get you
    ' in again, until the first pass has finished its work.
    If fInHere Then Exit Sub
    fInHere = True
    
    With Me.lstInventory
        For Each varItem In .ItemsSelected
            strName = .Column(2, varItem)
            lngParentID = .Column(0, varItem)
            Select Case .Column(1, varItem)
                ' This will handle only forms and reports.
                Case "Forms"
                    Call DocumentForm(strName, lngParentID)
                Case "Reports"
                    Call DocumentReport(strName, lngParentID)
            End Select
        Next varItem
    End With
    
    Call SysCmd(acSysCmdClearStatus)
    Me.cmdViewResults.Enabled = True
    
ExitHere:
        fInHere = False
    Exit Sub

HandleErr:
    MsgBox Err.Number & ": " & Err.Description, , "DocumentSelected"
    Resume ExitHere
End Sub

4.3.3.3 Visiting all the objects

The DocumentForm and DocumentReport procedures do the same things, though in slightly different ways. They both document the properties of the main object itself, followed by the properties of each of the sections (forms can have up to 5 sections, reports up to 25). Finally, both procedures walk through the collection of controls on the main object, documenting all the properties of each control. The following code shows DocumentForm, but DocumentReport is almost identical:

Private Sub DocumentForm( _
  ByVal strName As String, ByVal lngParentID As Long)
    ' You must first open the form in design mode, and then
    ' retrieve the information. With forms, you can open the
    ' form in hidden mode, at least.

    Dim db As Database
    Dim rstObj As DAO.Recordset
    Dim rstProps As DAO.Recordset
    Dim lngObjectID As Long
    Dim frm As Form
    Dim ctl As Control
    Dim intI As Integer
    Dim obj As Object
    
    On Error GoTo HandleErr
    Call SysCmd(acSysCmdSetStatus, "Getting information on form " & _
     strName & ".")
    
    Set db = CurrentDb( )
     ' No need to open the form if it's THIS form.
    If strName <> Me.Name Then
        DoCmd.OpenForm strName, View:=acDesign, WindowMode:=acHidden
    End If
    Set rstObj = db.OpenRecordset("zstblSubObjects", _ 
     dbOpenTable, dbAppendOnly)
    Set rstProps = db.OpenRecordset("zstblProperties", _
     dbOpenTable, dbAppendOnly)

    ' Handle the form properties first.
    Set frm = Forms(strName)
    AddProps rstObj, rstProps, frm, "Form", lngParentID
    
    ' Handle the five possible form sections.
    For intI = 0 To 4
        Set obj = frm.Section(intI)
        AddProps rstObj, rstProps, obj, "Section", lngParentID
Form_Next_Section:
    Next intI
    
    ' Handle all the controls.
    For Each ctl In frm.Controls
        AddProps rstObj, rstProps, ctl, GetControlType(ctl), lngParentID
    Next ctl
    
    ' Don't close the form that's running all this.
    If Me.Name <> strName Then
        DoCmd.Close acForm, strName
    End If

ExitHere:
    Exit Sub

HandleErr:
    Select Case Err
        Case acbErrInvalidSection
            Resume Form_Next_Section
        Case Else
            MsgBox Err & ": " & Err.Description, , "DocumentForm"
    End Select
    Resume ExitHere
End Sub

The procedure starts by opening the requested object in design mode so it can get the information it needs. It cannot open the objects in normal view mode, because that would run the objects' event procedures, which might have unpleasant side effects.

Starting with Access 2002, you can specify a WindowMode when you use DoCmd.OpenReport. This allows you to hide a report when you open it, which is nice when you are opening it in design view.

As shown in our example, if the code tries to open the current form, it simply skips the open step. (This means, of course, that your documentation on the current form will be different than that of other forms: it's already open in form view, and the rest will be opened in design view.) Skipping the current form isn't an issue if you're documenting reports. When it's complete, DocumentForm/Report also closes the object (as long as it wasn't the current form). This is shown in the following code fragment from the DocumentForm procedure:

' No need to open the form if it's THIS form.
If strName <> Me.Name Then
   DoCmd.OpenForm strName, View:=acDesign, WindowMode:=acHidden
End If
.
. ' All the real work happens here...
.
' Don't close the form that's running all this.
If Me.Name <> strName Then
   DoCmd.Close acForm, strName
End If

DocumentForm next opens two recordsets, to which it adds rows as it documents your objects. These are specified as append-only recordsets in order to speed up the processing. The relevant code is:

Set rstObj = db.OpenRecordset("zstblSubObjects", _
 dbOpenTable, dbAppendOnly)
Set rstProps = db.OpenRecordset("zstblProperties", _
 dbOpenTable, dbAppendOnly)

Next, the procedure documents all the properties of the main object itself. As it will do when documenting all the objects, it calls the AddProps procedure. AddProps expects to receive references to the two recordsets, a reference to the object to be documented, the text to appear in the list box for the object's type, and the ID value for the main, parent object. The code fragment that calls AddProps appears as follows:

' Handle the form properties first.
Set frm = Forms(strName)
AddProps rstObj, rstProps, frm, "Form", lngParentID

The procedure then documents the properties of the sections. For forms, there can be at most five sections (detail, form header/footer, page header/footer). For reports, there can be up to 25: the same 5 as for forms, plus a header and footer for up to 10 report grouping sections. Note that any section may or may not exist. Therefore, the code traps for this error and jumps on to the next numbered section if the current one doesn't exist. The portion of the code that documents section properties is:

   ' Handle the five possible form sections.
   For intI = 0 To 4
      Set obj = frm.Section(intI)
      AddProps rstObj, rstProps, obj, "Section", lngParentID
Form_Next_Section:
   Next intI

Finally, DocumentForm/Report visits each of the controls on the form or report, calling AddProps with information about each control:

' Handle all the controls.
For Each ctl In frm.Controls
   AddProps rstObj, rstProps, ctl, GetControlType(ctl), lngParentID
Next ctl

4.3.3.4 Recording property information

The AddProps procedure, shown here, does the work of recording information about the selected object into zstblSubObject and about all its properties into zstblProperties. Note the large error-handling section; several properties of forms, reports, sections, and controls are not available in design mode, and attempting to retrieve those property values triggers various error messages.

Private Sub AddProps(rstObj As DAO.Recordset, _
  rstProps As DAO.Recordset, obj As Object, _
  ByVal strType As String, ByVal lngParentID As Long)
  
    Dim lngObjectID As Long
    Dim prp As Property
    
    On Error GoTo HandleErr

    rstObj.AddNew
        rstObj("ParentID") = lngParentID
        rstObj("ObjectName") = obj.Name
        rstObj("ObjectType") = strType
        ' Get the new ID
        lngObjectID = rstObj("ObjectID")
    rstObj.Update
    For Each prp In obj.Properties
        rstProps.AddNew
            rstProps("ObjectID") = lngObjectID
            rstProps("PropName") = prp.Name
            rstProps("PropType") = prp.Type
            ' Store the first 255 bytes of the
            ' property value, converted to text.
            rstProps("PropValue") = Left(prp.Value & "", 255)
        rstProps.Update
    Next prp

ExitHere:
    Exit Sub
    
HandleErr:
    Select Case Err.Number
        ' Some property values just aren't available in the design view.
        Case acbErrInvalidView, acbErrNotInThisView, _
         acbErrCantRetrieveProp, acbErrCantGetProp
            Resume Next
        Case Else
            MsgBox Err.Number & ": " & Err.Description, , "AddProps"
    End Select
    Resume ExitHere
End Sub

To add a row about the object to zstblSubObjects, AddProps uses the AddNew method of the recordset and then fills in the appropriate fields. Just like on an Access form, when you add a new row to a recordset, Access fills in any autonumber values as soon as you begin editing the row. Here, we grab that new ObjectID value and store it in the variable lngObjectID, for use later as the object ID in the related properties table:

rstObj.AddNew
    rstObj("ParentID") = lngParentID
    rstObj("ObjectName") = obj.Name
    rstObj("ObjectType") = strType
    ' Get the new ID
    lngObjectID = rstObj("ObjectID")
rstObj.Update

Next, AddProps loops through all the properties in the object's Properties collection, adding a row for each to zstblProperties. Note that because tables don't support Variant fields, we've set the PropValue field to be a 255-character text field; the code converts the property value to text and truncates it to no more than 255 characters. Few properties require more text than that, but some, such as the row sources of combo boxes, could. You might want to use a memo field for these properties instead. Memo fields are somewhat less efficient, but they are more efficient starting with Jet 4.0 (Access 2000 or later) than they were in previous versions.

For Each prp In obj.Properties
    rstProps.AddNew
        rstProps("ObjectID") = lngObjectID
        rstProps("PropName") = prp.Name
        rstProps("PropType") = prp.Type
        ' Store the first 255 bytes of the
        ' property value, converted to text.
        rstProps("PropValue") = Left(prp.Value & "", 255)
    rstProps.Update
Next prp

The rest of the code in zsfrmVerifySettings's module deals with selecting items in the list box. You're welcome to peruse that code, but it's not crucial to understanding the object/property inventory.

4.3.3.5 Comments

If you're interested in working with multiselect list boxes in your applications, take the time to work through the code that manipulates the list box in this example. The code uses the Selected property of the list box, setting various rows to be selected or not by setting the value of the property. It also makes heavy use of the Column property, allowing random access to any item stored in the list box.

More than for most of the solutions in this book, effective use of the techniques covered here requires some of your own imagination. Not only are the techniques for providing the object and property inventory interesting, but the output itself can be useful as well. Since we developed this example, we've used it in several applications to verify that all the controls used the same fonts, that all the command buttons had their ControlTipText properties set, and that all the detail sections used the same background color. You should strive for design consistency in your applications, and this tool can help you achieve it.

4.3.4 See Also

For more information on using DAO in Access databases, see How Do I Use Data Access Objects (DAO) in New Databases? in the Preface.