OnNotInList Event

[Keith Wilby]

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

If MsgBox("You entered " & Me.cboManagerNa me _
& " - is this the manager's network logon ID?", vbYesNo, "Confrim
Manager ID") = vbYes Then
Response = acDataErrAdded
Else
Response = acDataErrContin ue
Me.cboManagerNa me.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.

[storrboy]

Re: OnNotInList Event

On Feb 7, 5:49 am, "Keith Wilby" <h...@there.com wrote:

I've never used this event before and I'm struggling. I have this code in
it:
>
If MsgBox("You entered " & Me.cboManagerNa me _
& " - is this the manager's network logon ID?", vbYesNo, "Confrim
Manager ID") = vbYes Then
Response = acDataErrAdded
Else
Response = acDataErrContin ue
Me.cboManagerNa me.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.

[Keith Wilby]

Re: OnNotInList Event

"Keith Wilby" <here@there.com wrote in message
news:45c9ab78$1 _1@glkas0286.gr eenlnk.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 Wilby]

Re: OnNotInList Event

"storrboy" <storrboy@sympa tico.cawrote in message
news:1170846240 .043009.211840@ h3g2000cwc.goog legroups.com...

On Feb 7, 5:49 am, "Keith Wilby" <h...@there.com wrote:

>I've never used this event before and I'm struggling. I have this code
>in
>it:
>>
>If MsgBox("You entered " & Me.cboManagerNa me _
> & " - is this the manager's network logon ID?", vbYesNo, "Confrim
>Manager ID") = vbYes Then
> Response = acDataErrAdded
>Else
> Response = acDataErrContin ue
> Me.cboManagerNa me.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.

[Keith Wilby]

Re: OnNotInList Event

"Keith Wilby" <here@there.com wrote in message
news:45c9b850$1 _1@glkas0286.gr eenlnk.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.

[Keith Wilby]

Re: OnNotInList Event

"Keith Wilby" <here@there.com wrote in message
news:45c9c7c8_1 @glkas0286.gree nlnk.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.

[Jana]

Re: OnNotInList Event

On Feb 7, 4:05 am, "Keith Wilby" <h...@there.com wrote:

"Keith Wilby" <h...@there.com wrote in message
>
news:45c9ab78$1 _1@glkas0286.gr eenlnk.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

[Keith Wilby]

Re: OnNotInList Event

"Jana" <Bauer.Jana@gma il.comwrote in message
news:1170861637 .200868.307490@ l53g2000cwa.goo glegroups.com.. .

>

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

Private Sub cboManagerName_ NotInList(NewDa ta 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.RecordsetClo ne
strCriteria = "[SurveyTakenID] = " & Me.txtSurveyTak enID
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 = acDataErrContin ue
Me.cboManagerNa me.Undo
End If

End Sub

Regards,
Keith.

[Jana]

Re: OnNotInList Event

On Feb 7, 8:46 am, "Keith Wilby" <h...@there.com wrote:

"Jana" <Bauer.J...@gma il.comwrote in message
>
news:1170861637 .200868.307490@ l53g2000cwa.goo glegroups.com.. .
>
Hi Jana, here's the code in its entirity - it now works but note the
commented line Me.Undo:
>
Private Sub cboManagerName_ NotInList(NewDa ta 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.RecordsetClo ne
strCriteria = "[SurveyTakenID] = " & Me.txtSurveyTak enID
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 = acDataErrContin ue
Me.cboManagerNa me.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

[salad]

Re: OnNotInList Event

Keith Wilby wrote:

"Jana" <Bauer.Jana@gma il.comwrote in message
news:1170861637 .200868.307490@ l53g2000cwa.goo glegroups.com.. .
>
Hi Jana, here's the code in its entirity - it now works but note the
commented line Me.Undo:
>
Private Sub cboManagerName_ NotInList(NewDa ta 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.RecordsetClo ne
strCriteria = "[SurveyTakenID] = " & Me.txtSurveyTak enID
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 = acDataErrContin ue
Me.cboManagerNa me.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 = acDataErrContin ue

Typically you are adding a value to a list, not changing the value in
the list.

[Keith Wilby]

Re: OnNotInList Event

"Jana" <Bauer.Jana@gma il.comwrote in message
news:1170870349 .408332.174800@ k78g2000cwa.goo glegroups.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.

[Keith Wilby]

Re: OnNotInList Event

"salad" <oil@vinegar.co mwrote in message
news:Jgoyh.2008 0$yx6.9814@news read2.news.pas. earthlink.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.

[Jana]

Re: OnNotInList Event

On Feb 8, 1:30 am, "Keith Wilby" <h...@there.com wrote:

"Jana" <Bauer.J...@gma il.comwrote in message
>
news:1170870349 .408332.174800@ k78g2000cwa.goo glegroups.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

[salad]

Re: OnNotInList Event

Keith Wilby wrote:

"salad" <oil@vinegar.co mwrote in message
news:Jgoyh.2008 0$yx6.9814@news read2.news.pas. earthlink.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.
>
>