It shouldn't be necessary, but if it works for you, go with it.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"Ray C" wrote in message
...
Hi Arvin
In your demo code, you have the sub

Private Sub cmb_Find_Box_3_AfterUpdate()

Me.cmb_Find_Box_3.Requery

End Sub
in your "frmEvents" (I changed the name of the Combo Box to fit my code)
When I integrated your code into mine. I found that the Record was
generated
in the table OK but I could not find the record afterwards using my normal
enquiry.
What I needed to do was to shut down and re open the whole database and
only
then could I find the new record with my enquiry.

What I have done is tto add the following line

Private Sub cmb_Find_Box_3_AfterUpdate()

Me.cmb_Find_Box_3.Requery
ME.Requery
End Sub
and this seems to have sorted the problem out. Does this sound right to
you?

Thanks and regards RayC


"Arvin Meyer [MVP]" wrote:

In the module there is the line:

Response = acDataErrAdded

That adds the entry into your combo box. You DO NOT need to requery the
combo. I suggest that you use the demo code and forms at:

http://accessmvp.com/Arvin/NotInListDemo.zip

To see how it works, then copy the exact code into each of your forms and
change the names of the form and fields to match yours. NewData and
Response
do not get changed. They are intrinsic to the code.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access

"Ray C" wrote in message
...
Hi Arvin, Sorry for my Typo leading you astray, the code is "New Data"
and
not "strNewData". in either event, the new data that is not currently a
record is passed to the form that you open in your code and does get
appended
to the table as a new record. My issie is with the Requery command and
where
that goes. as I tried nto explain in the body of my last message. I
seem
to
get errors wherever I put the "Requery" comand and without running
"Requery"
somewhere after closing the form that is used to enter the new record
information, the new information (that is in the table) is not shown in
the
combo box.

Hope that this makes sense.

Ray C

"Arvin Meyer [MVP]" wrote:

strNewData should be NewData
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"Ray C" wrote in message
...
Hi Arvin, still struggling I am afraid. Although I now have the new
record
being added to the table correctly (using your code), I get errors
when
i
try
to run the requery

Private Sub cmb_Find_Box_3_NotInList(NewData As String, Response As
Integer)

AddToList "frm_Store_Add", "StoreName", strNewData, Response '
Arvin
Mayer code
' Me.cmb_Find_Box_3.Requery
End Sub

I have the requery in the cmb_Find_Box_3_after update event but that
routine
does not get called (though it does in your test code???)
If I enter some dats that is not in the table (more correctly the
list
that
is based on the table), the NotInList routine kicks in and the
program
happilt toddles off to generate the new record (using your code).
When
it
returns from that task, you will see that i have
"Me.cmb_Find_Box_3.Requery.
However when the program gets to that line I get an error that says
"Run-Time
Error 2118" "You must save thr currentit feld before you run the
requery
action"
If I take out that line, I get an error that says "The Text you
entered
is
not an item in the list"
Either way, the table shows that my New Record is added but the
record
does
not show as a new item in the Combo drop Down list.
Clearly it is a problem with the requery commant but why does your
test
code
work ok bur mine gives problems? I guess the answer to that is just
that
you
are the expert and i am the novice.
Thanks Arvin

Ray C




"Arvin Meyer [MVP]" wrote:

Ray,

The example at:

http://accessmvp.com/Arvin/NotInListDemo.zip

is eaxctly what you want. Try importing everything into your
database
and
changing all the names to fit with your names.

You won't be able to do both fields with a single combo box, so
you'll
need
2 of them. I recommend getting 1 to work, then copying everything
with
the
new names. The generic code function I listed is perfect for you,
because
you can make the same NotInList function work with both combo
boxes.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access

"Ray C" wrote in message
...
Hi Arvin, I am not sure if I explained my problem badly or if I
am
just
too
dumb to understand your solution but I have tried the code that
you
provided
and I can not get it to generate a new Record in my Table.
Please let me try to explain my problem again and i would be
pleased
if
you
could give me any pointers as to where i am going wrong in my
understanding
of your answer.

I have a pretty standard table holding property information
(Address
etc)
but eac property has a NameAddress info. The Table holdsname and
addresswith
the Name (normaly the Town where it is) and also a Property
Number
(provided
by the Owner). The table has an "Auto Number Index" and holds
other
information about the property. Because the user may wish to
search
the
Table
by either the property name or the property number, I have two
combo
boxes
that providr drop down's for the user to select as needed. Once
the
user
has
selected the required property "Name" or the required property
"Number" i
have a ruotine that searches through the Address Table, finds the
record
and
displays all the fields in that record. All of that is fine.
What I am wanting to do is for the user to be given the ability
to
Add
a
New
Record to the Table if the information they entered in the Combo
Box
is
not
found in the Address Table.

My Current attempt at a sollution to this is to use the notIn
List
event
to
branch off to a routine that should generate a new *Blank" record
that
will
be appended to the end of the adress Table, and show the user a
form
full
of
Blank Fields (with the exception of the information they
originally
searched
for). The usr should then fill in the blank fields before saving
(or
rejecting) the "New Record" appended to the end of the Table.

As I tried to explain originally I experimented with the "DoCmd
GoToRecord
acForm, meName, acNewRec", this does generate a new Record in the
Table
and
present the user with a series of blank Fields to complete but
any
information that is enterd into the blank fields is inserted into
the
previous record.

I hope that this helps.
Thanks for your patience
RayC

"Arvin Meyer [MVP]" wrote:

That depends upon the constant you use:

acDataErrDisplay - (Default) Displays the default message
to
the
user.
You can use this when you don't want to allow the user to add a
new
value
to
the combo box list.

acDataErrContinue - Doesn't display the default message to
the
user.
You can use this when you want to display a custom message to
the
user.
For
example, the event procedure could display a custom dialog box
asking
if
the
user wanted to save the new entry. If the response is Yes, the
event
procedure would add the new entry to the list and set the
Response
argument
to acDataErrAdded. If the response is No, the event procedure
would
set
the
Response argument to acDataErrContinue.

acDataErrAdded - Doesn't display a message to the user
but
enables
you to add the entry to the combo box list in the NotInList
event
procedure.
After the entry is added, Microsoft Access updates the list by
requerying
the combo box. Microsoft Access then rechecks the string against
the
combo
box list, and saves the value in the NewData argument in the
field
the
combo
box is bound to. If the string is not in the list, then
Microsoft
Access
displays an error message.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"Ray C" wrote in message
...
Hi Arvin, thanks for coming back to me, it is much
appreciated.

I don't think that I need the generic NotinList code, I have
that. I
think
I
need to understand if the NotinList code will add a new record
to
the
Table
or of it will modify the Drop Down List only. Forgive me
pleas, I
get
confused when dealing with Tables and Forms.
Thanks RayC


"Arvin Meyer [MVP]" wrote:

If you're looking for generic NotInList code:

http://www.datastrat.com/Code/NotInListCode.txt

or a demo of that code is at:

http://accessmvp.com/Arvin/NotInListDemo.zip
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"Ray C" wrote in message
...
Can anyone help point out what i am doing wrong Please?
I want to generata a new record and I get to that point by
having
the
user
enter a search for a record in a Table via a combo. When
the
record
is
Found
in the Table, no problem The user can view the Record, Edit
it,
etc.