Access and SQL
Part 5: More Instant Queries
(Continued...)
Some Additional Features
Here is another example of an "Instant Query" dialog box with
some additional features. I have included an option for the user to
specify the Sort Order of the query results, using up to
three fields. There is a Clear All button so that the user
can quickly clear their selection from the list boxes. I have filled
each of the lists programmatically.
This part of the tutorial illustrates how to add these features.
I have not repeated the entire code listing, but the enhanced dialog
is included in the sample file accompanying this tutorial.
[Download it from here].
Offering Sort Options
Adding an instruction to an SQL statement to sort the data is
very simple. All that is required is the addition of an ORDER BY
clause listing the fields by which the data should be sorted, in the
required order. For example:
ORDER BY tblStaff.[LastName],tblStaff.[FirstName]
...sorts the data by the two fields LastName and
FirstName in that order. If, as in the example, no sort
direction is specified the default ascending (A-Z) is used. If a
descending sort is required the keyword DESC is added to the clause.
For example:
ORDER BY tblStaff.[BirthDate] DESC
...sorts the data by the BirthDate field in descending
(Z-A) order.
Letting the User Choose the Sort Fields
Allowing the user to choose a single field to sort by would be
very simple, and this could be achieved with a simple combo box
offering a list of fields. But sorting by a single field is often
not enough. Allowing them to choose two or three fields would be
much more useful, and the programming involved is only a little more
complex.
I have decided to let the user sort by up to three fields, and I
have provided three combo boxes for their choices...
The three combo boxes are the same, having only
different names (cboSortOrder1, cboSortOrder2 and
cboSortOrder3). I added a choice of suitable fields by creating
a Value List. A different option would be to select Field
List as the Row Source Type specifying the
appropriate table or query name from which the field names should be
taken. I didn't do this because I didn't want to confuse the user
with a long list of items, many of which were not relevant to
sorting. I also wanted to include the item Not Sorted at the
top of the list...
It is important to remember to set the combo box's Limit To
List property to Yes because if a non-existent field was
specified the SQL statement would fail and an error would result.
I made "Not Sorted" the Default Value of each combo box
and set the Enabled property of all but the first one to
False. The reason for disabling some of the combo boxes is that
I want the user to progressively enter sort fields. I didn't want to
have to write code that took account of the user leaving, for
example, the first two combo boxes with "Not Sorted" and choosing a
field from the third (people do that sort of thing!).
Coding the Combo Boxes
If I could rely on the user to use the Sort Order combo boxes
correctly I would only need the code to read their
values. But experience tells me not to leave such things to chance
so I must take steps to prevent things going wrong.
Disabling Unused Combo Boxes
When the form opens, only the first combo box is enabled. If the
user changes its value from "Not Sorted" to something else, then the
next combo box is enabled. By default its value is also "Not Sorted"
but if the user changes it to something else, the third combo box is
enabled (and so on for as many combo boxes as you have). This works
the other way too. If they change their mind and set the value of
one of the combo boxes back to "Not Sorted" then any following combo
box is disabled and its value set to "Not Sorted" again. Here's an
example of the code that does the job:
Private Sub cboSortOrder1_Change()
' Disable following sort options if "Not Sorted" is chosen
If Me.cboSortOrder1.Value = "Not Sorted" Then
With Me.cboSortOrder2
.Enabled = False
.Value = "Not Sorted"
End With
With Me.cboSortOrder3
.Enabled = False
.Value = "Not Sorted"
End With
Else
Me.cboSortOrder2.Enabled = True
End If
End Sub
A similar procedure is added to each combo box except the last
one, where it isn't necessary.
Preventing the Same Field from Being Chosen Twice
Although it would be ignored by Access anyway, I want to prevent
the user from choosing the same field more than once when they
specify the sort order. It has to be OK for more than one combo box
to hold the value "Not Sorted" but none of the other values can be
allowed to be duplicated. This code is applied to the each combo
box:
Private Sub cboSortOrder1_BeforeUpdate(Cancel As Integer)
' Check if sort field has already been chosen
If Me.cboSortOrder1.Value <> "Not Sorted" Then
If Me.cboSortOrder1.Value = Me.cboSortOrder2.Value _
Or Me.cboSortOrder1.Value = Me.cboSortOrder3.Value Then
MsgBox "You already chose that item."
Cancel = True
Me.cboSortOrder1.Dropdown
End If
End If
End Sub
I have used nested If Statements. If the value just chosen is
not "Not Sorted" it checks the other combo boxes to see if they
have the same value. If they don't then the user is allowed to
continue, but if a matching value is found the user is shown a
message, their choice is cancelled (which is why I used the
BeforeUpdate event - it allows you to cancel the user's action
before it is implemented) and the combo box list is opened to prompt
them to choose again.
Building the ORDER BY Clause
The OK button's cmdOK_Click procedure needs some
modification to build the ORDER BY clause and to include it in the
completed SQL statement.
A collection of nested If Statements is used to examine each
combo box in turn and check its value. If the value is "Not Sorted"
the If Statement terminates, but if the value is something else
(i.e. the name of a field) the value is added to a string variable
(named strSortOrder). If the value comes from any but the
first combo box, a comma is added in front of the field name because
the field names in an ORDER BY clause must be separated by commas:
If Me.cboSortOrder1.Value <> "Not Sorted" Then
strSortOrder = " ORDER BY tblStaff.[" &
Me.cboSortOrder1.Value & "]"
If Me.cboSortOrder2.Value <> "Not Sorted" Then
strSortOrder = strSortOrder & ",tblStaff.[" &
Me.cboSortOrder2.Value & "]"
If Me.cboSortOrder3.Value <> "Not Sorted" Then
strSortOrder = strSortOrder & ",tblStaff.[" &
Me.cboSortOrder3.Value & "]"
End If
End If
Else
strSortOrder = ""
End If
If the value of the first combo box is "Not Sorted" then I know
that the others must be the same so the Else part of the If
Statement creates an empty string.
To include the ORDER BY clause in the finished SQL statement the strSortOrder variable
is added to the end of the existing SQL statement:
strSQL = "SELECT tblStaff.* FROM tblStaff " & _
"WHERE Day(tblStaff.[BirthDate]) " & strDay & _
strMonthCondition & "Month(tblStaff.[BirthDate]) " & strMonth & _
strYearCondition & "Year(tblStaff.[BirthDate]) " & strYear & _
strSortOrder & ";"
You could further enhance this feature by letting the user choose
"Ascending" or "Descending" for each field. Each part of the if
statement above would then need its own nested if statement to add
the keyword "DESC" after the field name if "Descending" had been
chosen. [I'm tempted to add that if the user wants this degree of
flexibility it's time they learned how to use the Access Query tool
- or at least tried out the Access Query Wizard!]
Tips on Filling List Boxes and Combo Boxes
It is usual to specify the Row Source of a list box or combo
box during the design stage of building a form, but you can also
fill the list programmatically. Sometimes I use code to fill a list
because it needs to be dynamic (i.e. its content is not always the
same and must change to suit circumstances) and sometimes it just happens to be easier to do it with a bit
of code. Here are some examples:
Lists of Numbers
Suppose you want a simple list of numbers. In my second Instant
Query example I wanted a list of day numbers from 1 to 31
representing the days of the month. One
option is to type the list directly into the control's Row Source
property: 1;2;3;4;5;6;7 etc. all the way to 31. I'm bored already!
The methods are slightly different in Access 97/2000 and Access 2002-on.
Access 97/2000 Method:
Private Sub Form_Open(Cancel As Integer)
Dim i As Integer
Dim strDayList As String
For i = 1 To 31
strDayList = strDayList & "i" & ";"
Next i
With Me.lstDay
.RowSourceType = "Value List"
.RowSource = "strDayList"
End With
End Sub
As the list is built it is stored in a string variable strDayList.
The loop adds a number (i) followed by a semi-colon (;) to the
string each time the loop makes a circuit. When the loop, and the
string, are complete the strDayList variable is applied to
the RowSource property of the list box.
Access 2002 Method:
Private Sub Form_Open(Cancel As Integer)
Dim i As Integer
Me.lstDay.RowSourceType = "Value List"
For i = 1 To 31
Me.lstDay.AddItem i
Next i
End Sub
In Access 2002 the AddItem method was introduced for list
boxes and combo boxes, making the process even easier.
For each circuit of the loop the code adds a number (i) to the list
using the AddItem method.
The number increases by 1 each time until the specified maximum is
reached.
Both methods produce the same result:
This technique can be applied to all sorts of numerical lists.
Suppose you want the numbers 5 to 50 in steps of 5:
For i = 5 To 50 Step 5
Or perhaps the numbers 1 to 10 in reverse order:
For i = 10 To 1 Step -1
A List of Dates
My second list box required a list of months. Most people "think" in
terms of month names but my SQL needed the month numbers. I decided
to show them both using a two-column list:
A multi-column list is created using code in the same way as when
entering the information manually. I set the Column Count,
Column Widths and Bound Column properties manually in the
list box's property sheet (that could be done in code too) then ran
the following loop to fill the list:
Access 97/2000 Method:
Dim strMonthList As String
For i = 1 To 12
strMonthList = strMonthList & i & ";" &
Format(DateSerial(2000, i, 1), "mmmm")
& ";"
Next i
With Me.lstMonth
.RowSourceType = "Value List"
.RowSource = "strMonthList"
End With
As the list is built it is stored in a string variable
strMonthList. For each circuit of the loop it adds a number (i)
followed by a semi-colon (;) then a function that creates the name
of the month corresponding to that number adds the month name
followed by another semi-colon. When the loop, and the string, are
complete the strDayList variable is applied to the
RowSource property of the list box.
Access 2002 Method:
Me.lstMonth.RowSourceType = "Value List"
For i = 1 To 12
Me.lstMonth.AddItem i & ";" &
Format(DateSerial(2000, i, 1), "mmmm")
Next i
For each circuit of the loop the code adds a number (i) followed by
a semi-colon and a function that creates the name of the month
corresponding to that number. The number increases by 1 each time
until the specified maximum is reached.
A Dynamic List of Years
Years are just numbers so creating a list is simple but I wanted my
list to be dynamic, reflecting the date range in the BirthDate
field of my table:
I declared two variables, one to hold the oldest date in the table,
and the other to hold the newest date. The DMin and DMax
functions found me the oldest and newest dates, and the Year
function extracted the year number from them. I could then use the
resulting values to set the maximum and minimum values for my list:
Access 97/2000 Method:
Dim strYearList As String
Dim intFirst As Integer
Dim intLast As Integer
intFirst = Year(DMin("[BirthDate]", "tblStaff"))
intLast = Year(DMax("[BirthDate]", "tblStaff"))
For i = intFirst To intLast
strYearList = strYearList & i & ";"
Next i
With Me.lstYear
.RowSourceType = "Value List"
.RowSource = "strYearList"
End With
Access 2002 Method:
Dim intFirst As Integer
Dim intLast As Integer
intFirst = Year(DMin("[BirthDate]", "tblStaff"))
intLast = Year(DMax("[BirthDate]", "tblStaff"))
Me.lstYear.RowSourceType = "Value List"
For i = intFirst To intLast
Me.lstYear.AddItem i
Next i
Clearing Multi-Select List Boxes
Forms containing several controls often benefit from a button which
can reset the form by restoring the original values or simply by
emptying all the controls. A simple text box, or combo box can have
its value set to nothing:
Me.cboComboBoxName.Value = ""
The same applies to a list box where mutli-select is not enabled,
but if multi-select is enabled this method doesn't work. The code
has to look through the list and deselect each item. Each list box
is cleared with a loop like this:
Private Sub cmdClearAll_Click()
' Clear selections fro the list boxes
Dim varItem As Variant
For Each varItem In Me.lstDay.ItemsSelected
Me.lstDay.Selected(varItem) = False
Next varItem
' etc...
End Sub
Download the file
The enhanced dialog box is included in the sample file that
accompanies this tutorial. You can
[download the sample file from here].
©2004 Martin Green. All rights reserved.
martin@fontstuff.com
|
