Access and SQL
Part 5: More Instant Queries
In the last part of this tutorial I showed how you could
build an all-purpose query by supplying the user with a dialog box
from which they could choose their query criteria. The dialog box
had combo boxes displaying criteria choices and when the user
clicked a command button their choices were used to construct an SQL
statement. The SQL statement was then applied to a stored query and
the user saw the result of their choices without having to get
involved with query design. This tutorial takes the idea a stage further.
Offering More Choices
Instead of combo
boxes I am going to use list boxes. List boxes can offer the user
the ability to choose more than one item, a property known as
Multi Select. I am also going to allow the user to specify either
"And" or "Or" conditions. Here's how my dialog
box looks...
The user makes their choices from the list boxes and option
buttons and clicks the OK button to see the result. In the example
below the user has asked for a list of all the Female staff
working in the Design departments of the Nice and
Paris offices [click the thumbnail to see a full-sized image]:
Building the Dialog Box
The first job is to build the dialog box. I have decided to allow
the user to query three fields: Office, Department and
Gender. You will see that the code that powers the dialog box
is easily modified to suit any number of fields.
I am not going to describe the form building process in detail so, 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.
This illustration shows the names I have given to the various
objects on the dialog box...
There are three list boxes, one for each field, and two pairs of
option buttons. The option buttons allow the user to choose whether
the AND operator or the OR operator is used when
combining the criteria. Setting Up the List Boxes
Building the Lists
The Row Source property of a list box
(likewise a combo box) defines what makes up the list of items
displayed. You can specify the name of a table containing the list
of items, or a query which creates a suitable list, or you can
simply type the list directly into the properties sheet of the list
box (this is called a Value List). For the
first two fields I decided to use a query. You can create a stored
query for this and enter its name into the Row Source
property textbox but, unless you plan to use the query for something
else, it is a better idea to enter an SQL statement directly into
the Row Source. (Why? There's always a chance that the stored
query will get changed or deleted by someone, then your dialog won't
work. It's neater too, we access-heads like neat-and-tidy!).
Since this tutorial is about SQL we might as well take a look at the
SQL statement I used.
SELECT DISTINCT tblStaff.Office FROM
tblStaff ORDER BY
tblStaff.Office; The SQL statement queries the same
table that contains the data I am querying with my dialog box. The
"SELECT DISTINCT" clause instructs Access to return only one of each
of the items it finds. Without this a simple "SELECT" clause would
return an Office name for each record in the table. The
"ORDER BY" clause tells Access to return the values sorted into
alphabetical order. This method gives me the
advantage that should any new Offices or Departments be created as
data is added to the table, the SQL statement (which is run each
time the dialog opens) will ensure that the list box always displays
an up-to-date list. There is a possible disadvantage to using this
method. If the table on which the SQL statement is based contains a
very large number of rows, or if you are using a large number of
list boxes, this could seriously slow down the opening of the dialog
box as it waits for the queries to be processed. In this case I
would opt for using a purpose made table for the Row Source
of each list box. TIP: You don't need to
write the SQL statement into the Row Source yourself. Click
on the Row Source textbox and the properties window will
display a build button
to the right of the textbox. Click the build button to open the
Query Builder tool. This works just like the familiar Access query
tool. Use it to construct and test your Row Source query.
When you close the Query Builder it returns a ready-made SQL
statement to the Row Source textbox. Since the
Gender list box will contain only two items ("Male" and
"Female") there is little point in using an SQL statement so instead
I have used a Value List as the Row Source. To do this
type the values directly into the textbox, separating the items with
a semicolon (;). The actual values in the Gender field
are "M" and "F" so, to help the user, I have created a list
containing two columns. When creating a multi-list column using this
method enter the values for each row of the list together, e.g.
M;Male;F;Female. Here's how it looks on the properties sheet...
You must also specify which column of a multi-column list is the
Bound Column, meaning which is to be taken as the "value" of the
list box. Here it is the first column, containing the letters "M"
and "F". The words "Male" and "Female" are just there to help the
user decode the letters (and don't think I'm joking here! More than
once I've had to explain to a user what they meant... "I can type M
for Man but it won't accept W for Woman". No, not dumb - just not
thinking the same way as me. Okay, I'm being kind, I mean dumb!)
It is also necessary, with a multi-column list, to specify how many
columns are to be displayed and how wide the columns should be. If
you don't do this you will only see one column.
I have specified a Column Count of 2 and Column Widths
of 0.5cm and 1.5cm. Note that when using metric measurements Access
sometimes rounds the values to the nearest imperial equivalent,
hence 0.503cm and 1.501cm. TIP: You can
create a multi-column list using the Query Builder too. Just design
a query that returns more than one column, then specify Bound
Column, Column
Count and Column Widths as described above.
Enabling Multi Select
Unlike combo boxes, list boxes can offer the
facility to select more that one item from the list. But you must
enable the Multi Select feature for this to be possible. The
Multi Select property is located on the Other tab of the
list box property sheet.
I have chosen to enable "Simple" multi select. In this mode the user
clicks an item to select it, whilst clicking a selected item
deselects it. They can select as many items as they want by clicking
on each in turn. You can also choose "Extended" multi select, which
allows you to drag down the list to select a group of items, or use
click and shift-click to select a continuous group, or click and
control-click to select a non-continuous group.
Preparing the Option Buttons
I have used option buttons to offer the user the
choice of "AND" or "OR" when composing the query. I want each pair
of option buttons to work as an option group, so that only one
member of the group is selected at a time. When one button is
selected the other gets deselected. Normally this is
achieved on an Access form by placing the option buttons inside a
frame. Doing this automatically forces them to work as an option
group. But I didn't want to draw frames on my dialog so I'm relying
on code to take care of the "group" activity. I did consider using a
frame formatted in such a way as not to be visible but sometimes I
just like doing things the hard way! If you don't
specify default values for option buttons they start with a "Null"
value (neither True nor False). This can confuse the user, and will
confound my code if they don't change the situation by making a
choice. For this reason I have set the Default Value property
of each "And" button to True and that of each "Or" button to
False. The Stored Query
Create and save a query. Anything will do because
the dialog box code is going to change it anyway. I have called my
query qryStaffListQuery. Writing the VBA and
SQL Code
The following section explains step-by-step what
code is needed and how it works. You can write the code yourself, or
copy it from the code listing below and
paste it directly into the Access code window.
Coding the Option Buttons
An option button can have the value True or
False. Each option
button needs some VBA attached to its OnClick event that
says: "If my value is True make my partner's value False,
but if my value is False make my partner's value True."
A simple VBA If Statement will do it. A procedure like this is needed
for each option button: Private Sub
optAndDepartment_Click()
If Me.optAndDepartment.Value = True Then
Me.optOrDepartment.Value = False
Else
Me.optOrDepartment.Value = True
End If
End Sub TIP:
When you have to repeat blocks of code like this, it makes sense to
copy and paste then change the appropriate bits, but sometimes this
can be quite fiddly. Instead, make use of the Visual Basic Editor's
Replace tool (Edit > Replace or Keys: Ctrl+H).
Paste a copy of the original procedure then select it and open the
Replace tool. Make sure that Selected Text is chosen
and enter the original text item into the Find What textbox
and the new item in the Replace With textbox. Click the
Replace All button and your new procedure is written for you...
Coding the OK Button
Everything else runs on the OnClick event of
the OK button. When the user clicks the OK button the
code must assemble an SQL statement based on the user's choices from
the list boxes and option buttons. It must then apply the SQL
statement to the stored query and open the query so that the user
can see the result. Start by declaring the string
variables that are going to hold the information collected from the
dialog box. In my example they are as follows: This
variable will be used to collect the selections from the list boxes: Dim
varItem As Variant These
variables will hold the collected choices from each list box: Dim
strOffice As String
Dim strDepartment As String
Dim strGender As String
These variables will hold the And/Or choices from the option groups: Dim
strDepartmentCondition As String
Dim strGenderCondition As String
This variable will hold the completed SQL statement: Dim
strSQL As String Getting the Choices from the
List Boxes
The code must look at each list box in turn and find
out which items have been selected by the user. It's possible that
the user might not select anything at all. If this happens I will
assume that they want to see everything (i.e. selecting nothing will
have the same effect as selecting everything).
NOTE: I'm assuming that the data being queried is text. If the data
is non-text such as numbers or dates, some small but important
modifications have to be made. Read the note
about data types near the bottom of the page. The following code
uses a For...Next loop to gather all the selections from the
lstOffice list box and join them together, separated by
commas, into a text string. The text
string is stored in a variable called strOffice... For Each
varItem In Me.lstOffice.ItemsSelected
strOffice = strOffice & ",'" & Me.lstOffice.ItemData(varItem) _
& "'"
Next varItem The next
step is to check the length of the resulting string. If its length
is zero, it means that the user didn't select anything. So the next
section of code takes the form of an If Statement which builds a suitable criteria string representing
the users choice... If
Len(strOffice) = 0 Then
strOffice = "Like '*'"
Else
strOffice = Right(strOffice, Len(strOffice) - 1)
strOffice = "IN(" & strOffice & ")"
End If If the user
selects nothing the code inserts a wildcard into the criteria
clause, resulting in something like this: WHERE
tblStaff.[Office] Like '*’ But if the user
selects one or more items the code creates an IN clause (after
removing the leading comma from the string) like this:
WHERE tblStaff.[Office] IN('Amsterdam’,'London’,'Paris’)
This complete process is repeated for each list box and the
resulting criteria are stored in separate variables. I called my
variables strOffice, strDepartment and strGender.
Getting the Choices from the Option Buttons
The code needs to find out whether the user chose
And or Or from each pair of option buttons. It is only
necessary to look at the value of one button from each pair since I
know that if one's value is True then the other must be
False, and vice versa. Again an If Statement does the job... If
Me.optAndDepartment.Value = True Then
strDepartmentCondition = " AND "
Else
strDepartmentCondition = " OR "
End If
Each set of option buttons requires an if statement
like this. My dialog has two sets. The If Statements create suitable
strings representing the specified conditions and stores them in
separate variables. Mine are called strDepartmentCondition
and strGenderCondition. NOTE:
Notice that there are spaces each side of the text in the condition
strings " AND " and " OR ". The following section of code also
includes spaces as part of the string. It is very important when
combining hard-coded text and variables that you remember to include
spaces where they will be needed in your finished SQL string.
Building the Query's SQL Statement
Having gathered all the information from the dialog
box, it's now time to build the SQL statement. I have defined a
string variable called strSQL to hold it. Here's how the code
builds the SQL statement incorporating hard-coded SQL with the
variables created in the previous section...
strSQL = "SELECT tblStaff.* FROM tblStaff " & _
"WHERE tblStaff.[Office] " &
strOffice & _
strDepartmentCondition & "tblStaff.[Department]
" &
strDepartment & _
strGenderCondition & "tblStaff.[Gender]
" &
strGender & ";"
Test the Code
Now is a good time to test the code you have written
so far. An easy way to do this is to display the SQL statement in a
message box. Do this by adding the line:
MsgBox strSQL Alternatively you can "print" the SQL statement to the
Immediate Window of the Visual Basic Editor by adding the line: Debug.Print
strSQL Before you run your code check that you have everything you need by
comparing it with Code Listing 1
then compile it with Debug > Compile... If the
dialog box is in Design View switch it into Form View and click
Save, then make some choices from the list boxes and option
buttons and click the OK button. If you asked for a message
box it will be displayed now, and you will be able to read the SQL
statement. Or you can return to the Visual Basic Editor to
view the resulting SQL statement in the Immediate Window (choose View > Immediate Window
or Keys:
Ctrl+G).
This gives you the opportunity to make sure that your code is
working correctly before proceeding to the next step.
Apply the SQL Statement to the Stored Query
All that remains is to apply the SQL statement to
the stored query and open the query for the user to view the
results. There are two ways to write the code for this, depending on
whether you want to use DAO
or ADO.
If you use Access 97 you must use the DAO method. If you use Access
2000 or later you can use either.
If you are using Access 97 you have to write the
code this way, but if you use Access 2000 or later you can use DAO
instead of the default ADO if you prefer (many Access developers
prefer DAO because of its simpler coding style). If you are not
using Access 97 you need to set a reference to DAO. To do this,
in the Visual Basic Editor go to Tools > References and
scroll down the list until you find Microsoft DAO 3.6 Object
Library (if you can't find 3.6 use the highest number
available). Place a tick in the check box and click OK.
Two additional variables must be declared (Note: Access 97 users can
omit the DAO. prefixes, but the code will work fine if they
are left in place.): Dim
db As DAO.Database
Dim qdf As
DAO.QueryDef Here is the code which will apply the
SQL statement to the query: Set
db = CurrentDb
Set qdf =
db.QueryDefs("qryStaffListQuery")
qdf.SQL = strSQL
Set qdf = Nothing
Set db = Nothing
The first two lines tell Access that we are talking about the
current database, and name the query we are concerned with. The
third line applies the new SQL statement to the query. The last two
lines empty the db and qdf variables to clear the
memory.
Because this code deals with database structure, you
need to set a reference to ADOX.
To do this, in the Visual Basic Editor go to Tools > References
and scroll down the list until you find Microsoft ADO Ext. 2.x
for DDL and Security (where x is the highest number you
have). Place a tick in the check box and click
OK. Two additional variables must be declared: Dim
cat As New ADOX.Catalog
Dim cmd As
ADODB.Command In ADO the information about the
structure of a database is stored in its Catalog. The
Command object in ADO is similar to the QueryDef object
in DAO. Here is the code which will apply the SQL statement to the
query: cat.ActiveConnection =
CurrentProject.Connection
Set cmd =
cat.Views("qryStaffListQuery").Command
cmd.CommandText = strSQL
Set
cat.Views("qryStaffListQuery").Command = cmd
Set cat = Nothing
The first line tells Access that we are talking about the current
database, and the second line identifies our stored query (in ADO a
query is a View or a Procedure depending on its type).
The third line applies the new SQL statement and the fourth line
saves the changes back to the catalog. Finally, the catalog variable
is set to Nothing to clear the item from the memory.
Open the Query and Close the Dialog Box
The code to open the query and close the dialog box
is the same for DAO ad ADO:
DoCmd.OpenQuery "qryStaffListQuery"
DoCmd.Close acForm, Me.Name You can consider the
project finished at this point, but it can be made a little more
user-friendly with the addition of a few refinements...
Refinements: Leave the Form and Query Open
Now that you have made it really easy for the user
to query their data, they are sure to want to run lots of queries!
You can save them the trouble of reopening the dialog box each time
by omitting the line DoCmd.Close acForm, Me.Name and adding
some extra code to allow them to leave the query window open too.
They can arrange the query window and dialog box so that they can
see both on the screen, then as they make their choices in the
dialog they will see the results in the query window as soon as they
click the OK button. In fact the query must be
closed before the new SQL statement can be implemented (the SQL
statement will be applied to the query even if it is open but the
user will not see the changes until the query is run again).
The following code should be placed at the beginning of the
cmdOK_Click procedure, immediately following the variable
declarations: If
SysCmd(acSysCmdGetObjectState, acQuery, "qryStaffListQuery")
= acObjStateOpen Then
DoCmd.Close acQuery, "qryStaffListQuery"
End If It simply checks
to see if the query is already open and, if it is, closes it. The
code will reopen it after the new SQL statement has been applied. If
the query takes more than a moment to run, the user will see query
close then open. To make this process invisible turn off screen
updating by placing this line before the code above:
DoCmd.Echo False
...and add the following line to the very end of the procedure
before the End Sub line to restore screen updating:
DoCmd.Echo True
TIP: When testing your code it's a good idea
to temporarily remove or disable the line that turns off screen
updating (DoCmd.Echo False) in case anything goes wrong and
you're left with a frozen window. The line which restores screen
updating (DoCmd.Echo True) usually comes near the end of the
procedure. If there is an error somewhere which prevents the
procedure reaching this line then screen updating will not be
restored and you will be left with a blank or frozen screen. Even
after several years as a VBA developer I still forget to do this,
and sometimes I find myself staring at a blank screen wondering what
the heck has happened! If it happens to you, don't panic - instead
switch to the Visual Basic Editor and open the
Immediate Window (Ctrl+G). Type DoCmd.Echo True
into the Immediate Window and press Enter and when you return
to the Access window you will find that Screen Updating has been
restored. If you have a procedure that switches off screen updating,
remember always to include a line in your error handler to restore
screen updating in the event of an error.
Refinements: Restore a Missing Query
Users (that includes you and me!) have the annoying habit of
deleting things if they don't know what they are. If the code can't
find the query because someone has deleted or renamed it there will
be an error. In the last tutorial in this series I explained how to
create a function to test for
the existence of a query. This time, I'll explain how to include
the code in the cmdOK_Click procedure. The method is slightly
different for DAO and ADO so I'll outline both.
Using DAO (Access 97)
If you haven't already added the db and qdf variables
(as described above) you should add them now,
together with a third variable blnQueryExists.
Dim
db As DAO.Database
Dim qdf As
DAO.QueryDef
Dim blnQueryExists As Boolean
The code is in two parts, and should be placed right at the
beginning of the cmdOK_Click procedure, after the variable
declarations. The first part uses a For...Next loop to search
through the database's queries looking for one with the name
specified. If it finds the named query it sets the value of the
blnQueryExists variable to True and exits the loop (it
must exit the loop now because any other query it finds will set the
variable's value back to False)...
Set db = CurrentDb
blnQueryExists = False
For Each qdf In
db.QueryDefs
If qdf.Name = "qryStaffListQuery" Then
blnQueryExists = True
Exit For
End If
Next qdf
The second part looks at the value of the blnQueryExists
variable and if it is False it creates a new copy of the
query...
If blnQueryExists = False Then
Set qdf =
db.CreateQueryDef("qryStaffListQuery")
End If
Application.RefreshDatabaseWindow
The last line is not usually necessary because the new query will be
opened later in the procedure. But, in case something unforeseen
happens, this makes sure that the new query appears without the user
having to refresh the window themselves.
Using ADO (Access 2000 onwards)
If you haven't already added the cat and cmd variables
(as described above) you should add them now,
together with the variables qry and blnQueryExists.
Dim cat As New
ADOX.Catalog
Dim cmd As New
ADODB.Command
Dim qry As
ADOX.View
Dim blnQueryExists As Boolean
The code is in two parts, and should be placed right at the
beginning of the cmdOK_Click procedure, after the variable
declarations. The first part uses a For...Next loop to search
through the database's queries (a query in ADO is called a View)
looking for one with the name specified. If it finds the named
query it sets the value of the blnQueryExists variable to
True and exits the loop (it must exit the loop now because any
other query it finds will set the variable's value back to False)...
Set cat.ActiveConnection =
CurrentProject.Connection
blnQueryExists = False
For Each qry In cat.Views
If qry.Name = "qryStaffListQuery" Then
blnQueryExists = True
Exit For
End If
Next qry
The second part looks at the value of the blnQueryExists
variable and if it is False it creates a new copy of the
query...
If blnQueryExists = False Then
cmd.CommandText = "SELECT * FROM tblStaff"
cat.Views.Append "qryStaffListQuery", cmd
End If
Application.RefreshDatabaseWindow
Note that when using VBA to create a new query, ADO insists that I
provide the query with an SQL statement. Anything will do, but it
won't accept an empty string (""). With DAO I can create an "empty"
query definition (the procedure supplies the SQL later anyway).
Here is the completed code listing for the dialog box. It is in a
format that you can copy and paste directly into the Access code
window.
Code Listing 2 (DAO) for Access
97
Code Listing 2 (ADO) for Access
2000 (and later)
When composing SQL statements it is important to get everything
right. SQL is a simple language but it is very precise and does not
tolerate errors. In this tutorial I have made use of the IN() clause
which is very useful for presenting a list of items when specifying
your criteria:
WHERE tblStaff.[Office] IN('Amsterdam','London',Paris')
...is the same as:
WHERE tblStaff.[Office] = 'Amsterdam' OR tblStaff.[Office] =
'London' OR tblStaff.[Office] = 'Paris'
The IN() clause can be used for any data type, not just text as I
have illustrated here, but you must remember to use the appropriate
qualifier when composing the SQL. The qualifier is a symbol
enclosing the data which tells Access what the data type is and, of
course, the data type must match the data type of the field that the
criteria applies to (my Office field is a text field).
Text criteria must be enclosed in quote marks. SQL will
accept single quotes (') or double quotes (") but since here I am
creating the SQL statement as a VBA text string the whole thing is
itself enclosed in double quotes, so I use single quotes for the
individual criteria items.
Date criteria must be enclosed in hash marks (#) (you might
call them number signs). Remember also that when hard-coding dates
into SQL they must always be written in the US format of
month/day/year. [Note: If your Windows regional settings dictate
that you use the European day/month/year date format it is you
should use that format when entering criteria into the grid of the
Access query design window. Access converts it to the US format when
it constructs the SQL statement. Take a look at the query in SQL
View and you will see it.]
Number criteria should be entered without any qualifier.
So, to modify the code I used to gather data from the list boxes:
If the list box contains a list of dates:
For Each
varItem In Me.lstBirthDate.ItemsSelected
strBirthDate = strBirthdate & ",#" &
Me.lstBirthDate.ItemData(varItem) & "#"
Next varItem
Note that the single quotes have been replaces by hash marks (marked
in red). The resulting IN() clause looking something like this:
WHERE tblStaff.[BirthDate] IN(#9/27/1950#,#2/7/1968#,#6/19/1977#)
If the list box contains a list of numbers:
For Each
varItem In Me.lstID.ItemsSelected
strID = strID & "," & Me.lstID.ItemData(varItem)
Next varItem Note that
the single quotes have been removed leaving just the comma and,
since no qualifier is required for numerical data, the closing
qualifier is removed completely. The resulting IN() clause looking
something like this: WHERE tblStaff.[ID]
IN(25,50,75,100) Some Additional Features
If you want to add extra functionality to your
dialog box take a look at part two of this tutorial. It shows you
how to build list box lists with code, how to let the user choose
how the data is sorted by add an ORDER BY clause to the query, and
how to add a Clear All button to reset the list boxes...
Go to the next part of the tutorial >>>
CORRECTION: The sample file accompanying this tutorial
originally contained a typo in the
error-handling code. The last line of the cmdOK_Click
procedure of each of the sample forms read Resume cmdOK_Click_Err
when it should have read Resume cmdOK_Click_Exit. This error
was corrected on 12 April 2004.
©2004 Martin Green. All rights reserved.
martin@fontstuff.com
|
AccessAndSQL5_97.zip [82KB]