Adding a new record in an autolookup query?

[Guest]

Right now I have a form based on an autolookup query such that when I type in
my VendorID, it pulls out all the information automatically if the vendor
already exists. It also allows me to edit and save all the existing
information for that vendor which is perfect. However, in the form, when I
click on add a new record (little box with an arrown and star at the bottom
of the form),a new form comes up and I enter a VendorID number which doesn't
currently exist in the database, but it won't allow me to save/add that new
reord since it says it doesn't recognize the VendorID (obviously since it's
new). It displays an error message.

Is there any way for Access to let me add a new record in a form that is
based on an autolookup query?!

Thank you for any help.

 

[Michel Walsh]

The basic principle is that you should see the record you add, but you don't
see records added by other users until you requery. Now, the problem is what
is defined as being 'you'? It is not that evident, since, as example, your
code is NOT you. So, your best chance to be recognized as 'you' is to use
the same form (recordset) to add the record as the form (recordset) you use
to see the record, or, through your code, to use DoCmd object (if you use a
form). It may happen that even if you do that, the engine will still see
your running code as someone else than 'you', and then, you really have to
requery recordset, or the form. Note that after you requery, the recordset
is placed back in the first "row" of the recordset, so you may have to
'check' the primary key value of the actual record, make the requery, then
try to move to the record having the saved value for its primary key value.

Does all that makes sense?

Vanderghast, Access MVP