A Microsoft Access tool to automatically rename your form controls

I love Microsoft Access.

There, I said it.

Thare are so many things I love about it that I wouldn’t know where to start with listing them all.

So, it’s just as well that this article isn’t about that. It’s about fixing one of those things that’s just a bit of a pain.

Use prefixes for your controls? Access doesn’t!

I remember the first time I attended an Access course all those years ago. “It’s a good idea to name your forms with a prefix of frm, your tables with a prefix of tbl…” and so on, said the instructor. And that made sense.

So I was a bit surprised when we used the wizard to make a form from our table called tblCustomers. Access asked what to call the form and being told “frmCustomers” decided to add heading label in nice big, bold characters saying “frmCustomers”. And so I recaptioned it “Customers”.

But it doesn’t end there. Because it seems that Access doesn’t get the idea of prefixes, when you add controls to the form, they all get named the same as their data sources, in the case of data-bound controls, or random things such as “Label34” for non-data-bound controls.

Does this matter? After all, there a thousand articles on the web saying that you don’t need to use prefixes.

For my money, yes it does. Firstly, the non-data-bound controls. If I’m writing code that should in a given situation re-caption the “Orders” label to “Edit order” or “New order” depending on what’s happening, how do I do that if there are 20 labels, all called “Label42” or whatever? How am I supposed to know which one is which when Intellisense lists 20 labels all called “Labelx”?

But there’s a bigger problem with the data-bound controls. If I’ve got a column in the form’s recordsource called “OrderDate”, and I’ve got a textbox called “OrderDate”, how am I supposed to tell in my code whether I’m referring to the textbox or the data behind it?

So I’m definitely in the camp that wants to rename the controls on every form.

But if I have a form with 15 fields on it, each with a label and a control, plus subsidiary things such as command buttons, it takes a while. It’s not hard, but it’s dull. It’s the kind of job I always wish I could train my cat to do.

So here’s the solution I’ve developed. The code below automatically renames most of the controls on any given form. It does so by making a couple of assumptions, so if you don’t use the same conventions I do, you’ll want to tweak this code to match your requirements.

Assumptions made for this code to work

  1. You name every column in your table with a three-character prefix indicating the data type, such as strFirstName, datOrderDate and blnActive
    The code replaces the first three characters in the ControlSource property for data-bound controls with a prefix indictating the object type (txt, cbo, chk etc)
  2. You want no spaces in control names.
    The code removes spaces, and rather than replacing them with underscores or dashes as some people do, it simply indicates the start of each word by replacing the first character with its upper case equivalent

One other thing, before we start. BEWARE! If you have code attached to your controls – this code will break it!! Or, more precisely, it will break the link between the control and its code. For example, if you’ve got a button called Command1, then the code behind its click event will be in a sub called Sub Command1_Click(). But having run this routine, your button might be called cmdOK – and of course, there’s no code behind the cmdOK_Click event – so you’ll need to copy the code over to make it work again.

That warning out of the way, here’s the code, broken down bit by bit. The full code is at the bottom of this post.

Sub FixControlNames(formToFix As String)
Dim ctl As Control, txt As TextBox, lbl As Label
Dim cmd as CommandButton
Dim chk As CheckBox, opt As OptionButton, cbo As ComboBox, lst As ListBox
Dim strOldName As String, strNewName As String
Dim strLabelWords() As String, i As Integer
 
End Sub

 

So, we begin by setting up the Sub so that it will accept a string parameter, being the name of the form to fix. We then create variables – a generic “control” variable called ctl that will allow us to loop through everything on the form, and some specific control type variables to allow us to access the proerties (and get help for them through intellisense) as we start working with them.

There’s also a string array set up (strLabelWords) which will allow us to use the split function on our label captions in order to get to the individual words and then capitalise their first letters. Finally, we have two string variables – strOldName and strNewName which allow us to note the existing name into a variable and then use the variable, rather than the control property, which is rather more efficient. We can also use the variables to generate an output in the immediate window to show what we’ve done with each of the controls

Next we do some basics. We’re going to use the Immediate Window for some feedback as to what’s happening, and that begins by simply confirming the form’s name, which we then open in design view:

Debug.Print "----- Form: " & formToFix & " -----"""
'Cannot change properties of the controls unless in design view
DoCmd.OpenForm formName:=formToFix, View:=acDesign

 

Next we use our object variable ctl to loop through all the controls on the form with a for each loop. We note the name of each control in the immediate window, and test what kind of control it is, so that we know what to do next. We use the IF TypeOf ctl Is …

'Loop through controls
For Each ctl In Forms(formToFix).Controls
    strOldName = ctl.Name
    If TypeOf ctl Is Label Then
           ....
    Elseif TypeOf ctl is CommandButton Then
           ....
    ElseIf TypeOf ctl Is TextBox Then
           ....
    ElseIf TypeOf ctl Is CheckBox Then
           ....
    ElseIf TypeOf ctl Is OptionButton Then
           ....
    ElseIf TypeOf ctl Is ComboBox Then
           ....
    ElseIf TypeOf ctl Is ListBox Then
           ....
    Else
           ....
    End If
 ....
Next ctl

 

Within each block of code, we’ll figure out the new name to assign to the control in question, and store that in strNewName. Then, at the end of the loop, we check if we’ve created a new name for the contol, as we won’t have for all controls (we don’t bother with renaming things like subforms or tab controls, although obviously it would be simple to extend this code to do so). If we’ve got a new name in strNewName, we set the control’s name to it, and report that. If we haven’t, we just report that nothing’s changed:

If Len(strNewName) = 0 Then
    Debug.Print strOldName, "NOT CHANGED"
Else
    ctl.Name = strNewName
Debug.Print strOldName, "=>>", strNewName
End If

 

Finally, we get to the meat of the procedure – the bit where we actually do the renaming.

What happens here depends on the type of control. If the control is a label, then use the caption of the label as the basis for the label’s name. We’re assuming here that we have “sensible” captions (nothing like “Last name – please don’t include suffixes such as Junior”) but that’s a fairly safe assumption given that we’re the ones who designed the basic forms!

As mentioned a little earlier, we’re using object variables of specific types so that we can “see” the properties such as Caption (for a label or button) and ControlSource (for data-bound conrols). So, our first job is to point our specific variables to the generic control variable, depending on the outcome of our TypeOf function. In the first block, we’ve discovered that ctl references a label, so we set our label variable (lbl) equal to ctl. Now we can access the Caption property, we can use that to rename the control.

The simplest idea would be to simply remove the spaces from the caption and add the control prefix. But the problem with this is that you end up with names like lblCustomerfirstname, which is okay except that lblCustomerFirstName would be better. So, we take the caption, and use the Split function to split it into an array of strings.

Then we use a loop to go through the array, capitalising the first letter of each word with UCASE and LEFT, and ensuring the rest is in lower case using LCASE and MID. Because we don’t know how many words will be in the caption, and so how many elements will be in the array, we use the LBOUND and UBOUND functions for the loop.

If Left(strOldName, 3) <> "lbl" Then
  'Use the caption
  Set lbl = ctl
  strNewName = "lbl"
  'Capitalise the start of each word
  strLabelWords = Split(lbl.Caption, " ")
  For i = LBound(strLabelWords) To UBound(strLabelWords)
      strNewName = strNewName & UCase(Left(strLabelWords(i), 1)) & _
           LCase(Mid(strLabelWords(i), 2))
  Next i
Else
  strNewName = ""
End If

 

Note that we also check that we’ve not already got an “lbl” prefix – I’ve been cauught before where I’ve already renamed a control and so end up with double-prefixes!

The code is much the same for the other controls. The essential difference is that if a control is data-bound then we use the name of the control source as the basis for the name. Again, we check that we haven’t already got the appropriate prefix, then we remove the first three characters (the prefix that identifies the data type), remove any spaces, and then attach an appropriate prefix for the control type:

ElseIf TypeOf ctl Is TextBox Then
  If Left(strOldName, 3) <> "txt" Then
    'Use the control source
    Set txt = ctl
    strNewName = "txt" & Mid(Replace(txt.ControlSource, " ", ""), 4)
 Else
    strNewName = ""
 End If

 

And that’s it!

The complete procedure listing is below – you’re welcome to copy it if you think it might be useful to you. If you think it can be improved – or if you have your own little code samples that you’ve come to rely on, add your comment below and join the conversation!

Sub FixControlNames(formToFix As String)
 
Dim ctl As Control, txt As TextBox, lbl As Label
Dim cmd As CommandButton
Dim chk As CheckBox, opt As OptionButton
Dim cbo As ComboBox, lst As ListBox
 
Dim strOldName As String, strNewName As String
 
Dim strLabelWords() As String, i As Integer
 
Debug.Print "--------- Form: " & formToFix & " ------------------"""
'Cannot change properties of the controls unless in design view
DoCmd.OpenForm formName:=formToFix, View:=acDesign
 
'Loop through controls
For Each ctl In Forms(formToFix).Controls
  strOldName = ctl.Name
  If TypeOf ctl Is Label Then
      If Left(strOldName, 3) <> "lbl" Then
          'Use the caption
          Set lbl = ctl
          strNewName = "lbl"
          'Capitalise the start of each word
          strLabelWords = Split(lbl.Caption, " ")
          For i = LBound(strLabelWords) To UBound(strLabelWords)
              strNewName = strNewName & _
                  UCase(Left(strLabelWords(i), 1)) & _
                  LCase(Mid(strLabelWords(i), 2))
          Next i
      Else
          strNewName = ""
      End If
  ElseIf TypeOf ctl Is CommandButton Then
      If Left(strOldName, 3) <> "cmd" Then
          'Use the caption
          Set cmd = ctl
          strNewName = "cmd"
          'Capitalise the start of each word
          strLabelWords = Split(cmd.Caption, " ")
          For i = LBound(strLabelWords) To UBound(strLabelWords)
              strNewName = strNewName & _
                  UCase(Left(strLabelWords(i), 1)) & _
                  LCase(Mid(strLabelWords(i), 2))
          Next i
      Else
          strNewName = ""
      End If
  ElseIf TypeOf ctl Is TextBox Then
      If Left(strOldName, 3) <> "txt" Then
          'Use the control source
          Set txt = ctl
          strNewName = "txt" & _
              Mid(Replace(txt.ControlSource, " ", ""), 4)
      Else
          strNewName = ""
      End If
  ElseIf TypeOf ctl Is CheckBox Then
      If Left(strOldName, 3) <> "chk" Then
          'Use the control source
          Set chk = ctl
          strNewName = "chk" & _
              Mid(Replace(chk.ControlSource, " ", ""), 4)
      Else
          strNewName = ""
      End If
  ElseIf TypeOf ctl Is OptionButton Then
      If Left(strOldName, 3) <> "opt" Then
          Set opt = ctl
          strNewName = "opt" & _
             Mid(Replace(opt.ControlSource, " ", ""), 4)
      Else
          strNewName = ""
      End If
  ElseIf TypeOf ctl Is ComboBox Then
      If Left(strOldName, 3) <> "cbo" Then
          Set cbo = ctl
            strNewName = "cbo" & _
                Mid(Replace(cbo.ControlSource, " ", ""), 4)
      Else
         strNewName = ""
      End If
    ElseIf TypeOf ctl Is ListBox Then
        If Left(strOldName, 3) <> "lst" Then
            Set lst = ctl
            strNewName = "lst" & _
                Mid(Replace(lst.ControlSource, " ", ""), 4)
        Else
            strNewName = ""
        End If
  Else
      strNewName = ""
  End If
  If Len(strNewName) = 0 Then
      Debug.Print strOldName, "NOT CHANGED"
  Else
      ctl.Name = strNewName
      Debug.Print strOldName, "-->", strNewName
  End If
Next ctl
 
DoCmd.Close acForm, formToFix, acSaveYes
 
End Sub

About the Author:

Andrew Richards is the Managing Director of TheIT Service. His background in training goes back to 2000, and he was involved in IT networking and support beyond that. Now he spends his time living and breathing the GDPR (fun!) and building databases.
When not doing these things, he can be found attempting to train his various sheep and chickens.

One Comment

  1. Peter K February 25, 2016 at 8:06 pm - Reply

    Hi Andrew

    Thanks so much for sharing this, words cannot express what a hero you are!

    It worked (almost) perfectly with the exception of renaming labels where I had prefixed the data type with ‘lng’ :

    lngClientID –> txtClientID
    Label0 –> lblLngclientid

    As you can see it worked for the textbox but not for the label. I’m new to access and not currently able to work out the split part of the code to find a fix.

    Even so, it’s certainly preferable to renaming them all manually.

    Cheers, Pete

Leave A Comment