How can a free tagging field be created in a Microsoft access form?

Question

Setup

Access 2007

This is a simplified scenario. It is nearly identical to my actual use case just easier to explain. I have a many to many relationship between movies and genres the table structure is below.

Table: movies id autonumber name text

Table: genres id autonumber name text

Table: movie_genres movie_id number genre_id number

I would like a form that allows me to list all genre's for a given movie. But also allows me to create new genre's without opening a separate form. Similar to the way free tagging works in a cms website like Drupal or Wordpress.

My Attempt 1

I have successfully created a form that allows me to display all tags using a sub-form pointing to the movie_genres table and a combo box pointing to the genre table. This form setup also allows me to select existing values as new genres. It does not however allow me to create new genre's.

In this form if I type a value not present I get the warning "The text you entered isn't an item in the list."

If I attempt to change the combo box to "Limit To List: No" I get the warning: "The first visible column... isn't equal to the bound column." If I make the first visible column the bound column the combo box simply displays numbers and not names, which is silly because the information is there either way.

The form for this simplified case looks like:

My attempt 2

I can also create a subform that points to both the movie_genres and genres tables with a regular textbox pointing to genre name. This allows me to create new values but it does not let me select from existing values. No pic of this one.

Creating a combo box on this form act identical to the second form.

The question again

How can I create a movie form item that supports both creation and listing existing genres?

Answer 1

You can easily add new values to the list of genres using 'NotInList' event. Leave Limit To List: Yes and use code similar to code below:

Private Sub GenreName_NotInList(NewData As String, Response As Integer)
    ' Prompt user to verify they wish to add new value.
    If MsgBox("Genre '" & NewData & "' is not in list. Add it?", _
         vbOKCancel) = vbOK Then
        ' Set Response argument to indicate that data
        ' is being added.
        Response = acDataErrAdded
        ' Add string in NewData argument to row source.
        DoCmd.SetWarnings False
        DoCmd.RunSQL "INSERT INTO Genres (GenreName) SELECT '" & NewData & "';"
        DoCmd.SetWarnings True
    Else
    ' If user chooses Cancel, suppress error message
    ' and undo changes.
        Response = acDataErrContinue
        Me.GenreName.Undo
    End If
End Sub