Access Cookbook - Ken Getz [91]
' 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
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
TIP
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.
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