473,322 Members | 1,778 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

OnNotInList Event

I've never used this event before and I'm struggling. I have this code in
it:

If MsgBox("You entered " & Me.cboManagerName _
& " - is this the manager's network logon ID?", vbYesNo, "Confrim
Manager ID") = vbYes Then
Response = acDataErrAdded
Else
Response = acDataErrContinue
Me.cboManagerName.Undo
End If

Firstly, the message box displays the *old* value instead of the one I type
in. Secondly, if I answer "Yes" to the message box I get the "The item is
not in the list" error - I thought that was supposed to be suppressed?

I'm missing something obvious here, but what?

Many thanks,
Keith.
Feb 7 '07 #1
13 3087
On Feb 7, 5:49 am, "Keith Wilby" <h...@there.comwrote:
I've never used this event before and I'm struggling. I have this code in
it:

If MsgBox("You entered " & Me.cboManagerName _
& " - is this the manager's network logon ID?", vbYesNo, "Confrim
Manager ID") = vbYes Then
Response = acDataErrAdded
Else
Response = acDataErrContinue
Me.cboManagerName.Undo
End If

Firstly, the message box displays the *old* value instead of the one I type
in. Secondly, if I answer "Yes" to the message box I get the "The item is
not in the list" error - I thought that was supposed to be suppressed?

I'm missing something obvious here, but what?

Many thanks,
Keith.
Take another look at the help file topic for this event and you may
see that you have a couple of things wrong.
Manager ID") = vbYes Then
Response = acDataErrAdded
After using this response constant, Access is expecting to see the new
value in the list of the combobox, otherwise another error will
occurr.
There is also another argument to use 'NewData' which is supposed to
contain the new value. Referencing the combobox before it has been
successfully updated will only return the last accepted value.

Feb 7 '07 #2
"Keith Wilby" <he**@there.comwrote in message
news:45**********@glkas0286.greenlnk.net...
>
Firstly, the message box displays the *old* value instead of the one I
type in.
OK sussed that out (".Text" qualifier required) but still struggling with
the other issue.
Feb 7 '07 #3
"storrboy" <st******@sympatico.cawrote in message
news:11**********************@h3g2000cwc.googlegro ups.com...
On Feb 7, 5:49 am, "Keith Wilby" <h...@there.comwrote:
>I've never used this event before and I'm struggling. I have this code
in
it:

If MsgBox("You entered " & Me.cboManagerName _
& " - is this the manager's network logon ID?", vbYesNo, "Confrim
Manager ID") = vbYes Then
Response = acDataErrAdded
Else
Response = acDataErrContinue
Me.cboManagerName.Undo
End If

Firstly, the message box displays the *old* value instead of the one I
type
in. Secondly, if I answer "Yes" to the message box I get the "The item
is
not in the list" error - I thought that was supposed to be suppressed?

I'm missing something obvious here, but what?

Many thanks,
Keith.

Take another look at the help file topic for this event and you may
see that you have a couple of things wrong.
Manager ID") = vbYes Then
Response = acDataErrAdded
After using this response constant, Access is expecting to see the new
value in the list of the combobox, otherwise another error will
occurr.
I sussed the NewData thing about a second after I pressed "send" ;-) but I'm
still not sure what you mean by "Access is expecting to see the new value in
the list of the combobox" - isn't that what acDataErrAdded is supposed to
do? I've tried requerying the combo but that's not it.

Thanks.
Keith.
Feb 7 '07 #4
"Keith Wilby" <he**@there.comwrote in message
news:45**********@glkas0286.greenlnk.net...

If anyone else is struggling with this, this would seem to be the answer:

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

I didn't realise I had to code the saving of the new data.

Keith.
Feb 7 '07 #5
"Keith Wilby" <he**@there.comwrote in message
news:45********@glkas0286.greenlnk.net...

This now works for me except that, when I click in my subform I get the
"write conflict" dialog box (this happens after the "Response =
acDataErrAdded" line executes). If I click "save changes" I get an error
stating that the save wouldn't work because of related records in another
table. If press ESC then all is well. I'm totally at a loss as to what's
happening here, can anyone suggest things to check?

Thanks.
Keith.
Feb 7 '07 #6
On Feb 7, 4:05 am, "Keith Wilby" <h...@there.comwrote:
"Keith Wilby" <h...@there.comwrote in message

news:45**********@glkas0286.greenlnk.net...
Firstly, the message box displays the *old* value instead of the one I
type in.

OK sussed that out (".Text" qualifier required) but still struggling with
the other issue.
Keith:
Please publish your full code so we can see what's happening.

Jana

Feb 7 '07 #7
"Jana" <Ba********@gmail.comwrote in message
news:11**********************@l53g2000cwa.googlegr oups.com...
>
Hi Jana, here's the code in its entirity - it now works but note the
commented line Me.Undo:

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

If MsgBox("You entered " & NewData & " - is this the manager's network logon
ID?", vbYesNo, "Confrim Manager ID") = vbYes Then
Dim rs As DAO.Recordset, strCriteria As String
Set rs = Me.RecordsetClone
strCriteria = "[SurveyTakenID] = " & Me.txtSurveyTakenID
With rs
.FindFirst strCriteria
.Edit
![ManagerName] = NewData
.Update
End With
rs.Close
Set rs = Nothing
Response = acDataErrAdded
Me.Undo 'I've no idea why this is needed, but omiting it generates a
"write confilct" error when you click a subform
Else
Response = acDataErrContinue
Me.cboManagerName.Undo
End If

End Sub

Regards,
Keith.
Feb 7 '07 #8
On Feb 7, 8:46 am, "Keith Wilby" <h...@there.comwrote:
"Jana" <Bauer.J...@gmail.comwrote in message

news:11**********************@l53g2000cwa.googlegr oups.com...

Hi Jana, here's the code in its entirity - it now works but note the
commented line Me.Undo:

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

If MsgBox("You entered " & NewData & " - is this the manager's network logon
ID?", vbYesNo, "Confrim Manager ID") = vbYes Then
Dim rs As DAO.Recordset, strCriteria As String
Set rs = Me.RecordsetClone
strCriteria = "[SurveyTakenID] = " & Me.txtSurveyTakenID
With rs
.FindFirst strCriteria
.Edit
![ManagerName] = NewData
.Update
End With
rs.Close
Set rs = Nothing
Response = acDataErrAdded
Me.Undo 'I've no idea why this is needed, but omiting it generates a
"write confilct" error when you click a subform
Else
Response = acDataErrContinue
Me.cboManagerName.Undo
End If

End Sub

Regards,
Keith.
Keith:

I may be having an off day, but I am really confused. You are using a
bound form with a combo box on it. You then use the combo box to add
a record to the data that your form is already bound to. Thus, you
are entering two instances of the same data into your data source.
Once is done through the form, the other is done through your code.
This is causing the error, as you're writing twice to the same data
set. I think that's why the Me.Undo fixes your issue. You're undoing
the record made/changed by the form. My confusion lies in your use of
the combo box. What is the Row Source of your combo box? Is it
always blank, meaning that the NotInList event is triggered every time
someone enters a record? What, exactly, are you trying to accomplish
with this combo box?

Here's some background on form basics: A form that's bound to data
(meaning it's Record Source property is not blank) is ALREADY writing
to your data when you complete the fields. So, creating a form based
on a table or a writeable query is basically a pretty way for you to
type in new records or to edit existing ones. No different than
typing directly in the table, just prettier and with the ability to
validate data, etc.

A combo box is usually linked to a different data source than your
form is, unless you're using it to locate a record in your form. For
instance, say you have a movie rental business. Your database would
have a table of customers, a table of movies, and a table of the
movies a customer has rented out. Each customer has an ID and each
movie has an ID. One customer can rent multiple movies. You don't
want to force your employees to memorize all the customer and movie
IDs, so you create a form called Customers, and a subform called
MoviesRented. On your Customers form, you have an unbound combo box
that lets you type in a customer's name. When you exit that combo
box, it finds and displays the customer's record. Now, on your
MoviesRented subform, you have a combo box that is linked to your
table of movies. Your employee enters the name of the movie, and the
combo box then grabs the ID that matches that title and adds it to the
table of rented movies.

Usually, the NotInList event is used to add a record to the Row Source
of a combo box. So, in our movie database example, if an employee
entered a movie title that wasn't in the list, you could give them the
option to add the movie to the movies table and then make it instantly
available in the combo box for them to select it for the customer to
rent. That would also make the title available to other employees who
might be renting out the same title.

I hope this rambling message helps you to sort out what you need to do
on your end, or at least guide you to new questions to ask. Let me
know the overall idea of what you're trying to do, and perhaps we can
steer you in the right direction.

HTH,
Jana

Feb 7 '07 #9
Keith Wilby wrote:
"Jana" <Ba********@gmail.comwrote in message
news:11**********************@l53g2000cwa.googlegr oups.com...

Hi Jana, here's the code in its entirity - it now works but note the
commented line Me.Undo:

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

If MsgBox("You entered " & NewData & " - is this the manager's network logon
ID?", vbYesNo, "Confrim Manager ID") = vbYes Then
Dim rs As DAO.Recordset, strCriteria As String
Set rs = Me.RecordsetClone
strCriteria = "[SurveyTakenID] = " & Me.txtSurveyTakenID
With rs
.FindFirst strCriteria
.Edit
![ManagerName] = NewData
.Update
End With
rs.Close
Set rs = Nothing
Response = acDataErrAdded
Me.Undo 'I've no idea why this is needed, but omiting it generates a
"write confilct" error when you click a subform
Else
Response = acDataErrContinue
Me.cboManagerName.Undo
End If

End Sub

Regards,
Keith.

For some odd reason the .edit would appear to conflict with an "add".

Since you aren't adding a value, maybe
Response = acDataErrAdded
should be
Response = acDataErrContinue

Typically you are adding a value to a list, not changing the value in
the list.
Feb 7 '07 #10
"Jana" <Ba********@gmail.comwrote in message
news:11**********************@k78g2000cwa.googlegr oups.com...
>
I may be having an off day, but I am really confused.
I can assure you that the off day and confusion are all mine!
You are using a
bound form with a combo box on it. You then use the combo box to add
a record to the data that your form is already bound to. Thus, you
are entering two instances of the same data into your data source.
Once is done through the form, the other is done through your code.
This is causing the error, as you're writing twice to the same data
set. I think that's why the Me.Undo fixes your issue. You're undoing
the record made/changed by the form.
This is all too painfully obvious now that you have mentioned it.
My confusion lies in your use of
the combo box. What is the Row Source of your combo box? Is it
always blank, meaning that the NotInList event is triggered every time
someone enters a record? What, exactly, are you trying to accomplish
with this combo box?
The row source is [ManagerName]. What I'm trying to do is allow users to
enter text that is not in the list, but prompt them with an "are you sure"
message box. This is the first time I've ever used the OnNotInList event.
>
Here's some background on form basics: A form that's bound to data
(meaning it's Record Source property is not blank) is ALREADY writing
to your data when you complete the fields. So, creating a form based
on a table or a writeable query is basically a pretty way for you to
type in new records or to edit existing ones. No different than
typing directly in the table, just prettier and with the ability to
validate data, etc.

A combo box is usually linked to a different data source than your
form is, unless you're using it to locate a record in your form. For
instance, say you have a movie rental business. Your database would
have a table of customers, a table of movies, and a table of the
movies a customer has rented out. Each customer has an ID and each
movie has an ID. One customer can rent multiple movies. You don't
want to force your employees to memorize all the customer and movie
IDs, so you create a form called Customers, and a subform called
MoviesRented. On your Customers form, you have an unbound combo box
that lets you type in a customer's name. When you exit that combo
box, it finds and displays the customer's record. Now, on your
MoviesRented subform, you have a combo box that is linked to your
table of movies. Your employee enters the name of the movie, and the
combo box then grabs the ID that matches that title and adds it to the
table of rented movies.

Usually, the NotInList event is used to add a record to the Row Source
of a combo box. So, in our movie database example, if an employee
entered a movie title that wasn't in the list, you could give them the
option to add the movie to the movies table and then make it instantly
available in the combo box for them to select it for the customer to
rent. That would also make the title available to other employees who
might be renting out the same title.

I hope this rambling message helps you to sort out what you need to do
on your end, or at least guide you to new questions to ask. Let me
know the overall idea of what you're trying to do, and perhaps we can
steer you in the right direction.
Did you hear that? That was the sound of a huge penny dropping in my head
;-)

I'm fairly well versed in how to use db objects but my confusion has stemmed
from using a select distinct on [ManagerName] in the *main* table as the
combo's row source instead of using a lookup. Quite obviously (to me now) I
should be using that code to add the new data to a lookup. Many thanks for
your help, you've certainly cleared the trees so that I can see the wood.
Ironically I did originally have a lookup table but I wrongly thought that
this method didn't need one.

Regards,
Keith.
Feb 8 '07 #11
"salad" <oi*@vinegar.comwrote in message
news:Jg******************@newsread2.news.pas.earth link.net...
>
Typically you are adding a value to a list, not changing the value in the
list.
Thanks for your response salad but it was just me being a thicko (see my
response to Jana).

Regards,
Keith.
Feb 8 '07 #12
On Feb 8, 1:30 am, "Keith Wilby" <h...@there.comwrote:
"Jana" <Bauer.J...@gmail.comwrote in message

news:11**********************@k78g2000cwa.googlegr oups.com...
I may be having an off day, but I am really confused.

I can assure you that the off day and confusion are all mine!
You are using a
bound form with a combo box on it. You then use the combo box to add
a record to the data that your form is already bound to. Thus, you
are entering two instances of the same data into your data source.
Once is done through the form, the other is done through your code.
This is causing the error, as you're writing twice to the same data
set. I think that's why the Me.Undo fixes your issue. You're undoing
the record made/changed by the form.

This is all too painfully obvious now that you have mentioned it.
My confusion lies in your use of
the combo box. What is the Row Source of your combo box? Is it
always blank, meaning that the NotInList event is triggered every time
someone enters a record? What, exactly, are you trying to accomplish
with this combo box?

The row source is [ManagerName]. What I'm trying to do is allow users to
enter text that is not in the list, but prompt them with an "are you sure"
message box. This is the first time I've ever used the OnNotInList event.


Here's some background on form basics: A form that's bound to data
(meaning it's Record Source property is not blank) is ALREADY writing
to your data when you complete the fields. So, creating a form based
on a table or a writeable query is basically a pretty way for you to
type in new records or to edit existing ones. No different than
typing directly in the table, just prettier and with the ability to
validate data, etc.
A combo box is usually linked to a different data source than your
form is, unless you're using it to locate a record in your form. For
instance, say you have a movie rental business. Your database would
have a table of customers, a table of movies, and a table of the
movies a customer has rented out. Each customer has an ID and each
movie has an ID. One customer can rent multiple movies. You don't
want to force your employees to memorize all the customer and movie
IDs, so you create a form called Customers, and a subform called
MoviesRented. On your Customers form, you have an unbound combo box
that lets you type in a customer's name. When you exit that combo
box, it finds and displays the customer's record. Now, on your
MoviesRented subform, you have a combo box that is linked to your
table of movies. Your employee enters the name of the movie, and the
combo box then grabs the ID that matches that title and adds it to the
table of rented movies.
Usually, the NotInList event is used to add a record to the Row Source
of a combo box. So, in our movie database example, if an employee
entered a movie title that wasn't in the list, you could give them the
option to add the movie to the movies table and then make it instantly
available in the combo box for them to select it for the customer to
rent. That would also make the title available to other employees who
might be renting out the same title.
I hope this rambling message helps you to sort out what you need to do
on your end, or at least guide you to new questions to ask. Let me
know the overall idea of what you're trying to do, and perhaps we can
steer you in the right direction.

Did you hear that? That was the sound of a huge penny dropping in my head
;-)

I'm fairly well versed in how to use db objects but my confusion has stemmed
from using a select distinct on [ManagerName] in the *main* table as the
combo's row source instead of using a lookup. Quite obviously (to me now) I
should be using that code to add the new data to a lookup. Many thanks for
your help, you've certainly cleared the trees so that I can see the wood.
Ironically I did originally have a lookup table but I wrongly thought that
this method didn't need one.

Regards,
Keith.- Hide quoted text -

- Show quoted text -
Keith:

Very glad to hear that my ramblings were understood and helpful ;-)

Jana

Feb 8 '07 #13
Keith Wilby wrote:
"salad" <oi*@vinegar.comwrote in message
news:Jg******************@newsread2.news.pas.earth link.net...
>>Typically you are adding a value to a list, not changing the value in the
list.


Thanks for your response salad but it was just me being a thicko (see my
response to Jana).
Not a prob. I was confused, wondering what you were trying to do.
Regards,
Keith.

Feb 8 '07 #14

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Andy Read | last post by:
Hello all, I have the requirement to produce source code that produces an object hierarchy. Example: Root | Folder 1
18
by: Christopher W. Douglas | last post by:
I am writing a VB.NET application in Visual Studio 2003. I have written a method that handles several events, such as closing a form and changing the visible status of a form. I have some code...
8
by: Mark | last post by:
Hi, I'm looking for some ideas on how to build a very simple Event processing framework in my C++ app. Here is a quick background ... I'm building a multithreaded app in C++ (on Linux) that...
3
by: MaryO | last post by:
I was hoping someone could help me with the OnNotInList function from Access. I downloaded this code below. I keep getting an error on the fAddToList – Sub or Function not defined. Please help...
13
by: Charles Law | last post by:
Mr "yEaH rIgHt" posted the following link about a week ago in answer to my question about removing event handlers. > http://www.vbinfozine.com/t_bindevt.shtml Following on from that post, the...
12
by: Jack Russell | last post by:
My unstanding of all VB up to and including vb6 is that an event could not "interrupt" itself. For instance if you had a timer event containing a msgbox then you would only get one message. ...
41
by: JohnR | last post by:
In it's simplest form, assume that I have created a usercontrol, WSToolBarButton that contains a button. I would like to eventually create copies of WSToolBarButton dynamically at run time based...
9
by: jeff | last post by:
New VB user...developer... Situation...simplified... - I want to wrap a pre and post event around a system generated where the pre-event will always execute before the system event and the...
19
by: Daniela Roman | last post by:
Hello, I try to fire an event under a button click event and maybe anybody can give a clue please. I have let's say a WEB grid with PageIndexChanged event: private void...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.