| Cascading Lists... |
| Coding Note |
|
Sometimes lines of code can be very long! Where I
have had to break a line of code to fit it on the page I have used
the
<Blah blah blah should be written: <Blah blah blah blah> |
symbol to indicate that the line below is a continuation and that
the code should be written as a single line
|
Cascading Lists for Access FormsCombo Boxes and List Boxes are popular tools for both assisting the user and controlling data input. Their functionality can be enhanced by changing the contents of the list depending on circumstances. For example, the user makes a choice from one list which causes the contents of another list to change. This concept of cascading or interdependent lists is quite easy to put into practice with a little VBA programming. This tutorial demonstrates several different ways to create cascading lists on your Access forms. I will use combo boxes, rather than list boxes, as the latter have a more specific purpose (to allow the user to make multiple selections). If you are new to form building, or have not ever put a combo box on to a form, have a look at a step-by-step tutorial first. What Do Cascading Lists Do?The purpose of cascading lists is to give the database designer greater control over data input, and to make things easier for the user. On their own, combo boxes make data input easier and more reliable. When two or more combo boxes work in conjunction with each other they can help to reduce the user's decision making by prompting them with only relevant data. In this example there are two combo boxes: one displays a list of countries, the other displays a list of cities. If they worked independently the user could choose a country and a city but might get the combination wrong. Also, the list of cities in would have to show all the cities available. The combo boxes can be linked in a number of ways. The illustration below shows how linked combo boxes might work. The user chooses a country first then opens the city list. They see a list of cities relevant to the country they selected. To see the demo, point at the numbered items below and watch the image change (if necessary scroll your browser window so that you can see the whole image).
1. The form has two regular combo boxes
If your browser does not have JavaScript enabled my rollover effects for the image above won't work. You can see the individual images by clicking the thumbnails below:
The following examples show several different ways of achieving this effect and are presented in an increasing order of complexity. Example 1: Multiple Row Source TablesThis is the simplest method to code but it requires a table for the main combo box and several tables for the dependent combo box. This example has a table (tblCountries) that is assigned as the Row Source of the main combo box (cboCountry). There are also three more tables (tblFrance, tblUnitedKingdom, and tblUnitedStates) which will provide in their turn the row source for the dependent combo box (cboCity). To begin with, no row source is specified for the dependent combo box.
The plan is to have the contents of the cboCity list change to reflect the user's choice from cboCountry. This will be achieved by programmatically defining the Row Source property of cboCity using the After Update event of cboCountry. Here's the code that does the job: Private Sub cboCountry_AfterUpdate() On Error Resume Next Select Case cboCountry.Value Case "France" cboCity.RowSource = "tblFrance" Case "United Kingdom" cboCity.RowSource = "tblUnitedKingdom" Case "United States" cboCity.RowSource = "tblUnitedStates" End Select End Sub How it works...The code for this method is very simple. The AfterUpdate event fires when the user makes a choice from the cboCountry combo box. The code uses a Case Statement to assign one of the city tables to to the Row Source property of the cboCity combo box according to the user's choice. Example 2: A Single Row Source TableThis example uses a single table which provides data for the Row Source of both combo boxes. The table (tblAll) contains two fields, one for the name of the City and the other for the name of the Country to which it belongs...
The Row Source property of the cboCountry combo box takes the form of an SQL statement which represents a query of the tblAll table returning the unique values found in the Country field, sorted into ascending order: SELECT DISTINCT tblAll.Country FROM tblAll ORDER BY tblAll.Country; As in the previous example, no row source is specified for the dependent combo box. The code will deal with that. The following procedure runs on the After Update event of the cboCountry combo box: Private Sub cboCountry_AfterUpdate() On Error Resume Next cboCity.RowSource = "Select tblAll.City " & _ "FROM tblAll " & _ "WHERE tblAll.Country = '" & cboCountry.Value & "' " & _ "ORDER BY tblAll.City;" End Sub How it works...When the user makes a choice from the cboCountry combo box the AfterUpdate event fires. The attached code defines a Row Source to the cboCity combo box in the form of an SQL statement. NOTE: When writing SQL in VBA code I usually write my SQL statements as shown above with each clause on a separate line. I think this makes them easier to read. If you do this remember to enclose each line in quotes, concatenate (i.e. join) each line with an ampersand (&) and insert the line continuation character space+underscore ( _) when you break the code line. The SQL statement changes according to the user's choice as determined by the inclusion of cboCountry.Value in the WHERE clause. If, for example, the user had chosen France then the line containing the WHERE clause would read: "WHERE tblAll.Country = 'France' " & _ Example 3: Synchronising the ListsThis example uses the same code for the After Update event of the cboCountry combo box to assign the appropriate Row Source to the cboCity combo box as in the previous example. This time an additional procedure has been added to synchronise the cboCity Row Source with any existing value that might already be in the City field. When a Row Source is assigned to the cboCity combo box, it remains assigned until it is reassigned by a change in the cboCountry combo box. So, when navigating through existing set of records, the list of cities may not be appropriate for the city shown in the City field. For example, you enter a record for an address in the United Kingdom so cboCity displays a list of UK cities. You then move back through the records and stop on an address in France. You might wish to edit that address so you open the cboCity combo and instead of seeing a list of French cities you see a list of UK cities! This happened because that was the last Row Source assigned to the combo box and nothing has happened since to change it. This example corrects that using the following code on the form's On Current event: Private Sub Form_Current()
On Error Resume Next
' Synchronise country combo with existing city
cboCountry = DLookup("[Country]", "tblAll",
How it works...The OnCurrent event fires when a form moves from one record to another (or when the form is refreshed or requeried). The first line uses the DLookup function (this works like Excel's VLOOKUP function). This gets the value (i.e. the existing city name) from the City field, looks for it in tblAll table, and returns the corresponding Country name which it places in cboCountry combo box. (NOTE: If, as in my example there is a Country field in the form's underlying table then this data should be in place anyway - but I'm just making sure! In fact, you might want to omit or modify this line if you were allowing users to enter city names that were not on the list.) The next line is the same as the one that runs on the AfterUpdate event of the cboCountry combo box but we need it here because the AfterUpdate event has not been fired. Example 4: Mixing Controls - Using an Option GroupThe method works just as well with different combinations of controls. Here I have replaced the cboCountry combo box with an Option Group called grpCountry with option buttons for the various countries. The illustration below shows how it works. To see the demo, point at the numbered items below and watch the image change (if necessary scroll your browser window so that you can see the whole image).
1. If there is no city chosen, the Country group displays a "Null"
value
If your browser does not have JavaScript enabled my rollover effects for the image above won't work. You can see the individual images by clicking the thumbnails below:
Writing the cascade code...This form incorporates all the features of the previous example (e.g. synchronising the form with the data) but the code differs because I have used an Option Group (named grpCountry) instead of a combo box for the user to choose a country. Of course, an option group does not require a Row Source. Instead, all the options are on view as labelled option buttons (sometimes called "radio buttons"). Each Option Button has an Option Value property. This value must be a whole number but it can be whatever you want (within limits - its data type is Long) but it is logical to give them sequential numbers so mine are France(1), United Kingdom(2) and United States(3). When the user chooses one of the options by clicking it a black dot appears in the button and the Option Group has a value corresponding to the Option Value of the selected button. So, if the user selects the United States button the Option Group has a value of 3. NOTE: When you draw a group of Option Buttons you should draw the Option Group frame first, then draw the Option Buttons inside. When you do this all the buttons inside the frame become part of the same group and function properly. Only one can be selected at a time, and the selected button passes its value to the group. The code used to assign a Row Source to the cboCity combo box runs on the After Update event of the grpCountry Option Group (NOT the event of the individual button). The event fires when the user selects one of the buttons: Private Sub grpCountry_AfterUpdate() On Error Resume Next Dim strCountry As String Select Case grpCountry.Value Case 1 strCountry = "France" Case 2 strCountry = "United Kingdom" Case 3 strCountry = "United States" End Select cboCity.RowSource = "Select tblAll.City " & _ "FROM tblAll " & _ "WHERE tblAll.Country = '" & strCountry & "' " & _ "ORDER BY tblAll.City;" End Sub How it works...First of all, a Case Statement checks the value of the group and puts the corresponding country name a string variable called strCountry. Then an SQL statement, incorporating the variable and querying the tblAll table, is assigned as the row source of the cboCity combo box. It works in much the same way as previously but the code gets the name of the country from an Option Group instead of a Combo Box. Writing the synchronise code...Similarly the forms On Current event is used to synchronise the Option Group with the City field, and again the code is a bit more complex: Private Sub Form_Current()
On Error Resume Next
Dim strCountry As String
If IsNull(cboCity.Value) Then
grpCountry.Value = Null
End If
' Synchronise country combo with existing city
strCountry = DLookup("[Country]", "tblAll",
How it works...You might have noticed that in the first illustration none of the option buttons are selected, all are greyed out. Form designers often like to set one of the buttons of an option group as the "default" button so that it is already selected when the form opens or when the user moves to a new record. It's easy to do, just find the option group's Default Value property and set it to whatever value represents the button you want selected. But I didn't want to do that. Since I was going to synchronise the option group country with any existing city, I decided to give the group a Null value if no city name was present. This is achieved by the If Statement at the start of the code procedure. Next comes the code to determine the correct country for the existing city (if present). Like the previous example it uses the DLookup function, but unlike the previous example it can't feed the country name straight into the option group. It has to convert the name to a number (the value of the appropriate option button) so it uses a Case Statement to do this. Finally, the same code as before synchronises the combo box list with the existing city. Download the FileYou can download a fully working demo database illustrating the techniques covered in this tutorial. 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).
|
||||||||||
| ^ top | |||||||||||
|
|
