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:
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 and Department 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 the Department 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:
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:
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.
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 or False). 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.
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:
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
You can view the completed code procedure containing all the enhancements described above at http://www.fontstuff.com/access/acctut17code.htm. The code is in the form of plain text so it can be pasted directly into your procedure.
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). Find the files at http://www.fontstuff.com/downloads.
©2003 Martin Green - www.fontstuff.com - martin@fontstuff.com - All rights reserved