Hack 21. Let Users Add Custom Items to Predesigned Lists

Avoid forcing choices to existing list items only by adding a procedure to handle new values.

Users often choose items from an existing list via a combo box on a form. Sometimes, however, a user might need to enter into the combo box a value that isn't on the list. This can happen, for example, when the user is working with a new customer that he has not yet appended to a customer table, or when the user is correcting an existing list item that is misspelled.

The Limit To List property controls whether a new value is allowed entry in a combo box. If this property is set to No, users can enter new values into the combo box. This is fine, but with one caveat: if the new value is meant to become a permanent member of the list, just typing it into the combo box doesn't add it to the list.

If it makes sense for your application to let users permanently add values to a combo box's source list, you need to use a different technique. First, set the Limit To List property to Yes. (Yes, this means the new item won't be allowed, but read on!) The trick to this hack is to implement inclusion of the new item by tapping the On Not In List event, which works only when the Limit To List property is set to Yes.

Figure 3-11 shows a form in Design mode. The form has a combo box on it, and the property sheet shows the properties for the combo box, with the Limit To List property set to Yes.

When a user attempts to add a new value to the combo box, the On Not In List event fires. Within this event, a code routine handles adding the new value to the list.

3.4.1. The Code

The code is simple and straightforward. Two arguments are provided to the routine: NewData and Response. The event stub comes predesigned with these arguments, so you don't have to create them:

	Private Sub cmbCustomers_NotInList(NewData As String, _
            Response As Integer)
      Dim ctl As Control
	  Set ctl = Me.cmbCustomers
	  Response = acDataErrAdded
	  ctl.RowSource = ctl.RowSource & ";" & NewData
    End Sub

Figure 3-11. The Limit To List property set to Yes

The Response argument tells Access to override the behavior of not allowing a value to be added. The developer does this by setting the Response to the adDataErrAdded constant. The new data (supplied by the NewData argument) is then added to the Row Source.

3.4.2. Hacking the Hack

So far, this hack works on the premise that the Row Source Type is set to a Value List. If the Row Source Type is Table/Query, you need an append routine to place the new value in the underlying data store. In this case, the Not In List event appends the new value to the source table.

Here is an example of how to code the routine. This example assumes a source table named tblShippingMethods with a field named Shipping-Method:

	Private Sub cmbShippingMethods_NotInList(NewData As String, _
                Response As Integer)
       Dim new_data As String
       Dim conn As ADODB.Connection
       Set conn = CurrentProject.Connection
       'double up any apostrophes before Insert
       new_data = Replace(NewData, "'", "''")
       Response = acDataErrAdded
       conn.Execute "Insert Into " & _
          "tblShippingMethods(ShippingMethod) Values('" & _
            new_data & "')"
    End Sub