Writing VBA-Code with VBA
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 “[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 necessary body lines of the procedure manually between those opening and closing statements.
Sample opening and closing statements 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.
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 selected state display it’s 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 between Database and VBA Windows with 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 name 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:
Form’s name "frmSample" is passed as first parameter to the Write_Code() Function and Command Button’s name "cmdRun" is as 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 it’s VBA 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 of the Command Button (cmdRun), and 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 click event procedure on a Form. Create two Text Boxes on the Form, enter the Form Name and Control Name in them and use the text box names in both parameters of the Write_Code() function.