Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Writing VBA-Code with VBA

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 “[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 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
  1. Open a new blank Form in Design View.
  2. Add a Command Button control on the Form.
  3. While the Command button is in selected state display it’s Property Sheet (F4 or ALT+Enter).
  4. Change the Name Property Value to cmdRun.
  5. Change the Caption Property Value to Run Report.
  6. Save and close the Form with the name frmSample.
  7. 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.
  8. 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
  9. 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".
  10. Display the Debug Window (Ctrl+G).
  11. Type the following line in the Debug Window and press Enter Key:
    Write_Code "frmSample","cmdRun"

    Form’s name "frmSample"  is passed as first parameter to the Write_Code() Function and Command Button’s name "cmdRun" is as second parameter.

  12. 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.

  13. 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 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.

Share:

No comments:

Post a Comment

Comments subject to moderation before publishing.

MS-Access Tips on your Finger-Tip

  • Download Android App 'MSA Guru' Version of LEARN MS-ACCESS TIPS AND TRICKS from Google Play Store.

Want to Post Free Ads on the Web


Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

Search

Popular Posts

Blog Archive

Powered by Blogger.

Follow by Email

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object Graph Charts Query VBA msaccessQuery Calculation Event List Boxes Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference TreeView Control ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button ImageList Control Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join ListView Control Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code