MS-Access / Getting Started

---

Setting and Retrieving Built-In Object Properties

The built-in properties that you would be most familiar with are those that affect the way form and report controls work. Even DAO objects have properties that can be manipulated in the same way. For example, to change a TextBox's Enabled property, you can refer to it in either of the following two ways:

Me!TextBox1.Enabled = False
Me!TextBox1.Properties("Enabled") = False

To check the name of a recordset's Field object, you retrieve its Name property. The following two examples are equivalent ways to check this property:

Debug.Print rst.Fields(0).Name
Debug.Print rst.Fields(0).Properties("Name")

All objects have a default property, which is the property that is referenced when you call the object itself. For example, when you test a Field object directly, you are actually referring to its Value property. The following lines of code all refer to the Field object's Value property:

rst.Fields(0)
rst.Fields(0).Properties("Value")
rst.Fields(0).Properties(0)
rst.Fields(0).Value

Creating Object Properties

You can create user-defined properties for persistent DAO objects, such as tables and queries. You can't create properties for nonpersistent objects, such as recordsets. To create a user-defined property, you must first create the property, using the Database's CreateProperty method. You then append the property using the Properties collection's Append method. That's all there is to it.

Using the example of a field's Description property, the following code demonstrates just how easy it is:

Public Sub SetFieldDescription(strTableName As String, _
    strFieldName As String, _
    varValue As Variant, _
)
    Dim dbs As DAO.Database
    Dim prop As DAO.Property
    Set dbs = CurrentDb

    'Create the property
    Set prop = dbs.CreateProperty("Description", dbText, varValue)

    'Append the property to the object Properties collection
    dbs(strTableName)(strFieldName).Properties.Append prop
    Debug.Print dbs(strTableName)(strFieldName).Properties("Description")

    'Clean up
    Set prop = Nothing
    Set dbs = Nothing
End Sub

You could even create a special user-defined property for a table in the same way, as the following code shows. This approach can be used with all persistent objects.

Public Sub CreateSpecialTableProp(strTableName As String, _
    strPropName As String, _
    lngPropType As DataTypeEnum, _
    varValue As Variant)

    Dim dbs As DAO.Database
    Dim prop As DAO.Property

    Set dbs = CurrentDb

    'Create the property
    Set prop = dbs.CreateProperty(strPropName, lngPropType, varValue, False)

    'Append the property to the object Properties collection
    dbs(strTableName).Properties.Append prop

    Debug.Print dbs(strTableName).Properties(strPropName)

    'Clean up
    Set prop = Nothing
    Set dbs = Nothing
End Sub

For another example, let's say you wanted to create a Yes/No field, but tell Access to make the field a checkbox instead of a text box. You can create the DisplayControl property to specify the type of control for Access.

Public Sub CreateYesNoField(strTableName As String, _
	strFieldName As String)

    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim prop As DAO.Property

    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs(strTableName)

    'Create and append the field
    Set fld = tdf.CreateField(strFieldName, dbBoolean)
    tdf.Fields.Append fld

    'Create the property
    Set prop = dbs.CreateProperty("DisplayControl", _
	dbInteger, acCheckBox)

    'Append the property to the object Properties collection
    fld.Properties.Append prop

    'Clean up
    Set prop = Nothing
    Set fld = Nothing
    Set tdf = Nothing
    Set dbs = Nothing
End Sub