Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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 needsome 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:

1. Import zsfrmVerifySettings from

   04-03.MDB into
   your own database.

2. Load zsfrmVerifySettings in form view. As it loads, it will build the
   object property inventory, creating tables and queries as necessary.

3. 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.

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

4. 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.

5. 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.

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

6. 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):

1. Open zsfrmVerifySettings and select all the forms in your application
   from the list of objects.

2. Click on the Document Selected Items button. Go out for lunch while
   it does its work.

3. 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.

4. 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.

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

5. 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.

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

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.