auto run macro if 1st mon of month

[_Bigred]

(Access 2000)


Does anyone know how to automatically run a macro, when the db is opened if
it is the 1st monday of each month?

I would like the macro to print out several reports automatically if it is
the 1st monday of the month and the db is opened?

TIA,
_Bigred

 

[Steve Schapel]

Bigred,

The short answer is that you need to put a condition in your macro like
this...
Weekday(Date())=2 And Day(Date())<8

However, you may possibly need to cater to these situations:
a) the first monday is a holiday and the database is not used that day.
b) the database might be opened and closed more than once each day, and
you don't want the reports printed every time.

 

[_Bigred]

Hello Steve,

The first point you make about holidays is not a problem, because even on
holidays we will be running this macro (if it works). But do you have any
ideas on how to get around the macro running (printing all reports) every
time you open the db, if it is the 1st monday of the month?

Any ideas would be greatly appreciated, I will try the condition you gave to
see how it works.
Thanks,
_Bigred

 

[Steve Schapel]

Bigred,

One way to handle this...
1. Make a simple table, one text field, one record, and put last
month's date in there in format 0401
2. Make an Update Query which will update the value of this field to
Format(Date(),"yymm")
3. Include an OpenQuery action in your macro to run theis Update
4. Expand your macro Condition to include this, i.e.
Weekday(Date())=2 And Day(Date())<8 And
Format(Date(),"yymm")>DLookup("[YourField]","YourTable")

This way, the first time the database opens on the 1st Monday of the
month, the macro will run, and in the process will put reference to this
month in the lookup table, so the second time it is opened on that day,
the condition will then fail and therefore the macro will not run.

 

[_Bigred]

I will definitely check this out and see what I can get working.

The code previously worked great, now I will checkout your additional
"tweaks" and see what happens.

Thanks again Steve,
_Bigred

Bigred,

One way to handle this...
1. Make a simple table, one text field, one record, and put last
month's date in there in format 0401
2. Make an Update Query which will update the value of this field to
Format(Date(),"yymm")
3. Include an OpenQuery action in your macro to run theis Update
4. Expand your macro Condition to include this, i.e.
Weekday(Date())=2 And Day(Date())<8 And
Format(Date(),"yymm")>DLookup("[YourField]","YourTable")

This way, the first time the database opens on the 1st Monday of the
month, the macro will run, and in the process will put reference to this
month in the lookup table, so the second time it is opened on that day,
the condition will then fail and therefore the macro will not run.

--
Steve Schapel, Microsoft Access MVP

Hello Steve,

The first point you make about holidays is not a problem, because even on
holidays we will be running this macro (if it works). But do you have any
ideas on how to get around the macro running (printing all reports) every
time you open the db, if it is the 1st monday of the month?

Any ideas would be greatly appreciated, I will try the condition you gave to
see how it works.
Thanks,
_Bigred

 

[_Bigred]

Seems to have worked properly.
Thanks Steve,
_Bigred

I will definitely check this out and see what I can get working.

The code previously worked great, now I will checkout your additional
"tweaks" and see what happens.

Thanks again Steve,
_Bigred

Bigred,

One way to handle this...
1. Make a simple table, one text field, one record, and put last
month's date in there in format 0401
2. Make an Update Query which will update the value of this field to
Format(Date(),"yymm")
3. Include an OpenQuery action in your macro to run theis Update
4. Expand your macro Condition to include this, i.e.
Weekday(Date())=2 And Day(Date())<8 And
Format(Date(),"yymm")>DLookup("[YourField]","YourTable")

This way, the first time the database opens on the 1st Monday of the
month, the macro will run, and in the process will put reference to this
month in the lookup table, so the second time it is opened on that day,
the condition will then fail and therefore the macro will not run.

--
Steve Schapel, Microsoft Access MVP

Hello Steve,

The first point you make about holidays is not a problem, because even on
holidays we will be running this macro (if it works). But do you have any
ideas on how to get around the macro running (printing all reports) every
time you open the db, if it is the 1st monday of the month?

Any ideas would be greatly appreciated, I will try the condition you gave to
see how it works.
Thanks,
_Bigred

 

[Steve Schapel]

Happy to hear we have made progress, Bigred. Thanks for letting us know