Being Responsive With Microsoft Access Query Criteria

access refresh a report,access refresh form, ms access refresh form,access vba refresh form, refresh access database, microsoft access query criteria

Introduction

Reports can be static but when required users appreciate the possibility of executing them with specific Microsoft access query criteria. For instance, delimiting dates or geographical areas. In this tutorial, we will provide a simple example where the user will be able to run a report after defining some Microsoft Access query criteria regarding available fields.

First of all, the user will have to create an empty access database. Only dummy data is required to accomplish this task, so the same data used for tutorial #3 is feasible here as well. Base data used can be downloaded from the link below:

https://community.tableau.com/docs/DOC-1236

After downloading the file, it received some treatment, as some columns were removed and the actual sample data is added as part of this tutorial do be downloaded as an Excel file.

First Steps

The first mandatory step is to put data into a blank Microsoft Access database. We will use a simpler approach to import data (already used in tutorial #3,( https://mydatacareer.com/access-report-using-sql-query/), just copy and paste data from Excel into Access. All rows in Excel side should be selected (including column headings), after that paste them to Access.

Once a blank Access database is available, open the provided Microsoft Excel file. Both files can be open side by side as illustrated below:

Now inside the Access file, on the left pane, right click and from the context menu select paste

 The reader should answer yes when prompted regarding column headings

After importing data, rename the table to tblOrders as shown in the next pictures

Now the base report data is imported, let us start building the report and the conditions form.

Our dummy data has several fields but for the sake of this tutorial, a plain outcome list is enough.

The next steps will explain how to build a dynamic SQL statement pulling inserted values from a user defined form.

Based in the form inputs VBA code will rebuild the report underlying SQL query, close the report if it is open and re-open it again with the updated SQL query.

So let’s start by defining the base static query for the report, for this example we will apply conditions to the fields marked below (Order Date and Order Priority)

We will need to create a Microsoft Access query for the report. The query will produce a simple list of fields. Let’s create it using the Access design pane

Then select tblOrders and click add and close the show table form

Double click on the available fields as presented bellow (or simply pick the each field with left mouse button and drag each one bellow, the order is not relevant). Add Order Id, Order Date and all others inside the red rectangle.

Click save button

And name the query

Now let’s build the criteria form that will also work as an Access refresh form (forcing the report to be presented and refreshed). First, create a blank Access form.

Once the form is present please save it as frmCriteria

Now to add the controls let’s put it in design mode. Two text boxes and one combo box will be added. Right click in the white area and select design view from the context menu

Now with the form in design view please add two text boxes (that will be used to delimit Order Date) and one combo box that will filter priority.

Add the text box for start date (change its label to Start Date). Add a second text box to store End Date (you can simply select the existing one, copy and paste). Change its label to End Date.

After both Labels and Text boxes are added lets define some relevant properties. Right click and select Properties so the pane becomes visible

Now select Start Date label and define object name as lblStartDate (remember naming conventions are very important)

Now select its right text box and name it txtStartDate, also define its data type as short date and Show Date Picker to “For Dates”.

Execute similar steps for bellow label and text boxes related to End Date, just use names lblEndDate and txtEndDate. These controls will be used to delimit Order Dates. They will not be mandatory, more about this shortly when we look how to build the underlying SQL statement.

Now let’s add the combo box used to filter the Order Priority. From the design area select the Combo Box control and click on the form area

The combo box will need to present a list of values so the user can select one. We can use Access wizard to define it. After clicking on the form the wizard opens

We will use a query that returns the distinct list of values present in field Order Priority. Click next and select tblOrders from available tables

Add field Order Priority

Click Next and assign the ascending order to that field, click next again bypassing column width

Define Combo box label and click finish

Click view form and you will get a form like the one below

Click in the Combo box and you will see an exhaustive list of values present in the Order Priority field

We need to remove the duplicate values present in list. To achieve that we need to edit the underlying Combo box query. Once we will do it using the properties pane, we will also change the label and combo box names. Check bellow, first let’s change the names

Now let’s change the row source property. The query assigned to this property is the following one (please note table names and fields may appear inside square brackets that is mandatory if fields’ names have spaces otherwise they are optional). 

Here, we are setting a Microsoft Access Query Criteria to pull all values present in the field called Order Priority.

SELECT [tblOrders].[Order Priority]

FROM tblOrders

ORDER BY [Order Priority];

Let’s change it to

SELECT DISTINCT A.[Order Priority]

FROM tblOrders AS A

WHERE TRIM(NZ(A.[Order Priority], ”)) <> ”

ORDER BY A.[Order Priority];

The changes were

The DISTINCT operator will eliminate duplicates. The full table name in columns prefix was replaced by a table alias (A). A WHERE clause was added that uses NZ function to replace NULL values by the empty string, then trims the outcome and excludes values equal to empty string. This logic handles NULL values and empty string ones in the Order Priority field in case they appear. To assign the new query to the property value just edit it or copy it from here and paste it.

Save the form, preview it again and click the combo box, you should see a distinct list of available values

Now let’s add a button to trigger the report

Click in the form and rename the button to cmdRunReport. Change its label to Run Report.

The base criteria form is now ready. We need a report to interact with it. Let’s build a simple one using Access report wizard. A simple list report will be enough to illustrate things. On the create tab click Report Wizard.

Select the previously created query

Add all fields

Bypass grouping levels and order criteria

Select tabular view and click finish

Your report should look similar to the image below

Now save the report, close it and rename it to rptOrdersReport.

Now rename it

Finally we have the three required Lego pieces, the Form, the base Query and the Report.

We can start developing the required changes to build a dynamic report based on user parameter definition.

We will now pay attention to the logic we are implementing. Right click in query qryOrdersReport and select design mode; we need to check its underlying SQL statement

Once the query is in design view let’s turn to SQL view instead

The underlying SQL statement is presented below. We are once again setting a Microsoft Access Query Criteria.

(as a side note you can use online SQL formatters like http://www.dpriver.com/pp/sqlformat.htm or https://codebeautify.org/sqlformatter once SQL statements can be tricky to read sometimes). After making it pretty using the second website and defining a table alias (O)

We get

SELECT O.[Order ID],

        O.[Order Date],

        O.[Order Priority],

        O.[Order Quantity],

        O.Sales,

        O.Discount,

        O.Region

FROM tblOrders AS O

This will be the base query for our report. Once this part will be constant, it makes sense to define one in VBA code. Let’s do it and while in VBE we will also implement the refreshing process. Right click on the previously created button and select Code Builder

This will open the VBA editor and the first step will be to add the directive Option Explicit on the top so all variables require proper declaration. This is a good programming principle that avoids creating all variables as variants by default (it saves machine resources).

We will also add a constant that will store the base SQL statement. Check below

Option Compare Database

Option Explicit

Const strBaseSql As String = “SELECT O.[Order ID], O.[Order Date], O.[Order Priority], O.[Order Quantity], O.Sales, O.Discount, O.Region ” & _

                             “FROM tblOrders AS O”

Now let’s pay attention to the method cmdRunReport_Click that will trigger the process. This event will parse the possible inserted values in the form. If there are values in any of the controls, the code will add the proper where clauses to the base SQL query filtering the outcome that way.

Please copy and paste the bellow code to the corresponding click event

Private Sub cmdRunReport_Click()

    Dim strSql As String ‘variable used to store the entire SQL Statement

    Dim strWhereClause As String ‘variable used to store possible where clause

   

    On Error GoTo cmdRunReport_Click_Err:

   

    strWhereClause = “” ‘default initial value

    ‘the following tests logic is to perform a sequential check to each control in the form if the control being tested

    ‘is not null strWhereClause is concatatenated to itself always adding an AND clause between if the control is null

    ‘the code will not even enter inside the if clause

    ‘One very important note regarding dates, they are being enclosed inside #a_date# as Access uses it to parse their type

    ‘the value is also being pasted to Access in format YYYY-MM-DD the international date format

    If Not IsNull(Me.txtStartDate.Value) Then

        strWhereClause = strWhereClause & IIf(strWhereClause <> “”, ” AND “, “”) & “O.[Order Date] >= #” & Format(Me.txtStartDate.Value, “yyyy-mm-dd”) & “# “

    End If

    If Not IsNull(Me.txtEndDate.Value) Then

        strWhereClause = strWhereClause & IIf(strWhereClause <> “”, ” AND “, “”) & “O.[Order Date] <= #” & Format(Me.txtEndDate.Value, “yyyy-mm-dd”) & “# “

    End If

    If Not IsNull(Me.cmbOrderPriority.Value) Then

        strWhereClause = strWhereClause & IIf(strWhereClause <> “”, ” AND “, “”) & “O.[Order Priority] = ‘” & Me.cmbOrderPriority.Value & “‘ “

    End If

   

    ‘if any of the coditions is added the strWhereClause string will need to be concatenated to WHERE word

    strWhereClause = IIf(strWhereClause <> “”, ” WHERE “, “”) & strWhereClause

   

    ‘in this step we are building the final SQL statement and the final ORDER BY clause will always be by Order ID and Order Date

    strSql = strBaseSql & strWhereClause & ” ORDER BY O.[Order ID], O.[Order Date]”

   

    ‘this step will rebuild the underlying report SQL query, this is indeed the most important step

    ‘we are making use of the  collections provided by Access database engine

    ‘the collection we are using is called QueryDefs, it provides developers access to query objects

    ‘and all their properties, including the underlying SQL statement

    ‘by using this approach the Query SQL statement can be changed in run time by VBA code

    CurrentDb.QueryDefs(“qryOrdersReport”).SQL = strSql

   

    On Error Resume Next

    DoCmd.Close acReport, “rptOrdersReport”, acSaveNo

    Err.Clear

    On Error GoTo cmdRunReport_Click_Err:

    DoCmd.OpenReport “rptOrdersReport”, acViewPreview

    Exit Sub

‘Error handlers should be present on all methods

cmdRunReport_Click_Err:

    Select Case Err.Number

        Case Else

            MsgBox Err.Number & ” – ” & Err.Description, vbCritical + vbOKOnly, “System Error …”

    End Select

End Sub

The actual VBA editor should look like

One important note regarding the text marked in blue. That piece of code will always try to close the report even if it is not open. To achieve that we are using an On Error Resume next directive that will not stop the code if an error happens. If the report is open it will be closed otherwise the execution will continue and any error will be cleaned in Err.Clear line.

Once the report is closed, the VBA code causes it to open again forcing the underlying changed query to be re-executed and new data is presented to the user. In other words, Access will refresh access report using vba.

As explained in the code comments the collection QueryDefs is used to access the base query properties. You should look to the collection as a list of objects, the one used provides developers a list of all available queries in Access database. To access each collection element one should use a key, in this case the key value is the query name.

After accessing the desired query developers can change and get its properties. Please note not all properties can be changed.

Now you can try the project, just put the created form in view layout, insert values in existing criteria fields and refresh the report. You should notice the values present in the report changing.

Conclusion

This is a simple dynamic report tutorial. It is possible to build highly complex criteria forms it is even possible to produce forms that produce dynamic reports by changing their appearance. We are referring to moving controls to different positions or even change the number of columns displayed. Report criteria forms should also validate user input, for instance it does not make sense to insert a Start Date bigger than an End Date. This validation was not implemented in this tutorial.

The secret behind this is to understand SQL construction logic very well as developers must test all possible input combinations and build the SQL accordingly. That can be complex indeed.

This tutorial provides simple examples we will produce an extension to this one allowing users to use multi-select lists and defining sorting criteria regarding output data.