Part 1 of this series explained a simple technique for getting a form to collect criteria for a report. The method makes it easy to create small custom forms that have appropriate text boxes, or list boxes, etc, to request criteria from the user. However, if you are creating a separate form for each report, it gets cumbersome. What I am proposing is a single form to launch all the reports for a system. The form will know which criteria fields are needed for each report. The following is a screen shot of the finished form, showing some of the criteria fields.

(article continues after sponsor spot)

NOTE: All the VBA code segments on the Database Lessons site assume that you have DAO references active. If you are not sure what this means, and you are using Microsoft Access 2000 or higher, click here.

Step 1 is to create a table that will hold the data required by the form. This form needs (at a minimum) the name of the report and a flag to indicate which criteria fields are required for the report. Here is a screen shot of the finished design of the table.

I then filled in data for several of the reports, for testing purposes. One system that I have worked on had over 100 records in this table. The ReportName field values match the actual saved names of the report objects in the database. The ReportCriteriaFlags field needs a little more explanation. Notice that this field allows multiple entries, separated by semicolons. Also note that the first and last character are ALWAYS semicolons. Finally, you can see that I have 3 distinct values. You may have dozens of possible values, depending on the types of criteria you need, although more than a dozen types of criteria make for a crowded form.

• ;CUST; indicates that the report requires a customer ID as a criteria
• ;SALESV; indicates that the report requires a range of sales volumes for a criteria
• ;SALESR; indicates that the report requires a sales rep for a criteria

Let's build the form.

• I started by creating a blank form, without any source for its data; an unbound form.
• I added to this form a listbox, 3 text fields, and a combo box.
• The fields you add will depend on the criteria fields that you need.
• Give all the fields AND their labels proper names.
• Set all the fields and their labels to have Visible=False.
• The listbox stays visible.
• Set the properties for the listbox as suggested by the next screen shot. The Row Source is simply a query that picks up all the fields from the table, sorting them by ReportName.

I think we are now ready for the VBA code.

The main piece of code is what happens when you click on a report in the listbox. Logically, we have to make all criteria fields invisible, and then make the ones we want visible again. This code goes in the AfterUpdate event of the ListBox.

Private Sub lstReports_AfterUpdate()

    '--- reset the visibility of all criteria fields
    Me.lblCustomerID.Visible = False
    Me.lblSalesMin.Visible = False
    Me.lblSalesMax.Visible = False
    Me.lblSalesPerson.Visible = False
    Me.txtCustomerID.Visible = False
    Me.txtSalesMin.Visible = False
    Me.txtSalesMax.Visible = False
    Me.cboSalesPerson.Visible = False
    
    '--- reset the value of the criteria fields
    Me.txtCustomerID = Null
    Me.txtSalesMin = Null
    Me.txtSalesMax = Null
    Me.cboSalesPerson = Null
    
    '--- if needed,  make the customer ID criteria field visible
    If InStr(1, Me.lstReports.Column(2), ";CUST;", vbTextCompare) > 0 Then
        Me.lblCustomerID.Visible = True
        Me.txtCustomerID.Visible = True
    End If
    
    '--- if needed,  make the sales volume criteria fields visible
    If InStr(1, Me.lstReports.Column(2), ";SALESV;", vbTextCompare) > 0 Then
        Me.lblSalesMin.Visible = True
        Me.lblSalesMax.Visible = True
        Me.txtSalesMin.Visible = True
        Me.txtSalesMax.Visible = True
    End If

    '--- if needed,  make the sales person criteria field visible
    If InStr(1, Me.lstReports.Column(2), ";SALESR;", vbTextCompare) > 0 Then
        Me.lblSalesPerson.Visible = True
        Me.cboSalesPerson.Visible = True
    End If

End Sub

First, the code that resets the values of the criteria fields is optional. Some users want the system to remember the values since they tend to run several reports in a row using the same criteria for each one.

Next, look especially at the code segments like this one:

• If InStr(1, Me.lstReports.Column(2), ";CUST;", vbTextCompare) > 0 Then

What this is saying is, if the chosen report has the string ";CUST;" in its ReportCriteriaFlags field (we see it as the 3rd column in the listbox, but Access counts columns from 0) then do what comes within the IF, which in this case is to make the Customer ID text box and its label visible. Now you can see why the labels were also named, although I must admit that I normally do not name my text box labels.

InStr looks to see if the 3rd parameter is a part of the 2nd parameter, and returns its place, counting from 1. Look at a more specific example, taken from the VBA Editor's immediate screen (Ctrl-G). This tells us that "def" starts at the 4th character in "abcdefg".

? InStr(1, "abcdefg", "def", vbTextCompare)
4

If it does not match, the answer will be 0 (zero).

? InStr(1, "abcdefg", "dfe", vbTextCompare)
0

This article is a bit longer than average. I'll finish it next week.

 
Note: This web site dedicated to MS Access database users is an independent publication of Richard W. Killey and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.
 

© 2006, 2007 Richard W. Killey. All Rights Reserved.