Even though Access is the best rapid development tool for databases I have ever used, some things can be drudgery. Take form controls for example. Access names the controls on a form the same as the control source. This makes it easy to have errors. You may try to use the name of the control but Access reads it as the name of the field. To avoid errors like this we have to go through the controls on a form and rename them one at a time. This can really slow down development and it gets to be drudgery after a few forms. So to save myself some time I wrote the following procedure which renames the most used controls on a form in a flash.

The code works by looping through the various controls on a form and adding an appropriate prefix to the existing name. You can run the procedure from the Immediate window using the following code:

Call NameFormControls("frmMyFormName")

Below is the procedure that does all the work. All you need to do is add it to a Standard Module and call it using the name of your form.

Public Sub NameFormControls(strFormName As String)

    Dim frm As Form
    Dim ctl As Control
    Dim strCtlName As String
    Dim ctlLabel As Label

    ' Open the Form in Design View and make it invisible
    DoCmd.OpenForm strFormName, acDesign, , , , acHidden

    ' Instatiate the form
    Set frm = Forms(strFormName)

    ' Loop through all the controls on the Form
    For Each ctl In frm.Controls
        ' Name specific controls according to their type
        ' making sure the control has not already been named
        Select Case ctl.ControlType
            Case acTextBox
                If Left(ctl.Name, 3) <> "txt" Then
                    ctl.Name = "txt" & ctl.Name
                End If
            Case acComboBox
                If Left(ctl.Name, 3) <> "cbo" Then
                    ctl.Name = "cbo" & ctl.Name
                End If
            Case acListBox
                If Left(ctl.Name, 3) <> "lst" Then
                    ctl.Name = "lst" & ctl.Name
                End If
            Case acCheckBox
                If Left(ctl.Name, 3) <> "chk" Then
                    ctl.Name = "chk" & ctl.Name
                End If
            Case acLabel
                Set ctlLabel = ctl
                If Left(ctl.Name, 3) <> "lbl" Then
                    ctl.Name = "lbl" & ctlLabel.Caption
                End If
        End Select
    Next ctl

    ' Close and save the Form
    DoCmd.Close acForm, strFormName, acSaveYes

    ' Release Memory
    Set ctlLabel = Nothing
    Set ctl = Nothing
    Set frm = Nothing 

End Sub

This code even names the labels! You may want to change the caption or format a label in some situations and this makes it much easier to manage.

I hope this code can help you make your Access development work a little easier and faster.