Run Macro once a year.

[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.

 

[Steve Schapel]

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.

 

[Guest]

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

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.

 

[Steve Schapel]

Very good, Ann.