Combo - Not in List Event

Would anyone be able to help me with any explanation as to why my Combo
"NotInList" Event refuses to work?
The Combo seems to work fine when providing a list of iems to select from
but I want to provide the user with a method of entering somenting that is
not in the underlying Table and thereby habing the option to create a new
record. I though that "NotIn List" would be the way to do it. By using the
"NotInList" Event for the Combo, the program should pick up that the item is
not in the Table and ask the user if they want to create a new record. I have
tried all I know (which isn't much) but despite everything if I enter
something that is in the Table the program goes to the combo "AfterUpdate"
Event but if I enter something in the Combo that is not in the Table, it
still goes to the "AfterUpdate" Event (and returns an error) whereas it
should go to the "NotInLis" routine. I have repeatedly enterered, scrubbed
out and re-entered the "NotInList" Event in the Properties for that Combo.

Any help Appreciated RayC

Here's one of my NotInList events for a similar situation:


Private Sub cboSupplier_NotInList(NewData As String, Response As Integer)
'-- We may need to add another Supplier
Response = MsgBox("[" & NewData & "] is not yet a Supplier..." & vbCr &
vbCr & _
"Would you like to add a New Supplier to your DataBase?",
vbYesNo)

If Response = vbYes Then
'-- Create a new Supplier record
Dim db As DAO.Database
Dim MySQL As String
Set db = CurrentDb()
MySQL = "Insert Into tblSuppliers(CoName,PriCat) " & _
"Values(""" & NewData & """,27)"
db.Execute MySQL, dbFailOnError
Set db = Nothing
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub


Ray C wrote:
Would anyone be able to help me with any explanation as to why my Combo
"NotInList" Event refuses to work?
The Combo seems to work fine when providing a list of iems to select from
but I want to provide the user with a method of entering somenting that is
not in the underlying Table and thereby habing the option to create a new
record. I though that "NotIn List" would be the way to do it. By using the
"NotInList" Event for the Combo, the program should pick up that the item is
not in the Table and ask the user if they want to create a new record. I have
tried all I know (which isn't much) but despite everything if I enter
something that is in the Table the program goes to the combo "AfterUpdate"
Event but if I enter something in the Combo that is not in the Table, it
still goes to the "AfterUpdate" Event (and returns an error) whereas it
should go to the "NotInLis" routine. I have repeatedly enterered, scrubbed
out and re-entered the "NotInList" Event in the Properties for that Combo.

Any help Appreciated RayC

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via http://www.accessmonster.com

Try using the generic code at:

http://www.datastrat.com/Code/NotInListCode.txt

There's a demo database of it at:

http://www.accessmvp.com/Arvin/NotInListDemo.zip
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"Ray C" wrote in message
...
Would anyone be able to help me with any explanation as to why my Combo
"NotInList" Event refuses to work?
The Combo seems to work fine when providing a list of iems to select from
but I want to provide the user with a method of entering somenting that is
not in the underlying Table and thereby habing the option to create a new
record. I though that "NotIn List" would be the way to do it. By using the
"NotInList" Event for the Combo, the program should pick up that the item
is
not in the Table and ask the user if they want to create a new record. I
have
tried all I know (which isn't much) but despite everything if I enter
something that is in the Table the program goes to the combo "AfterUpdate"
Event but if I enter something in the Combo that is not in the Table, it
still goes to the "AfterUpdate" Event (and returns an error) whereas it
should go to the "NotInLis" routine. I have repeatedly enterered, scrubbed
out and re-entered the "NotInList" Event in the Properties for that Combo.

Any help Appreciated RayC

Whenever someone says "My Not In List" event doesn't work, the first question
I ask is, have you set the combobox "Limit to List" property to "Yes?"

Try, in Form Design View, selecting the combobox, then going to

Properties - Data

and set the "Limit to List" property to "Yes."

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via http://www.accessmonster.com

Thanks for your input guys. my version follows your various examples but, in
my program, if I enter something that is NOT in the dropdown list, the
program goes to the "AfterUpdate" routine as normal. I can not get the
program to go to my "NotInList" routine if I enter something in the Combo
that is not in the Combo List.
I just wodered if anyone had come accross this before and if the was a known
reason as to why this would happen
regards RayC

"ruralguy via AccessMonster.com" wrote:

Here's one of my NotInList events for a similar situation:


Private Sub cboSupplier_NotInList(NewData As String, Response As Integer)
'-- We may need to add another Supplier
Response = MsgBox("[" & NewData & "] is not yet a Supplier..." & vbCr &
vbCr & _
"Would you like to add a New Supplier to your DataBase?",
vbYesNo)

If Response = vbYes Then
'-- Create a new Supplier record
Dim db As DAO.Database
Dim MySQL As String
Set db = CurrentDb()
MySQL = "Insert Into tblSuppliers(CoName,PriCat) " & _
"Values(""" & NewData & """,27)"
db.Execute MySQL, dbFailOnError
Set db = Nothing
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub


Ray C wrote:
Would anyone be able to help me with any explanation as to why my Combo
"NotInList" Event refuses to work?
The Combo seems to work fine when providing a list of iems to select from
but I want to provide the user with a method of entering somenting that is
not in the underlying Table and thereby habing the option to create a new
record. I though that "NotIn List" would be the way to do it. By using the
"NotInList" Event for the Combo, the program should pick up that the item is
not in the Table and ask the user if they want to create a new record. I have
tried all I know (which isn't much) but despite everything if I enter
something that is in the Table the program goes to the combo "AfterUpdate"
Event but if I enter something in the Combo that is not in the Table, it
still goes to the "AfterUpdate" Event (and returns an error) whereas it
should go to the "NotInLis" routine. I have repeatedly enterered, scrubbed
out and re-entered the "NotInList" Event in the Properties for that Combo.

Any help Appreciated RayC

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via http://www.accessmonster.com

.

Did you see Linq's post? Have you confirmed that the Limit To List property
is set to True?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Ray C" wrote in message
...
Thanks for your input guys. my version follows your various examples but,
in
my program, if I enter something that is NOT in the dropdown list, the
program goes to the "AfterUpdate" routine as normal. I can not get the
program to go to my "NotInList" routine if I enter something in the Combo
that is not in the Combo List.
I just wodered if anyone had come accross this before and if the was a
known
reason as to why this would happen
regards RayC

"ruralguy via AccessMonster.com" wrote:

Here's one of my NotInList events for a similar situation:


Private Sub cboSupplier_NotInList(NewData As String, Response As Integer)
'-- We may need to add another Supplier
Response = MsgBox("[" & NewData & "] is not yet a Supplier..." & vbCr
&
vbCr & _
"Would you like to add a New Supplier to your
DataBase?",
vbYesNo)

If Response = vbYes Then
'-- Create a new Supplier record
Dim db As DAO.Database
Dim MySQL As String
Set db = CurrentDb()
MySQL = "Insert Into tblSuppliers(CoName,PriCat) " & _
"Values(""" & NewData & """,27)"
db.Execute MySQL, dbFailOnError
Set db = Nothing
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub


Ray C wrote:
Would anyone be able to help me with any explanation as to why my Combo
"NotInList" Event refuses to work?
The Combo seems to work fine when providing a list of iems to select
from
but I want to provide the user with a method of entering somenting that
is
not in the underlying Table and thereby habing the option to create a
new
record. I though that "NotIn List" would be the way to do it. By using
the
"NotInList" Event for the Combo, the program should pick up that the
item is
not in the Table and ask the user if they want to create a new record. I
have
tried all I know (which isn't much) but despite everything if I enter
something that is in the Table the program goes to the combo
"AfterUpdate"
Event but if I enter something in the Combo that is not in the Table, it
still goes to the "AfterUpdate" Event (and returns an error) whereas it
should go to the "NotInLis" routine. I have repeatedly enterered,
scrubbed
out and re-entered the "NotInList" Event in the Properties for that
Combo.

Any help Appreciated RayC

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via http://www.accessmonster.com

.

Hi Doug

Jusst received that and he is spot on. I have set the properties as
suggessted and it is now working fine.

Thanks so much to everyone for their input. Regards Ray C

"Douglas J. Steele" wrote:

Did you see Linq's post? Have you confirmed that the Limit To List property
is set to True?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Ray C" wrote in message
...
Thanks for your input guys. my version follows your various examples but,
in
my program, if I enter something that is NOT in the dropdown list, the
program goes to the "AfterUpdate" routine as normal. I can not get the
program to go to my "NotInList" routine if I enter something in the Combo
that is not in the Combo List.
I just wodered if anyone had come accross this before and if the was a
known
reason as to why this would happen
regards RayC

"ruralguy via AccessMonster.com" wrote:

Here's one of my NotInList events for a similar situation:


Private Sub cboSupplier_NotInList(NewData As String, Response As Integer)
'-- We may need to add another Supplier
Response = MsgBox("[" & NewData & "] is not yet a Supplier..." & vbCr
&
vbCr & _
"Would you like to add a New Supplier to your
DataBase?",
vbYesNo)

If Response = vbYes Then
'-- Create a new Supplier record
Dim db As DAO.Database
Dim MySQL As String
Set db = CurrentDb()
MySQL = "Insert Into tblSuppliers(CoName,PriCat) " & _
"Values(""" & NewData & """,27)"
db.Execute MySQL, dbFailOnError
Set db = Nothing
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub


Ray C wrote:
Would anyone be able to help me with any explanation as to why my Combo
"NotInList" Event refuses to work?
The Combo seems to work fine when providing a list of iems to select
from
but I want to provide the user with a method of entering somenting that
is
not in the underlying Table and thereby habing the option to create a
new
record. I though that "NotIn List" would be the way to do it. By using
the
"NotInList" Event for the Combo, the program should pick up that the
item is
not in the Table and ask the user if they want to create a new record. I
have
tried all I know (which isn't much) but despite everything if I enter
something that is in the Table the program goes to the combo
"AfterUpdate"
Event but if I enter something in the Combo that is not in the Table, it
still goes to the "AfterUpdate" Event (and returns an error) whereas it
should go to the "NotInLis" routine. I have repeatedly enterered,
scrubbed
out and re-entered the "NotInList" Event in the Properties for that
Combo.

Any help Appreciated RayC

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via http://www.accessmonster.com

.


.