Sometimes you'll need to add code programmatically to your Form or Report Module, for example, when you need to add the same code to all controls of a certain type on a form. When the form has a handful of controls it's no big deal, but when there may be dozens or hundreds of controls it can be daunting. The code below will cycle through all of the controls on your form, determine if the control type has to be updated, check to see if the control already has an event defined, and if not, create the event and append the code. Otherwise it'll print the name of the procedure it didn't modify. Prior to running the code please add the reference "Microsoft Visual Basic for Applications Extensibility 5.3" to your project.

Note: The form specified in strForm below must be in design view prior to running the code in order for this to work.


You can call the procedure using:
AddCodeToControls("Form_myForm", "Msgbox " & chr(34) & "Hello World" & chr(34) , "BeforeUpdate"

   1  
   2  ' This code was originally written by Juan Soto at AccessExperts.net.
   3  ' It is not to be altered or distributed,
   4  ' except as part of an application.
   5  ' You are free to use it in any application,
   6  ' provided the copyright notice is left unchanged.
   7  '
   8  ' Code Courtesy of
   9  ' Juan Soto at AccessExperts.net
  10  
  11  Public Function AddCodeToControls(strFormName As String, strCode As String, strProcedure As String)
  12      On Error Resume Next
  13      Dim VBAEditor As VBIDE.VBE
  14      Dim VBProj As VBIDE.VBProject
  15      Dim VBComp As VBIDE.VBComponent
  16      Dim CodeMod As VBIDE.CodeModule
  17      Dim obj As Object
  18      Dim frm As Form
  19      Dim ctl As Access.Control
  20      Dim lngHeaderLine As Long
  21      
  22      Set VBAEditor = Application.VBE
  23      Set VBProj = VBAEditor.ActiveVBProject
  24      Set VBComp = VBProj.VBComponents(strFormName)
  25      Set CodeMod = VBComp.CodeModule
  26      Set frm = Forms(strform)
  27      For Each ctl In frm.Controls
  28          If ctl.ControlType = acCheckBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acListBox _
  29              Or ctl.ControlType = acTextBox Then
  30              'Search if object already has an after update event
  31              lngHeaderLine = CodeMod.ProcStartLine(ctl.Name & "_" & strProcedure, vbext_pk_Proc)
  32              If Err > 0 Then
  33                  'Procedure does not exist, create it
  34                  lngHeaderLine = CodeMod.CreateEventProc(strProcedure, ctl.Name)
  35                  CodeMod.InsertLines lngHeaderLine + 1, strCode
  36              Else
  37                  'Procedure does exist, print name for manual editing later
  38                  Debug.Print ctl.Name & "_" & strProcedure & " Not Modified"
  39              End If
  40          End If
  41      Next ctl
  42  
  43  End Function