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 processed data table as Report Source for printing selected data items, like Customer Invoices or Customer Ledger Statement, etc.

How do we take a printout 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.

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.
Use the Form Wizard and design multiple items (continuous) Form using the Order Details Table (sample data view image is given below) and save it with the name Frm_OrderDetails.
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 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 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 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 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.

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

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.
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 by 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.
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.
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 de-select all the 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 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.
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 the Quantity values 100,200 & 300 and click the OK button to close the control and filter the records you have selected.
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 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 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 and try the effect of the Filter and Toggle Filter action on the Form as well as on the Report.

Technorati Tags: Reports, Microsoft

Saving Report Pages as separate PDF Files

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

Introduction.

You are printing several Customer Invoices as a single Report, running into several pages. You have problems with this kind of Report 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 saves the cost of stationery and also delivers the invoices to the customer's mailbox instantly, without delay. Besides that, customers can easily view the Invoices on their machines or take printouts at their end, if that becomes necessary. In short, part of our task is passed on to the customers, saving our time and money. I don’t think I can stretch it anymore.

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
NB: I will be using Tables from the Northwind.mdb database. But, the queries, reports and Codes 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.
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 Invoice_Orders_1 Query as Record Source.
Sample Report Design Image is given below:
Save the Report with the name Rpt_Invoice. Sample Report Preview Image is the next one:

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. Besides that, it calculates the Total Value after the 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 a specific OrderID in the second Query (Invoice_Orders_1), created using the earlier Query (Invoice_Orders_0) as a 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 Order-Id level Invoice is saved in a 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 to 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.

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 to indicate what it does at each step, please go through them to understand the code better.

Technorati Tags: Utility

AutoNumber with Date and Sequence Number

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

Introduction

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 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 the 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 Registration Card Number.

We can generate this number automatically with a Function. Let us try it 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:
The Cardno Field is a text type with 11 characters in length. Both 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 Access2007 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
Access2007 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 of 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:

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

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

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 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.
Save the Form and open it in a normal view. Now you can distinguish the date and sequence number very easily.

Finding Patient Record.

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 historical 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:

Click on the CardNo field to select it.
Press Ctrl+F to display the search control (the 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 checkmark from the search options Search Field as Formatted. Put the check-mark on when searched with the dash separating the date and sequence number.