Run process based on dates

[nissan240zx]

Hello All,
I have an access database that has 3 macros that needs to be run based on certain requirements.

Macro 1 - is to run on weekdays
Macro 2 - to run on Financial MonthEnd
Macro 3 - to run on Financial MonthStart

The dates for Financial MonthEnd and MonthStart will be stored in 2 tables called Dt_MonthEnd & Dt_MonthStart.

I want to setup a VB process that will run Macro 1, 2 & 3 based on date requirements.

For example

Every weekday run Macro 1
Every Financial MonthEnd run Macro 1 and Macro 2
Every Financial MonthStart run Macro 1 and Macro 3

Any leads ....

Thanks in advance...





A good programmer is someone who looks both ways before crossing a one-way street. - Doug Linder

 

[tonymce]

is this to be run automatic or via user input?
automatic can be a pain but can be done in a 3rd party app but is the db is opened every day, or stays open you can do somthing like this:
my code is a bit ropey i am not ysing any referance so you will have to refine it and make it work kk


function get_marcro_to_run
'First we need to get todays date
dim dte
dte = now()

'Now we need to determine if it month end or start
for i = 1 to rst.recordcount ' this is the record count for Dt_MonthEnd
if rst.Dt_MonthEnd = dte then
'run macro month end
docmd.Runmacro"Dt_MonthEnd "
return

end if
next i


for i = 1 to rst.recordcount ' this is the record count for Dt_MonthStart
if rst.Dt_MonthStart= dte then
'run macro month end
docmd.Runmacro"Dt_MonthStart"
return
end if
next i

''Right we now know it is not monthend or monthstrt so get the day

dim dayname
dayname = day(now() 'This is wrong i cannot remember the day format command it will return the name of the day you can find it somwhere

case select dayname
case "MONDAY"
docmd.runmarco"weekdays"
CASE "TUESDAY"
end select
end function


this is not precise but it is a start
you can call this on a times interval using either a timer or somthing
add a variable that knows if it has been called then waits until the next day
somthing like this:

public hasruns as bolean
public lastrundate
'timer code

if hasfun= true then
if month(lastrundate) = month(now) then
if day(lastrundate) > day(now) then hasrun = false end if
else
hasrun = false
end if
else
lastrundate = now()
call get_macro_to_run
end if

hope this helps
have fun

 

[nissan240zx]

Thanks..
The db process has to run automatically..based on dates..


A good programmer is someone who looks both ways before crossing a one-way street. - Doug Linder

 

[mp9]

You might find that the Handy Access Launcher meets your requirements. It's freeware, and has quite a lot of day scheduling options (weekdays, weekends, first/last day of month, first/last weekday of month, etc). As well as scheduling macro runs, it also enables scheduled db compact/repairs.

You can find HAL at

http://www22.brinkster.com/accessory/resources/001.shtml

 

[nissan240zx]

Thanks...HAL sure can help..but its same as doing it on Windows Schedular...
I am using Windows to run my process now..set on date and time..

A good programmer is someone who looks both ways before crossing a one-way street. - Doug Linder