Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

اینجــــا یک کتابخانه دیجیتالی است

با بیش از 100000 منبع الکترونیکی رایگان به زبان فارسی ، عربی و انگلیسی

Access Cookbook, 2nd Edition [Electronic resources] - نسخه متنی

Ken Getz; Paul Litwin; Andy Baron

| نمايش فراداده ، افزودن یک نقد و بررسی
افزودن به کتابخانه شخصی
ارسال به دوستان
جستجو در متن کتاب
بیشتر
تنظیمات قلم

فونت

اندازه قلم

+ - پیش فرض

حالت نمایش

روز نیمروز شب
جستجو در لغت نامه
بیشتر
لیست موضوعات
توضیحات
افزودن یادداشت جدید










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"


/ 232