Editable, Appendable ComboBox(?) in MS Access

Question

My Goal:

A form field (in MS Access) that is has some drop down choices. If the wanted value isn't in the lookup table, the user should be able to add it by typing it in.

Let's suppose the lookup table has rows: A, B, C, D. The user wants "E" which doesn't exist yet. Ideally they'd "override" and type in "E", which would then be added to the lookup table for future entries.

My google-fu has failed on this. Is there a term for this that I should be using? What are some good approaches? (I've been playing with combo-box and its wizard so far).

Thank you for any suggestions!

Answer 1

Aha, solved my own here:

http://allenbrowne.com/ser-27-01.html

Access 2007

To use the new properties in Access 2007:

1. Open your form in design view.
2. Right-click the combo, and choose Properties.
3. On the Data tab of the Properties box, set Allow Value List Edits to Yes, and List Items Edit Form to the name of the form to use for adding items to the list.

When you are using this form, you can now right-click the combo, and choose Edit List Items.

There is also advice for older versions of Access.

Answer 2

You can try the following code:

Private Sub Combo33_NotInList(NewData As String, Response As Integer)

   Dim strSql     As String
   If MsgBox(NewData & " not in list, add?", _
      vbYesNo + vbQuestion) = vbYes Then
      strSql = "insert into tblStudents (name) values(" & NewData & ")"
      CurrentDb.Execute strSql
      Response = acDataErrAdded
   End If

End Sub

Note I used a table name of Students, and field name of Sname. So, just change the table name, and the field to whatever you used.