Run Macro once a year.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to run a macro to update a table with carryover vacation time from one
year to the next. I want this to run once when the database is opened,
update the table and then not run again until the next year. The problem is
that it isn't a database that is opened everyday so I can't make it run on a
specific date and I also don't know the first time it will be opened each
year.

Any help would be greatly appreciated...I'm not a programmer but understand
enough to make changes to existing code.

Thanks in advance.
 
Ann,

If I understand you correctly, you want this macro to run the first time
the database is opened in any given calendar year.

First of all, make a simple table with one field, Date/Time data type.
This will just have one record which is the date of the last carryover
vacation update. Let's call the table VacsUpdated and the field UpdateDate.

Then make a macro, which you will assign on some appropriate event in
your database... often this is the On Open event property of a form that
is opened whenever the database application is opened. In the Condition
of this macro (if you can't see a Condition column in the macro design
window, select it from the View menu), you can put the equivalent of this...
DLookup("Year([UpdateDate])","VacsUpdated")<Year(Date())

Add the actions to the macro to process your vacation time update, it
sounds like you already have that sorted. In the Condition of each
action after the first, put:
...
which will cause the Condition to apply to all actions in the macro.
Then, at the end of the macro you will need to use an OpenQuery action
or a RunSQL action, to run an Update Query to reset the UpdateDate in
the VacsUpdated table to the current date, and then that's that taken
care of for another year.
 
Steve,

Thanks so much for your help. I understood what you were having me do right
away. I've already added what I needed according to your instructions and it
worked great!!

Thanks again...Ann

Steve Schapel said:
Ann,

If I understand you correctly, you want this macro to run the first time
the database is opened in any given calendar year.

First of all, make a simple table with one field, Date/Time data type.
This will just have one record which is the date of the last carryover
vacation update. Let's call the table VacsUpdated and the field UpdateDate.

Then make a macro, which you will assign on some appropriate event in
your database... often this is the On Open event property of a form that
is opened whenever the database application is opened. In the Condition
of this macro (if you can't see a Condition column in the macro design
window, select it from the View menu), you can put the equivalent of this...
DLookup("Year([UpdateDate])","VacsUpdated")<Year(Date())

Add the actions to the macro to process your vacation time update, it
sounds like you already have that sorted. In the Condition of each
action after the first, put:
...
which will cause the Condition to apply to all actions in the macro.
Then, at the end of the macro you will need to use an OpenQuery action
or a RunSQL action, to run an Update Query to reset the UpdateDate in
the VacsUpdated table to the current date, and then that's that taken
care of for another year.

--
Steve Schapel, Microsoft Access MVP

I need to run a macro to update a table with carryover vacation time from one
year to the next. I want this to run once when the database is opened,
update the table and then not run again until the next year. The problem is
that it isn't a database that is opened everyday so I can't make it run on a
specific date and I also don't know the first time it will be opened each
year.

Any help would be greatly appreciated...I'm not a programmer but understand
enough to make changes to existing code.

Thanks in advance.
Click to expand...
Click to expand...
 
Back
Top