Rocky Smolin
rockysmolin at bchacc.com
Thu Jan 15 13:45:13 CST 2015
Thank you all who rang in on this problem. I fixed it, but didn’t solve
it.
I rolled my own - that is, I abandoned the NotInList event and in the After
Update event of the combo box I did a Dcount of the occurrences of the data
the user entered. If zero I asked if they wanted to add that entry to the
list.
If yes, saved the record (which has the effect of saving it to the combo box
list, since the Row Source of the combo box is a SELECT DISTINCT on the
table which the form is bound to), requeried the combo box walla! all’s
good. If no, UNDO’d the combo box entry.
The lines of code was about the same as the Not In List event. Access has
taught me not to solve every problem but top go around it - many times it is
faster.
R
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David McAfee
Sent: Wednesday, January 14, 2015 9:53 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Not In List Event Not Triggering
A very simple not in list sample:
LimitToList : YES
On Not In List : [Event Procedure]
Row Source: SELECT tblSomeTable .SomeField FROM tblSomeTable ;
Private Sub cboMyComboBox_NotInList(NewData As String, Response As Integer)
Dim strsql As String, x As Integer x = MsgBox("Do you want to add this value
to the list?", vbYesNo) If x = vbYes Then
strsql = "Insert Into tblSomeTable ([SomeField]) values ('" & NewData &
"')"
'MsgBox strsql
CurrentDb.Execute strsql, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub
On Wed, Jan 14, 2015 at 8:56 AM, Jurgen Welz <jwelz at hotmail.com> wrote:
> I meant acDataErrAdded, not acDataErrContinue. Sorry for any confusion.
>
>
> Ciao
> Jürgen Welz
> Edmonton, Alberta
>
> > From: jwelz at hotmail.com
> > To: accessd at databaseadvisors.com
> > Date: Wed, 14 Jan 2015 09:32:07 -0700
> > Subject: Re: [AccessD] Not In List Event Not Triggering
> >
> > The NotInList event does not fire if you've set the limit to list
> property to No.
> >
> > Put a break (F9 key on the code line defining the Sub procedure) on
> > the
> ones where you think the code is running.
> >
> > Your description tells me that, because you have set the limit to
> > list
> to No on the other combos, the data is simply going into the table and
> is available next time you open the form. The reason it's going in is
> not due to the running of some code. It's not running anywhere.
> >
> > In the combo you are having problems with, the bound field is not
> > the
> first visible field hence the error you're not getting with the
> others that simply allow the data to be added without the NIL firing.
> What the not in list event does is use the error as a trigger to run
> some code and return Access a constant that tells it to ignore the
> error, "Response = acDataErrContinue"
> >
> > Ciao
> >
> > Jürgen Welz
> >
> > Edmonton, Alberta
> >
> > > From: rockysmolin at bchacc.com
> > > To: accessd at databaseadvisors.com
> > > Date: Wed, 14 Jan 2015 07:04:24 -0800
> > > Subject: Re: [AccessD] Not In List Event Not Triggering
> > >
> > > Something IS different Charlotte but I'll be dipped if I can find it.
> The
> > > combo is bound. And the value entered becomes part of the combo
> > > box
> list
> > > when the form is reopened and the combo box is requeried. The
> > > requery
> is at
> > > the end of the NIL event in all those procedures. So they work
> > > just
> dandy.
> > >
> > > R
> > >
> > >
> > > -----Original Message-----
> > > From: accessd-bounces at databaseadvisors.com
> > > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> > > Charlotte
> Foust
> > > Sent: Wednesday, January 14, 2015 6:14 AM
> > > To: Access Developers discussion and problem solving
> > > Subject: Re: [AccessD] Not In List Event Not Triggering
> > >
> > > It appears the Not In List event is firing properly, so check any
> > > even slight differences between this combobox and the others. Is
> > > it bound
> or
> > > unbound? Is another event interfering? I know you said the others
> were set
> > > up the same way, but obviously something is different.
> > >
> > > Charlotte
> > > On Jan 13, 2015 2:46 PM, "Rocky Smolin" <rockysmolin at bchacc.com>
> wrote:
> > >
> > > > Dear List:
> > > >
> > > > This one's got me stumped. A combo box has a not in list event.
> > > > Entering a value not in the list does NOT trigger the event.
> > > >
> > > > The field before this one is also a combo box with a Not In List
> event.
> > > > It's working perfectly.
> > > >
> > > > I can't see ANY difference between the two but there must be
> something.
> > > >
> > > > I did a decompile just to cover that option. No cigar.
> > > >
> > > > Any idea - no matter how outlier - is welcomed.
> > > >
> > > > MTIA
> > > >
> > > > Rocky
> > > >
> > > > --
> > > > AccessD mailing list
> > > > AccessD at databaseadvisors.com
> > > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > > Website: http://www.databaseadvisors.com
> > > >
> > > --
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > >
> > > --
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com