Access Cookbook - Ken Getz [160]
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:
Import basHandleProperties into your application.
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
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
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:
Create a new property object, using the CreateProperty method of an existing object.
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