Access Cookbook - Ken Getz [162]
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"
7.10. Detect Whether an Object Exists
Problem
You create and delete objects as your application runs. At some point, you need to be able to tell whether an object exists and make decisions based on that fact. But you can't find a function in Access that will tell you if a specific object already exists. Are you missing something? This ought to be a basic part of the product!
Solution
You haven't missed anything: Access really doesn't supply a simple method of determining if a specific object already exists. On the other hand, this is really quite simple, as long as you understand two important concepts: Access's support for DAO Container objects, and the ways you can use error handling to retrieve information. This solution uses these two subjects to provide a function you can call to check for the existence of any object.
Load and run frmTestExist from 07-10.MDB. This form, shown in Figure 7-14, lets you specify an object name and its type and then tells you whether that object exists. Certainly, you wouldn't use this form as-is in any application—its purpose is to demonstrate the acbDoesObjExist function in basExists (07-10.MDB). To make your exploration of frmTestExist easier, Table 7-8 lists the objects that exist in 07-10.MDB. Try entering names that do and don't exist, and get the types right and wrong, to convince yourself that the acbDoesObjExist function does its job correctly.
Figure 7-14. frmTestExist lets you check for the existence of any object in the current database
Table 7-8. The sample objects in 07-10.MDB
Object name
Object type
tblTest
Table
qryTest
Query
frmTest
Form
frmTestExist
Form
basExists
Module
Follow these steps to use acbDoesObjExist in your own applications:
Import the module basExists from 07-10.MDB. This module contains the acbDoesObjExist function.
To check for the existence of any object, call acbDoesObjExist, passing to it the name of the object to check for and a value from the AcObjectType enumeration indicating the object's type. The type parameter must be chosen from the values acTable, acQuery, acForm, acReport, acMacro, or acModule. For example, to check for the existence of a table