Append Queries in a Macro

[NewMiMi]

I am working in Access 2003. I want to add a new employee number to multiple
tables by using a macro attached to a button. I have created the queries,
but am having trouble getting the macro set up correctly. I am have not used
macros lately.

Uner new Macro, my first action is OpenQuery. I provided the query name. As
the query runs you have to answer by pressing the "yes" button after the
popup says "You are about to run an append query that will modify data in
your table". After pressing "yes", you will get another popup that says "you
are about to append 1 row(s)". You would then press the "yes" button. How
do I make the macro append these 7 tables seamlessly. I have done this
before, many years ago and just can't seem to grasp it now.

Any help is appreciated.

Thank you.

 

[Steve Schapel]

NewMiMi,

Put a SetWarnings/No action in your macro, before the first of the OpenQuery
actions.

Wow, adding records with the same Employee number to 7 different tables!
That's very unusual - are you sure you have this set up correctly?

 

[NewMiMi]

Perfect. Works beatifully! Thank you so much. I probably do not have this
database set up to most programmers liking, but it works. I wrote this
database 8 years ago and am in the process of "tweaking" a few things and
adding much more information to the tables.

I have one last question. After the macro runs, I would like a message to
pop up and say "ID added". Is this possible? If it is not a simple thing to
do, I will forego.

Thanks again.

 

[Steve Schapel]

NewMiMi,

Well, you could simply put a MsgBox action at the end of the macro.
Probably ok, but of course does not actually test whether the queries
successfully appended the expected records.

 

[NewMiMi]

You have been a great help so far. I have a problem though. I have attached
the macro to a button on a form. The macro does not work on the first try on
the form, but works on the first try when you manually run the macro. The
macro will append the ID, from the form, on the second try. Any idea on why
this would happen? Again thank you so much for your help.

 

[Steve Schapel]

NewMiMi,

It is hard to know, really, since you haven't really given much detail about
what you are doing. But here's a guess... if you are entering a new record
on your form, and then running a query that depends on the value of field(s)
in the newly entered record, you may need to save the record first, since
the query is not able to access the values from that record until it is in
the table. Therefore, perhaps try to put a RunCommand/SaveRecord action at
the beginning of the macro.

--
Steve Schapel, Microsoft Access MVP

You have been a great help so far. I have a problem though. I have
attached
the macro to a button on a form. The macro does not work on the first try
on
the form, but works on the first try when you manually run the macro. The
macro will append the ID, from the form, on the second try. Any idea on
why
this would happen? Again thank you so much for your help.

__________ Information from ESET NOD32 Antivirus, version of virus signature database 4364 (20090824) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

 

[NewMiMi]

You are the man! It works correctly now. Again thank you.