Combo Boxes
The combo box is a potent control that can combine the processes to search, select, and populate data into fields [or other objects], as well as limit the values that a user may enter. Many nuances affect how combo boxes work; this section addresses some of the more common ones. It also covers some new features, including two new properties: Allow Value List Edits and List Items Edit Form.
The combo box and list box have a lot of the same properties and functionality. The primary difference is that the contents of the list box are always displayed on the form, so the list box takes more space. List box values are also limited to the list, which can be updated programmatically by adding values to the row source, but not directly by the user. Combo boxes, on the other hand, generally use the space of a normal text box and employ a drop-down list to display the number of rows specified in the properties. The row source can be set to be updateable or to allow only existing values.
Before continuing, let's clarify the difference between control source and row source. The control source is specified if the value selected in the combo box will automatically [rather than programmatically] be stored in a field. The last question in the Combo Box Wizard, for instance, is about remembering or storing the selected value. If you choose to store the selected value in a field, that field becomes the control source. The row source provides the values that are displayed and used by the combo box. It can be a table, a query, a value list, or a field list. The row source value can even be an empty string when the form opens and then be set programmatically. For the most part, the row source consists of multiple columns with only one column visible to the user. The other columns can be used to populate other fields or as the link to other record sources. For example, one column could be the field that links the current form to a subform. Again, referring to the wizard provides a quick demonstration of some of the properties related to the row source. When you tell the Combo Box Wizard, for instance, that the combo box will look up values in a table or query, you are specifying the row source. The wizard then goes through the process of selecting the fields for the columns and allowing you to specify the column widths. The wizard automatically adds the primary key if one is available.
The other main element that you'll work with is controlling the values that the user can use or add. But first, you'll tackle an easy example of using a combo box to display existing data.
Combo Box as a Look-Up
An excellent use for the combo box control is to display descriptive information for the user even though the data that's actually stored in the database is the value of some sort of key [primary or foreign]. For example, you may have a form in which users allocate sales and you want the users to identify a department. If that form [or even just the combo box] is bound to a table that stores the Department ID rather than the name of the department, you don't want to force the user to remember or figure out which Department ID to enter. Instead, you can use a combo box to display a list of departments [field name Department] and let the form manage how the Department ID is stored. The following table lists the combo box settings for this scenario. You will also find this in frmContactsComboLookUp in this chapter's database file named ComboBox.accdb.
Property | Value |
Control Source | Department [this is from the table Contacts] |
Row Source | Select DepartmentID and Department from tblDepartment |
Row Source Type | Table/Query |
Bound Column | 1 |
Limit To List | Yes |
Property | Value |
Allow Value List Edits | No |
List Items Edit Form | No value [leave blank] |
Column Count | 2 |
Column Width | 0";2" |
Two of the properties that are crucial to selecting the value that will be stored in the database are Bound Column and Column Width. Setting Bound Column to 1 means the data for the field that will be stored in the database is in the first column of the Row Source. Setting the Column Width of the bound column to 0 means that the bound column will not be displayed in the combo box.
If you are familiar with coding combo boxes, you probably noticed the new Access 2007 Combo Box properties Allow Value List Edits and List Items Edit Form. These new properties enable you to let users maintain their own set of valid values for the field without requiring you to write code. The next two sections examine those properties.
As in prior versions of Access, if the Limit To List property is set to No, the user is allowed to type whatever he wants as many times as he wants. The combo box only helps the user select previously defined values without restricting him to only those values, and it allows users to enter the same value multiple times and with myriad variations. When Limit To List is set to Yes, the user can select only the values in the list. However, if the user enters a value that isn't in the list, it can trigger a response depending on whether you've captured bad data entry using the On Not In List event and/or based on the settings for the new Allow Value List Edits property along with the List Items Edit Form property. All this is by way of saying you should be aware that the On Not In List event occurs before the settings of the new properties are even considered.
It may seem as though we are hammering on just a few properties, but slight variations in how they are combined can have a big impact on results.
Allow Value List Edits
The Allow Value List Edits property provides a simple way for you to allow the user to change the contents of the list without requiring the user to go into Design view to change the row source or you [the developer] to write code to change the row source. Keep in mind that this property is intended for combo boxes where the Row Source Type property is set to Value List meaning that it is not designed to work with other row source types, so don't be tempted to misapply this feature.
If Allow Value List Edits is set to No and the user enters a value that isn't in the list, the user will see the standard Access message [see Figure 10-9] advising him to select an item from the list.
Figure 10-9
Figure 10-9
If Allow Value List Edits is set to Yes and the user enters a value that isn't in the list, the user will see a message box that asks if he wants to edit the items in the list, as shown in Figure 10-10a. You'll also notice the extra question at the bottom of the message box, "Was this information helpful?" That message will appear if the user has enabled "Help Improve Access." Responding to this query will clear that message without closing the message box, as shown in Figure 10-10b.
If the user chooses Yes, to edit the list, the default Access Edit Items List form displays [see Figure 10-11]. This default form is clearly designed to capture only a simple list of values, and it is intended to be used only when the combo box Row Source Type is set to Value List, Inherit Value List is set to Yes, and both the the Bound Column and Column Count are set to 1. Keep in mind that this form provides no means for you to control the values that the user might enter, so terms may be entered multiple times with different spellings. In Figure 10-11, you can see that Dance has been added to the list from the combo box labeled "Value List With Edit:" even though "Dancing" was already in the list. If you want more control over what the user enters for the list or if you want to provide a multicolumn list, you should set the Row Source Type to be Table/Query and use the List Items Edit Form property to specify a custom form. We will discuss that approach shortly.
One thing to note about using this approach to allow value list edits is that the Row Source property of the combo box is actually updated, which in turn causes Access to respond as though the design of the form has changed. Because of this, when the user closes the form that contains the [updated] combo box, Access will display a message box to ask if the user wants to ".. .save changes to the design of the form [form name]". This is not a good message for users to see, so you may want to use the DoCmd.Save acForm, Me.Name statement to save the form in the Form_AfterUpdate event. [This is obviously not a desired scenario, and you can hope for a resolution in an early Access update.]
When allowing value list edits by using the default Access Edit Items List form, it is critical that the Row Source Type is Value List and Inherit Value List is set to Yes. This allows Access to properly handle the update. With other property configurations, users may get a warning and be asked if they want to save changes to the design of the form. After all, a change to a value list behind a control is a change to the form's design.
List Items Edit Form
As mentioned earlier, the big advantages of the new Allow Value List Edits property is that you don't have to write code in the On Notln List event to capture the new value, and it provides a method that allows the user to include the values he wants in the list. But please remember [and this is worth repeating] that the new Allow Value List Edits property is intended only for combo boxes and list boxes with a Row Source Type of Value List. If you are using the Row Source Type of Table/Query, you can use the new List Items Edit Form property to display an alternate form that allows the user to edit the list.
First you must first create a form that will maintain the data that is displayed from the row source of the combo box. Then you set the List Items Edit Form property to name of that form. You could get a little fancy with your form by programmatically taking the invalid value that the user entered in the combo box and loading it into the appropriate field on your edit form. That kind of defeats the concept of using this new feature to avoid writing code, but then this book is about code and creating solutions that save time and prevent errors. Coming up is one example of using code to optimize use of a couple of the new "no code required" features.
Because users can use the Navigation pane, it might be beneficial to programmatically restrict the capability to open your edit forms, such as by using the Is Loaded event [see discussion later in this chapter]. After all, you don't want users to get an error message if they open a form from the Navigation pane. [In the database for this section, EditValueList.accdb, the form frmMayorUpdate includes a method to prevent the form from being opened from the Navigation pane.]
This example uses a form called frmContactMayor with combo box cboCity, which uses a look-up table to capture the city for the contact, storing the data in the field named City in tblContacts. For this exercise, assume that the application needs to capture the mayor of every city. Basically, this means that if a user enters a new city, you want the application, through a form, to require him to enter the name of that city's mayor. To accomplish this, the example uses a table of mayors called tblMayors and a frmMayorUpdate form to update that table. You also want to think about creating an index on city/state and requiring that to have a unique value.
This example is provided in the chapter download file, EditValueList.accdb. To get the process started, you'll create [or modify] the combo box, cboCity, to have the key property values shown in the following table.
Property | Value |
Control Source | City |
Row Source | tblMayors |
Row Source Type | Table/Query |
Bound Column | 2 |
Limit To List | Yes |
Allow Value List Edits | Yes |
List Items Edit Form | frmMayorUpdate |
Column Count | 2 |
Column Widths | 0";2" |
To ensure that the mayor's name is provided, the form should use one of the other techniques you've learned in this book. For instance, you could use the Form_BeforeUpdate event to see if the mayor's name field has a value. The example uses Form_Open to check for the value.
Additionally, set the frmMayorUpdate form Pop Up property to Yes, and set the Modal property to Yes, and set Cycle to Current Record [found on the Other tab of the property sheet]. You do this so that the Form_Open code in the form frmMayorUpdate always executes. One reason for making this a modal form is to force the user to close the form before he can enter additional data in the form frmContactMayor [or before he can work with other objects, for that matter]. If the user is allowed to leave frmMayorUpdate open while continuing to enter data on frmContactMayor, the next invalid entry in cboCity won't fire the Form_Open event in frmMayorUpdate.
At this point, you're about ready to put the following code in the Open event for frmMayorUpdate. First you establish a constant to be used for the "calling form." That makes this snippet more portable. Instead of replacing the name of the calling form throughout the code, you merely change the value for the constant, cFormUsage.
Const cFormUsage = "frmContactMayor" | |
Private Sub Form_Open[Cancel As Integer] | |
Dim strText As String | |
Dim rs As Recordset | |
' Don't let this form | be opened from the Navigator |
If Not CurrentProject. | .AllForms[cFormUsage].IsLoaded Then |
MsgBox "This form | cannot be opened from the Navigation Pane.", _ |
vblnformation | + vbOKOnly, "Invalid form usage" |
Cancel = True | |
Exit Sub | |
End If | |
strText = Forms[cFormUsage]!cboCity.Text | |
If strText = "" Then | |
' If the City is empty, the user may have opened the form from the navigator |
' while the other form is opened [thus it passed the above test] MsgBox "This form is intended to add Cities for the '" & i Forms[cFormUsage].Caption & "' form.", _
vblnformation + vbOKOnly, "Invalid form usage" Cancel = True Exit Sub End If
' If you use the following syntax to insert the new value, ' make sure that the user hasn't entered an apostrophy ['] in his text. ' Of course there are many ways to add the record DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tblMayors [City] VALUES ['" & strText & "']" Me.Requery
DoCmd.SetWarnings True
' Now point to the row just added and set the filter so the user can't scroll Set rs = Me.RecordsetClone rs.FindFirst "City = '" & strText & "'" If Not rs.EOF Then
Me.Bookmark = rs.Bookmark Me.Filter = "[ID] = " & Me.ID Me.FilterOn = True End If
Me.Mayor.SetFocus End Sub
After the user indicates that she wants to edit the items in the list, the process will open frmMayorUpdate because it is the form specified as the value for the List Items Edit Form. The Form_Open event looks at the cboCity field in frmContactMayor and stores the text of that field in strText. The code then inserts the City that doesn't have a value associated with the Mayor's name into tblMayors. With the record inserted, the code makes a copy of the recordset of the form, uses the FindFirst method to locate that record, and moves to it using the Bookmark property. Finally it sets the focus on the Mayor name field, so that it is easy for the user to enter the required data.
Of course, you could accomplish that with a lot less code, as shown in the following snippet: Private Sub Form_Open[Cancel As Integer]
DoCmd.RunCommand acCmdRecordsGoToNew ' insert a new record Me.City = Forms["frmContactMayor"]!cboCity.Text Me.txtMayor.SetFocus End Sub
This code simply goes to a new record on the form and copies the text from cboCity to the City field. An advantage of this method is that you can then define the Mayor name field in tblMayors as required and let Access determine if the data entered by the user is valid. Then you don't need the Form_BeforeUpdate check to make sure the user entered something in the Mayor name field.
Now that you've worked through this example, take a moment to think about related business rules. This scenario has some obvious issues that would have to be addressed, such as the potential to have multiple cities with the same name, the opportunity for people to use different spellings of the same city, and the need to be able to rename a city or mayor. But it does provide a fairly elegant process to allow users to add values.
If you prefer to write code [and most developers do], there is another way to allow the users to update the list of valid values available in a combo box. It provides the means for you to validate the value and saves you from having to create another form. It's the Not In List event, which you look at next.
The Not In List event is triggered when the Limit To List property is set to Yes and the user enters data that is not in the list. It occurs independently of the settings for Allow Value List Edits and List Items Edit Form properties, so you can use it to control how your application responds when invalid data is entered in a combo box.
Because combo boxes are usually based on a look-up table, the following example offers the user a chance to add a value that is not in the list. To provide a friendlier dialogue with the user, it also demonstrates how to create a custom message box such as the one shown in Figure 10-12. As you can see, the user tried to use "Entertainment" as the main category. Because it isn't in the list, he's asked if he'd like to add it.
As a refresher, you can use the Limit To List property to control what happens when a user enters data into a control. If the value is set to No, it places no restrictions on what is entered. If the value is set to Yes, several things can be triggered, including the following:
The user is limited to what is in the list.
If other data is entered, users are asked to choose from the list.
Entering other data can trigger the NotlnList event.
Because combo boxes are typically based on a look-up table, the following example provides code to offer the user a chance to add a value that is not in the list. It also creates a custom message box.
Private Sub cboMainCategory_NotInList[NewData As String, Response As Integer] On Error GoTo Error_Handler Dim intAnswer as Integer intAnswer = MsgBox["""" & NewData & """ is not an approved category. " & vbcrlf
& "Do you want to add it now?", | ||
vbYesNo + vbQuestion, "Invalid | Category"] | |
Select Case intAnswer | ||
Case vbYes | ||
Docmd.SetWarnings False | ||
DoCmd.RunSQL "INSERT INTO tlkpCategoryNotlnList [Category] | " & _ | |
"Select """ & NewData & "' | 1 n.n | |
DoCmd.SetWarnings True | ||
Response = acDataErrAdded | ||
Case vbNo | ||
Msgbox "Please select an item | from the list.", _ | |
vbExclamation + vbOkOnly, | "Invalid Entry" | |
Response = acDataErrContinue | ||
End Select | ||
Exit_Procedure: | ||
DoCmd.SetWarnings True | ||
Exit Sub | ||
Error_Handler: | ||
MsgBox Err.Number & ", " & Err.Description | ||
Resume Exit_Procedure | ||
Resume | ||
End Sub |
The NotlnList event comes with two parameters:
NewData As String: Holds the value that is not found in your list.
Response As Integer: Provides three intrinsic constants:
acDataErrContinue: Suppresses the standard error message.
acDataErrAdded: Suppresses the standard error message, and refreshes the entries in the combo box.
acDataErrDisplay: Displays the standard error message.
The NotlnList event property is literally asking, "This value is not in your table, so what should I do?" The example starts by telling Access to display a message box notifying the user that the name is not in the list. You use the vbYesNo constant to provide buttons to get the user's answer and the vbQuestion constant to display the Question icon in the message box.
The user can choose Yes or No. If Yes [vbYes] is selected, the code adds the new value using the INSERT INTO SQL command that appends NewData to the specified lookup table. Because it is an append query, the standard append query warnings are triggered. Docmd.SetWarnings False turns off these warnings, and later you use Docmd.SetWarnings True to turn them back on.
Next, you need to set the Response, one of the intrinsic constants for the message box. If the user has responded Yes, acDataErrAdded automatically refreshes the combo box. If the user chooses No, Response is set to equal acDataContinue, which allows the user to continue without adding the item to the list. Either of those constants [responses] will suppress the default Access message. The point here is that by creating custom messages boxes you proivde a more informative and professional user interface.
Field List
One of the values for Row Source Type that hasn't been mentioned yet is Field List. Setting the Row Source Type to Field List makes the values displayed in the combo box be the actual names of the fields [rather than the data contained in the field] found in the table or query specified in the row source. The Field List value is most commonly used when you're going to allow the user to select field names to build a custom filter. Of course, Access 2007 already provides some powerful new ways to filter a form, so they may be a better alternative.
But if your form has several fields, a drop-down field finder might help users quickly get to the fields that they are looking for. To create such a tool, first add an unbound combo box to a form. In the combo box properties, set the Row Source Type to Field List. Then, in Row Source, enter the table or query that is used for your form. In the AfterUpdate event of the combo box type the following code:
Private Sub cboSearchFields_AfterUpdate[] Dim strFieldChoice as String strFieldChoice = Me.cboSearchFields.Value
DoCmd.GotoControl strFieldChoice
End Sub
The user can select the field from the combo box and be taken straight to that field. This code assumes that the Name property of the controls on your form are the same as the names of the fields in your Row Source. However, that probably isn't the case because you've implemented good naming conventions so the controls have prefixes such as txt; in which case you'll have to interpret the names. The following Select Case statement is an example of how to do that:
Private Sub cboSearchFields_AfterUpdate[] Dim strFieldChoice as String strFieldChoice = Me.cboSearchFields.Value
Select Case strFieldChoice Case "CustomerFirstName"
strFieldChoice = "txtCustomerFName" Case "CustomerLastName"
strFieldChoice = "txtCustomerLName" End Select
DoCmd.GotoControl strFieldChoice End sub
The user will see the easy-to-interpret term CustomerFirstName but the code in the Select Case statement switches the value to equal the actual Name property of the control: txtCustomerFName.
Continue reading here: Synchronizing Two Combo Boxes Using After Update
Was this article helpful?
+1 0