NotInList Event Procedure Not Working

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 a

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

Did you set the Limit to List property for the combobox to Yes?

--

Ken Snell
MS ACCESS MVP
http://www.accessmvp.com/KDSnell/


"skyrise" u49824@uwe wrote in message news:921a3f9309ba8@uwe...
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 a

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


Ken Snell (MVP) wrote:
Did you set the Limit to List property for the combobox to Yes?

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:
[quoted text clipped - 87 lines]
Resume Combo13_NotInList_Exit
End Sub

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?

skyrise wrote:
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

Did you set the Limit to List property for the combobox to Yes?

[quoted text clipped - 3 lines]
Resume Combo13_NotInList_Exit
End Sub

If the combo box is locked, then the user is unable to do anything in the
combo box with respect to entering data or selecting an item. Therefore, the
NotInList event procedure cannot be run because the event never occurs.

Have you put breakpoints in the event procedure to be sure that it's not
running -- perhaps it's erroring right away and exiting the procedure
through the error handler?

--

Ken Snell
MS ACCESS MVP
http://www.accessmvp.com/KDSnell/



"skyrise" u49824@uwe wrote in message news:921ab1201c7ca@uwe...
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


Ken Snell (MVP) wrote:
Did you set the Limit to List property for the combobox to Yes?

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:
[quoted text clipped - 87 lines]
Resume Combo13_NotInList_Exit
End Sub

Sorry, my mistake. The Locked Property is set to "No". I wish that was the
problem.

Ken Snell (MVP) wrote:
If the combo box is locked, then the user is unable to do anything in the
combo box with respect to entering data or selecting an item. Therefore, the
NotInList event procedure cannot be run because the event never occurs.

Have you put breakpoints in the event procedure to be sure that it's not
running -- perhaps it's erroring right away and exiting the procedure
through the error handler?

These are the primary Properties settings:
Row Source = the SELECT Query Statement
[quoted text clipped - 15 lines]
Resume Combo13_NotInList_Exit
End Sub

What are the ColumnWidths and ColumnCount properties?

--

Ken Snell
MS ACCESS MVP
http://www.accessmvp.com/KDSnell/


"skyrise" u49824@uwe wrote in message news:921c48ad5e21a@uwe...
Sorry, my mistake. The Locked Property is set to "No". I wish that was
the
problem.

Ken Snell (MVP) wrote:
If the combo box is locked, then the user is unable to do anything in the
combo box with respect to entering data or selecting an item. Therefore,
the
NotInList event procedure cannot be run because the event never occurs.

Have you put breakpoints in the event procedure to be sure that it's not
running -- perhaps it's erroring right away and exiting the procedure
through the error handler?

These are the primary Properties settings:
Row Source = the SELECT Query Statement
[quoted text clipped - 15 lines]
Resume Combo13_NotInList_Exit
End Sub

Also, is your SQL statement correct for the combo box:

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

You're selecting data from a table called lkpJournalName, but the fields are
from the table lkpPublicationName ?

--

Ken Snell
MS ACCESS MVP
http://www.accessmvp.com/KDSnell/


"skyrise" u49824@uwe wrote in message news:921c48ad5e21a@uwe...
Sorry, my mistake. The Locked Property is set to "No". I wish that was
the
problem.

Ken Snell (MVP) wrote:
If the combo box is locked, then the user is unable to do anything in the
combo box with respect to entering data or selecting an item. Therefore,
the
NotInList event procedure cannot be run because the event never occurs.

Have you put breakpoints in the event procedure to be sure that it's not
running -- perhaps it's erroring right away and exiting the procedure
through the error handler?

These are the primary Properties settings:
Row Source = the SELECT Query Statement
[quoted text clipped - 15 lines]
Resume Combo13_NotInList_Exit
End Sub

Column Count = 2
Column Width = 0";2.073"

Ken Snell (MVP) wrote:
What are the ColumnWidths and ColumnCount properties?

Sorry, my mistake. The Locked Property is set to "No". I wish that was
the
[quoted text clipped - 14 lines]
Resume Combo13_NotInList_Exit
End Sub

The table was renamed to lkpPublicationName. Missed changing that one after
the FROM statement. No effect on the Event Procedure though.

When I go back to the form and try to input a new publication name, I get the
Access default error message, "The text you entered is not in the list.
Select and item in the list, or enter text that matches one of the listed
items." So it seems like it is erroring rigt away and exiting the procedure
through that Error Handler.

I put in a break point on the first line and tried to add new text.
Immediately got the error message.

Ken Snell (MVP) wrote:
Also, is your SQL statement correct for the combo box:

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

You're selecting data from a table called lkpJournalName, but the fields are
from the table lkpPublicationName ?

Sorry, my mistake. The Locked Property is set to "No". I wish that was
the
[quoted text clipped - 14 lines]
Resume Combo13_NotInList_Exit
End Sub