Limit to List ComboBox.
Combo Boxes in tables and forms are used to quickly insert frequently used values into data fields. The source data for a ComboBox can come from a table, a query, or a value list. To use it, the User clicks the drop-down arrow to display the available options and selects the required value. Alternatively, the user can type values directly into the ComboBox control.
However, one important property setting determines how the combo box behaves:
-
The first setting restricts the user to selecting only from the existing list and prevents invalid values in the target field.
Limit to List = Yes
On Not in List = [Event Procedure]
When the Limit to List property is set to Yes, you can only select or type values that already exist in the combo box list. Any manually entered values that are not in the list will be rejected. To use a new value, it must first be added to the source table (or query/value list) that supplies data to the combo box.
Example:
Suppose you have a table containing a list of fruits and have only two items: Apple and Cherry. This list is used as the source for a combo box on a Sales Form. If the Limit to List property is set to Yes, you cannot type Orange directly into the field. Instead, you must first add Orange to the fruit table; only then will it appear as a valid option in the combo box.
The On-Not-in-List Event.
When the On Not in List Property is set to an Event Procedure, it runs when the user enters a new value (Orange) manually into the ComboBox Control Source. We can write code in the Event Procedure to add the new value to the ComboBox Source List (after the User approves) and update the ComboBox on the Form.
This method saves the time that would otherwise be spent opening the ComboBox source Table and manually adding new items. Besides that, the values added directly to the source table do not refresh the ComboBox contents, and the new addition will not appear in the list.
Let us try this using the above example items as Source Data.
Combo Box Row Source Table.
Create a new Table with a single field named Fruit and select the Data Type Text.
Save the Table Structure and name it Fruitlist.
Open the Table in Datasheet View and key in Apple and Cherry as two records.
Close and save the Table with the records.
Create another table with the following Structure:
Table Structure Field Name Data Type Size ID AutoNumber Description Text 50 Quantity Numeric Long Integer UnitPrice Numeric Double Before saving the Structure, click on the second Field Data Type (Text) Column to select it.
Click on the Lookup Tab on the Property Sheet below.
Combo Box Property Settings.
Select the Display Control Property and select ComboBox from the drop-down control.
The Row Source Type Property will be Table/Query; if it is not, then select it from the drop-down control.
Click on the Row Source Property drop-down control and select the Table Fruit list from the displayed list of Tables.
Change the Column Width Property and List Width Property Values to 1".
Change the Limit to List Property Value to Yes.
Save the Table Structure as Sales.
Open the Table in Datasheet View and add a new record with Apple, 100, and 1.5 in the Description, Quantity, and UnitPrice Fields, respectively.
Close and save the Table with the record.
Select the Sales Table, and select Form from the Insert Menu.
Create a Form using the Form Wizard in Column Format and save it as Sales.
Testing Settings.
Open the Sales Form in the normal view.
Since we have added the Combo Box to the Table Structure, it already appears on the form.
Press Ctrl++ (or click on the New Record control on the Record Navigation control) to add a new blank record on the Form.
Click the drop-down control of the ComboBox, and you will find the list of fruits: Apple and Cherry there.
Try to key in Orange in the Description field and press Enter.
You will be greeted with the following error message:
If you want to enter the value Orange on the Form, you must add that item to the Fruit List source Table first.
To add a new item, open the Fruit List table, enter Orange as a new record, and then close the table.
However, this action will not automatically refresh the ComboBox contents to include Orange. To see the updated value, you must either close and reopen the Sales form or add a CommandButton to the Form and write code that requeries the ComboBox contents.
What we did manually in response to the above error message can be automated by writing a VBA Subroutine that runs through the On Not In List Event Procedure. You don't need to close and reopen the Form to refresh the ComboBox contents either.
Add New Item through VBA.
Open the Sales Form in Design View.
Click on the Description Field to select the Combo Box control.
Display the Property Sheet (View -> Properties).
Find and click on the On Not in List Property.
Select Event Procedure from the drop-down list.
Click the Build button (...) To open the VBA Module.
Copy and paste the following Code into the Module, overwriting the top and bottom Procedure lines already appearing in the Module:
Private Sub Description_NotInList(NewData As String, Response As Integer) Dim strmsg As String, rst As Recordset, db As Database If Response Then strmsg = "Entered Item not in List!" & vbCr & vbCr & "Add to List...?" If MsgBox(strmsg, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in List") = vbYes Then Set db = CurrentDb Set rst = db.OpenRecordset("FruitList", dbOpenDynaset) rst.AddNew rst![Fruit] = NewData rst.Update rst.Close Me.Description.Undo Me.Description.Requery Me![Description] = NewData Me.Refresh End If Response = 0 End If End SubSave and Close the Sales Form.
Trial Run Program.
Open it in a normal view.
Now, type the name of any fruit not available in the Combo Box list (say, Peach) in the Description field.
You will be greeted with the following Message Box:
Click the CommandButton with the Label Yes to add the new item keyed in the Description Field to the Fruit List Table and refresh the Combo Box List.
Now, click on the drop-down control of the ComboBox, and you can see that the new item is added to the list and accepted in the Description Field as well.
