|< Day Day Up >|
Because many times combo boxes and list boxes are filled in by data from tables or queries, instead of hardcoded values, you sometimes might want to change the underlying query for a control. A good example of this is if you have a form asking you to choose a store location. When the form first opens, all locations are available. Let's assume that there are combo boxes for state and location. It wouldn't be uncommon for a client to ask you to show only locations for a particular state when a user selects state. It also might not be a stretch for the client to ask that the combo box for location not be activated until the user chooses a state. Here are the steps you would follow to accomplish these two tasks.
First, using the form's OnOpen event (or OnCurrent event if this is a data entry form), set the Enabled property of the Location combo box to FALSE. Next, put code in the AfterUpdate event of the State combo box to change the underlying query of the Location combo box by changing the Row Source property via VBA. You can see what is currently in the Row Source property by looking at the Data tab of the combo box properties. Example 11-3 includes code for both of these events.
Example 11-3. Code to restrict locations to particular states
Private Sub Form_Open(Cancel As Integer) Me.LocationCombo.Enabled = False End Sub Private Sub StateCombo_AfterUpdate( ) Me.LocationCombo.Enabled = True Me.LocationCombo.RowSource = "SELECT [tbl_Locations].[Location] " & _ "FROM [tbl_Locations] Where " & _ "[tbl_Locations].[State] = """ & Me.StateCombo.Value & """;" Me.LocationCombo.Requery Me.LocationCombo.SetFocus End Sub
If you need help writing the query, go to the Query Designer in the Access GUI to write the query, and then switch to SQL view. Also, notice that I put the value of the state in double quotes. I could have just as easily used single quotes because I can be certain that a state will not have a single quote in its string, but I think it is a good practice to put all of the string in queries in double quotes to avoid the issue. If you use single quotes, whenever an apostrophe is used in text, you get errors in your application. This can be very frustrating for you and users because you might not test for this error when you build the application.
To make this even more user friendly, add code that changes the value of the location box to an empty string whenever the state value changes. Users might also ask to have all locations available by default (instead of having the Location combo box not enabled) with restrictions by state when a state is selected. Eliminate the Where clause from the query string in the properties tab of the Location combo box and get rid of the lines of code setting the enabled property.
These same techniques apply to list boxes. An interesting way to perform them with list boxes is to have a group of radio buttons that make your selection and set the SQL for the list box based on it. For example, let's say you are building a form to search for a car. You have radio buttons that let you choose whether to search by color, engine, or body style. Do this by adding an option group to the form. When you create the option group, Access assigns a numeric value to each selection. Next, have code for the option group's OnChange event to change the SQL of the list box.
In addition, you might want to give the user the ability to choose more than one item from the list box, by setting the Multi-Select property of the list box on the Other tab of the properties dialog to Extended. See Example 11-4. for the code for the option group.
Example 11-4. Code example to change queries based on a selection
Private Sub Frame6_AfterUpdate( ) Select Case Me.Frame6.Value Case 1 ' This is the value for Color Me.SearchList.RowSource = "Select Color from tbl_Colors" Me.SearchList_Label.Caption = "Color" Case 2 ' This is the value for Engine Me.SearchList.RowSource = "Select Engine from tbl_Engines" Me.SearchList_Label.Caption = "Engine" Case 3 ' This is the value for Style Me.SearchList.RowSource = "Select Style from tbl_Styles" Me.SearchList_Label.Caption = "Style" End Select Me.SearchList.Requery End Sub
There are a couple things to take note of in the example. First, I change the caption for the label of the list box based on the selection. That certainly doesn't do anything for functionality, but it does add a nice touch. Second, it is unnecessary to have the Requery event in each condition of the Select Case because you can set the SQL for the source in the Select Case statement and requery at the end.
If you allow your users to make multiple selections, you need a way to get the selected values. The list box has a collection called ItemsSelected that is a collection of Variants. You can iterate through this collection with a For Each...Next loop to get the values. In Example 11-5, I show how to create a button that brings up a message box for each value selected in a multi-select list box.
Example 11-5. Code to cycle through a multi-select list box
Private Sub Command19_Click( ) Dim itm As Variant For Each itm In Me.SearchList.ItemsSelected MsgBox Me.SearchList.ItemData(itm), vbOKOnly Next itm End Sub
This is a very simplistic example of how to cycle through this collection. I often store the values in a string that I use in conjunction with an IN clause in SQL to perform a search. Once you understand how to cycle through the collection, you can use this technique to perform a number of tasks.
|< Day Day Up >|