January 2012 ~ LEARN MS-ACCESS TIPS AND TRICKS

How to Use Form Filter on Report

a.p.r. pillaiJanuary 31, 2012Reports No comments

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.

Import the Order details and Products Table from the Northwind sample database.
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.
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.
Open the Form Frm_OrderDetails in the design view.
Select the Command Button Tool from the Toolbox and draw a Command Button on the Header Section of the Form.
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.
Change the Name Property Value to cmdRpt and change the Caption Property Value to Run Report (see the Form image above).
Select the Event Tab of the Property Sheet.
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.
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.
Save the form Frm_OrderDetails and open it in normal view.
Click on any record with the same Order ID in more than one record or any record you like.
Click on the Run Report Command Button to open the Rpt_OrderDetails with the selected record.
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.

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.
Make a copy of the form Frm_OrderDetails and paste it with the name Frm_OrderDetails2.
Open the form Frm_Orderdetails2 in the design view.
Click on the Command Button to select and display its property sheet (F4).
Select the Event Tab and select the On Click property.
Click on the Build (. . .) button to open the VBA window.

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

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.
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.
Click on the Order ID column on any record.
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.
Click on the Select All Option to deselect all items.
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.
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.
Close the Report.

Try it with a different Field.
Place the cursor on the Quantity Field on any record on the Form.
Click on the Filter Toolbar Button to display the Filter Control.
Select the items with Quantity values 100, 200 & 300 and click the OK button to close the control and filter the selected records.
Open the Report by clicking on the Run Report Command Button and check the report contents.
Close the Report.
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).
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

Saving Report Pages as separate PDF Files

a.p.r. pillaiJanuary 20, 2012Utilities 2 comments

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.

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.
Open a new Query in SQL View without selecting any Table/Query from the displayed list.

Data Preparation Queries.

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];

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));
```
Save the new Query with the name Invoice_Orders_1.

Design Sample Report.

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.

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

AutoNumber with Date and Sequence Number

a.p.r. pillaiJanuary 10, 2012Auto-Number, How Tos 17 comments

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

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.
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.
Save the Table with the name Patients.
Use the Form Wizard to design a Datasheet Form for the Patient's Table and name the Form as frm_Patients.
Open the Form in Design View.
Click on the CardNo Field to select it.
Display the Property Sheet (F4). If you are using Access 2007, then you can select CardNo from the Selection Type drop-down list.
Select the Data Tab and set the following Property Values as shown below:
- Enabled = Yes
- Locked = Yes
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.
Select the Event Tab on the Property Sheet.
Select the Before Insert event property and select Event Procedure from the drop-down list.
Click on the Build (...) Button to open the VBA Module of the Form.
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
```
Save the Form frm_Patients with the changes made.
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.
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

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.
Open the Patients' Table directly in Datasheet View.
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.
For example, if the date displayed is 20120109001, then change it to the previous day, like 20120108001.
When you have completed changing all the records, close the Table.
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.

Open the frm_Patients in Design View.
Click on the CardNo field to select it.
Display the Property Sheet (F4) of the Field.
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.
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:

Click on the CardNo field to select it.
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.