can "not in list" be used to start a macro to enter new records

[Wasim274]

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?

 

[Ken Snell [MVP]]

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

 

[Wasim274]

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?

 

[Steve Schapel]

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

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

 

[Wasim274]

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.