"On Not in List" event for Access 2007

A

alipscomb

I am creating a library catalog DB where the main table, “bookTable†includes
relevant book fields. One such field is the publisher’s name, “Publisher.â€
Publisher is a combo box that looks-up publisherTable, which has an
AutoNumber ID and a Publisher field. On my new book entry form I want to
allow the user to add a new value to the Publisher combo box, populating the
original publisherTable as well. I added code under the “On Not in Listâ€
event, but it seems the event does not execute the code. I only get the "The
text you entered isn't an item in the list" error, but that’s all that
happens. I am creating this DB in Access 2007, but I have successfully used
this functionality for many instances in another DB using Access 2000. I’m
not sure if there is something I’m missing in Access 2007. Any help Is
appreciated. The Publisher field’s table and form properties follow, as well
as the VB code:

The Publisher properties in the table are as follows:
Row Source Type=Table/Query
Row Source=SELECT publisherTable.PublisherID, publisherTable.Publisher FROM
publisherTable;
Bound Column=1
Column Count=2
Column Widths=0â€;1â€
LimitToList=Yes

The Publisher form properties:
Row Source Type=Table/Query
Row Source=SELECT publisherTable.PublisherID, publisherTable.Publisher FROM
publisherTable;
Bound Column=1
Column Count=2
Column Widths=0â€;1â€
LimitToList=Yes
Auto Expand=Yes
On Not in List=[Event Procedure]

VB Code:
Private Sub Publisher_NotInList(NewData As String, Response As Integer)

Dim strMsg As String
Dim rst As DAO.Recordset
Dim db As DAO.Database

strMsg = "'" & NewData & "' is not in the list. "
strMsg = strMsg & "Would you like to add it?"

If vbNo = MsgBox(strMsg, vbYesNo + vbQuestion, "New Publisher") Then

Response = acDataErrDisplay

Else

Set db = CurrentDb()
Set rst = db.OpenRecordset("publisherTable")

rst.AddNew
rst("Publisher") = NewData
rst.Update
Response = acDataErrAdded
rst.Close

End If

End Sub
 
L

Larry Linson

Did you check the setting of the Limit To List property? That could be your
problem.

Larry Linson
Microsoft Office Access MVP
 
A

alipscomb

It's set to Yes. That's what invokes the "The text you entered isn't an item
in the list" error. I also listed my table and form properties. I'm just not
sure why my event procedure isn't executing. I've done this many times before
in Access 2000.

Larry Linson said:
Did you check the setting of the Limit To List property? That could be your
problem.

Larry Linson
Microsoft Office Access MVP

alipscomb said:
I am creating a library catalog DB where the main table, "bookTable"
includes
relevant book fields. One such field is the publisher's name, "Publisher."
Publisher is a combo box that looks-up publisherTable, which has an
AutoNumber ID and a Publisher field. On my new book entry form I want to
allow the user to add a new value to the Publisher combo box, populating
the
original publisherTable as well. I added code under the "On Not in List"
event, but it seems the event does not execute the code. I only get the
"The
text you entered isn't an item in the list" error, but that's all that
happens. I am creating this DB in Access 2007, but I have successfully
used
this functionality for many instances in another DB using Access 2000. I'm
not sure if there is something I'm missing in Access 2007. Any help Is
appreciated. The Publisher field's table and form properties follow, as
well
as the VB code:

The Publisher properties in the table are as follows:
Row Source Type=Table/Query
Row Source=SELECT publisherTable.PublisherID, publisherTable.Publisher
FROM
publisherTable;
Bound Column=1
Column Count=2
Column Widths=0";1"
LimitToList=Yes

The Publisher form properties:
Row Source Type=Table/Query
Row Source=SELECT publisherTable.PublisherID, publisherTable.Publisher
FROM
publisherTable;
Bound Column=1
Column Count=2
Column Widths=0";1"
LimitToList=Yes
Auto Expand=Yes
On Not in List=[Event Procedure]

VB Code:
Private Sub Publisher_NotInList(NewData As String, Response As Integer)

Dim strMsg As String
Dim rst As DAO.Recordset
Dim db As DAO.Database

strMsg = "'" & NewData & "' is not in the list. "
strMsg = strMsg & "Would you like to add it?"

If vbNo = MsgBox(strMsg, vbYesNo + vbQuestion, "New Publisher") Then

Response = acDataErrDisplay

Else

Set db = CurrentDb()
Set rst = db.OpenRecordset("publisherTable")

rst.AddNew
rst("Publisher") = NewData
rst.Update
Response = acDataErrAdded
rst.Close

End If

End Sub
Click to expand...
Click to expand...
 
G

Graham Mandeno

Hi alipscomb

Sometimes the "link" between a control and its event procedure goes missing
for no apparent reason.

First, click on "On Not In List" in the property sheet for your combo box
and then click on the [...] button to the right of [Event Procedure].

This should take you directly to the event procedure code. If it takes you
instead to an empty procedure declaration, then cut the code form your
existing procedure body and paste it into the new one, then delete the old
procedure.

If it does take you to the correct code, then try compacting and repairing
the database.

If it still doesn't work, try deleting the combo box *and* the event proc
(copy the code from the body first) and recreate the combo.

BTW, if the response to your question is "No" then you should set Response
to acDataErrContinue, not acDataErrDisplay. The latter will display the
default "The text you entered isn't an item in the list" message *as well
as* your own custom message.
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand


alipscomb said:
I am creating a library catalog DB where the main table, "bookTable"
includes
relevant book fields. One such field is the publisher's name, "Publisher."
Publisher is a combo box that looks-up publisherTable, which has an
AutoNumber ID and a Publisher field. On my new book entry form I want to
allow the user to add a new value to the Publisher combo box, populating
the
original publisherTable as well. I added code under the "On Not in List"
event, but it seems the event does not execute the code. I only get the
"The
text you entered isn't an item in the list" error, but that's all that
happens. I am creating this DB in Access 2007, but I have successfully
used
this functionality for many instances in another DB using Access 2000. I'm
not sure if there is something I'm missing in Access 2007. Any help Is
appreciated. The Publisher field's table and form properties follow, as
well
as the VB code:

The Publisher properties in the table are as follows:
Row Source Type=Table/Query
Row Source=SELECT publisherTable.PublisherID, publisherTable.Publisher
FROM
publisherTable;
Bound Column=1
Column Count=2
Column Widths=0";1"
LimitToList=Yes

The Publisher form properties:
Row Source Type=Table/Query
Row Source=SELECT publisherTable.PublisherID, publisherTable.Publisher
FROM
publisherTable;
Bound Column=1
Column Count=2
Column Widths=0";1"
LimitToList=Yes
Auto Expand=Yes
On Not in List=[Event Procedure]

VB Code:
Private Sub Publisher_NotInList(NewData As String, Response As Integer)

Dim strMsg As String
Dim rst As DAO.Recordset
Dim db As DAO.Database

strMsg = "'" & NewData & "' is not in the list. "
strMsg = strMsg & "Would you like to add it?"

If vbNo = MsgBox(strMsg, vbYesNo + vbQuestion, "New Publisher") Then

Response = acDataErrDisplay

Else

Set db = CurrentDb()
Set rst = db.OpenRecordset("publisherTable")

rst.AddNew
rst("Publisher") = NewData
rst.Update
Response = acDataErrAdded
rst.Close

End If

End Sub
Click to expand...
 
A

alipscomb

Thanks, Graham.

I have it working now. I tried Compact & Repair, then deleting the combo
box, but neither fixed the problem. I'm not sure what it was, but I just
started over. Fortunately I was in the very early development stage.

Graham Mandeno said:
Hi alipscomb

Sometimes the "link" between a control and its event procedure goes missing
for no apparent reason.

First, click on "On Not In List" in the property sheet for your combo box
and then click on the [...] button to the right of [Event Procedure].

This should take you directly to the event procedure code. If it takes you
instead to an empty procedure declaration, then cut the code form your
existing procedure body and paste it into the new one, then delete the old
procedure.

If it does take you to the correct code, then try compacting and repairing
the database.

If it still doesn't work, try deleting the combo box *and* the event proc
(copy the code from the body first) and recreate the combo.

BTW, if the response to your question is "No" then you should set Response
to acDataErrContinue, not acDataErrDisplay. The latter will display the
default "The text you entered isn't an item in the list" message *as well
as* your own custom message.
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand


alipscomb said:
I am creating a library catalog DB where the main table, "bookTable"
includes
relevant book fields. One such field is the publisher's name, "Publisher."
Publisher is a combo box that looks-up publisherTable, which has an
AutoNumber ID and a Publisher field. On my new book entry form I want to
allow the user to add a new value to the Publisher combo box, populating
the
original publisherTable as well. I added code under the "On Not in List"
event, but it seems the event does not execute the code. I only get the
"The
text you entered isn't an item in the list" error, but that's all that
happens. I am creating this DB in Access 2007, but I have successfully
used
this functionality for many instances in another DB using Access 2000. I'm
not sure if there is something I'm missing in Access 2007. Any help Is
appreciated. The Publisher field's table and form properties follow, as
well
as the VB code:

The Publisher properties in the table are as follows:
Row Source Type=Table/Query
Row Source=SELECT publisherTable.PublisherID, publisherTable.Publisher
FROM
publisherTable;
Bound Column=1
Column Count=2
Column Widths=0";1"
LimitToList=Yes

The Publisher form properties:
Row Source Type=Table/Query
Row Source=SELECT publisherTable.PublisherID, publisherTable.Publisher
FROM
publisherTable;
Bound Column=1
Column Count=2
Column Widths=0";1"
LimitToList=Yes
Auto Expand=Yes
On Not in List=[Event Procedure]

VB Code:
Private Sub Publisher_NotInList(NewData As String, Response As Integer)

Dim strMsg As String
Dim rst As DAO.Recordset
Dim db As DAO.Database

strMsg = "'" & NewData & "' is not in the list. "
strMsg = strMsg & "Would you like to add it?"

If vbNo = MsgBox(strMsg, vbYesNo + vbQuestion, "New Publisher") Then

Response = acDataErrDisplay

Else

Set db = CurrentDb()
Set rst = db.OpenRecordset("publisherTable")

rst.AddNew
rst("Publisher") = NewData
rst.Update
Response = acDataErrAdded
rst.Close

End If

End Sub
Click to expand...
Click to expand...
 
G

Graham Mandeno

They do that to you sometimes. Regular backups - that's the important
message here.

I'm glad you didn't lose too much time over it.
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

alipscomb said:
Thanks, Graham.

I have it working now. I tried Compact & Repair, then deleting the combo
box, but neither fixed the problem. I'm not sure what it was, but I just
started over. Fortunately I was in the very early development stage.

Graham Mandeno said:
Hi alipscomb

Sometimes the "link" between a control and its event procedure goes
missing
for no apparent reason.

First, click on "On Not In List" in the property sheet for your combo box
and then click on the [...] button to the right of [Event Procedure].

This should take you directly to the event procedure code. If it takes
you
instead to an empty procedure declaration, then cut the code form your
existing procedure body and paste it into the new one, then delete the
old
procedure.

If it does take you to the correct code, then try compacting and
repairing
the database.

If it still doesn't work, try deleting the combo box *and* the event proc
(copy the code from the body first) and recreate the combo.

BTW, if the response to your question is "No" then you should set
Response
to acDataErrContinue, not acDataErrDisplay. The latter will display the
default "The text you entered isn't an item in the list" message *as well
as* your own custom message.
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand


alipscomb said:
I am creating a library catalog DB where the main table, "bookTable"
includes
relevant book fields. One such field is the publisher's name,
"Publisher."
Publisher is a combo box that looks-up publisherTable, which has an
AutoNumber ID and a Publisher field. On my new book entry form I want
to
allow the user to add a new value to the Publisher combo box,
populating
the
original publisherTable as well. I added code under the "On Not in
List"
event, but it seems the event does not execute the code. I only get the
"The
text you entered isn't an item in the list" error, but that's all that
happens. I am creating this DB in Access 2007, but I have successfully
used
this functionality for many instances in another DB using Access 2000.
I'm
not sure if there is something I'm missing in Access 2007. Any help Is
appreciated. The Publisher field's table and form properties follow, as
well
as the VB code:

The Publisher properties in the table are as follows:
Row Source Type=Table/Query
Row Source=SELECT publisherTable.PublisherID, publisherTable.Publisher
FROM
publisherTable;
Bound Column=1
Column Count=2
Column Widths=0";1"
LimitToList=Yes

The Publisher form properties:
Row Source Type=Table/Query
Row Source=SELECT publisherTable.PublisherID, publisherTable.Publisher
FROM
publisherTable;
Bound Column=1
Column Count=2
Column Widths=0";1"
LimitToList=Yes
Auto Expand=Yes
On Not in List=[Event Procedure]

VB Code:
Private Sub Publisher_NotInList(NewData As String, Response As Integer)

Dim strMsg As String
Dim rst As DAO.Recordset
Dim db As DAO.Database

strMsg = "'" & NewData & "' is not in the list. "
strMsg = strMsg & "Would you like to add it?"

If vbNo = MsgBox(strMsg, vbYesNo + vbQuestion, "New Publisher") Then

Response = acDataErrDisplay

Else

Set db = CurrentDb()
Set rst = db.OpenRecordset("publisherTable")

rst.AddNew
rst("Publisher") = NewData
rst.Update
Response = acDataErrAdded
rst.Close

End If

End Sub
Click to expand...
Click to expand...
Click to expand...
 
P

Peter Wilson

Had the same problem with "On Click".

Did a repair, tried a new form in same db. Nothing worked. The event just wasn't triggered.

Eventually, created a new DB and imported everything from the old one and then it just started working.

Thanks MS :(
 
P

Peter Wilson

I take it back. The problem was me having security options turned on.
I think it might have something to do with the macro settings in the trust center.
Seems that all macros/events are blocked if the db is not trusted.

Sorry MS
 
J

Jan Baird

Jan Baird is out of the country until September 20. Every effort will be
made to respond to messages, but please be patient.
 
J

Jan Baird

Jan Baird is out of the country until September 20. Every effort will be
made to respond to messages, but please be patient.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Not In List 2 values 5
Not in list not working 1
Not in list requery issue 4
Not In List help needed 1
Update Combo Box when Not In List and update related info in form 1
NotinList problem 7
Combo Box Selection Code on NOT IN LIST 4
Not in list warning 2

Top