The code for today's article will show you how to access and modify the properties of various Microsoft Access objects through VBA code. So, what we're building today is an Access Object Enhancifier. Word's Spell Checker informs me there are no spelling suggestions for this word, but I'm growing more and more fond of it, Hey, if it catches on, enhancifier could even become naturalized into the English language. (I hope the White House is monitoring this site. If President Bush uses enhancifier in his next speech, I'm taking credit for it!)

Since there's a myriad of potential applications for this trick, I cannot possibly describe them all, but the principals learned may, with a little playing and practice, be applied to virtually any property related issue you face. Here's how we're going to apply this technique:

1. Loop through Pubs sales table and list all its properties
2. Loop through fields of Pubs sales table, setting Description for each
3. Loop through form controls to read RowSource property of combo box
4. Loop through form controls and set Status bar and Tool Tip text

download code for this article

Fun With Tables

This code has found its way into the pages of the DBJ site before, but without a trumpet blast to announce it, as today. In order to relink tables, you need to loop through the TableDefs collection (a collection containing all the table definitions for your database), set their Connect property and initiate the RefreshLink method for each object. This process from my February 2006 article is very similar to what we will do today.

List Table Properties
First, let's explore the code to loop through the properties of any given table.  The function, ListTableProperties, takes the name of a table in your database as an argument.  (For the sake of brevity, this example contains no error code to check if that table really exists.)  To interrogate properties, you need to create a Property object, aTableDef object, and a Database object.

Some Access expert users may omit the Database object assignment and simply use CurrentDb() as though it were your dbs object, but I've had trouble in some instances with that practice, so I always create a top-level database object and kill it when finished.

To assign the TableDef object, you use the SET command, as shown below.  Since we don't necessarily know (for this exercise) the specific property we want to view, a For Each loop is created to loop through ALL the properties and report back on their name and value.  The string variable, strOut, is used to build the list and the final results are loaded into a message box and output as the results of the function.

Public Function ListTableProperties(ByVal sTable As String) As String
    On Error GoTo Err_Handler
    
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim prp As DAO.Property
    Dim strProp As String
    Dim strOut As String
    
    DoCmd.Hourglass True
    
    Set dbs = CurrentDb
    
    strOut = sTable & vbCrLf & String(40, "-") & vbCrLf
    
    Set tdf = dbs.TableDefs(sTable)
    For Each prp In tdf.Properties
        If prp.Name = "NameMap" Or prp.Name = "GUID" Then
            ' Do nothing for these ... they return funky data
        Else
            ' Output the property name and value where value exists
            If prp.Value <> "" Then
                strOut = strOut & prp.Name & " = " & prp.Value & vbCrLf
            End If
        End If
    Next
    
    ListTableProperties = strOut
    
    MsgBox strOut
' -------------- Sample Output --------------
    'Print ListTableProperties("authors")
    '
    'authors
    '----------------------------------------
    'Name = authors
    'Updatable = True
    'DateCreated = 3/8/2006 7:14:56 AM
    'LastUpdated = 9/6/2006 1:28:49 PM
    'Attributes = 0
    'RecordCount = 23
    'Orientation = 0
    'OrderByOn = False
    'DefaultView = 2
' -------------- Sample Output --------------
    
Exit_Here:
    Set tdf = Nothing
    Set dbs = Nothing
    DoCmd.Hourglass False
    Exit Function
Err_Handler:
    MsgBox Err.Description, vbExclamation, "Error"
    Resume Exit_Here
End Function

It should be noted that this code omits any property that doesn't have a value assigned, so there are many other table properties that could be considered.  Some of these properties can be edited and some cannot.  You cannot, for example, change the date a table was created by simply reassigning the property.  You may, however, change the Orientation and OrderByOn properties by simply assigning them new values.

Set Table Column Descriptions
To demonstrate the process of assigning a value to a property, let's look at a real-life example, something you might actually require.  In this example, all fields of the given table will have their Description property set to an adapted version of the column name.  Notice that in the image below, each field of the table has a description property set to a more human-readable version of the column name, with spaces between the words.  This was accomplished with my Enhancifier utility, crude though it may be.

As an aside, this property change might interest you, because when you use the Wizard to create a form from a table, Access uses the Description property to automatically update the Status Bar Text property (something we will do in the next demo) with this value.  This is called "eating your own dog food" and Microsoft does it well.  So, if you apply this code to 'fix' all your tables so each field has a description, these values will cascade to forms made with the Forms Wizard.

Public Function SetTableColumnDescriptions(sTable As String) As Boolean
    On Error Resume Next
    
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim prp As DAO.Property
    Dim strValue As String
    Dim strOut As String
            
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs(sTable)
    
    ' Loop through all the fields (columns)
    For Each fld In tdf.Fields
        ' Grab the field name and add spaces to make it human readable
        strValue = fld.Name
        ' See the AddSpacesToName function below.  (see download file)
        strValue = AddSpacesToName(strValue)
        
        ' Attempt to read and set the property
        If fld.Properties("Description") = "" Then
            fld.Properties("Description") = strValue
        End If
        
        ' If the property didn't exist, there will have been an error and
        ' it needs to be added.
        If Err.Number = 0 Then
            ' No problem.  Property existed and the value was set.
        ElseIf Err.Number = 3270 Then
            ' This error means the property was not found.
            ' We need to create it.
            Err.Clear
            
            Set prp = fld.CreateProperty("Description", dbText, strValue)
            fld.Properties.Append prp
            
            If Err.Number <> 0 Then
                MsgBox Err.Description, vbExclamation, "Error"
            End If
        Else
            ' Not sure what the error was.  Report to user.
            MsgBox Err.Description, vbExclamation, "Error"
        End If
    Next
    
    Set prp = Nothing
    Set tdf = Nothing
    Set dbs = Nothing
End Function

This example gets a little tricky, because the way it works is that when no value for Description was previously assigned, the property doesn't actually exist.  I saw one example on the newsgroup that was more elegant than this, but it works as shown above, to simply attempt to set the property and if it errs out with Err.Number = 3270, then you know the property needs to be created.

To create a property, you need to call the Field.CreateProperty method when setting your property object and then append it to the Field.Properties collection, as shown above.  I'm using an additional function, which code is not shown anywhere in this article, but is available in the download.

Fun With Forms

Working with forms is just like working with tables, but there are different properties and a different set of objects that forms contain.  While tables have fields (columns), forms contain controls.  Controls have properties as well, that may be read and/or changed.

Modify Form Controls
The process here is the same as the example above, but instead of setting a TableDef object to 'sales' we will set an Access.Form object to a form, namely 'frmEmployees' in our example.  Instead of looking at the properties of a Field object, we will interrogate the Control object for its properties.  Unlike table fields, form controls have different types and expose different properties, so we use the Form.ControlType property to help us proceed without generating errors.

In this example, we want to change the Caption for Labels and the Status Bar Text for bound controls.  (As long as we are setting the Status Bar text, we can set the Tool Tip text at the same time.)   Another useful change would be to rename all the controls with meaningful prefixes; txt for TextBox, cbo for ComboBox, etc.  The form wizard always names labels as Label1, Label2, etc, which is a little irritating for the anal programmer, so I like to run through them and create new names that associate them with their caption.  All of this can be done in a few seconds with the following code:

Public Function ModifyFormControls(ByVal sForm As String) As Boolean
    On Error GoTo Err_Handler
    
    Dim frm As Access.Form
    Dim ctl As Access.Control
    Dim strName As String
    Dim strPrefix As String
    Dim strStatusText As String
    Dim strMsg As String
    
    ' Begin by opening the form in design view, so it may be 
    ' accessed by our Form object variable.
    DoCmd.OpenForm sForm, acDesign
    Set frm = Forms(sForm)
    
    ' Loop through all the controls on the form, checking its type
    to ' know how to proceed.  (Labels don't have properties of a text box.)
    For Each ctl In frm.Controls
        If Left(ctl.Name, 3) = "lbl" Then Stop
        Select Case ctl.ControlType
            Case acLabel
                strPrefix = "lbl"
                strName = Replace(ctl.Caption, " ", "")
                strStatusText = ""
            Case acTextBox
                strPrefix = "txt"
                strName = ctl.ControlSource
                strStatusText = AddSpacesToName(strName)
            Case acCheckBox
                strName = ctl.ControlSource
                strPrefix = "chk"
                strStatusText = AddSpacesToName(strName)
            Case acListBox
                strPrefix = "lst"
                strName = ctl.ControlSource
                strStatusText = AddSpacesToName(strName)
            Case acComboBox
                strPrefix = "cbo"
                strName = ctl.ControlSource
                strStatusText = AddSpacesToName(strName)
            Case Else
                strPrefix = "obj"
                strName = ctl.Name
                strStatusText = ""
        End Select
        
        strName = UCase(Left(strName, 1)) & Mid(strName, 2)
    
        ' set the control's Name property
        ctl.Name = strPrefix & strName
        
        ' For data controls, set their status bar text and
        ' tool tip text properties.
        If
        strStatusText <>  "" Then
            ctl.StatusBarText = strStatusText
            ctl.ControlTipText = strStatusText
        End If
        ' Just for fun, report the RowSource property to
        ' user if control is the sort that has one.
        If ctl.ControlType = acComboBox Then
            strMsg = ctl.Name & vbCrLf & "SQL = " & ctl.RowSource
            MsgBox strMsg, vbInformation, "List/Combo Property"
        End If
    Next
    
    DoCmd.Close acForm, sForm, acSavePrompt
    
Exit_Here:
    Set ctl = Nothing
    Set frm = Nothing
    Exit Function
Err_Handler:
    MsgBox Err.Description, vbExclamation, "Error"
    Resume Next
End Function

There's so much more we could do ...

As I mentioned at the outset, there are so many applications for enhancifing object properties. Usually, the approach is straightforward and the above code is enough to build upon for your needs. Reports, Queries and even Modules have properties that you may read and set. So, the next time you have a daunting task in front of you, think about how you might enhancify your objects.