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?
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:
Import zsfrmVerifySettings from 04-03.MDB into your own database.
Load zsfrmVerifySettings in form view. As it loads, it will build the object property inventory, creating tables and queries as necessary.
Once the form has presented the list of forms and reports, click on the items you want documented. Click again on an item to remove it from the list of selected items. In Figure 4-5, for example, three items are to be documented. You can also use the Select All, Select All Forms, and Select All Reports buttons to select groups of items.
When you've selected all the forms or reports you'd like to manipulate, click the Document Selected Items button. This will work its way through the list of selected items and document all the properties of each control on each of those items.
When the documentation process is finished (it may take some time to work through all the items you've selected), click the View Results button. This will open zsqryProperties, which is shown in Figure 4-6. It lists all the properties of all the objects and the sections and controls on those objects.
Use the toolbar buttons to control sorting and filtering so that you can view only the properties you want for the objects in which you're interested.
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):
Open zsfrmVerifySettings and select all the forms in your application from the list of objects.
Click on the Document Selected Items button. Go out for lunch while it does its work.
Once it's finished, click on the View Results button, which brings up zsqryProperties, showing one row for each property of each object you selected. For a large set of forms or reports, this query could return tens of thousands of rows.
Choose Records Filter Advanced Filter/Sort and build a filter that sorts on Parent and limits the output to rows with "ControlTipText" in the PropName field and "Command Button" in the ObjectType field. Figure 4-7 shows this filter.
Apply the filter by clicking on the funnel button on the toolbar or by right-clicking on the filter design area and choosing Apply Filter/Sort. You will see only the rows for the command buttons' ControlTipText properties. Look for the rows in which there's no value in the PropValue column. Those are the buttons that don't yet have a value set. Figure 4-8 shows the output of the sample query. It's quite clear which buttons don't yet have their ControlTipText properties set.
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.
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
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
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
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
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.
For more information on using DAO in Access databases, see How Do I Use Data Access Objects (DAO) in New Databases? in the Preface.