how to populate a table of lookups using "Not in list"

Question

Table 1: My general information table

• Organization: A lookup/relationship field that defaults to "N/A" but pulls values from...

Table 2: Organization List

• No ID field, just the names of the organizations in the order which they were added

My data entry form has a combo box for organization and I would like it to update when I add an organization that hasn't been added before. I know I am supposed to use the "Not in List" event, but I don't know how to update the Organization list using this event. How do I do this?

Answer 1

Make sure the Limit To List property of the combo-box is set to Yes.

Add a On Not In List event to insert the value to your source table when a new value appears:

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

    With DoCmd
        .SetWarnings False
        .RunSQL "INSERT INTO [Organization List](Organizations) VALUES ('" & NewData & "')"
        Response = acDataErrAdded
        .SetWarnings True
    End With

End Sub

Edit... nearly forgot... before I answer, what have you tried? :)

Edit 2... the example given is for a string value. Remove the ' from either side of New Data if it's a numeric value (but probably not if it's an organisation name).

Edit 3... The INSERT SQL is just one way of putting data into a table. You may prefer the RecordSet.Add and .Update methods.