Run macro after closing a table or file....

[Walt]

Hello all...

I am helping an VERY inexperienced user setup a table in a back end
MDB. They are data entry people and have no skill in running queries/
macros, etc so I'm hoping to keep this solution as automatic as
possible.

Essentiallt the user will be updating data in a single table and after
they finish the updating I would like to run a macro (in the same MDB)
to update data in other downstream tables.
Is it possible to design a macro in access to automatically run after
a user closes a table or even the file?

I am familiar with the autoexec macro that can run when the file
opens, but is there an option for when a table or file closes?

wdp

 

[Jerry Whittle]

If the data entry is done in a form, then it's possible to run a macro when
the form closes. Not so with a table, query, or even closing the database
(with an exception).

The exception would be a having a hidden form which would close before the
database closes. Even that could be a problem. Also what happens when the
user just powers off the machine or loses power? If it's critical that the
data gets updated in the other tables, it may not happen in something like a
power failure.

Also the fact that other tables need to be updated when records are added to
another table is a real worry. That should not need to happen with a well
designed relational database.

As you are inexperienced, I highly recommend getting some relational
database training or reading "Database Design for Mere Mortals" by Hernandez
before proceeding any further on this database.

 

[Walt]

Thanks for the response.
The new data is updated directly in the data table.
There are no forms in this mdb.
wdp


"You can run a macro on close event of the form!"

 

[Walt]

All I was asking for is how to trigger a macro to automatically do
other things (without actually clicking on the macro), not sure why
you jumped into insult/lecture mode about the design without having
any details?

The new data is modified by the user directly in the data table in a
format the user can easily understand.
There are currently no forms in this mdb. The design is simple -
table, couple queries and an output table.

If a table cannot be used to trigger a macro and only a form can, is
it possible to have the form open minimized using the AutoExec macro
(so the user doesn't have to) and when the file is closed, that would
trigger the closing of the form and trigger the macro to run?

wdp

ps. Access is a data management tool with broad capabilities far more
complex than the too-often mis-understood and oversimplified "must be
a relational database" format..

 

[Jerry Whittle]

I didn't mean to insult; rather to instruct which explains the lecture mode.

You can use Autoexec to open a form. In the form's On Open event you could
put some code like:

Me.Visible = False

On the On Close event you could run the macro. I know that this will work
with something like a Message Box; however, I'm not sure about a macro
running before the database closes. You may want to put in a delay using the
Timer control on the form.

As I said before, it surely won't work if there is a power failure or a user
gets inpatient and pulls the power plug.

As for your PS, I understand completely. People often use it for a
spreadsheet.

 

[Walt]

if i were you, instead of minimizing or hiding the form I'll use the formand
set the Default View property to Datasheet and set the Record Source to the
table/query.

NuBie - Thanks for this. Per your suggestion I made an autoexec to
open the form in datasheet so the user doesn't have to open it. Then
I set the Form properties to run a second macro "OnClose" (under event
tab in form properties).
That seems to work just great!

wdp

ps2 "People often use it for a spreadsheet. - Jery Whittle, Microsoft
Access MVP"
Just because you think someone uses it not as a "relationship
database" you call it a spreadsheet? In the real world Access is
much more free flowing than the restrictive tool MVP's appear to think
it is.....