Recipe 7.9 Handle Object Properties, in General

7.9.1 Problem

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?

7.9.2 Solution

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.

Figure 7-13. frmTestProperties lets you set and get the Description property of any 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.

Table 7-7. Using the acbGetProperty and acbSetProperty functions

Function name	Usage	Parameters	Returns
acbGetProperty	Retrieve the value of the specified property of the specified object.	obj As Object: a reference to any existing object. strProperty As String: the name of the property to retrieve.	The value of the requested property, or Null if that property or object doesn't exist.
acbSetProperty	Set the value of the specified property of the specified object.	obj As Object: a reference to any existing object. strProperty As String: the name of the property to set. varValue As Variant: the value of the property; varPropType As Variant (optional): the data type of the new property (if the code has to create it). One of dbBoolean, dbByte, dbInteger, dbLong, dbCurrency, dbSingle, dbDouble, dbDate, dbText, dbLongBinary, dbMemo, or dbGUID. If you skip this, Access will use dbText.	The old value of the property, if it existed, or Null otherwise.

The only objects to which you can add properties are databases, tables, queries, fields, indexes, and relations. Attempts to add a new property to any other kind of object will fail.

To use these new functions in your own applications, follow these steps:

1. Import basHandleProperties into your application.

2. To set a property, call acbSetProperty. This function returns the old value of the property. For example:

   Dim db As DAO.Database Dim varOldDescription As Variant Set db = CurrentDb( ) varOldDescription = acbSetProperty(db, "Description", "Sample Database") If Not IsNull(varOldDescription) Then    MsgBox "The old Description was: " & varOldDescription End If

3. To get the value of a property, call acbGetProperty. For example:

   Dim db As DAO.Database Dim varDescription As Variant Set db = CurrentDb( ) varDescription = acbGetProperty(db, "Description") If Not IsNull(varDescription) Then    MsgBox "The database description is: " & varDescription End If

7.9.3 Discussion

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

If you want to refer to the same property with an explicit reference, you can use this syntax:

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 create a new property, you must follow these three steps:

1. Create a new property object, using the CreateProperty method of an existing object.

2. Set the properties of this new property, including its name, type, and default value (you can merge this step with the previous step by supplying the information when you call CreateProperty).

3. Append the new property to the Properties collection of the host object. For example, to add a Description property to the current database, you might write code like this:

   Dim db As DAO.Database Dim prp As Property Set db = CurrentDb( ) ' Step 1 Set prp = db.CreateProperty( ) ' Step 2 prp.Name = "Description" prp.Type = dbText prp.Value = "Sample Database" ' Step 3 db.Properties.Append prp

   To combine Steps 1 and 2, you could set the properties of the new property at the time you create it:

   ' Steps 1 and 2 Set prp = db.CreateProperty("Description", dbText, "Sample Database") ' Step 3 db.Properties.Append prp

   Once you've followed these steps, you should be able to retrieve the database's Description property with a statement like this (note that you must use the explicit reference to the Properties collection in this case, because Description is a user-defined property):

   Debug.Print CurrentDb.Properties!Description

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:

• If you ask it to set a property that doesn't currently exist, it attempts to create that property and then sets its value.

• The data type is declared optional, using the DataTypeEnum enumerated type, with dbText as the default value. If you don't tell it what the data type of the new property is to be (i.e., if you leave that parameter blank), the code will use the dbText type by default.

• The function returns the old value of the property, if there was one, so you can store it away and perhaps reset it once you're done with your application.

• To make sure the code will work with either user-defined or built-in properties, the code uses an explicit reference to the Properties collection.

• To tell if it needs to try to create the property, the function traps the acbcErrPropertyNotFound error condition (error 3270); if that error occurs, it uses the CreateProperty method to try to create the necessary property.

• If you try to assign an invalid property value, Access triggers the acbcErrDataTypeConversion error condition (error 3421). In that case, there's not much acbSetProperty can do besides alerting you to that fact and returning Null.

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"