• Access Home
• Continue the tutorial:
  Part 1: Introduction
  Part 2: VBA & SQL
  Part 3: Examples
  Part 4: Building Queries
  Part 5: More Instant Queries
  Part 6: Dynamic Reports
• Printer Friendly Version
• Download the File

Sometimes lines of code can be very long! Where I have had to break a line of code to fit it on the page I have used the symbol to indicate that the line below is a continuation and that the code should be written as a single line

<Blah blah blah 
blah>

should be written:

<Blah blah blah blah>

Access and SQL
Part 4: Building Queries "On the Fly"

This tutorial is about using SQL to build queries "on the fly" (or as we propellorheads say: "at run-time"). This means that you build the queries when you need them, rather than trying to anticipate the user's needs and preparing them in advance.

The tutorial will deal with regular "select" queries, ones which filter the source data and show the result to the user (as opposed to "action" queries which manipulate the data in some way).

You can download a copy of the database used in this tutorial. It contains completed examples of the forms, queries and code described in the tutorial. Follow the link at the bottom of this page. The database contains a table listing the details of the staff of a fictional multinational company. It contains the sort of personal details you might expect such as FirstName, LastName, BirthDate and Gender together with business details such as Office, Department, JobTitle and Email.

This tutorial is in two parts. This first part will show you how to create a fully working multi-purpose query. The second part will explain how to add some refinements to create a really professional query tool. You will find a link to the second part of the tutorial at the bottom of this page.

Why Build Queries on the Fly?

Like many Access developers, I tend to create very few stored queries. Instead I try to create a small number of general purpose queries that can be changed on demand to suit the user's requirements. This allows my databases to be much more flexible and I don't have to guess what the users might want to know. I can then use switchboards and dialog boxes to gather the information from the user which is used to create the query's SQL statement. The user is really building a query themselves but they don't need to know anything about Access to do it.

Another important reason for working this way is that inquisitive (or careless!) users might delete or change stored queries, and not know how to replace them.

Building a Multi-Purpose Query

The Plan...

The aim of this project is to create a single stored query whose criteria can be changed to suit the user's requirements. The query will be opened when the user clicks a button on a dialog box. The dialog box will also be used to gather the criteria from the user.

The Stored Query

You need a query that can be used as the basis of our multi-purpose query. Its design is completely irrelevant because it is going to be changed each time it is used, but Access doesn't let you create an "empty" query so you'll have to make something up (anything will do).

Ask Access for a new query in design view, add a table, put a field into the grid and then close and save the query. Give it a sensible name - in this example I am calling the query qryStaffListQuery.

The Dialog Box

I have decided that my users will probably want to query on three different criteria: Office, Department and Gender. So, the first step is to build a dialog box to help the users specify their criteria. It's up to you what your dialog box looks like and what it contains. I have chosen to use combo boxes showing lists of all the possible criteria choices. Future tutorials in this series will show examples of other design methods.

If you are not experienced in building dialog forms like this one, take a look at my tutorial Customizing Access Parameter Queries which contains detailed step-by-step instructions.

Point at the labels next to the combo boxes in the illustration below to see what is contained on their lists...

So, now we have the required components of the multi-purpose query. The next step is to write the code to make them work together.

Writing the VBA and SQL Code

When the user clicks the dialog's OK button several things must happen:

• Gather the user's choices from the combo boxes and write them into an SQL statement.
• Apply the SQL statement to the stored query.
• Open the stored query.
• Close the dialog box.

The code to carry out these operations will run on the On Click event of the OK button.

In form design view right-click the OK button and choose Properties to open its properties window and locate On Click on the Events tab. Click in the white bar then click the Build button: . In the Choose Builder dialog select Code Builder and click OK. You are now ready to write the code...

Declare and Fill the Variables

The first few lines of code establish contact with the database, telling Access that we are referring to the current database (i.e. the one containing the code) and identifying the query that we are going to work on. In addition, a string (i.e. text) variable is declared, which I have called strSQL. It will hold the SQL statement that will be applied to the query:

   Dim db As DAO.Database
   Dim qdf As DAO.QueryDef
   Dim strSQL As String
   Set db = CurrentDb
   Set qdf = db.QueryDefs("qryStaffListQuery”)

NOTE: I am using DAO language here because I think it is simpler for this sort of work. DAO is the default for Access 97, but the default for Access 2000/2002 is ADO. Access 97 users need do nothing (you can omit the "DAO." bits if you want but it doesn't really matter) but Access 2000/2002 users need to set a reference to DAO so that their database understands the code. In the Visual Basic Editor go to Tools > References. In the dialog box scroll down to "Microsoft DAO 3.x Object Library" (where x is the highest number if you have more than one) and put a tick in the box. Click OK to set the reference. You only need to do this once for the database and any code you put in it will be able to use the reference.

Build the SQL Statement

Next comes a VBA statement which places a text string into the strSQL variable. There was a detailed explanation of how I like to write my VBA/SQL and the rules you need to know in the second tutorial in this series: Access and SQL Part 2: Putting VBA and SQL Together.

This code combines SQL keywords and clauses into which have been placed references to the combo boxes that contain the user's criteria choices:

   strSQL = "SELECT tblStaff.* ” & _
                 "FROM tblStaff ” & _
                 "WHERE tblStaff.Office='" & Me.cboOffice.Value & "’ ” & _
                 "AND tblStaff.Department='" & Me.cboDepartment.Value & "’ ” & _
                 "AND tblStaff.Gender='" & Me.cboGender.Value & "’ ” & _
                 "ORDER BY tblStaff.LastName,tblStaff.FirstName;”

Although it doesn't seem to make a readable SQL statement as it is, when Access reads the code and substitutes, for example, Me.cboOffice.Value with London a sensible SQL statement results.

Here is what your code should look like so far [click the thumbnail to see a full-sized image]:

Test the Code

Now is a good time to test the code you have written so far and there are a couple of ways you can do this. You can "print" the SQL to the Immediate Window or you can display it in a message box (or you can do both!). Here's what to do:

Using the Immediate Window:

In Access 97 the Immediate Window is called the Immediate (lower) pane of the Debug Window. In all versions the window can be displayed by pressing Ctrl+G from the VBA code window. First, add the following line of code before the End Sub line of your cmdOK_Click procedure:

   Debug.Print strSQL

Using a Message Box:

Add the following line of code before the End Sub line of your cmdOK_Click procedure:

   MsgBox strSQL

Now you are ready to run a test. Switch to Access and open your dialog box in Form View (now is a good time to save the form!). Make some choices from the combo boxes and click the OK button...

If you chose to use a message box it will open displaying the SQL string that your code created from the choices in the dialog box. Read the SQL statement to check that it makes sense:

If you chose to use the Immediate Window, switch to the VBA code window and press Ctrl+G to open the Immediate Window where the SQL string will be displayed (it is written in a single line):

The message box method is quick and, because I am familiar with SQL, it is my preferred method. Using the Immediate Window has the advantage that you can select and copy from it the SQL statement that your code generated and paste it into a query to test it. To do this first select and copy the SQL statement then return to the Access database window and choose Create query in  design view. Close the Show Table box and open the SQL window by choosing View > SQL View. Delete any entry that is already there (it usually shows SELECT;) and paste in your SQL statement. Run the query and check the result.

If you see an error message when you try to run your SQL you can trace the error and correct it. Read the section on "Debugging Your SQL Code" in Part 2 of this series. When you are satisfied that this part of your code is working properly, you can remove the line(s) Debug.Print strSQL or MsgBox strSQL.

Update and Open the Query

All that remains is to apply the SQL statement to the stored query that you saved earlier, and to close the dialog box.

Enter the line:

   qdf.SQL = strSQL

to apply your new SQL statement to the stored query. There is no need to give a command to save this change to the query because this happens automatically. Next add the lines:

   DoCmd.OpenQuery "qryStaffListQuery"
   DoCmd.Close acForm, Me.Name

to open the query displaying the results of the user's criteria choices, and to close the dialog. Finally add the lines:

   Set qdf = Nothing
   Set db = Nothing

which empty the variables used to identify the query and the database. All variables normally lose their values when a procedure finishes, and the memory that was allocated to them is freed. But sometimes Access forgets to clear "object" variables (those with which you have to use the "Set" keyword) so it is good coding practice to clear them manually like this, just to make sure. Your finished code should look like this [click the thumbnail to see a full-sized image]:

Return to the Access database window and save the dialog box form (to save your code changes).

Job Done!

Your Multi-Purpose Query is now ready to run. Remember that the query should be run from the dialog box. If you open the stored query it will display records using the same criteria as the last time it was run.

The Multi-Purpose Query will work fine as it is, but you can make it even more user-friendly with the addition of a few refinements. The second part of this tutorial shows you how to turn your multi-purpose query into a really professional tool.

Go to the next part of the tutorial >>>