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+Gfrom 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.

Adding Some Refinements to the Project

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.

These refinements deal with the potential problems that could occur if the user doesn't use the query in the way that you expected, and offers additional functionality:

• Let the user leave one or more combo boxes blank.
• Have the dialog box remain open for further queries to be run.
• Automatically build a new query if the original is deleted.
• Add an error-handling routine.

The code for these enhancements are considered separately, but a link to a complete code listing incorporating all the enhancements, as well as links to ready-made sample files can be found at the bottom of this page.

Dealing with Null Entries

What do you want to happen if the user leaves one or more combo boxes empty? Consider this example: supposing your user chooses a specific Office andDepartment but leaves the Gender combo box blank...

Their actions suggest that they are not interested in Gender so they haven't specified any criteria. But what they get is this...

when what they were expecting to see is this...

Because of the way the code was written, the criteria for the Gender field goes into the SQL statement as: tblStaff.Gender='’ . The user left the combo box empty so when its value was inserted into the SQL statement it appeared as a pair of quote marks with nothing in between (i.e. a zero length string) which gets interpreted as "Nothing". Since everyone in the list has a gender there are no records to display.

NOTE: when a field in a record is left empty its value is Null (which is different from '' which represents a zero length string). Your user may be looking for records with null values. If this is likely you should take this into consideration when building your code.

The user was probably expecting to see records for both genders. Similarly, if they left the Department combo box empty, specifying no criteria for theDepartment field suggests that they want to see records for all the departments. You can extend the argument to assume that if they leave all the combo boxes empty they want to see all the records.

If you were creating the query manually you would not specify any criteria at all for the field in question but, since we are using a method which (in order to be flexible) is expecting criteria for each of the fields, we must find a way to say "show me all the records for this field” when the user leaves a combo box empty.

The way I do this is to use the asterisk (*) wildcard. It is a simple matter to construct an If Statement which examines the user's input and if it finds that they have left a combo box empty it inserts the criteria: Like "*”

But it isn't quite as simple as that. We also have to modify the "skeleton" SQL statement because at the moment there is an equals sign following each field name, and the inserted value is enclosed in quotes. If we did not alter this the result would be something like this (the unnecessary characters are marked in red):

   tblStaff.Department='Like "*”’

when it should be:

   tblStaff.Department Like "*”

To achieve this the code needs three sets of modifications. First of all three new string variables are declared (strOffice, strDepartment and strGender) to hold the user input for each of the three fields.

Next, If Statements are written to examine the user input for each field and construct an appropriate criteria string. Here's an example:

   If IsNull(Me.cboOffice.Value) Then
      strOffice = " Like '*’”
   Else
      strOffice = "='” & Me.cboOffice.Value & "’ ”
   End If

The first part of the If Statement applies if the user left the combo box empty and adds the value:  Like '*’ to the variable. Note that there is a space before the word Like because this is needed in the final SQL string. Note also that I have used single quotes ( ' ’ ) around the asterisk. This is because this piece of code has quotes inside quotes. Neither SQL nor VBA care if you use single or double quotes (as long as opening and closing quotes are of the same kind), but you must alternate the types if they are nested like this.

The second part of the If Statement comes into effect if the user made a choice from the combo box. Instead of placing the combo box value directly into the SQL string (as in the original example) an equals sign and quotes are added to it. In the original example these were included in the skeleton SQL statement. The result is something like:  ='London’

Finally, the SQL string is modified to receive the newly formatted criteria. The finished code looks like this [click the thumbnail to see a full-sized image]:

Leaving the Dialog Box Open

You might find it useful to leave the dialog box open instead of closing it after the user clicks OK. This will allow the user to run more queries without having to reopen the dialog box each time. The dialog box can be made to remain open by simply removing the line:

   DoCmd.Close acForm, Me.Name

But this isn't enough! Although choosing a different combination of criteria and clicking OK again will apply the new SQL statement to the query, the query datasheet will not change (i.e. the user will not see the new results) until the query is run again. This means closing the query window and reopening it. You could instruct the user to close the query window before choosing new criteria, but we can do it easily with code.

We need to include some code to detect if the query window is open, and if so close it before applying the new SQL statement and reopening the query. To make this happen seamlessly we can turn of screen updating (called Echo in Access VBA) before closing the query and turn it on again after reopening it.

This code will replace the lines that apply the SQL string to the query and then open it.

The SysCmd(acSysCmdGetObjectState...) method can be used to detect whether or not the query is open. It returns a value of 1 (or acObjectStateOpen) if the object referred to is open, or 0 (zero) if it is closed or doesn't exist.

Here is the modified section of code:

   DoCmd.Echo False
   If Application.SysCmd(acSysCmdGetObjectState, acQuery, "qryStaffListQuery") 
   = acObjStateOpen Then
      DoCmd.Close acQuery, "qryStaffListQuery"
   End If
   qdf.SQL = strSQL
   DoCmd.OpenQuery "qryStaffListQuery"
   DoCmd.Echo True

The finished code looks like this [click the thumbnail to see a full-sized image]:

What if Someone Deletes the Stored Query?

This project makes use of a stored query and, as I mentioned at the beginning of this tutorial, one of the reasons I prefer not to rely on stored queries (if I can help it) is that they tend to get interfered with by users.

Since this query is redefined each time it is used, all we need to do it check to see if it exists before running the rest of the code. If it can not be found, then we must create a new one.

A Function to Check for the Existence of a Query

It is quite likely that you will want to check for the existence of a query more that once in your database so, rather than include the necessary procedure in the form's own code, I suggest that you use it to create a function that can be used from anywhere.

It is a good idea to keep your custom functions in a separate module, so go to the Modules tab of the database window and create a new module. Save it with a meaningful name (I have called mine modFunctions).

The code for the custom function is quite simple. As with the previous code for this project I have used DAO. We have already set a reference to DAO in this database but remember to do so if you want to use this function in another database (Access 97 users need not do this).

Here's the code for the function:

The function accepts a single argument, the name of the query, which must be entered in quotes as it is a string. The function returns "boolean" (i.e. True orFalse). The first part of the function declares the variables and sets the value to False. Then a simple For... Next loop looks at each query in the database and compares its name with the one supplied to the function. If it finds a match it sets the function's value to True and the function exits.

Using the Function in the Dialog Box Code

Now we have a function we can make use of it to check if the stored query exists before running the rest of the code. The code line:

   Set qdf = db.QueryDefs("qryStaffListQuery”)

would result in an error if Access could not find the query, so this line must be replaced by some code that checks for the existence of the query (using our custom function) and creates one if it can't find it. Replace it with this:

   If Not QueryExists("qryStaffListQuery”) Then
      Set qdf = db.CreateQueryDef("qryStaffListQuery”)
   Else
      Set qdf = db.QueryDefs("qryStaffListQuery”)
   End If

The finished code looks like this [click the thumbnail to see a full-sized image]:

Add an Error-Handling Routine

However well you write your code, planning for every eventuality and potential mishap, the determined user will find a way to crash your code (ask any professional developer!). Even the most experienced programmer can fail to spot a potential problem or unforeseen circumstance that could result in an error. For this reason every procedure you write should include an error handler.

You error handler could be written to cope with certain problems that you know might arise. For example, if we had not included the code to allow the user to leave combo boxes empty, an error would occur. There would be an error if someone had deleted the our query and we had not included the code to establish its existence. You could write error handling code to deal with these specific events. But if you think you have all the bases covered a "generic" error handler will suffice.

The reason for including an error handler in your procedure is to give you control over what happens if something goes wrong. If you don't do this Access displays the standard VBA error message, which probably means nothing to the user, with a button prompting them to "Debug". If they click the button they find themselves in the VBA code window, with their database in VBA break mode, and panic ensues!

Your error handler can take charge of the situation, return the database to a state of stability by closing the procedure and tidying up any unfinished jobs, and display a friendly and reassuring message to the user.

The very first line of your code should be a statement pointing to the error handler, so enter the following line immediately below the line Private Sub cmdOK_Click() :

   On Error GoTo cmdOK_Click_err

Then go to the end of the procedure, above the line End Sub, and enter:

   Exit Sub
   cmdOK_Click_err:

Note that there is a colon (:) at the end of the last line. This denotes that the line is a "label". When you finish typing and move out of the line it jumps to the left margin of the code window. Code labels act like a bookmark and are not executed as code.

The line you typed at the beginning of the procedure tells the code that, if there is an error, it should jump straight to this label and continue from there. The purpose of the Exit Sub before the label is to terminate the procedure if it reaches that point without an error occurring.

Below the label, enter the following code. It will deal with any error that might occur by displaying an explanatory message:

   MsgBox "An unexpected error has occurred." & _
      vbCrLf & "Please note of the following details:" & _
      vbCrLf & "Error Number: " & Err.Number & _
      vbCrLf & "Description: " & Err.Description _
      , vbCritical, "Error"
   Resume cmdOK_Click_exit

Finally, enter another label above the line Exit Sub that you typed earlier (see Note below):

   cmdOK_Click_exit:

Note: The last line of the error handler sends the code execution back (i.e. Resume...) to a point in the main procedure from which it can continue and tie up any loose ends. "Tidying-up" code usually occurs at the end of a procedure so, the cmdOK_Click_exit: label should really be before the lines that clear the object variables and, if you have included the code that checks for the existence of the query (in which screen updating, or Echo, is switched off) it should also be before the line that switches screen updating on again, like this:

cmdOK_Click_exit:
   DoCmd.Echo True
   Set qdf = Nothing
   Set db = Nothing
   Exit Sub

The Finished Procedure

You can view the completed code procedure containing all the enhancements described above  by [following this link]. The code is in the form of plain text so it can be pasted directly into your procedure.

Download the File

You can download a fully-working database file containing this tutorial's completed project in either Access 97 or Access 2000 format. The database contains two forms: a basic form (without enhancements) as created in the first part of the tutorial, and an enhanced form including all of the enhancements described above. The files are provided in Access 97 and Access 2000 format, and also as Zip files for faster download (you will need a copy of WinZip or a similar program to extract the zipped files).

AccessAndSQL4_97.mdb	AccessAndSQL4_97.zip	Access 97 format.
AccessAndSQL4_2000.mdb	AccessAndSQL4_2000.zip	Access 2000 format.