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.
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.
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!).
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.
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.
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.
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!]
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:
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.
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.
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 2000 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
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:
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.
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.
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:
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
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
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
©2004 Martin Green - www.fontstuff.com - martin@fontstuff.com - All rights reserved