Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

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

Share:

No comments:

Post a Comment

Comments are subject to moderation before publishing.

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

http://clicky.com/101199826
Blog Directory Visit blogadda.com to discover Indian blogs

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

Forms How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation Class Module Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Array Queries msaccess reports External Links msaccess tips Accesstips Downloads Objects Property Menus and Toolbars Controls Event MsaccessLinks Process Controls Art Work Collection Object WithEvents msaccess How Tos Combo Boxes Graph Charts List Boxes VBA msaccessQuery Command Buttons Command Button Data Dictionary Object Emails and Alerts Form Query RaiseEvent Report Calculation Custom Wizards DOS Commands Data Type ms-access functions msaccess functions msaccess graphs msaccess reporttricks Item Object Reference msaccessprocess security advanced Access Security Add Custom Functions Field Type Fields Form Instances Key Macros Menus SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Copy Expression Join Methods Microsoft Numbering System Records Recordset Security Split Table Time Difference Utility Workgroup Wrapper Classes database ms-access msaccess wizards reference text tutorial vba code Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export 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 Tables Union Query User Users Water-Mark Word automatically commands function hyperlinks iSeries Date iif msaccess msaccess alerts pdf files restore switch toolbar updating upload

Featured Post

WithEvents and All Form Control Types

Introduction So far we have worked extensively with only one type of Control: the TextBoxes on Form, enabled with Events to Raise and captur...

Labels

Blog Archive

Recent Posts