Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

How to Use Form Filter on Report

Normally we use a Query or processed data table as Report Source for printing selected data items, like Customer Invoices or Customer Ledger Statement etc.

How do we take a print out of the current record on the Form?

Let us try this simple method to start with, before using the Form Filter on Report.  We need some sample data, a Form and a Report to try this out.

  1. Import the Order details and Products Tables from the Northwind.accdb 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.
  3. Use the Form Wizard and design a multiple items (continuous) Form using the Order Details Table (sample data view image is given below) and save it with the name Frm_OrderDetails.
  4. Open the Form Frm_OrderDetails in 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 selected state display the Property Sheet (Design - - >Tools - - > Property Sheet or use F4) and select 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 skeleton of the Command Button Click Event Procedure.
  10. Copy and paste the middle line of Code between the existing header and footer lines 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 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 on the Report. 

Next, we will do it with more flexibility after setting the Filter Property Value of the Form. This can be done after selecting one or more Order IDs for filtering the data on the Form, before we open the Report for printing the data on the Form.  We must set a reference to the Filter Property of the Form replacing the criteria setting we have used earlier: "[order id] = " & Me![order id], on the DoCmd.OpenReport command.  

With this change the user can filter data based on any column value on the Form (like Order Id or Quantity or UnitPrice etc.) and use the result set to print the Report.

  1. Make a copy of the form Frm_OrderDetails and paste it with the name Frm_Orderdetails2.
  2. Open the form Frm_Orderdetails2 in design view.
  3. Click on the Command Button to select and display it’s property sheet (F4).
  4. Select  the Event Tab and select the On Click property.
  5. Click on the Build (. . .) button to open the VBA window.
  6. Copy the following Code and paste replacing the existing lines of code on 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
  7. 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 normal view.
  8. Click on the Command Button to open the Report.

    You will be greeted with a message asking to 'Apply a filter to the Form first'. When you click on the Command Button the program checks whether the Form's Filter Property is set with a Filter condition or not, like the text we have used in the first example above, or like the sample shown in Step-7 above.

    Note: Remember, once you apply a filter on the form the text of the last filter condition remains saved on the Filter Property.  The Filter action is supported with another property value setting: FilterOn = True/False. When you Toggle the filter action on the Form the FilterOn property value changes but the Filter property value criteria text is not removed. You will be greeted with the above message only when the Filter Property Value is empty.

  9. Click on the Order ID column on any record.
  10. Click on the Filter Toolbar button (see the image below)to display the Filter selection control.

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

  11. Click on the Select All Option to de-select all the items.
  12. 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.

  13. Click on the Run Report Command Button on the Header of the Form to open the Report in Print Preview with the records filtered on the Form.
  14. Close the Report.
  15. Place the cursor on the Quantity Field on any record on the Form.
  16. Click on the Filter Toolbar Button to display the Filter Control.
  17. Select the items with the Quantity values 100,200 & 300 and click OK button to close the control and filter the records you have selected.
  18. Open the Report by clicking on the Run Report Command Button and check the report contents.
  19. Close the Report.
  20. Click on the Toggle Filter Toolbar buttonThe Filter action is reversed and all the records are back on the Form (or the FilterOn Property Value is set as False now).
  21. 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 keep using it 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 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 and try the effect of the Filter and Toggle Filter action on the Form as well as on the Report.

Technorati Tags: ,
Share:

Saving Report Pages as separate PDF Files

You are printing several Customer Invoices as a single Report, running into several pages.  You have problems with this kind of Reports in segregating the Invoices and physically mailing them to the customers. 

If all Invoices can be saved on disk in separate PDF Files then it is easy to send them through emails to the customers.  This will not only save the cost of stationery but also delivers the invoices to the customers’ mailbox instantly without delay.  Besides that customers can easily view the Invoices on their machines or take printouts at their end, if that become necessary.  In short, part of our tasks are passed on to the customers, saving our time and money.  I don’t think I can stretch it any more.

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.accdb) sample database:
    • Order Details
    • Products 

    NB: I will be using Tables from the Northwind.mdb database. But, the queries, Report and Code will be running under Access2007. The Products Table is not used directly in our Query or Report but the ProductID combobox in the Order Details table references this table for product description.

  2. Open a new Query in SQL View without selecting any Table/Query from the displayed list.
  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 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.
  6. Design a Report to print Sales Invoice using Invoice_Orders_1 Query as Record Source.

    Sample Report Design Image is given below:

    Sample Report Preview Image is the next one:

  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

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 Order Details Table for the Customer Invoice Report. Besides that it calculates the Total Value after Discount of each record in the Query. But, in this Query we have not set any criteria to select any specific OrderID  or Range of OrderIDs for printing the Invoices.

We are setting criteria to select specific OrderID in the second Query (Invoice_Orders_1),  created using the earlier Query (Invoice_Orders_0) as Source. The two step query simplifies the SQL and we must take the SQL into the VBA Code to redefine the Query, with changing OrderIDs, so that each OrderId level Invoice is saved in separate PDF file on Disk. 

We have one more Query (the third Query saved in SQLParam String Variable is used directly in the Set rst = db.OpenRecordset(SQLParam, dbOpenDynaset) statement) that is totally confined into the Create_PDF() Function.  When the Create_PDF() Function is called you must give the Order Start Number, Order End Number and Target Folder Address, where the PDF Files are to be saved, as function parameters. The third Query extracts all the Order Numbers within the Start Number and End Number range.  These will be taken one by one to print Invoices in individual 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 then 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 its’ details or refinement.

Keeping that in focus let us continue to review what we are doing in the VBA Code lines.  You must call the Function using the following

Syntax:

Create_PDF  StartNumber,  EndNumber, ”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 Rpt_Invoice Report is Invoice_Orders_1 Query. This is redefined for each OrderId as criteria for printing the Rpt_Invoice in PDF format.  The PDF files are saved in the location specified as third parameter C:\My Documents.

Each line in the VBA Code is commented to indicate what it does in each step, please go through them to understand the code better.

Technorati Tags:
Share:

AutoNumber with Date and Sequence Number

We always make use of the Autonumber feature in tables to create a unique ID field for the table. It is easy to define and always starts the auto-number with 1 and increments by 1 for each record, unless you set the New Values property to Random rather than the default value Increment.

What is the solution if we need different sequence numbers for the records that we create on each day?

For example, assume we are working on a Hospital Project and patients getting registered in the hospital on a particular day should have a unique Registration Card Number consisting of current date and a three digit sequence number in the format: yyyymmdd000. The sequence number must reset to 001 when the date changes.

If the Hospital maintains history records of the patients in physical files, organized by Date, Month and Year-wise order it is easy for them to locate any file with the Registraion Card Number.

We can generate this number automatically with a Function.  Let us try it with a sample Table and Form.

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
  1. Create a sample Table with the following Structure, as shown in the image given below:
  2. The CardNo Field is a text type with 11 characters in length.  Both second and third fields are also text fields with sizes 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 Patients 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 Access2007 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. Access2007 users select Form from the Selection Type drop-down control. Earlier version users click on the top left corer of the Form (in the intersection where a black rectangle is shown) to ensure that the Property Sheet belongs to the Form and not of 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 current date in yyyymmdd format and the sequence number 001 as suffix.
  16. Type few more records.  Since, we have locked the CardNo field Users cannot edit this field’s contents. Sample image is given below:
  1. Now, we will test whether the sequence number resets to 001 or not when the date changes. To do that first close the frm_Patients Form.
  2. Open the Patients Table directly in Datasheet View.
  3. Change the 7th and 8th digit from left (the dd digits of the date) to 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 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 few more records on the Form. 

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

You can see that the Sequence number at the end of the CardNo resets to 001 with current date and subsequent records’ last three digits gets 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 also set to Yes the User can select this field and search for a specific CardNo, if needed.

If you would like to display the sequence number part separate 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 never stored in the table, it is used for display purposes only.  But, if you enter a 0 between the two semi-colons like 99999999-999;0;_ then the dash character also will be stored in the CardNo field on the table.  It is better if we don’t do that.

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

Assume that a Patient approaches the registration desk with her Registration Card, the staff member at the desk should search for the patient’s record with the CardNo to retrieve her history record, to know the location of her personal file, to know which doctor the patient attended last etc.  You have two search options when the search control is displayed. 

Try the following:

  1. Click on the CardNo field to select it.
  2. Press Ctrl+F to display the search control (search control image is given below).

As shown on the image above you can search for the CardNo without the dash if you remove the check mark from the search option Search Field as Formatted.  Put the check-mark on when searched with the dash separating date and sequence number.

Technorati Tags: ,,
Share:

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads


Blogs Directory

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

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

Featured Post

Function Parameter Array Passing

Last week we have explored the usage of ByVal (By Value) and ByRef (By Reference),  in the Function Parameter, to pass the value from  a Va...

Labels

Blog Archive

Recent Posts