How to Use Form Filter on Report

Introduction.

Normally, we use a query or a processed data table as the record source for a report when printing selected items, such as customer invoices or customer ledger statements.

But what if you want to print only the current record displayed on a form?

Let’s begin with a simple method before moving on to using the form filter with the report. For this, we’ll need some sample data, a form, and a report to work with.

Sample Trial Run.

1. Import the Order details and Products Table from the Northwind sample database.

2. Use the Report Wizard and design a Report using the Order Details Table as Record Source (sample report view is given below) and save the Report with the name Rpt_OrderDetails.

   

   Right-click to open Large Image in New Tab/Window.

3. Use the Form Wizard and design a multiple-item (continuous) Form using the Order Details Table (sample data view image is given below) and save it with the name Frm_OrderDetails.

   

   Right-click to open Large Image in New Tab/Window.

4. Open the Form Frm_OrderDetails in the design view.

5. Select the Command Button Tool from the Toolbox and draw a Command Button on the Header Section of the Form.

6. While the Command Button is still in the selected state, display the Property Sheet (Design ->Tools -> Property Sheet or use F4) and select the All Tab on the Property Sheet.

7. Change the Name Property Value to cmdRpt and change the Caption Property Value to Run Report (see the Form image above).

8. Select the Event Tab of the Property Sheet.

9. Select [Event Procedure] from the drop-down list of the On Click event property and click on the Build (...) button at the right edge of the property to open the VBA window with the empty Subroutine stub of the Command Button Click Event Procedure.

10. Copy and paste the middle line of Code between the empty Sub-Routine Stub of the Command Button Click event procedure:

    Private Sub cmdRpt_Click()
    
       DoCmd.OpenReport "Rpt_OrderDetails", acViewPreview, , "[order id] = " & Me![order id]
    
    End Sub
    
    

    Check the middle line of the above code. The last parameter to the 'DoCmd.OpenReport' command is a Filter condition to select the current record's Order ID number to filter all records with the same Order ID number. We have inserted one extra comma between the printing option and the filter condition to skip the choice of using the name of a Query as the Report Source Data.

    The filter condition "[Order Id] = " & Me![Order ID] states that "take all the records with the current record Order ID Number as source data" for the Report.

11. Save the form Frm_OrderDetails and open it in normal view.

12. Click on any record with the same Order ID in more than one record or any record you like.

13. Click on the Run Report Command Button to open the Rpt_OrderDetails with the selected record.

14. Close the Report, and you may try it again after selecting some other record on the Form.

The above example uses only a single record or several records with the same Order ID to print the Report. 

Several Items Selection.

1. Next, we’ll add more flexibility by using the Filter property of the form. This allows us to filter the data on the form—such as by selecting one or more Order IDs—before opening the report for printing.

   Instead of hardcoding the criteria, for example:

   "[Order ID] = " & Me![Order ID]
   

   in the DoCmd.OpenReport command, we can reference the form’s Filter property directly.

   With this approach, the user can filter records based on any column value on the form (such as Order ID, Quantity, or Unit Price) and then use the resulting dataset to print the report.

2. Make a copy of the form Frm_OrderDetails and paste it with the name Frm_OrderDetails2.

3. Open the form Frm_Orderdetails2 in the design view.

4. Click on the Command Button to select and display its property sheet (F4).

5. Select the Event Tab and select the On Click property.

6. Click on the Build (. . .) button to open the VBA window.

7. Copy the following code and paste replacing the existing lines of code in the Form Module:

   Private Sub cmdRpt_Click()
       If Me.Filter = "" Then
           MsgBox "Apply a filter to the Form first."
       Else
           DoCmd.OpenReport "Rpt_OrderDetails", acViewPreview, , Me.Filter
       End If
   End Sub

8. Check the last parameter setting in the DoCmd.OpenReport statement. We are asking the Report to use whatever criteria setting is available in the current Form’s (Me) Filter Property Value (like ('[Order Details].[Order ID] In (30,31,32)') to pick records for the Report. Save the Form and open it in its normal view.

9. Click on the Command Button to open the Report.

   When you click the command button, you may see a message prompting you to “Apply a filter to the form first.” This happens because the program checks whether the form’s Filter property contains a filter condition—similar to the example we used earlier, or the sample shown in Step 7.

   Note: Once a filter is applied, the filter condition remains stored in the form’s Filter property, even after the filter action is turned off. The actual filtering is controlled by another property: FilterOn, which can be set to either True or False. When you toggle the filter action on the form, the FilterOn property changes accordingly, but the filter criteria text in the Filter property is not cleared. You will only see the above message if the Filter property is completely empty.

10. Click on the Order ID column on any record.

11. Click on the Filter Toolbar button (see the image below) to display the Filter selection control.

    

    Right-click to open Large Image in New Tab/Window.

    A list of the selected field values (Order ID numbers) is displayed, and shows all the values are check-marked, indicating that all the values are in the selected state.

12. Click on the Select All Option to deselect all items.

13. Now, put check marks on the Order ID Numbers 30, 31 & 32, and click OK to close the Filter Control.

    The form now shows only records of Order ID numbers 30, 31 & 32.

14. Click the Run Report Command Button on the Header Section of the Form to open the Report in Print Preview with the records filtered on the Form.

15. Close the Report.

    Try it with a different Field.

16. Place the cursor on the Quantity Field on any record on the Form.

17. Click on the Filter Toolbar Button to display the Filter Control.

18. Select the items with Quantity values 100, 200 & 300 and click the OK button to close the control and filter the selected records.

19. Open the Report by clicking on the Run Report Command Button and check the report contents.

20. Close the Report.

21. Click on the Toggle Filter Toolbar button.  The Filter action is reversed, and all the records are back on the Form (or the FilterOn Property Value is set as False now).

22. Now, click on the Run Report Command Button to preview the Report. 

The Report shows the last filtered records only, rather than all the records from the Form.  When we toggle the filter, Microsoft Access sets the FilterOn Property Value to False, nullifying the effect of the filter action on the form, without removing the filter condition string inserted in the Filter Property, because the User may click the Toggle Filter button again to bring back data filtered by the last set filter condition. Our program keeps repeatedly using the filter setting because the Filter Property value is not empty, and we are not checking the status of the FilterOn Property setting.

But we can solve this issue with a few changes in our program as follows:

Private Sub cmdRpt_Click()
    If Me.FilterOn Then
        DoCmd.OpenReport "Rpt_OrderDetails", acViewPreview, , Me.Filter
    Else
        DoCmd.OpenReport "Rpt_OrderDetails", acViewPreview
    End If
End Sub

Change the program as shown above. Try the effect of the Filter and Toggle Filter on the Form and Report.

Technorati Tags: Reports, Microsoft

• Apply the Filter to the Table directly
• Dynamic Lookup in Query Column
• Creating a Watermark on MS-Access Reports
• iSeries Date in exported Data
• Change the Query Top Values property with VBA

Read More

Saving Report Pages as separate PDF Files

Introduction.

When printing multiple customer invoices as a single report, you often face the challenge of separating them for physical mailing.

A more efficient solution is to save each invoice as a separate PDF file on disk. This makes it simple to send invoices directly to customers via email. The benefits are immediate: reduced stationery costs, faster delivery straight to the customer’s inbox, and greater convenience for customers, who can view the invoices on their devices or print them if needed.

In short, part of the workload shifts to the customer—saving both time and money on your end.

Prepare for a Sample Run.

Let us try this with one or two Tables from the Northwind.mdb sample database.

1. Import the following Tables from the Northwind.mdb (or Access2007 Northwind) sample database:
   • Order Details
   • Products 

   Note: The examples use tables from the Northwind.mdb database. However, the queries, reports, and code will be executed in Access 2007. The Products table is not used directly in the query or report, but the ProductID combo box in the Order Details table references it to display product descriptions.

2. Open a new Query in SQL View without selecting any Table/Query from the displayed list.

Data Preparation Queries.

3. Copy and paste the following SQL string into the new Query’s SQL editing window and save the Query with the name Invoice_Orders_0:

   SELECT [Order Details].OrderID,
    [Order Details].ProductID,
    [Order Details].Quantity,
    [Order Details].UnitPrice,
    [Order Details].Discount,
    [Quantity]*((1-[Discount])*[UnitPrice]) AS TotalValue
   FROM [Order Details];

4. After saving and closing the above Query, create another Query, using Invoice_Orders_0 as the source, with the following SQL:

   SELECT Invoice_Orders_0.*
   FROM Invoice_Orders_0
   WHERE (((Invoice_Orders_0.OrderID)=10258));

5. Save the new Query with the name Invoice_Orders_1.

Design Sample Report.

6. Design a Report to print Sales invoices using the Invoice_Orders_1 Query as the Record Source.

   Sample Report Design Image is given below:

   

   Right-click to open Large Image in New Tab/Window.

   Save the Report with the name Rpt_Invoice. Sample Report Preview Image:

   

   Right-click to open Large Image in New Tab/Window.

   
   

   The Create_PDF() Function.

   
   
7. Copy and paste the following VBA Code into a Standard Module and save it:

   Public Function Create_PDF(ByVal OrderStart As Integer, ByVal OrderEnd As Integer, ByVal strPath As String)
   '--------------------------------------------------------------------------------
   'Author : a.p.r. pillai
   'Date   : January 2012
   'Rights : All Rights(c) Reserved by www.msaccesstips.com
   '--------------------------------------------------------------------------------
   'Function Parameters:
   ' 1. - OrderID Start Number
   ' 2. - OrderID End Number
   ' 3. - Target Folder Address, sample: C:\My Documents
   '--------------------------------------------------------------------------------
   Dim strsql_1 As String, strsql As String, criteria As String
   Dim db As Database, rst As Recordset, QryDef As QueryDef
   Dim int_Order As Integer, outFile As String, T As Date
   Dim SQLParam As String, i As Integer, msg As String
   
   'Invoice Query Definition, Order Number must be added at the end as criteria
   strsql_1 = "SELECT Invoice_Orders_0.*  FROM Invoice_Orders_0 "
   strsql_1 = strsql_1 & " WHERE (((Invoice_Orders_0.OrderID)="
   
   'Query definition for Order Numbers between OrderStart and OrderEnd numbers
   SQLParam = "SELECT DISTINCT [Order Details].OrderID FROM [Order Details] "
   SQLParam = SQLParam & "WHERE ((([Order Details].OrderID) Between " & OrderStart & " And " & OrderEnd & ")) "
   SQLParam = SQLParam & " ORDER BY [Order Details].OrderID;"
   
   Set db = CurrentDb
   'open the OrderIDs parameter list to process one by one
   Set rst = db.OpenRecordset(SQLParam, dbOpenDynaset)
   'open the Report Query definition to incorporate OrderID criteria
   Set QryDef = db.QueryDefs("Invoice_Orders_1")
   
   i = 0 'take a count of invoices printed
   Do While Not rst.EOF 'cycle through the parameter list
     'get the order number
     int_Order = Nz(rst!OrderID, 0)
     If int_Order > 0 Then 'if any blank record ignore
        i = i + 1
        'create the criteria part for the Invoice Query
        criteria = int_Order & "));"
        'complete the Invoice SQL by adding the criteria.
        strsql = strsql_1 & criteria
        'Redefine the Invoice Query to print the Invoice
        QryDef.sql = strsql
        db.QueryDefs.Refresh
     
        'PDF file's target path and Order Number is the file name.
        outFile = strPath & "\" & int_Order & ".PDF"
   
        'Save the report as pdf file.
        DoCmd.OutputTo acOutputReport, "Rpt_Invoice", "PDFFormat(*.pdf)", outFile, False, "", 0, acExportQualityPrint
     
     '2 seconds delay loop to give enough time for Access to create the file on disk.
     T = Timer
     Do While Timer < T + 2
       DoEvents
     Loop
    End If
     rst.MoveNext
   Loop
   rst.Close
   
   msg = "Order Start Number: " & OrderStart & vbCr & vbCr
   msg = msg & "Order End Number: " & OrderEnd & vbCr & vbCr
   msg = msg & "Invoices Printed: " & i & vbCr & vbCr
   msg = msg & "Target Folder: " & strPath
   
   MsgBox msg, , "Create_PDF()"
   
   Set rst = Nothing
   Set db = Nothing
   Set QryDef = Nothing
   
   End Function

How it Works.

Now, let us take a look at what preparations we have made so far:

The first query (Invoice_Orders_0) selects the required fields from the Order Details table for the Customer Invoice Report. It also calculates the total value of each record after applying the discount. However, this query does not include any criteria for selecting a specific OrderID or range of OrderIDs for invoice printing.

The second query (Invoice_Orders_1) is based on Invoice_Orders_0 and applies criteria to select a specific OrderID. Using a two-step query structure keeps the SQL simpler. Later, we incorporate the SQL into VBA code to dynamically update the criteria with different OrderIDs, so that each invoice can be generated and saved as a separate PDF file.

In addition, we use a third query—defined as a SQL string variable (SQLParam) within the Create_PDF() function. This query is executed through the statement:

Set rst = db.OpenRecordset(SQLParam, dbOpenDynaset) 

When calling the Create_PDF() function, you must provide three parameters: the Order Start Number, the Order End Number, and the target folder path where the PDF files should be saved. The third query retrieves all order numbers within the specified range, and each order is processed individually to generate separate invoice files.

NB:  To make this sample exercise simple, we are using only the transaction file to print the Invoices.  As you can see from the Report specimen shown above, it doesn’t have any Customer Address printed on it.  If this is required, we must consider setting relationships with the Customer Address Table in the Report Query and include the address fields also. The main idea behind this whole exercise is to save the report of individual Invoices in separate PDF files, rather than going into their details or refinement.

Let us keep that point in mind, and we will continue to review what we are doing in the VBA Code lines.  You must call the Function using the following:

Syntax:

Create_PDF  Start_Number,  End_Number, ”PDF Files Target Folder”)

Example-1:

Create_PDF 10248,10265,”C:\My Documents”
Example-2:
x = Create_PDF(10248,10265,”C:\My Documents”)

You may call the function from a Command Button Click Event Procedure, after setting the Parameter values in Text Boxes on the Form

Example-3:
Create_PDF Me![txtSNumber], Me![txtENumber], Me![txtPathName]

With the Start Number and End Number values, the Parameter Query is redefined to extract all the Order Numbers between those two numbers from the Order Details table so that they can be used for extracting Order-wise items for printing individual Invoices.  The SELECT DISTINCT clause suppresses duplicates from the parameter list.

The data source of the Rpt_Invoice Report is Invoice_Orders_1 Query. This is redefined for each Order-Id as criteria for printing the Rpt_Invoice in PDF format.  The PDF files are saved in the location specified as the third parameter, C:\My Documents.

Each line in the VBA Code is commented at each step. Please go through them to understand the code.

Technorati Tags: Utility

• Dynamic Lookup in Query Column
• Creating a Watermark on MS-Access Reports
• iSeries Date in exported Data
• Change the Query Top Values property with VBA
• Creating Animated Command Buttons

Read More

AutoNumber with Date and Sequence Number

Introduction

In most cases, we use the AutoNumber feature of tables to create a unique ID field. It is easy to set up, starts at 1, and increments by 1 for each new record (unless the New Values property is set to Random instead of the default Increment).

But what if we need different sequence numbers for each day’s records?

For example, in a hospital project, each patient registered on a given day must be assigned a unique Registration Card Number in the format:

yyyymmdd000

Here, the prefix is the current date, and the last three digits represent a daily sequence number starting at 001 and resetting whenever the date changes.

This approach is useful if the hospital keeps physical patient records organized by date, month, and year, since the registration number itself makes it effortless to locate any file.

We can create this unique number for each record using a custom VBA function, demonstrated below with a sample Table and Form.

The Autonum() Function

Before that, copy and paste the following Function Code into a Standard VBA Module and save it:

Public Function Autonum(ByVal strField As String, ByVal strTable As String) As String
Dim dmval As String, dt1 As String, dt2 As String, Seq As Integer, dv As String

'get the highest existing value from the table
dmval = Nz(DMax(strField, strTable), 0)

'if returned value is 0 then the table is new and empty
'create autonumber with current date and sequence 001
If Val(dmval) = 0 Then
   dv = Format(Now(), "yyyymmdd") * 1000 + 1
   Autonum = dv
   Exit Function
End If

'format the number as an 11 digit number
dv = Format(dmval, "00000000000")
'take the 3 digit sequence number separately
Seq = Val(Right(dv, 3))
'take the date value separately
dt1 = Left(dv, 8)
'get today's date
dt2 = Format(Now(), "yyyymmdd")
'compare the latest date taken from the table
'with today's date
If dt1 = dt2 Then 'if both dates are same
   Seq = Seq + 1 'increment the sequence number
   'add the sequence number to the date and return
   Autonum = Format(Val(dt1) * 1000 + Seq)
   Exit Function
Else 'the dates are different
   'take today's date and start the sequence with 1
   Autonum = Format(Val(dt2) * 1000 + 1)
End If

End Function

The Sample Table and Form

1. Create a sample table with the following structure, as shown in the image given below:

   

   Right-click to open Large Image in New Tab/Window.

2. The Cardno Field is a text type with 11 characters in length.  Both the second and third fields are also text fields with sizes of 10 and 50 characters, respectively.

3. Save the Table with the name Patients.

4. Use the Form Wizard to design a Datasheet Form for the Patient's Table and name the Form as frm_Patients.

5. Open the Form in Design View.

6. Click on the CardNo Field to select it.

7. Display the Property Sheet (F4). If you are using Access 2007, then you can select CardNo from the Selection Type drop-down list.

8. Select the Data Tab and set the following Property Values as shown below:

   • Enabled = Yes

   • Locked = Yes

9. Access 2007 users select Form from the Selection Type drop-down control. Earlier version users click on the top left corner of the Form (in the intersection where a black rectangle is shown) to ensure that the Property Sheet belongs to the Form and not to any other control on the Form.

10. Select the Event Tab on the Property Sheet.

11. Select the Before Insert event property and select Event Procedure from the drop-down list.

12. Click on the Build (...) Button to open the VBA Module of the Form.

13. Copy the middle line of the following procedure and paste it in the middle of the empty Form_BeforeInsert() lines of code in the Form module.

    Private Sub Form_BeforeInsert(Cancel As Integer)
      Me![CARDNO] = Autonum("CardNo", "Patients")
    End Sub

14.  Save the Form frm_Patients with the changes made.

15. Open the form in normal view and type Mr., in the Title Field, and type some name in the Patient Name field.  You can see that the first field is filled with the current date in yyyymmdd format and the sequence number 001 as the suffix.

16. Type a few more records.  Since we have locked the CardNo field, Users cannot edit this field’s content. A sample image is given below:

Right-click to open Large Image in New Tab/Window.

Test Run of the Code

1. Now, we will test whether the sequence number resets to 001 or not when the date changes. To do that, close the frm_Patients Form.

2. Open the Patients' Table directly in Datasheet View.

3. Change the 7th and 8th digits from left (the dd digits of the date) to the previous date in all the records that you have entered so far. 

4. For example, if the date displayed is 20120109001, then change it to the previous day, like 20120108001.

5. When you have completed changing all the records, close the Table.

6. Open the Form frm_Patients in normal view and try adding a few more records on the Form. 

Tip:  If you prefer to test it on different dates in the next few days, you may do so rather than changing the dates and trying it now.

You can see that the Sequence number at the end of the CardNo resets to 001 with the current date, and subsequent records’ last three digits will be incremented automatically. 

The user cannot change the CardNo manually because we have set the Locked Property Value of the field to Yes.  Since the Enabled Property Value is also set to Yes, the User can select this field and search for a specific CardNo, if needed.

Displaying the Number Segment Separately

If you would like to display the sequence number part separately from the date with a dash (like 20120109-005), we can do that by changing the Input Mask Property of the field, without affecting how it is recorded on the table.

1. Open the frm_Patients in Design View.

2. Click on the CardNo field to select it.

3. Display the Property Sheet (F4) of the Field.

4. Type 99999999-999;;_ in the Input Mask property. 

   Tip: When you set the input mask this way, the dash character between the date and sequence number is used for display only—it is not stored in the table. However, if you insert a 0 between the two semicolons, for example: 99999999-999;0;_ then the dash will also be stored in the CardNo field of the table. It’s generally better to avoid this, since the dash is meant only for readability and not for storage.

5. Save the Form and open it in a normal view.  Now you can distinguish the date and sequence number.

Finding Patient Record.

Assume a patient arrives at the registration desk with her Registration Card. The staff member can use the CardNo to look up her record, retrieve her history, locate her physical file, and check which doctor she last consulted. When the search control is displayed, two options are available for performing the search.

Try the following:

1. Click on the CardNo field to select it.

2. Press Ctrl+F to display the search control (the search control image).

Right-click to open Large Image in New Tab/Window.

As shown in the image above, you can search for the CardNo without the dash if you remove the checkmark from the search options Search Field as Formatted.  Turn the check mark on when searching with the dash separating the date and sequence number.

Download Demo Database.

DateAndSeq.zip

Technorati Tags: Autonumber, Forms, Property

1. Auto-Numbering in Query Column
2. Product Group Sequence with Auto-Numbers.
3. Preparing Rank List.
4. Auto-Number with Date and Sequence Number.
5. Auto-Number with Date and Sequence Number-2.

Read More

Assigning Module Level Error Trap Routines

Assigning Module-Level Error Trap Routines.

Last week, I introduced a function that automatically inserts error-handling lines into a VBA function or subroutine. While readers appreciated its usefulness, some felt the process was a bit cumbersome.

Before running that function, the user had to identify some text to search for and then execute the function with that text as a parameter. The utility relied on the 'Text.Find()' method of the Module object to locate the specified text and select the corresponding line within the target function or subroutine. From that starting point, it could determine other details—such as the total number of lines in the procedure, the line number of the header, and the line number of the end statement. These values were necessary to insert the error-handling lines in the correct locations.

However, when working with multiple functions or subroutines, this method becomes time-consuming, as each one must be processed individually.

In this article, we’ll explore an improved version of the utility that scans an entire module and inserts error-handling lines into all functions and subroutines in a single pass.

Before we dive in, here are links to the earlier articles, in case you’d like to revisit the simpler methods we tried using the Module object:

• Write VBA Code with VBA
• VBA Module Object and Methods
• Prepare a list of Procedure Names from a Module
Utility for inserting VBA Error Handler Code

The ErrorTrap() Function.

The new function is much simpler to use.  Copy and paste the following code into a new Standard Module and save it:

Public Function ErrorTrap(ByVal str_ModuleName As String)
On Error GoTo ErrorTrap_Error
'--------------------------------------------------------------
'Program : Inserting Error Handler Lines automatically
'        : in a VBA Module 
'Author  : a.p.r. pillai
'Date    : December, 2011
'Remarks : All Rights Reserved by www.msaccesstips.com
'--------------------------------------------------------------
'Parameter List:
'1. strModuleName - Standard Module or Form/Report Module Name
'--------------------------------------------------------------

Dim objMdl As Module, x As Boolean, h As Long, i As Integer
Dim w As Boolean, lngR As Long, intJ As Integer, intK As Integer
Dim linesCount As Long, DeclLines As Long, lngK As Long
Dim str_ProcNames(), strProcName As String, strMsg As String
Dim start_line As Long, end_line As Long, strline As String
Dim lng_StartLine As Long, lng_StartCol As Long
Dim lng_EndLine As Long, lng_EndCol As Long, procEnd As String
Dim ErrHandler As String, lngProcLineCount As Long
Dim ErrTrapStartLine As String, lngProcBodyLine As Long

Set objMdl = Modules(str_ModuleName)

linesCount = objMdl.CountOfLines
DeclLines = objMdl.CountOfDeclarationLines
lngR = 1
strProcName = objMdl.ProcOfLine(DeclLines + 1, lngR)
If strProcName = "" Then
   strMsg = str_ModuleName & " Module is Empty." & vbCr & vbCr & "Program Aborted!"
   MsgBox strMsg, , "ErrorTrap()"
   Exit Function
End If
strMsg = strProcName
intJ = 0

'Determine procedure Name for each line after declaraction lines
For lngK = DeclLines + 1 To linesCount
  
  'compare procedure name with ProcOfLine property
  If strProcName <> objMdl.ProcOfLine(lngK, lngR) Then
     'increment by one
     intJ = intJ + 1
     'get the procedure name of the current program line
     strProcName = objMdl.ProcOfLine(lngK, lngR)
  End If
Next lngK

ReDim str_ProcNames(intJ)

strProcName = strMsg: intJ = 0
str_ProcNames(intJ) = strProcName
For lngK = DeclLines + 1 To linesCount
  'compare procedure name with ProcOfLine property
  
  If strProcName <> objMdl.ProcOfLine(lngK, lngR) Then
     'increment array index by one
     intJ = intJ + 1
     'get the procedure name of the current program line
     strProcName = objMdl.ProcOfLine(lngK, lngR)
     str_ProcNames(intJ) = strProcName
     
  End If
Next
   
For intK = 0 To intJ
    ErrHandler = ""
    ErrTrapStartLine = ""
    'Take the total count of lines in the module including blank lines
    linesCount = objMdl.CountOfLines

    strProcName = str_ProcNames(intK) 'copy procedure name
    'calculate the body line number of procedure
    lng_StartLine = objMdl.ProcBodyLine(strProcName, vbext_pk_Proc)
    'calculate procedure end line number including blank lines after End Sub
    lng_EndLine = lng_StartLine + objMdl.ProcCountLines(strProcName, vbext_pk_Proc) + 1
    
    lng_StartCol = 0: lng_EndCol = 150
    start_line = lng_StartLine: end_line = lng_EndLine
    
    'Check for existing Error Handling lines in the current procedure
    x = objMdl.Find("On Error", lng_StartLine, lng_StartCol, lng_EndLine, lng_EndCol)
    If x Then
         GoTo NxtProc
    Else
     'Create Error Trap start line
         ErrTrapStartLine = "On Error goto " & strProcName & "_Error" & vbCr
    End If

    ErrHandler = vbCr & strProcName & "_Exit:" & vbCr
    
    lngProcBodyLine = objMdl.ProcBodyLine(strProcName, vbext_pk_Proc)
    
    'Set procedure start line number to Procedure Body Line Number
    lng_StartLine = lngProcBodyLine
    'calculate procedure end line to startline + procedure line count + 1
    lng_EndLine = lng_StartLine + objMdl.ProcCountLines(strProcName, vbext_pk_Proc) + 1
    
    'Save end line number for later use
    'here lng_endline may include blank lines after End Sub line
    lngProcLineCount = lng_EndLine
    
    'Instead of For...Next loop we could have used the .Find() method
    'but some how it fails to detect End Sub/End Function text
    For h = lng_StartLine To lng_EndLine
      strline = objMdl.Lines(h, 1)
      i = InStr(1, strline, "End Sub")
      If i > 0 Then
          'Format Exit Sub line
          ErrHandler = ErrHandler & "Exit Sub" & vbCr & vbCr
          lngProcLineCount = h 'take the correct end line of End Sub
          h = lng_EndLine + 1
          GoTo xit
      Else
         i = InStr(1, strline, "End Function")
         If i > 0 Then
          'Format Exit Function line
          ErrHandler = ErrHandler & "Exit Function" & vbCr & vbCr
          lngProcLineCount = h 'or take the correct endline of End Function
          h = lng_EndLine + 1
          GoTo xit
        End If
      End If
xit:
    Next

   'create Error Handler lines
   ErrHandler = ErrHandler & strProcName & "_Error:" & vbCr
   ErrHandler = ErrHandler & "MsgBox Err & " & Chr$(34) & " : " & Chr$(34) & " & "
   ErrHandler = ErrHandler & "Err.Description,," & Chr$(34) & strProcName & "()" & Chr$(34) & vbCr
   ErrHandler = ErrHandler & "Resume " & strProcName & "_exit"
 
  'Insert the Error catch start line immediately below the procedure header line
   objMdl.InsertLines lngProcBodyLine + 1, ErrTrapStartLine
   
 'Insert the Error Handler lines at the bottom of the Procedure
 'immediately above the 'End Function' or 'End Sub' line
   objMdl.InsertLines lngProcLineCount + 2, ErrHandler
     
NxtProc:
Next

strMsg = "Process Complete." & vbCr & "List of Procedures:" & vbCr
For intK = 0 To intJ
  strMsg = strMsg & "  *  " & str_ProcNames(intK) & "()" & vbCr
Next
MsgBox strMsg, , "ErrorTrap()"

ErrorTrap_Exit:
Exit Function

ErrorTrap_Error:
MsgBox Err & " : " & Err.Description, , "ErrorTrap()"
Resume ErrorTrap_Exit
End Function

Running the Function.

You can run this function from the Debug Window or from a Command Button Click Event Procedure.  Sample run on Standard Module:

ErrorTrap “Module Name”

Example-1:

ErrorTrap "Module3"

Module 3 will be scanned for Procedure Names, and each procedure is checked for the presence of existing Error Handling lines.  If the ‘On Error Goto’ statement is encountered anywhere within a procedure, then that procedure is skipped and goes to the next one to check.

To run on the Form or Report Module, use the following Syntax:

ErrorTrap "Form_FormName"

Example-2:

ErrorTrap "Form_Employees"

Example-3

ErrorTrap "Report_Orders"

When the ErrorTrap() function completes working with a module, it displays the list of procedures found in that Module. Sample run image is given below:

If you run the ErrorTrap() Program on a Form/Report that doesn’t have a VBA Module (or its Has Module Property value is set to No), then a Subscript out of Range message is displayed, and the program will be aborted.

Saving the code in the Library Database

It is better if you save this Program in your Library Database and link the Library Database to your Project.  Visit the Link: Command Button Animation for details on how to use a database as a Library Database with your own Custom Functions.

I tried to take the ErrorTrap() Function one step further to scan through the entire database Modules and insert error trap routines in all of them, saving each module immediately after changes.  But Access 2007 keeps crashing every time, and finally, I discarded the idea.  Besides, the above function gives the user more control to review the module subjected to this function for any kind of side effects.

I did the test runs on this function several times and found it ok, but field testing may be required in different environments to detect logical errors.  If you find any such errors, please give me feedback through the comment section of this page.  Review each module immediately after running this function for accuracy and use it at your own risk. 

Technorati Tags: Utility, Microsoft

• iSeries DATE in exported Data
• Change the Query Top Values property with VBA
• Creating Animated Command Buttons
• Data Change Monitoring
• Continued on Page 2 of Report

Read More

Utility for inserting VBA Error Handler Code

Utility for inserting VBA Error Handler Code.

So far, we have explored several examples of working with the VBA Module Object’s Properties and Methods. We learned how to insert a subroutine into a form module using the InsertLines() method, how to upload VBA code from a text file into a form module with the AddFromFile() method, and how to generate a list of functions and subroutines from a specific module programmatically.

Now, we’ll move on to creating a practical utility program that can automatically insert standard error-handling code into VBA functions and subroutines. Before we begin, let’s first look at a typical error-trap routine that we usually add to a subroutine. Such a routine helps us manage unexpected errors and ensures the program exits gracefully—without abruptly halting execution or inconveniencing the user during normal operations.

Sample Error Handler.

Private Sub cmdRunReport_Click()
On Error Goto cmdRunReport_Click_Error
.
.
.
.
cmdRunReport_Click_Exit:
Exit Sub

cmdRunReport_Click_Error:
MsgBox Err & " : " & Err.Description,,"cmdRunReport_Click()"
Resume cmdRunReport_Click_Exit

End Sub

The blue lines in the earlier example represent the Error handler code, while the dotted section holds the actual logic of the procedure. Normally, as developers, we focus first on writing the core logic of the program (the dotted section), and only later—during the finishing stage—do we add the error-handling routines. In some cases, such as file-handling programs or procedures with heavy validation checks, setting up error-trap routines gets higher priority right from the start.

Our goal here is to automate the insertion of error-handling lines at both the beginning and the end of a procedure. Every serious program benefits from structured error handling, but writing these lines manually for each subroutine or function can be time-consuming. If some of your existing code lacks proper error-handling routines, you can easily fix that now using the utility program we are about to build.

Notice in the example that lines such as _Exit: and _Error: are suffixed with the program name, e.g., cmdRunReport_Click_Exit:. These labels are automatically derived from the subroutine or function name. The first error-handling line will be inserted immediately after the procedure declaration line (e.g., Sub … or Function …), while the rest of the error-handling block will be placed at the end of the procedure.

To achieve this, we first need to extract certain details about the procedure itself:

• The starting line number of the procedure.

• The ending line number of the procedure.

• The procedure name.

With these details, we can then insert the error-trap lines in the right places. Fortunately, VBA exposes all this information through the Module object, which provides access to the required property values of any line of code within a function or subroutine.

A plan to find the specific location in the Module.

To make the approach clearer, let’s outline a simple plan for our program:

1. Locate a unique text string inside the VBA module, somewhere within the target Function or Subroutine.

   • For this step, we will use the Find() method of the Module object.

2. Identify procedure details once the search lands on the desired line inside the Function or Subroutine.

   • The Find() method not only stops on the target line but also provides important information, such as:

     • The line number of the match (modules are internally numbered line by line, including blank lines).

     • The starting column number where the search text is found.

     • The ending column number where the search text ends.

With this information in hand, we can determine the exact position of the procedure within the module, which is essential for inserting our error-handling lines at the right places.

The syntax of the Find() Method is as follows:

1. Modules(ModuleName).Find strSearchText, lngStart_Line, lngStart_Column, lngEnd_line, lngEnd_Column, [[WholeWord], [MatchCase], [PatternSearch]]

   Sample Code:

   Set mdl = Modules("Form_Employees")
   
   With mdl
     .Find “myReport”, lngStart_Line, lngStart_Column, lngEnd_line, lngEnd_Column, False
   End With

• The first parameter of the Find() method is simply the search text you want to locate.

  The next four parameters define where to begin and end the search:

  • StartLine → the line number where the search begins.

  • StartColumn → the column position on the start line.

  • EndLine → the line number where the search ends.

  • EndColumn → the column position on the end line.

  For example, suppose you want to locate the second occurrence of the text "myReport" that appears somewhere after line 25 in the module. In that case, you might set:

  lngStart_Line = 25
  lngStart_Column = 0   ' start from the beginning of the line
  

  If "myReport" is part of a command like:

  DoCmd.OpenReport "myReport"
  

  then setting lngStart_Column = 10 would be sufficient, but leaving it at 0 is perfectly fine.

  Once the search text is located, the Find() method automatically updates all four parameters with the actual positions of the match. In other words, you’ll get:

  • lngStart_Line → the line number where the match begins.

  • lngStart_Column → the exact column where the match starts.

  • lngEnd_Line → the line number where the match ends.

  • lngEnd_Column → the exact column where the match ends.

  This way, the method not only confirms the presence of the search text but also tells you exactly where it sits in the module — which is critical for inserting error-handling code at the right spots.

• When the search operation is successful, we can extract information related to that Program line to use for working within that particular Function or subroutine. We will read the following information about a particular program to insert the Error Handler lines of Code at appropriate locations in the Program:

  • Get the Program Name from the ProcOfLine Property (or in the expanded form Procedure name of the Line we found through search) of the program line.

  • Get the Procedure Body Line Number from the ProcBodyLine Property. The line number on which the program Private Sub cmdRunReport_Click() starts. This line number + 1 is the location where we can insert the first line (On Error Goto label statement) of the Error Handler.

  • Get the Number of Lines in this Procedure, from the ProcCountLines Property.  Even though this is useful information, it has some drawbacks.  If there are blank lines above the procedure Name or below the End Sub or End Function line (if it is the last procedure in a Module, then it can have blank lines at the end), they are also included in the count.  So we must take corrective action or take alternative measures to correct the values.

• Once the above information is available, we can write the Error Handler lines into String Variables and use the Module's ObjectInsertLines() Method to place them at the beginning and the end of the procedure.

The ErrorHandler() Function.

1. Open the VBA Editing Window (ALT+F11).

2. Insert a new Standard Module.

3. Copy and paste the following VBA Code into the Module and save it:

Public Function ErrorHandler(ByVal strModuleName As String, _
                                ByVal strSearchText As String, _
                                Optional ByVal lng_StartLine As Long = 1)
On Error GoTo ErrorHandler_Error
'--------------------------------------------------------------------------------
'Program : Inserting Error Handler Lines automatically
'        : in VBA Functions or Sub-Routines
'Author  : a.p.r. pillai
'Date    : December, 2011
'Remarks : All Rights Reserved by www.msaccesstips.com
'--------------------------------------------------------------------------------
'Parameter List:
'1. strModuleName - Standard Module or Form/Report Module Name
'2. strSearchText - Text to search for within a
'   Function or Sub-Routine
'3. lng_StartLine - Text Search Start line Number, default=1
‘Remarks: Standard/Form/Report Module must be kept open before running this Code
'--------------------------------------------------------------------------------
Dim mdl As Module, lng_startCol As Long
Dim lng_endLine As Long, lng_endCol As Long, x As Boolean, w As Boolean
Dim ProcName As String, lngProcLastLine As Long
Dim ErrTrapStartLine As String, ErrHandler As String
Dim sline As Long, scol As Long, eline As Long, ecol As Long
Dim lngProcBodyLine As Long, lngProcLineCount As Long
Dim lngProcStartLine As Long, start_line As Long, end_line As Long

Set mdl = Modules(strModuleName)
lng_startCol = 1
lng_endLine = mdl.CountOfLines
lng_endCol = 255

With mdl
    .Find strSearchText, lng_StartLine, lng_startCol, lng_endLine, lng_endCol, False
End With

'lng_StartLine - line number where the text is found
'lng_StartCol  -  starting column where the text starts
'lng_EndCol    - is where the search text ends
'lng_EndLine   - end line where the text search to stop
'if search-text is found then lng_StartLine and lng_EndLine will
'point to the same line where the search-text is found
'otherwise both will be zero

If lng_StartLine > 1 Then
  'Get Procedure Name.
  'The vbext_pk_Proc system constant
  'dictates to look within a Function or Sub Routine
  'Not to consider Property-Let/Get etc.
   ProcName = mdl.ProcOfLine(lng_endLine, vbext_pk_Proc)
   
   'Get Procedure Body Line Number
   lngProcBodyLine = mdl.ProcBodyLine(ProcName, vbext_pk_Proc)
   
   'Look for existing Error trap routine, if any
   'if found abort the program
   sline = lngProcBodyLine: scol = 1: ecol = 100: eline = lng_endLine
   x = mdl.Find("On Error", sline, scol, eline, ecol)
   If x Then
      MsgBox "Error Handler already assigned, program aborted"
      Exit Function
   End If
   
 'Get Line Count of the Procedure, including
 ' blank lines immediately above the procedure name
 'and below, if the procedure is the last one in the Module
   lngProcLineCount = mdl.ProcCountLines(ProcName, vbext_pk_Proc)
   
 'Create Error Trap start line
   ErrTrapStartLine = "On Error goto " & ProcName & "_Error" & vbCr
 'Compose Error Handler lines
   ErrHandler = vbCr & ProcName & "_Exit:" & vbCr

'determine whether it is a Function procedure or a Sub-Routine
'lng_StartLine = lng_endLine:
lng_startCol = 1: lng_endCol = 100: lng_endLine = lngProcBodyLine + lngProcLineCount
'save the startline and lng_EndLine values
start_line = lng_StartLine: end_line = lng_endLine

'Check whether it is a Function Procedure or a Sub-Routine
w = mdl.Find("End Function", lng_StartLine, lng_startCol, lng_endLine, lng_endCol, False)

If w Then 'Function Procedure
   'Take correct procedure line count excluding
   'blank lines below End Sub or End Function line
   lngProcLineCount = lng_StartLine
   ErrHandler = ErrHandler & "Exit Function" & vbCr & vbCr
Else
   lng_StartLine = start_line: lng_endLine = end_line: lng_startCol = 1: lng_endCol = 100
   w = mdl.Find("End Sub", lng_StartLine, lng_startCol, lng_endLine, lng_endCol, False)
   If w Then 'Sub-Routine
     lngProcLineCount = lng_StartLine
     ErrHandler = ErrHandler & "Exit Sub" & vbCr & vbCr
   End If
End If
   'create Error Handler lines
   ErrHandler = ErrHandler & ProcName & "_Error:" & vbCr
   ErrHandler = ErrHandler & "MsgBox Err & " & Chr$(34) & " : " & Chr$(34) & " & "
   ErrHandler = ErrHandler & "Err.Description,," & Chr$(34) & ProcName & "()" & Chr$(34) & vbCr
   ErrHandler = ErrHandler & "Resume " & ProcName & "_exit"
 
  'Insert the Error catch start line immediately below the header line
   mdl.InsertLines lngProcBodyLine + 1, ErrTrapStartLine
   
 'Insert the Error Handler lines at the bottom of the Procedure
 'immediately above the 'End Function' or 'End Sub' line
   mdl.InsertLines lngProcLineCount + 2, ErrHandler
   
End If

ErrorHandler_Exit:
Exit Function

ErrorHandler_Error:
MsgBox Err & " : " & Err.Description, , "ErrorHandler()"
Resume ErrorHandler_Exit

End Function

Running the Code.

Since this program is a coding aid, you must keep the target Module (Standard/Form/Report) open before running this program to insert the error-handling code segment into the target Function/subroutine.

You may call the ErrorHandler() Function from the Debug Window or from a Command Button Click Event Procedure as shown below:

'The third parameter is optional, you may omit it
ErrorHandler "Form_Employees","myReport",1

This will start searching for the text myReport from the beginning of the Employees Form Module, stop within the program where the search finds a text match, and insert the Error Handling Code lines at the beginning and end of the program.

If the text 'myReport' appears in more than one Function/Sub-Routine in the Module, then you must give the third parameter (Search starts line number) to start searching for the text beyond the area where exclusion is required. Example:

'Look for text 'myReport' from line 20 onwards only 
ErrorHandler "Form_Employees","myReport",20

When the ErrorHandler() Function is run, first, it will look for the presence of existing error handling lines starting with 'On Error', and if found, assumes that the error handling lines are already present in the Function/Sub-Routine and stops the program after displaying the following message:

‘Error Handler already assigned, program aborted.'

Comments have been added throughout the code to explain the purpose of each section and improve readability. If you find any logical errors in the program, please share your feedback in the comments section below. To help prevent spam, posting comments requires signing in with a Gmail account.

Technorati Tags: Utility, Microsoft
Keywords: Error, Function, Sub-Routine, Form, Module

Earlier Post Link References:


• Change the Query Top Values property with VBA
• Creating Animated Command Buttons
• Data Change Monitoring
• Continued on Page 2 of Report
• Product Group Sequence with Autonumbers

Read More

Prepare a List of Procedure Names from a Module

Prepare a List of Procedure Names from a Module.

How to prepare a list of Procedure Names (Function or Sub-Routine Names) from a Standard Module or Class Module?

Earlier Articles.

1. Writing VBA Code.
2. Uploading Code from an external Text File into a Module.

We have seen how to insert a cmdButton_Click() Event Procedure into a Form Module using the Module Object's InsertLines() Method in the first Article. We learned how to upload the program from a Text File using the Module Object's AddFromFile() method.

In this example, we will prepare a list of Procedures from a Standard Module and from a Class Module.  Here, we will learn the usage of the following Properties of the Module Object:

With Modules(strModuleName):
   lng_BodyLines = .CountOfLines ‘ Total Number of lines of Code in the Module
   lng_LinesAtGobalArea = .CountOfDeclarationLines ‘Takes a count of lines in the Global declaration area
   str_ProcedureName = .ProcOfLine(LineNumber, NumberOfLines) ‘indexed list of Code lines with their Procedure names 
 End with 

The ProcOfLine (stands for Procedure Name of the current line of VBA Code) is an indexed list, and we must provide the Code Line index number to check which procedure the Code Line belongs to.  The second index value is normally 1 if inspecting on a line-by-line basis. If you prefer to check two or more lines together, then change this number accordingly.

The Procedure Steps.

The VBA Pseudo Code is given below.

1. Take Count of Code Lines of the Module, Standard Module or Class Module.

2. Take Global Declaration Lines Count.

3. First Procedure Name  =  Count of Global declaration Lines + 1

4. Array(0) = First Procedure Name. Saves the Function/Sub-Routine name into an Array.

5. Scan through the remaining lines of code:

     A)   Check the .ProcOfLine property value for the Procedure Name of the current line of Code.

     B)  If the Procedure Name of the current line is same as of the previous line, then go to C.

              Else save the current Code line’s Procedure Name in the next element of the Array().

     C)  Move to the next Code line and if End-of-lines reached, then go to D else repeat from A.

     D)  Create a string with the Procedure Names from the saved Array.

     E)  Display the list of Procedures in a Message Box.

     F)  End of Program.

The VBA Code.

Now let us write our VBA Code for the above program.

Public Function ListOfProcs(ByVal strModuleName As String)
'------------------------------------------------------
'Courtesy : Microsoft Access
'------------------------------------------------------
Dim mdl As Module
Dim linesCount As Long, DeclLines As Long
Dim strProcName As String, lngR As Long, intJ As Integer
Dim str_ProcNames() As String, lngK As Long
Dim strMsg As String

Set mdl = Modules(strModuleName)
'Total Count of lines in the Module
linesCount = mdl.CountOfLines

'Take the count of Global declaration lines
DeclLines = mdl.CountOfDeclarationLines
lngR = 1

'The first line below the declaration lines
'is the first procedure name in the Module
strProcName = mdl.ProcOfLine(DeclLines + 1, lngR)
'Re-dimension the str_ProcNames() Array for a single element
'and save the procedure name in the Array.
intJ = 0
ReDim Preserve str_ProcNames(intJ)
str_ProcNames(intJ) = strProcName
'Determine procedure Name for each line after declaraction lines
For lngK = DeclLines + 1 To linesCount
'compare current Code-line’s procedure name with earlier line’s name
 ‘if not matching then we have encountered a new procedure name
 If strProcName <> mdl.ProcOfLine(lngK, lngR) Then
 'increment array index by one
 intJ = intJ + 1
 'get the procedure name of the current program line
 strProcName = mdl.ProcOfLine(lngK, lngR)
 'Redimension the array for a new element by
 'preserving the data of earlier elements
 ReDim Preserve str_ProcNames(intJ)
 'Save the procedure name in the array 
str_ProcNames(intJ) = strProcName
 End If 
Next lngK
 'create the list of Procedure Names from Array to display 
strMsg = "Procedures in the Module: " & strModuleName & vbCr 
For intJ = 0 To UBound(str_ProcNames)
 strMsg = strMsg & str_ProcNames(intJ) & vbCr 
Next
 MsgBox strMsg 
End Function 

Copy and paste the program into a Standard Module and save it.

Run Code Directly from the Debug Window.

To display a list of procedures from a Standard Module, display the Debug Window (Ctrl+G) and type the following command with a Standard Module name as a parameter, like a sample given below, and press the Enter Key:

ListOfProcs "Utility_Local"

A Sample run output of Procedure Names in a MsgBox is shown below:

Form/Report Module Procedure Listing.

To take the list of procedures from a Form or Report Module, use the following Syntax:

ListOfProcs "Form_myFormName"

or

ListOfProcs "Report_myReportName"

Sample run output from a Form Module.

Technorati Tags: Microsoft, VBA

• Creating Animated Command Buttons
• Data Change Monitoring
• Continued on Page 2 of Report
• Product Group Sequence with Autonumbers
• Invoke Word Mail-Merge from Access 2007

Read More

VBA Module Object and Methods

VBA Module Object and Methods.

The VBA Module Object has several interesting methods and properties.  Last week, we saw how to insert a Click Event Procedure in a Form Module with a Function. You can find this blog post here.

I am not suggesting that the 'frm.Module.CreateEventProc()' method we explored for writing a one-line Event Procedure in a Form Module. However, trying different techniques is always worthwhile because programming is an exploration of new possibilities. After all, this method is available in the Access Application Object Model for us to explore and learn.

Today, we will examine an alternative and simpler approach to the same example we covered last week. Instead of creating the Event Procedure programmatically line by line, we will write the entire procedure in a text file and then load it directly into the Form Module.

If you have already tried last week’s example, we can use the same ‘Sample’ Form for today’s trial run,  or do the following to get prepared:

Loading VBA Code from Text File.

1. Open a new Form in Design View.

2. Create a CommandButton on the Detail Section of the Form.

3. While the CommandButton is selected, display its Property Sheet (F4 or ALT+Enter).

4. Change the Name Property Value to cmdRun and the Caption Property Value to Run Report.

5. Save the Form named Sample.

6. If you have last week’s Sample form, then open it in Design View.

7. Display the Form Module, remove the existing program lines, and save the Form.

8. Open Notepad, copy and paste the following program lines into Notepad, and save it as c:\windows\temp\vbaprg.txt: 

   Private Sub cmdRun_Click()
   
       DoCmd.OpenReport "myReport", acViewPreview
   
   End Sub

9. Replace the report name "myReport" with one of your own Report Names from the database.

10. Open a Standard VBA Module, copy and paste the following main program into the Standard Module:

    The LoadFromTextFile() Function.

    Public Function LoadFromTextFile()
    Dim frm As Form, frmName As String, ctrlName As String
    
    frmName = "Sample"
    'ctrlName = "cmdRun"
    
    'Open the form in design view
    DoCmd.OpenForm frmName, acDesign
    
    'define the form object
    Set frm = Forms(frmName)
    
    'call the form's Module Object's AddFromFile() method
    'to read the program from the text file
    'and insert them into the Form Module
    frm.Module.AddFromFile "c:\windows\temp\vbaprg.txt"
    
    'Save and close the form with the code
    DoCmd.Close acForm, frmName, acSaveYes
    
    'Open the form in Normal view
    DoCmd.OpenForm frmName, acNormal
    
    End Function

11. Place the cursor in the middle of the code, then press F5 to run the Code.

12. Press ALT+F11 to display the Database window with the Sample Form open.

13. Click on the Command Button to open the Report in print preview.

14. Close the Report.

15. Change the Sample Form in Design View.

16. Open the form module and check for the program lines we have loaded from the vbaprg.txt File.

Technorati Tags: Form, Module, Text File

Earlier Post Link References:

• Data Change Monitoring
• Continued on Page 2 of Report
• Product Group Sequence with Autonumbers
• Invoke Word Mail-Merge from Access 2007
• Copy-Paste Data from Excel to Access 2007

Read More

Writing VBA-Code with VBA

Writing VBA code with VBA.

To create an Event Procedure in a Form or Report, you can either open the VBA Module and write the procedure manually or open the Class Module through the Event Property of a Control or Form after setting the Event property to [Event Procedure]. When the Class Module is opened in this manner, Microsoft Access automatically inserts the procedure's opening and closing statements (see the example below). You then add the required body of the procedure manually between these generated statements.

A sample empty Subroutine stub of the 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 through VBA. We will insert a Command Button Click Event Procedure in a Form Module using a Function Write_Code().  We learned something similar through an earlier article on the topic: Creating an 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.  The 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 selected, display its 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 named frmSample.

7. Open the VBA Editing Window (ALT+F11) and insert a new Standard Module. You can toggle the Database and Code Window with the 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 names 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 the Enter Key:

    Write_Code "frmSample","cmdRun"

    The Form’s name "frmSample"  is passed as the first parameter to the Write_Code() Function, and the Command Button’s name "cmdRun" is passed as the 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 its Code Module.

13. Click on the Command Button to open your Report in Print Preview with the cmdRun_Click() Event Procedure.  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 On Click Event property checks whether a programmed action or Macro name has already been assigned. If the property is empty, it inserts [Event Procedure] into the Property Sheet, preparing it for writing the VBA Event Procedure in the 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.

Earlier Post Link References:

• Continued on Page 2 of the Report
• Product Group Sequence with Autonumbers
• Invoke Word Mail-Merge from Access 2007
• Copy-Paste Data from Excel to Access 2007
• User-level Access Security and Access 2007

Read More