By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 2,171 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

OnNotInList Event

P: n/a
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
Share this Question
Share on Google+
13 Replies


P: n/a
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

P: n/a
"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

P: n/a
"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

P: n/a
"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

P: n/a
"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

P: n/a
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

P: n/a
"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

P: n/a
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

P: n/a
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

P: n/a
"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

P: n/a
"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

P: n/a
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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.