NotInList problems

  • Thread starter Thread starter Bryan
  • Start date Start date
B

Bryan

I am trying to allow the user to add an item to a list if it is not
found in a combobox. When the NotInList event is triggered I run a
function "AddItem" that has a custom dialog box to add an item to the
underlying table. The function either returns "Not Added", or the new
Items ID. I know the function works fine. Whe I try the code below I
get errors saying "Close action was cancelled" (referring to my custom
popup input box), & "The text you entered isn't an item in the list".
Then when I try to exit the form that has the combobox I get errors
saying "you must save the current field before you run the query
action" & the "you can not save this record at this time, do you still
want to exit" etc.
Does anybody see anything wrong with the code below?


Private Sub Item_NotInList(NewData As String, Response As Integer)
'create variable to hold function result
Dim AddStatus As String
AddStatus = AddItem

If AddStatus = "Not Added" Then
Response = acDataErrContinue
Else:
Response = acDataErrAdded
End If

End Sub
 
: I am trying to allow the user to add an item to a list if it is not
: found in a combobox. When the NotInList event is triggered I run a
: function "AddItem" that has a custom dialog box to add an item to the
: underlying table. The function either returns "Not Added", or the new
: Items ID. I know the function works fine. Whe I try the code below I
: get errors saying "Close action was cancelled" (referring to my custom
: popup input box), & "The text you entered isn't an item in the list".

It sounds like you haven't set the combobox's LimitToList
property to False.
--thelma
 
Thanks for your reply. The NotInList event can't even fire unless
LimitToList is set to yes. Any other thoughts?
 
: Thanks for your reply. The NotInList event can't even fire unless
: LimitToList is set to yes. Any other thoughts?

Sorry. I'm sleepy, probably too sleepy to be making much sense.
But I think that I remember trying to do such a thing and finding
that I couldn't circumvent the LimitToList restriction that way and
had to set it False and put the code that checked for valid
addition in the BeforeUpdate event
--thelma
 
After looking around on several forums I'm affraid that's what I'm
going to have to do. I have not found anybody who is able to use a
custom input box function to update a combobox when the NotInList event
is fired.
 
Bryan said:
I am trying to allow the user to add an item to a list if it is not
found in a combobox. When the NotInList event is triggered I run a
function "AddItem" that has a custom dialog box to add an item to the
underlying table. The function either returns "Not Added", or the new
Items ID. I know the function works fine. Whe I try the code below I
get errors saying "Close action was cancelled" (referring to my custom
popup input box), & "The text you entered isn't an item in the list".
Then when I try to exit the form that has the combobox I get errors
saying "you must save the current field before you run the query
action" & the "you can not save this record at this time, do you still
want to exit" etc.
Does anybody see anything wrong with the code below?


Private Sub Item_NotInList(NewData As String, Response As Integer)
'create variable to hold function result
Dim AddStatus As String
AddStatus = AddItem

If AddStatus = "Not Added" Then
Response = acDataErrContinue
Else:
Response = acDataErrAdded
End If

End Sub
Click to expand...

I googled Google Groups to find replies to NotInList questions I helped with a
long time ago. Here is the first one I found:

When I had problems with the NotInList event, I found three places that
really helped me understand what I was doing wrong:

1) http://www.mvps.org/access/forms/frm0015.htm


and

http://www.rogersaccesslibrary.com/TableOfContents3.asp#N

which has these two pages:

2)
http://www.rogersaccesslibrary.com/download3.asp?SampleName=NotInList

3)
http://www.rogersaccesslibrary.com/download3.asp?SampleName=NotInList



As to your code, without seeing the function "additem" code, I don't know how
the item is being added to the table.

Maybe this will help; code from a previous post. You will have to change
field/table names......



Here is an alternate way to add to a combo box without
using an additional form. Change the control/field names
to your usage.

Set the LimitToList to YES.

In the NotInList Event: (Watch for line wrap...)

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

'Cut/paste from here-----------------------
Dim db As DAO.Database
Dim rst As DAO.Recordset

' Prompt user to verify they wish to add new value.
' all one line
If MsgBox("Do you want to add '" & NewData & "' as a new
title?", vbYesNo) "Add New Item?") = vbYes Then

Set db = CurrentDb
Set rst = db.OpenRecordset("tScopeOfWorkHeaderList")
' the following adds the NewData to the table
With rst
.AddNew ' new record
!Header = NewData
.Update ' save it
End With
' close the recordset and cleanup
rst.Close
Set rst = Nothing
Set db = Nothing

' Continue without displaying default error message.
' This also does an automatic requery ******
Response = acDataErrAdded
Else
' don't add & continue
Response = acDataErrContinue
' clear the combo box
Me.Heading1.Undo
End If
'To here-----------------------
End Sub




The reason you get the error messages when you try to exit the form with the
combo box on it is because you have started to add a new record - maybe a
required field is empty (null). I think if you press the <ESC> key a couple of
times, you should be able to close the form without the errrror <g> message.

(I just noticed I misspelled error after I posted this) :D


HTH
 
The following code works for me in a database that manages vegetable
cooking. A combo box ' cboSelCookingTypeID' gets its data from a table
'ztblLookupCookingType' which has two fields.

CookingTypeID autonumber
CookingType text

The code is as follows:-

Private Sub cboSelCookingTypeID_NotInList(NewData As String, Response
As Integer)
Dim strMsg As String
Dim rst As Recordset

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

If vbNo = MsgBox(strMsg, vbYesNo + vbQuestion, "New Cooking Type")
Then
Response = acDataErrDisplay
Else
Set rst = CurrentDb.OpenRecordset("ztblLookupCookingType")
rst.AddNew
rst!CookingType = NewData
rst.Update
Response = acDataErrAdded
rst.Close
End If
End Sub

When the code runs on the 'Not in list' event it pops up a message box
that gives the user the option to add the new cooking type showing what
that is (this is useful because it deals with typos). If the user wants
to add the type it is added to the table.

Hope you can adapt this to fit your problem

Geoff G
 
It's what's happening in the AddItem function and any anciliary code we need
to see.
 
Back
Top