I have a macro which allows me to enter a new record if a "not in list" error
occurs. However this often does not work usually and never at the beginning
of a session. The error message overrides and the process has to be stopped.
If I exit the form and re-enter, the table is updated and the new record gets
recorded. How can I make this work reliably?

You need to use VBA code for the Not In List event so that you can
"override" the built-in ACCESS message and allow the record to be added
without such message issues.

This web page shows one way to do that:
http://www.mvps.org/access/forms/frm0015.htm

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


"Wasim274" <(E-Mail Removed)> wrote in message
news:E7D7C959-ADEB-43B1-AB70-(E-Mail Removed)...
>I have a macro which allows me to enter a new record if a "not in list"
>error
> occurs. However this often does not work usually and never at the
> beginning
> of a session. The error message overrides and the process has to be
> stopped.
> If I exit the form and re-enter, the table is updated and the new record
> gets
> recorded. How can I make this work reliably?

Thank you that is helpful. However, I was really trying to use a macro. I
thought Access 2007 allowed "OnError" (next) to be used from a Macro...or
could the code you kindly suggested be run from a macro?


"Ken Snell [MVP]" wrote:

> You need to use VBA code for the Not In List event so that you can
> "override" the built-in ACCESS message and allow the record to be added
> without such message issues.
>
> This web page shows one way to do that:
> http://www.mvps.org/access/forms/frm0015.htm
>
> --
>
> Ken Snell
> <MS ACCESS MVP>
> http://www.accessmvp.com/KDSnell/
>
>
> "Wasim274" <(E-Mail Removed)> wrote in message
> news:E7D7C959-ADEB-43B1-AB70-(E-Mail Removed)...
> >I have a macro which allows me to enter a new record if a "not in list"
> >error
> > occurs. However this often does not work usually and never at the
> > beginning
> > of a session. The error message overrides and the process has to be
> > stopped.
> > If I exit the form and re-enter, the table is updated and the new record
> > gets
> > recorded. How can I make this work reliably?
>
>
>

Wasim,

Probably for anyone to provide more explicit help, we would need to know the
details of the macro you are using, and also the nature of the error you are
getting. Can you please supply this information?

--
Steve Schapel, Microsoft Access MVP


"Wasim274" <(E-Mail Removed)> wrote in message
news:E655490C-63CA-4438-8BF7-(E-Mail Removed)...
> Thank you that is helpful. However, I was really trying to use a macro. I
> thought Access 2007 allowed "OnError" (next) to be used from a Macro...or
> could the code you kindly suggested be run from a macro?
>
>



__________ Information from ESET Smart Security, version of virus signature database 4153 (20090613) __________

The message was checked by ESET Smart Security.

http://www.eset.com

The following macro is called from a "not in list" event from a combo box.
Set Warnings (next)
Echo (off)
Hourglass
SendKeys + {HOME}..Yes (selects entry)
RunCommand (Copy)
OpenForm (Form Name "Frm_composer"
View "Form"
Data Mode "Edit"
Window Mode "Normal")
SetWarnings (No)
GoToRecord (Object Type "Form", Object Name "Frm_composer", Record "New"
GoToControl ("Composer")
RunCommand (Paste)
SendKeys ({ENTER},No)
GoToControl ("RegComposer")

When this macro completes, the data enry form "Frm_composer" is ready on
screen with the new name in the text box ready to be registered but the error
message "The text you entered is not in the list" overrides. The name then
has to be deleted before progress can be made. If the main data entry box is
then closed and re-opened I find the name has been added to the list and will
no longer, therefore, trigger the "not in list "error.
The curious thing is that after a few of these in any one session, the
procedure starts to work as intended and allows the new name to be entered.

"Steve Schapel" wrote:

> Wasim,
>
> Probably for anyone to provide more explicit help, we would need to know the
> details of the macro you are using, and also the nature of the error you are
> getting. Can you please supply this information?
>
> --
> Steve Schapel, Microsoft Access MVP
>
>
> "Wasim274" <(E-Mail Removed)> wrote in message
> news:E655490C-63CA-4438-8BF7-(E-Mail Removed)...
> > Thank you that is helpful. However, I was really trying to use a macro. I
> > thought Access 2007 allowed "OnError" (next) to be used from a Macro...or
> > could the code you kindly suggested be run from a macro?
> >
> >
>
>
>
> __________ Information from ESET Smart Security, version of virus signature database 4153 (20090613) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
>
>