Troubleshoot NotInList Event Procedure

[skyrise]

I have a small amount of experience with Access. A researcher has asked me to build a database to hold data pulled from published studies: Publication Name, Location of Study, Particpant Data, etc. The researcher wants several List fields in the the database to be updatable by the users. For example, instead of loading many possible Publications Names up front into a large Combo Box, allow an exact list of publications to be created as the users come across them. I can't get the Event Procedure to work and don't know what I need to do to fix it. This is the main thing that is holding up the build of the database. The database is needed within the next few days. PLEASE HELP!

Publication Name can be used as the example (I'm assuming the other fields can be set up the same way).

I've created a Lookup Table called "lkpPublicationName" to hold the list of publication names.
The table fields:
PublicationNameID - Primary Key autonumber.
PublicationName - Text field set to Allow Zero Length = Yes, Indexed = Yes (No Duplicates)

I'm using a form called "PublicationData" to created records. Fields such as "Publication Name" are being added in the Form Design view as Bound Combo Boxes that use a SELECT Query to pull the user selected information from it's Lookup Table and place it in the field on the form.

The PublicationData Table has primarily two fields of it's own:
PublicationRecordID - Primary Key autonumber.
PublicationNumber - Text field to enter the code that ID's the publication.

All other fields are represented in the table by the Primary Key field from their corresponding LookupTable (to link to the combo box that will be added on the form). These fields are set as Numbers with Properties of Field Size = Long Integer, Indexed = Yes (Duplicates ok). Some of these fields are:

PublicationNameID
PubYearID
StudyLocationID
Etc.

Here is the SELECT Query for Publication Name:

SELECT [lkpPublicationName].[PublicationNameID], [lkpPublicationName].[PublicationName] FROM lkpJournalName;


The names for the Combo Boxes appears as the name that Access automatically assigns.
The name for the Publication Name Combo Box is Combo13.

Here is the NotInList Event Procedure for Publication Name:

Private Sub Combo13_NotInList(NewData As String, Response As Integer)
On Error GoTo Combo13_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The publication name " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Add Publication Name?")
If intAnswer = vbYes Then
strSQL = "INSERT INTO lkpPublicationName ([PublicationName]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new publication name has been added to the list." _
, vbInformation, "Publication Name Added"
Response = acDataErrAdded
Else
MsgBox "Please choose a publication name from the list." _
, vbInformation, "Use Publication Name List"
Response = acDataErrContinue
End If


Combo13_NotInList_Exit:
Exit Sub
Combo13_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume Combo13_NotInList_Exit
End Sub

These are the primary Properties settings:
Row Source = the SELECT Query Statement
Row Source Type = Table/Query
Bound Column = 1
Limit To List = Yes
Allow Value List Edits = Yes
List Items Edit Form - (Blank - nothing selected)
Enabled = Yes
Locked = Yes
On Not In List = Event Procedure

An additional item to note is that some of the Tables for some of the Combo
Boxes contain an additional field for a Score Value of the selection.

For Example:
Ethnicity
White = 1
African American = 2
Hispanic = 3
Etc.

Are there any modifications that need to be made to the SQL script for a user
who is adding a new ethinic group? Does something have to be added to ask to
input the Score Value as well?

[RuralGuy]

Allow Value List Edits = NO <----------
List Items Edit Form - (Blank - nothing selected)
Enabled = Yes
Locked = NO <-----------------

[skyrise]

Here is a basic copy of the database. Several of my user update fields will be based on this premise.

Lookup Table = lkpPublicationName
Main Table = TBLPublicationData
Form = FRMPublicationData

On the Publication Data Form, I'm trying to drop in a combo box that will allow the user to select the name of the publication from the Publication Name Lookup Table. If the user comes across a new publication, the user will be able to add that name to the combo box as well as select it on future records.

The NotInList Event Procedure on the Combo Box is not allowing new text to be added. It is defaulting to the Access database error indicating that the text is not part of the list of options to select. The message box closes and the combo box list opens to show the text that is available.

[skyrise]

I think that I resolved the issue. (TY:scottmcd9999)

I selected the "Compact and Repair" function and enabled Macros on the database. Both of those things has the NotInList Event Procedure working.

[RuralGuy]

That's great. Thanks for posting back.