Writing VBA-Code with VBA ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

To insert an Event Procedure in a Form or Report we will open the VBA Module and write the code manually. If we open the Class Module through the Event Property on the Property Sheet of a Control or Form (after setting the “[Event Procedure]” value in the Event property) then the procedure’s opening and closing statements (see the example given below) will be inserted by Microsoft Access automatically. After that, we insert the necessary body lines of the procedure manually between those opening and closing statements.

Sample empty Subroutine stub of Form_Current() Event Procedure is shown below:

Private Sub Form_Current()

End Sub

Let us do it differently this time by programming a Command Button Click event procedure automatically through VBA. We are going to insert a Command Button Click Event Procedure in a Form Module with the help of a Function Write_Code(). We learned something similar through an earlier article on the topic: Creating Animated Command Button with VBA.

A sample Trial Run.

In this trick, the Command Button is programmed automatically to open a Report in Print Preview. Following are the lines of VBA Code we are going to insert into the Form Module automatically:

Private Sub cmdRun_Click()

    DoCmd.OpenReport "myReport", acViewPreview

End Sub

Open a new blank Form in Design View.
Add a Command Button control on the Form.
While the Command button is in the selected state display its Property Sheet (F4 or ALT+Enter).
Change the Name Property Value to cmdRun.
Change the Caption Property Value to Run Report.
Save and close the Form with the name frmSample.
Open VBA Editing Window (ALT+F11) and insert a new Standard Module. You can toggle Database and Code Window with the ALT+F11 Keyboard shortcut.

Copy and Paste the following Code into the Standard Module and save it:

Public Function Write_Code(ByVal frmName As String, ByVal CtrlName As String)
Dim frm As Form, x, txt As String, ctrl As Control

DoCmd.OpenForm frmName, acDesign, , , , acHidden
Set frm = Forms(frmName)
Set ctrl = frm.Controls(CtrlName)
With ctrl
    If .OnClick = "" Then
       .OnClick = "[Event Procedure]"
    End If
End With

x = frm.Module.CreateEventProc("Click", ctrl.Name)

txt = "DoCmd.OpenReport " & Chr$(34) & "myReport" & Chr$(34) & ", acViewPreview"
frm.Module.InsertLines x + 1, txt

DoCmd.Close acForm, frmName, acSaveYes
DoCmd.OpenForm frmName, acNormal

End Function

Replace the Report name "myReport" with one of your own Report names in the program line: txt = "DoCmd.OpenReport " & Chr$(34) & "myReport" & Chr$(34) & ", acViewPreview".
Display the Debug Window (Ctrl+G).
Type the following line in the Debug Window and press Enter Key:
```
Write_Code "frmSample","cmdRun"
```
The Form’s name "frmSample" is passed as the first parameter to the Write_Code() Function and Command Button’s name "cmdRun" is as the second parameter.
Press ALT+F11 to display the Database window. You can see that frmSample is already open in normal view after inserting the program lines in its Code Module.
Click on the Command Button to open your Report in Print Preview with the cmdRun_Click() Event Procedure. You may change the Form View into Design View, open the Form Module, and check the lines of Code we have inserted in there.

At the beginning of the above program the OnClick Event Property is checked, for the presence of any programmed action, like Macro Name, and if found empty, then inserts the text "[Event Procedure]" in the property in preparation for writing the program lines in the VBA Module.

In the next step, the Form Module’s CreateEventProc() method is called to create the Click Event Procedure of the Command Button: cmdRun. If you want a Double-Click Event procedure, rather than a Click() event procedure, then change the word "Click" to "dblClick".

Replace the DoCmd.OpenReport statement with appropriate Code for other actions like MouseMove.

You can call the Write_Code() function from a Command Button and click Event Procedure on a Form. Create two TextBoxes on the Form, enter the Form Name and Control Names in them respectively, and use the text box names in both parameters of the Write_Code() function.