This month, Doug Steele show you how to manipulate a ComboBox to display multiple fields and how to synchronize one Combo box with another.
My ComboBox displays multiple fields in it when I’ve got it dropped down, but once I select a value, it only displays a single field. Is there any way I can see the other fields once I’ve made a selection?
As you’ve noticed, all that displays when the ComboBox isn’t dropped down is the first visible column (which, remember, may not be the Bound column). However, all of the columns in the row source of the ComboBox are available to you programmatically, even if they’re not visible in the ComboBox when it’s dropped down.
ComboBoxes (and ListBoxes, for that matter) have a Column property that lets you refer to the columns. The actual syntax to refer to the property is:
control.Column(column, row)
The components are:
- Control–A Control object that represents the active ComboBox or ListBox control.
- Column–An integer that can range from 0 to the setting of the ColumnCount property minus 1.
- Row–An integer that can range from 0 to the setting of the ListCount property minus 1 (optional).
The reason that row is optional, by the way, is that when you omit it, the currently selected row will be displayed. In the case of a ListBox with Multiselect set to something other than None, this means the last row “touched” is used, whether or not that row is actually currently selected.
By taking advantage of the Column property, you can set the row source for a ComboBox to include as many fields as you like. In the ComboBox’s Properties, you can determine which fields will actually display when the ComboBox is dropped down by setting the ColumnWidths property for each field. You separate column entries using whatever list separator character is displayed in the List Separator box on the Number tab of the Regional Settings Properties dialog box (usually a semicolon).
Figure 1 shows how I’ve declared the row source for my ComboBox cboEmployees to be the query named qryEmployees. That query has seven fields in it. I’ve set the first column to be the Bound Column (which means that if I simply refer to Me.cboEmployees in code, I’ll get whatever value is in that column) and, by setting the ColumnWidths property to (0″;1.5″;0″;0″;1.5″;0″;1″), I’ve indicated that only the second, fifth, and seventh columns should be visible.
Figure 1
However, even though only three columns are displayed, I can refer to any of the columns (and any of the rows) in code. For example, assuming I have a number of TextBoxes defined on my form, I can populate those TextBoxes with the appropriate values from the ComboBox in the ComboBox’s AfterUpdate event with code like this:
Private Sub cboEmployees_AfterUpdate() Me.txtEmployeeID = Me.cboEmployees.Column(0) Me.txtTitle = Me.cboEmployees.Column(5) Me.txtFirstName = Me.cboEmployees.Column(3) Me.txtLastName = Me.cboEmployees.Column(2) Me.txtJobTitle = Me.cboEmployees.Column(4) Me.txtBirthdate = Me.cboEmployees.Column(6) End Sub
Since I’m not providing a row value to the Column property, I get the value from the currently selected row in the ComboBox. However, I can provide a row number, and get values from rows that aren’t selected as well. For instance, the following code will give details for each row in the ComboBox:
Dim lngRow As Long Dim strMessage As String For lngRow = 0 To (Me.cboEmployees.ListCount - 1) strMessage = strMessage & "Row " & _ lngRow & " is " & _ Me.cboEmployees.Column(3, lngRow) & _ " " & _ Me.cboEmployees.Column(2, lngRow) & _ " (Employee ID " & _ Me.cboEmployees.Column(0, lngRow) & ")" & _ vbCrLf Next lngRow MsgBox strMessage, vbOKOnly + vbInformation
Can I have the contents of one ComboBox change depending on the value selected in another ComboBox?
Assuming that your ComboBoxes are based on the contents of tables in your application (as opposed to being a list of values that you provide when creating the ComboBox), this is fairly easy to do.
One approach is to base the ComboBox on a parameter query. Parameter queries allow you to change the criteria used to limit what row(s) are returned dynamically. The simplest way is to type a prompt enclosed in square brackets in the Criteria cell of the query (as illustrated in Figure 2).
Figure 2
When the query is run, a dialog box will appear, prompting the user to enter a value, as illustrated in Figure 3.
Figure 3
However, it’s also possible to have the query determine what value(s) to use from a control on an existing form by replacing the preceding prompt with an expression referring to the control: Forms!NameOfForm!NameOfControl.
For example, if the name of the form is frmCascadingCombos, and the name of the control on that form is cboCity, you’d replace [Type the last name:] in the previous example with [Forms]![frmCascadingCombos]![cboCity] (illustrated in Figure 4).
Figure 4
There are two issues that you need to be aware of when you use this feature. First, the form must be open when the query is run. If it isn’t, you’ll get a prompt, as illustrated in Figure 5. Second, if you add too many form references (and what “too many” is will vary), you’ll get the error message “Query is too complex.”
Figure 5
You might think that if you use a query based on a value in a ComboBox as the row source for a second ComboBox on your form, you’ll be able to accomplish the desired goal. However, by default a ComboBox only gets its data once, when the form is opened. So, when you change the selected value in the first ComboBox, the second ComboBox isn’t updated. To have the second ComboBox react to changes in the first one, you must use the Requery method to update the underlying data. This code does the trick:
Private Sub cboCity_AfterUpdate() Me.cboEmployee.Requery End Sub
There are a couple of other points to mention. If you’d like your second ComboBox to display records even if nothing’s been selected in the first ComboBox, you can’t simply put the reference to the form control as illustrated earlier. This is because an empty ComboBox has a value of Null, so the underlying query needs to be able to handle a Null value as well. To handle this you must add a new column to the query, and set its criteria to “Is Null.” It’s critical that the “Is Null” is not on the same row as the parameter criteria. Figure 6 shows the right way to set up your query.
Figure 6
It’s not actually necessary to use a parameter query as outlined previously. In the first ComboBox’s AfterUpdate event, you could just update the RowSource property for the second ComboBox.
If you look in the accompanying sample database, you’ll see that the row source for the second ComboBox is set to this SQL statement that’s tied to another ComboBox:
SELECT EmployeeID, [TitleOfCourtesy] & " " & [FirstName] & " " & [LastName] FROM Employees WHERE (City = Forms!frmCascadingCombos!cboCity) OR (Forms!frmCascadingCombos!cboCity IS NULL) ORDER BY [LastName], [FirstName]
Rather than doing a requery in the AfterUpdate event (as I did earlier), you can use this code to rewrite the RowSource:
Private Sub cboCity_AfterUpdate() Dim strSQL As String strSQL = " SELECT EmployeeID, " & _ "[TitleOfCourtesy] & " " & _ "[FirstName] & " " & [LastName] & _ "FROM Employees " & _ "WHERE City = '" & _ Me!cboCity & "'" & _ "ORDER BY [LastName], [FirstName]" Me.cboEmployee.RowSourceType = "Table/Query" Me.cboEmployee.RowSource = strSQL End Sub
It’s not necessary to invoke the Requery method in this case: Changing the row source of the ComboBox automatically performs a requery.
I’m using the technique you showed to have the contents of one ComboBox change depending on the value selected in another ComboBox on a continuous form, and it’s not working. How come?
Unfortunately, the methods I showed earlier don’t work with a form in datasheet view. This is because Access only maintains a single recordset for all of the ComboBoxes on the form, rather than a separate recordset for each ComboBox. If the underlying recordset isn’t relevant to the specific record being looked at, the ComboBox won’t contain the necessary record and you’ll get blanks displayed instead of values (see Figure 7 and Figure 8).
Figure 7
Figure 8
Fortunately, there’s a way around this, and it’s fairly simple to implement provided that you recognize that there are two reasons for using a ComboBox. One reason, of course, is to allow the user to select from a list that’s customized based on other selections made. If you look in the accompanying database, you’ll see that even though not all of the values are being shown in the form, it’s still possible to use the ComboBox to select a value for a particular row. The other reason for using a ComboBox is to handle a foreign key in the table that points to the desired entry in some other table. The ComboBox allows you to translate that foreign key into something a little more meaningful–typically the desired entry in the other table. This is the function that’s failing in the continuous view.
You can get around this problem by ensuring that the underlying recordset of the form contains not only the foreign key value, but also the desired entry from the other table. You do this by creating a query that joins together the underlying table and whatever other table(s) contain the desired data and using that query as the form’s recordset.
Once you’ve got the description available to you in the form, you need to display it. My solution is to create a bound TextBox that displays the desired entry, and put the TextBox right on top of the ComboBox on the form. I make the TextBox marginally narrower than the ComboBox (I find that a difference of 250 twips, which is about 0.17″ or 0.44 cm, works well). Finally, to ensure that the ComboBox is based on the correct recordset, I put some code in the TextBox GotFocus event so that when the user enters the TextBox, the ComboBox is refreshed and the focus is switched to the ComboBox. Figure 9 illustrates what the form looks like.
Figure 9
In a form in the accompanying database, I have a TextBox named txtProductName and a ComboBox named cboProducts. In the form’s Load event, I’ve added code to ensure that txtProductName is properly positioned over top cboProducts:
Private Sub Form_Load() Me.txtProductName.Left = Me.cboProducts.Left Me.txtProductName.Width = _ Me.cboProducts.Width - 250 End Sub
It’s not really necessary to handle this positioning through code: You can simply permanently place TextBox txtProductName in the correct location. However, I prefer having the TextBox somewhere else on the form, so that it’s easier to see when I’m working on the form in design view. As a result, I need the code to take care of lining things up properly.
Since the GotFocus event of cboProducts already takes care of refreshing the ComboBox, all that’s left is to have the GotFocus event of txtProductName shift the focus to the ComboBox:
Private Sub cboProducts_GotFocus() Me.cboProducts.Requery End Sub Private Sub txtProductName_GotFocus() Me.cboProducts.SetFocus End Sub
As I said, the solution is very simple (only four lines of code), but it does the trick.
