Navigation:  Articles > Mar-1998 >

Applying a Naming Convention to a Legacy Application

Previous pageReturn to chapter overviewNext page

Helen Feddema          
One of the ways to make your programs easier to understand is using an effective naming convention. In this article, Helen provides some techniques for applying a naming convention to an existing application.
If you're using a naming convention like the Leszynski Naming Convention (LNC) for all of your VBA code, you know how much time and effort it saves by making database objects and variables self-documenting. When you must take over an application developed by another person (or perhaps yourself at an earlier stage of your development career, before you started using a naming convention), you face a daunting choice:

Continue development in an application where you don't know the data type of most variables without having to go back and find their declaration. In some cases you won't be sure whether a word in code references a function, variable, form, report, field, or control. This is particularly bothersome where the original developer used the same word (say "Sales") as a table name, form name, report name, field name, control name, and variable name; or perhaps used the same variable name (in different procedures) for variables of different data types.
Spend many hours manually renaming all database objects, controls, and variables to use a standard naming convention, then track down and fix all of the problems caused by the manual renaming. And hope that you got it right.

If you decide to apply a naming convention to the application (definitely a good idea), Access 97 lets you globally search and replace for text strings in modules. Unfortunately, that's only a small part of the renaming needed. The six types of database objects need to be renamed, and the form and report controls as well. To rename the top-level objects, an add-on such as "Find and Replace," SpeedFerret, or the DBRat utility from last month's issue is very helpful. I'll use Find and Replace (FAR) in this article, but you should be able to use other add-ons equally well, as long as they support input of original and replacement object names from a table.
To help automate this activity, I created a set of tables, forms, queries, macros, and a handful of procedures to aid in renaming database objects to a standard convention. I use the LNC, but you can use these techniques with any naming convention. You could even switch a database from one naming convention to another. All of these objects are in download file RENAMING.ZIP.
The first step in any activity as significant as this one is to make a copy of your database. You should save it under a name that identifies it as the unchanged version, in case you need to check how something was working before renaming. Then, import all objects from the LNC RENAMING.MDB database into the database you want to rename. While it might turn out that you don't need all of the objects that I've provided, it's easier to not use them if they're there. Tables 1-5 list the database objects used in renaming, with brief descriptions of what they do.
Table 1. Tables used in the renaming process.




Matches control type names with their integer values, for use in control renaming code.


Used by the registered version of FAR to do mass renames of database objects. It's filled by append queries.


A list of forms in the database, from which the user can select the forms whose controls should be renamed. Generated from code by zsqappFormsToRename.


A list of reports in the database, from which the user can select the reports whose controls should be renamed. Generated from code by zsqappReportsToRename.

Table 2. Queries used in the renaming process.




Appends original form names and proposed new names to zstblFAR, for use in database object renaming.


Fills zstblFormsToRename with names of forms.


Appends original macro names and proposed new names to zstblFAR, for use in database object renaming.


Appends original module names and proposed new names to zstblFAR, for use in database object renaming.


Appends original query names and proposed new names to zstblFAR, for use in database object renaming.


Appends original report names and proposed new names to zstblFAR, for use in database object renaming.


Fills zstblReportsToRename with names of reports.


Appends original table names and proposed new names to zstblFAR, for use in database object renaming.


Selects forms.


Selects macros.


Selects modules.


Selects queries.


Selects reports.


Selects tables.

Table 3. Forms used in the renaming process.




Displays forms so user can choose those whose controls should be renamed.


Displays reports so user can choose those whose controls should be renamed.


Datasheet subform displaying forms on fdlgFormsToRename.


Datasheet subform displaying reports on fdlgReportsToRename.

Table 4. Macros used in the renaming process.




Runs GenerateFormsTable function to start renaming form controls.


Runs GenerateReportsTable function to start renaming report controls.

Table 5 . Module containing routines used in the renaming process.




Contains procedures used in renaming form and report controls.

You'll notice that in the list of queries there's a set of select and append queries for each type of database object: tables, queries, forms, reports, macros, and modules. However, you generally won't need them all. You might, for instance, have any macros (except AutoNew and AutoKeys, which can't be renamed). And, generally, you'll only have a few modules, which can easily be renamed by hand and aren't referenced by name elsewhere in the application.
The select queries (zsqsel*) make use of the system table MSysObjects to select objects of a specific type. Forms, for example, are listed in the MsysObjects table with a type of 32768, while queries are of type 5. The zsqselForms query is shown in Figure 1.

199803_hf1 Figure 1
The append queries (zsqapp*) create a new object name by applying a prefix of your choosing to the object's original name. They then append the original name and the new name to the table zstblFAR, to be used for mass renaming with the FAR add-on. If you're dealing with a database that's been partially renamed by hand, you can use criteria to exclude objects that already have appropriate names, as in the modified zsqappForms query shown in Figure 2.

199803_hf2 Figure 2
Of course, you can also modify the append queries to apply a different naming convention of your choosing.
Example: renaming forms
To rename objects of a particular type (say, forms), clear zstblFAR of any existing records, then run the append query zsqappForms. Open zstblFAR and examine the new names in the ReplaceText column. At this point, you can do any needed hand-tuning in this table. You might, for example, want to strip out any spaces in the names or convert forms ending with "Subform" into "fsubFormName." Figure 3 shows zstblFAR as originally filled by the append query for a typical database.

Figure 3
To get FAR to do the renaming -- after editing the new form names in zstblFAR as needed -- start FAR and enter "***TABLE:zstblFAR" as the Find string. To make sure that all references to the renamed objects are found and changed, check all the check boxes and click all the command buttons. FAR will locate each instance of each original object name in zstblFAR and let you accept or reject the change. You can also choose to replace without asking, but I don't recommend it. There might, after all, be queries, macros, or some other non-form object with the same original name as your form (as in the "Sales" example I mentioned above). A replace without asking will rename all of those objects along with your forms.
Figure 4 shows a case in point: FAR found a reference to "Dealers" in a form module, and suggested changing it to "frmDealers". However, this reference is really to the table "Dealers," as you can guess by its placement in a row source SQL statement, so the proposed change should be rejected.

Figure 5 shows a case in which the change is appropriate and should be accepted. Here the appropriateness of the change is evident as the object is a member of the Forms collection.

199803_hf5_zoom40 Figure 5
In some cases, it might be difficult or impossible to guess what type of database object is being referenced. You can click the Edit Changes button to see more of the context, and if that isn't enough, leave the reference unchanged. You can also scrutinize the code later on, and try running the code to see what errors occur. If it does generate an error, you can go back to your original copy of the database to see what the name used to be.
Here's a tip: In zstblFAR, if you have any original object names that match the beginning of other object names (such as "Sales" and "Sales by Region"), move the shorter name to the end of the table. Since FAR searches down through the table records, when it finds "Sales," it will match it with "Sales by Region." That will give you a lot more proposed changes to reject. If, on the other hand, "Sales by Region" is earlier in the table, by the time FAR gets to "Sales," all the "Sales by Region" matches will have been converted to something else and, when FAR processes the "Sales" matches, it won't find any "Sales by Region."
Renaming controls
The next step is to rename controls on forms and reports. This is a more complex operation that requires running a number of append queries. I've also set the process up to handle forms and reports separately, so you must select which group you'll process from a pop-up form. With that done, you then run functions that cycle through the selected forms and reports, renaming all controls that aren't already named correctly and offering the user a chance to edit the proposed control names. The control name renaming functions automatically strip out spaces and non-alphanumeric characters when composing the new control name to avoid problems.
When running these functions, you have the choice of saving the old control name to the control's Tag property, as a reference in case the renaming causes any problems later on. Before setting this argument to True, it's best to check the controls in the application's forms to see if the Tag property is already being used in any way. This can be done by searching for the string "Tag" in CBF and modules, using FAR or whatever tool you've chosen.
The renaming functions
Now that I've given you a brief introduction to using these tools, I'll walk you through the routines that make it all work.
The simplest function is GenerateFormsTable, which simply generates a table of forms and then opens a form displaying those names with a checkbox to check the forms whose controls you want to rename:

DoCmd.SetWarnings False

DoCmd.RunSQL _

"DELETE zstblFormsToRename.* FROM zstblFormsToRename"

DoCmd.OpenQuery "zsqappFormsToRename"

'Open table for selection of forms to rename

DoCmd.OpenForm "fdlgFormsToRename"

The form with its datasheet subform is shown in Figure 6.

199803_hf6 Figure 6
The command button in the form's footer checks whether any forms have been selected, and if so, runs the RenameFormControls function (otherwise, it pops up a message box and asks if the user wants to select any forms for processing).
The GenerateReportsTable function and its supporting query and form are similar, just substituting "reports" for "forms."
The RenameFormControls function is more complicated. The function is given below (without standard error-trapping code and Dim statements), with explanation between the sections:

'Determine if original control names should be stored

strMessage = "When processing forms, should the " & _

"original control name be saved to the control's" & _

"Tag property?"

intTag = MsgBox(strMessage, vbYesNo + vbQuestion + _

 vbDefaultButton2, "Control Name Backup")

If intTag = vbYes Then

 fTag = True


 fTag = False

End If

'Pick up form name from table just created

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("zstblFormsToRename", _


Do Until rst.EOF

 strFormName = rst![FormName]

 'True in Use field indicates form is to be processed.

 If rst![Use] = True Then

   DoCmd.OpenForm strFormName, acDesign

   Set frm = Forms(strFormName)

   'Cycle through the Controls collection in the form

    For Each ctl In frm.Controls

      strControlName = ctl.Name

      strControlType = ctl.ControlType

      fUnbound = False

At this point the appropriate renaming function for each type of control must be run (you can find this code in Listing 1). You'll note that in that section I've organized the controls into groups depending on whether they have a control source, caption, source object, or none of the above. Each group has its own renaming function for all of the controls in that group. Within each group, each control has its own case statement, because each has a unique prefix. The control name, suggested prefix, and fTag (which indicates if the original control name is to be saved) are passed to the various control renaming functions. You can edit the suggested prefixes as desired to support your choice of a naming convention.

Listing 1. This case statement determines which renaming function to call.


Select Case strControlType

  'Controls with control source only

  Case acTextBox

     strPrefix = "txt"

     i = ControlCS(ctl, strPrefix, fTag)

  Case acComboBox

     strPrefix = "cbo"

     i = ControlCS(ctl, strPrefix, fTag)

  Case acCheckBox

     strPrefix = "chk"


     strControlSource = ctl.ControlSource

     If fUnbound = False Then  

        i = ControlCS(ctl, strPrefix, fTag)


        i = ControlCS(ctl, strPrefix, fTag)

     End If

  Case acListBox

     strPrefix = "1st"

     i = ControlCS(ctl, strPrefix, fTag)

  Case acOptionGroup

     strPrefix = "grp"

     i = ControlCS(ctl, strPrefix, fTag)

   Case acOptionButton

     strPrefix = "opt"


     strControlSource = ctl.ControlSource

     If fUnbound = False Then

        i = ControlCS(ctl, strPrefix, fTag)


        i = ControlNA(ctl, strPrefix, fTag)

     End if

  'Controls with caption only

  Case acToggleButton

     strPrefix = "tgl"

     i = ControlCA(ctl, strPrefix, fTag)

  Case acLabel

     strPrefix = "lbl"

     i = ControlCA(ctl, strPrefix, fTag)

  Case acCommandButton

     strPrefix = "cmd"

     i = ControlNA(ctl, strPrefix, fTag)


  'Controls with source object only

  Case acSubform

     strPrefix = "sub"

     i = ControlSO(ctl, strPrefix, fTag)


  'Controls with none of the above

  Case acObjectFrame

     strPrefix = "fru"

     i = ControlNA(ctl, strPrefix, fTag)

  Case acBoundObjectFrame

     strPrefix = "frb"

     i = ControlNA(ctl, strPrefix, fTag)

  Case acImage

     strPrefix = "img"

     i = ControlNA(ctl, strPrefix, fTag)

  Case acTabCtl

     strPrefix = "tab"

     i = ControlNA(ctl, strPrefix, fTag)

  Case acLine

     strPrefix = "lin"

     i = ControlNA(ctl, strPrefix, fTag)

  Case acPage

     strPrefix = "pge"

     i = ControlNA(ctl, strPrefix, fTag)

  Case acPageBreak

     strPrefix = "brk"

     i = ControlNA(ctl, strPrefix, fTag)

  Case acRectangle

     strPrefix = "shp"

     i = ControlNA(ctl, strPrefix, fTag)


End Select

Next, the user has a choice of saving and closing the processed form, or leaving it open and unchanged for further editing. This is a useful choice if you come across a mystery control and need to examine it further to decide what its name should be.

intReturn = _

 MsgBox("Save and close this form?", vbYesNoCancel _

 + vbQuestion + vbDefaultButton1, _

 "Form Controls Renamed")

If intReturn = vbYes Then

 DoCmd.Close acForm, strFormName, acSaveYes

ElseIf intReturn = vbNo Then

 'Leave the form open in design view

ElseIf intReturn = vbCancel Then

  Exit Function

End If

Finally, Listing 2 provides an example of one of the control renaming functions. The routine I've included is the one used when the control has a control source (the others can be found in LNC RENAMING.MDB in download file RENAMING.ZIP.

Listing 2. A sample renaming function.


Public Function ControlCS(ctl As Control, _

 strPrefix As String, fTag As Integer) As Integer


On Error GoTo ControlCSError

Dim strControlSource As String

Dim strOriginalControlName As String

Dim strNewControlName As String

Dim intReturn As Integer

Dim intRenameFail as Integer


strControlSource = Nz(ctl.ControlSource)

strOriginalControlName = ctl.ControlName


'Check whether control already is correctly named

If left(strOriginalControlName, 3) = strPrefix Then

 Exit Function

'If the control source is not empty, use it

ElseIf strControlSource <> "" Then

 strNewControlName = strPrefix & _


'Otherwise, use the original control name


 strNewControlName = strPrefix & _


End If


'Show the user

'  - the original control name

'  - the control type

'  - control source

'  - proposed new name

'and ask if the new name is acceptable.

intRenameFail = True

Do While intRenameFail

iIntRenameFail = False

intReturn = MsgBox( _

 "Rename " & _

   DLookup("[ControlTypeName]", "zstblControlType", _

   "[ControlType] = " & ctl.ControlType) _

   & " control currently named " _

   & strOriginalControlName & vbCrLf & _

   "(control source: " & strControlSource & ") " & _

 "to" & vbCrLf & strNewControlName & "?", _

 vbYesNo + vbQuestion + vbDefaultButton1, _

 "Rename controls")


'If the user clicks the Yes button, rename the control

If intReturn = vbYes Then

 If fTag = True Then

   ctl.Tag = ctl.ControlName

 End If

ctl.ControlName = strNewControlName

'Otherwise, pop up an input box to edit the name.

ElseIf intReturn = vbNo Then

 strNewControlName = _

   InputBox("Modify new control name", _

     "Rename control", strNewControlName)

   ctl.ControlName = strNewControlName

End If



  Exit Function


'If the proposed control name is already in use,

'return to the renaming dialog.

intRenameFail = True

If Err.Number = 2104 Then

 MsgBox "There is another control named " & _

   strNewControlName & "; please try again"

 strNewControlName = strNewControlName & "1"


 MsgBox ("Error No:  " & Err & "; error message:  " _

   & Error(Err))

End If

Resume Next

End Function

Handling problems
Three examples of the renaming functions at work are shown in the following figures. Figure 7 shows a proposed renaming of a standard bound text control whose name is the same as the field to which it's bound. My experience has been, by the way, that the great majority of proposed name changes are perfectly suitable, and can be accepted as is.

199803_hf7 Figure 7
One type of unacceptable proposed name change occurs when a label has an extraordinarily long caption, as in Figure 8. In this case, the user should just click the no button to open the input box for changing the proposed control name and give it a shorter name.

Figure 8
Another special case is a control with an expression as its control source, as in Figure 9. In some cases, you might be able to figure out what name is appropriate from scanning the expression, but not always. In these cases, the best plan is to leave the form or report open after pro-cessing its controls, so you can check out the mystery control and rename it manually. (You'll have the option of leaving the form or report open after all of its controls have been processed.)

199803_hf9 Figure 9

199803_hf10 Figure 10
For renaming variables in CBF and modules, you can use the built-in Access 97 search and replace functionality, as shown in Figure 10. As tempting as it might be to do a global search and replace for a variable name, it's advisable to limit the search and replace scope to the procedure level, because the same variable name might be used for variables of different data types in different procedures and modules.
Before you start renaming, you might find it useful to first print out the module and scrutinize the code to figure out the data type of each variable.
Try it yourself
I imported three tables (Categories, Customers, and Employees) from Northwind into the LNC Renaming database. I then ran the Forms Wizard to create a form from each table, and then I ran the Report Wizard to create a report from each table. These six objects should provide you with a test bed for trying out the renaming functions. Renaming these forms and reports will also give you an idea of how the functions work, to prepare you for using the functions in real-world applications.