Database Design Specialists

Bringing ideas together, creating new possibilities.

Code to rename all controls on a form with Leszynski-Reddick object names

Often I create a form using the wizards, which is convenient, but all the controls have the same name as the bound field (e.g. FirstName instead of txtFirstName for a text box). This can be confusing and problematic. The following code renames all the fields in the form specified. You can run it by opening the immediate window (Ctrl G) and typing:

UpdateFieldNames("FormToUpdate")

Note: If you run this code on a form with existing code, you'll need to update your code with the new names.
This code could easily be modified to update report field names too.

 
Function UpdateFieldNames(strFormName As String)
'Checks all field names in the specified form and changes them to standard naming conventions
'Open form specified
Dim frm As Form
Dim ctl As Control
	DoCmd.OpenForm strFormName, acDesign
	Set frm = Forms(strFormName)
'loop thru all controls For Each ctl In frm.Controls Select Case ctl.ControlType Case acLabel If Left(ctl.Name, 3) <> "lbl" Then ctl.Name = "lbl" & ctl.Name End If Case acRectangle If Left(ctl.Name, 3) <> "shp" Then ctl.Name = "shp" & ctl.Name End If Case acLine If Left(ctl.Name, 3) <> "lin" Then ctl.Name = "lin" & ctl.Name End If Case acImage If Left(ctl.Name, 3) <> "img" Then ctl.Name = "img" & ctl.Name End If Case acCommandButton If Left(ctl.Name, 3) <> "cmd" Then ctl.Name = "cmd" & ctl.Name End If Case acOptionButton If Left(ctl.Name, 3) <> "opt" Then ctl.Name = "opt" & ctl.Name End If Case acCheckBox If Left(ctl.Name, 3) <> "chk" Then ctl.Name = "chk" & ctl.Name End If Case acOptionGroup If Left(ctl.Name, 3) <> "fra" Then ctl.Name = "fra" & ctl.Name End If Case acBoundObjectFrame If Left(ctl.Name, 3) <> "bof" Then ctl.Name = "bof" & ctl.Name End If Case acTextBox If Left(ctl.Name, 3) <> "txt" Then ctl.Name = "txt" & ctl.Name End If Case acListBox If Left(ctl.Name, 3) <> "lst" Then ctl.Name = "lst" & ctl.Name End If Case acComboBox If Left(ctl.Name, 3) <> "cbo" Then ctl.Name = "cbo" & ctl.Name End If Case acSubform If Left(ctl.Name, 3) <> "sfr" Then ctl.Name = "sfr" & ctl.Name End If Case acObjectFrame If Left(ctl.Name, 3) <> "ole" Then ctl.Name = "ole" & ctl.Name End If Case acPageBreak If Left(ctl.Name, 3) <> "brk" Then ctl.Name = "brk" & ctl.Name End If Case acPage If Left(ctl.Name, 3) <> "pge" Then ctl.Name = "pge" & ctl.Name End If Case acCustomControl If Left(ctl.Name, 3) <> "ocx" Then ctl.Name = "ocx" & ctl.Name End If Case acToggleButton If Left(ctl.Name, 3) <> "tgl" Then ctl.Name = "tgl" & ctl.Name End If Case acTabCtl If Left(ctl.Name, 3) <> "tab" Then ctl.Name = "tab" & ctl.Name End If End Select Next DoCmd.Save acForm, strFormName DoCmd.Close acForm, strFormName UpdateFieldNames = "Updated" End Function