How do i get a Module to run on the last day of each month.

[Brendanpeek]

Hello all

I need to find a way to run a module on the last day of every month, But
without using the "On Timer" event. Since it is all ready being used. The
Module is called KPICalStep1.

Thank you for your time.

 

[Chris O'C via AccessMonster.com]

You can't run a module, it's a container object. You can run the procedures
contained in the modules but you have to know the names of those procedures.

Chris
Microsoft MVP

 

[Chris O'C via AccessMonster.com]

Once you have a list of procedures you want to run only on the last day of
the month, create a form, set it as your startup form, and in the form_open
event put this code:

if (Date() = DateSerial(Year(Date()),(Month(Date())+1),0)) then
call function1
call function2
'etc
end if

docmd.openform "switchboard"
docmd.Close acForm, me.name


Every time the db is opened it opens the form, checks if today is the last
day of the month and if it is, runs the functions, then opens the switchboard
and closes the startup form. If it isn't the last day of the month it opens
the switchboard and closes the startup form.

Chris
Microsoft MVP

 

[John Spencer]

1) Does the database get opened every day of the month?
2) Do you want the code to only run once on the last day or does it have to
run multiple times?

I will assume
++ that the database is not running continuously
++ you only want to run KPICalStep1 once per period
++ that the database may not always be opened on the last day of the month
when the last day of the month falls on a weekend or Holiday.

I would create a table with a field to record the next execution day.

When the database opens (assuming you have some form that always opens) use
the form's On load event to check to see if the current date is greater than
the next execution date.

If so, run your code and as part of the code set the next execution date to
the next end of the month.


Something like this would set the date for the end of the following month.

ExecutionDate = DateSerial(Year(ExecutionDate),Month(ExecutionDate)+1,0)



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Brendanpeek]

OK got ya about the modules. i already have a swictboard which is always open
and the event timer is used as well as the form open event, plus the Db never
gets closed or opened, once i have finished designing the Db and open it, it
will never be closed.

 

[Brendanpeek]

Please Refer to my second post.

1) Does the database get opened every day of the month?
2) Do you want the code to only run once on the last day or does it have to
run multiple times?

I will assume
++ that the database is not running continuously
++ you only want to run KPICalStep1 once per period
++ that the database may not always be opened on the last day of the month
when the last day of the month falls on a weekend or Holiday.

I would create a table with a field to record the next execution day.

When the database opens (assuming you have some form that always opens) use
the form's On load event to check to see if the current date is greater than
the next execution date.

If so, run your code and as part of the code set the next execution date to
the next end of the month.


Something like this would set the date for the end of the following month.

ExecutionDate = DateSerial(Year(ExecutionDate),Month(ExecutionDate)+1,0)



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Chris O'C via AccessMonster.com]

Access is the wrong tool to use for an app that must run 24/7. You shouldn't
back it up unless it's closed. To compact it, you close the db and reopen
the new file. In your scenario there'd be no good backups, no
compact/repairs. Disaster awaits you.

Chris
Microsoft MVP

 

[Brendanpeek]

ok cheers guess i need to rethink the whole thing.

 

[Rick Brandt]

ok cheers guess i need to rethink the whole thing.

Make another MDB file (perhaps from a copy of your current one) and set it
up so that all it does when you open it is run your functions and then
closes itself. Set up your windows scheduler to open that file on the last
day of each month. Use your current file for everything else you need to
do.