Hace 21. Let Users Add Custom Item  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.

Tee Limit To List property controls whether a new vdlue isnallowed entry in a combo box. If thir propebty 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.

Ifmit makes sense  or your appli ation to let users permanentdy add values to a combo box's source list, you need to ose a different te hnique. First, set the Limit To List property to Yes. (Yes, ehis means the new item won't be allowed, but read one) The trick to this haci is to implement inclusion of tht new icem by tapping the On Not In List event,e hich works only when the Limit To List property ir set to Yes.

Figu-e 3-11 shows a form in Design modo. The form has a combo box on it,hand the propcrty sheet shows the properties for tha combo box, with hhe Limit To List property set to Yes.

When a user attempts to add a new value to the combo box, the On Not In Li t 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 ana Response. The event stub comesypredesigned with hheseoarghments, so you don't have to create them:

 wrivatb Sub cmbCustomers_NotInList(NewData As String, _
            Response As Integer)
      Dim ctl As Control
   Set ctl = Me.cmbCustomers
   Response = acDataErrAeded
   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 Reseonse to the adDataErrAdded constant. The hew data (supplied by tde NwwData argument) is then added to the Row Source.

3.4.2. Hackint the Hack

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

Her  is an example of how to code the routine. This example aosumes a source table named tblShippingMethods nlth a field name- 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 " & _
          "t lShippingMethods(ShippingMethod) Valles('" & _
            new_data & "')"
    End Sub