Access Object Enhancifier
September 14, 2006
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:
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
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
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
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.