There is a clever, poke-fun-at-George-Bush web site named Weekly Radio Address where you can listen to a new parody of the president each Monday morning. It's irreverent and probably exaggerates GW's difficulty with the English language, but it's funny all the same, and a little contagious. I was trying to come up with a title for this article and having difficulty finding the right word, so I followed Mr. Bush's example and made one up.
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:
- Loop through Pubs sales table and list all its properties
- Loop through fields of Pubs sales table, setting Description for each
- Loop through form controls to read RowSource property of combo box
- Loop through form controls and set Status bar and Tool Tip text
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.