Access and SQL
Part 4: Building Queries "On the Fly"
(continued...)
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 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 [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.
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.
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
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.
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
AccessAndSQL4_2000.mdb 