|[ Team LiB ]|
Recipe 7.9 Handle Object Properties, in General
You don't understand how to get and set property values in Access. It seems as if there are different kinds of properties, and what works for one object and property doesn't work for another. Is there some way to settle this once and for all?
There really are two kinds of properties for objects in Access. Built-in properties are those that always exist for an object, and user-defined properties are properties that you or Access creates for an object when requested. The syntax for referring to each type is different, but this solution provides a method that works for either type. This solution uses the user-defined Description property as an example, but the techniques will work just as well for any other property. The interesting part of this solution is that the Description property is not a built-in property, and attempting to set or retrieve this property using the standard object.property syntax will fail.
This solution provides a sample form, which is useful only for demonstrating the technique. The real power of the solution comes from the module, basHandleProperties, which provides procedures you can use to set and get any kind of property. To try out the sample form shown in Figure 7-13, load and run frmTestProperties from 07-09.MDB. Choose a table from the list of tables, and notice the Description property shown in the text box below the list. If you choose a field from the list of fields, you'll also see the description for that field in the text box below the list. You can enter new text into the two text boxes, and the code attached to the AfterUpdate event of either text box will write the text back to the Description property of the selected table or field.
The sample form uses two functions from basHandleProperties, as shown in Table 7-7. These functions allow you to get or set any property of any object, as long as the object either already supports the property you're working with or allows you to create new properties to add the property if it doesn't already exist.
To use these new functions in your own applications, follow these steps:
Access provides two types of properties: built-in and user-defined. Built-in properties always exist and are part of the definition of the object. For example, the Name and Type properties are crucial for the existence of most objects. These are built-in properties. On the other hand, the Jet engine allows you to create new properties and add them to the Properties collection for all the objects it supports, including TableDefs, QueryDefs, Indexes, Fields, Relations, and Containers. These are user-defined properties.
In addition, Access itself, as a client of the Jet engine, creates several properties for you. For example, when you right-click on an object in the Database Explorer and choose Properties from the floating menu, Access allows you to specify the Description for the object. That Description property doesn't exist until you request that Access create it, using that dialog or in your own VBA code. The same goes for the Caption, ValidationRule, and DefaultValue properties of fields: those properties don't exist until you request that Access create them for you.
If you attempt to retrieve or set the value of a property that doesn't yet exist, Access will trigger a runtime error. Your code must be ready to deal with this problem. In addition, you may be used to working with built-in properties, to which you can refer using the simple object.property syntax. This syntax works only for built-in properties. For user-defined (and Access-created user-defined) properties, you must refer to the property using an explicit reference to the Properties collection that contains it. For example, to set the Format property of the City field within tblCustomers, you'll need an expression like this (and this expression will fail with a runtime error if the Format property hasn't yet been set):
CurrentDb.TableDefs("tblCustomers"). _ Fields("City").Properties("Format") = ">"
Because you can always refer to any property using an explicit reference to the Properties collection, you can simplify your code, and ensure that all property references work, by using the same syntax for built-in and user-defined properties. For example, field objects support the AllowZeroLength property as a built-in property. Therefore, this reference will work:
CurrentDb.TableDefs("tblCustomers"). _ Fields("City").AllowZeroLength = False
CurrentDb.TableDefs("tblCustomers"). _ Fields("City").Properties("AllowZeroLength") = False
This ability to refer to built-in and user-defined properties using the same syntax is the secret of the code presented in this solution.
To relieve you from worrying about the differences between user-defined and built-in properties and whether or not a property already exists for a given object, we've provided the acbGetProperty and acbSetProperty functions.
The acbGetProperty function is the simpler of the two: it attempts to retrieve the requested property. acbGetProperty may fail for two reasons: the object itself doesn't exist, or the property you've tried to retrieve doesn't exist (errors acbcErrNotInCollection and acbcErrPropertyNotFound, respectively). If either of these errors occurs, the function returns Null. If any other error occurs, the function alerts you with a message box before returning Null. If no error occurs, the function returns the value of the requested property. For an example of calling acbGetProperty, see Recipe 7.9.2 and 07-09.MDB.
The source code for acbGetProperty is:
Public Function acbGetProperty(obj As Object, _ strProperty As String) As Variant ' Retrieve property for an object. ' Return the value if found, or Null if not. On Error GoTo HandleErr acbGetProperty = obj.Properties(strProperty) ExitHere: Exit Function HandleErr: Select Case Err.Number Case 3265, 3270 ' Not in collection, not found. ' Do nothing! Case Else MsgBox Err.Number & ": " & Err.Description, , "acbGetProperty" End Select acbGetProperty = Null Resume ExitHere End Function
The acbSetProperty function is more interesting. It attempts to set the value of the property you pass to it. This function has several interesting characteristics:
The source code for acbSetProperty is:
Public Function acbSetProperty( _ obj As Object, strProperty As String, varValue As Variant, _ Optional propType As DataTypeEnum = dbText) ' Set the value of a property. On Error GoTo HandleErr Dim varOldValue As Variant ' This'll fail if the property doesn't exist. varOldValue = obj.Properties(strProperty) obj.Properties(strProperty) = varValue acbSetProperty = varOldValue ExitHere: Exit Function HandleErr: Select Case Err.Number Case 3270 ' Property not found ' If the property wasn't there, try to create it. If acbCreateProperty(obj, strProperty, varValue, propType) Then Resume Next End If Case 3421 ' Data type conversion error MsgBox "Invalid data type!", vbExclamation, "acbSetProperty" Case Else MsgBox Err.Number & ": " & Err.Description, , "acbSetProperty" End Select acbSetProperty = Null Resume ExitHere End Function
Only objects that are maintained by the Jet engine allow you to create new properties. That is, you can add properties to the Properties collections of Database, TableDef, QueryDef, Index, Field, Relation, and Container objects. You won't be able to add new properties to any object that Access controls, such as forms, reports, and controls. If you attempt to use acbSetProperty to set a user-defined property for an invalid object, the function will return Null. You can, however, use acbSetProperty and acbGetProperty with any Access object, as long as you confine yourself to built-in properties for those objects that don't support user-defined properties. For example, this code fragment will work as long as frmTestProperties is currently open:
If IsNull(acbSetProperty(Forms("frmTestProperties"), "Caption", _ "Test Properties")) Then MsgBox "Unable to set the property!" End If
User-defined properties are persistent from session to session. That is, they are saved in the TableDef along with the built-in and Access-defined properties. You can, however, delete a user-defined property using the Delete method on the property's parent collection. For example, you could delete the user-defined property defined earlier using the following statement:
CurrentDb.TableDefs("tblSuppliers").Fields("Address"). _ Properties.Delete "SpecialHandling"
|[ Team LiB ]|