Run macro automatically at the end of the month

  • Thread starter Thread starter Claire Rohan via AccessMonster.com
  • Start date Start date
C

Claire Rohan via AccessMonster.com

Hi!

I have written a macro which calculates the interest earned in a month after
prompting the user for a month end date. I want the system to automatically
run the macro when Date() is the last day of a month using Date() as the
input. I have a LastOfMonth function so I could write the code for checking
if Date() is a month end date but I just dont know where to put it and how to
make it run automatically.

Thanks for any help in advance!
Claire
 
The last day of the current month will be:

DateSerial(Year(Date()), Month(Date()) + 1, 0)
 
Hi Claire,

You didn't give any clues as to whether your application is on all the
time, runs several times each workday, run by one person ???

Different scenarios might appeal depending on your operational
environment and climate.

Worst case would be that you don't know how many people might be
running it from their own FrontEnd at any time. Some may leave the
application open, etc. ...

In that case, I would create a from with nothing but a timer control
on it. Set the form to invisible and have the Autoexec macro load it
before doing anything else (If you need some code to run on shut down,
put it in the Close event of that form. If you don't close the form
explicitly it will always be the last one to close).
If you don't have a table for local parameter variables, create one
and create a parameter named Last Of Month. put in a value (not
necessarily date/time type but having that sense) from your code that
does the Last of Month actions.

In the Load event of that invisible form, put the code to test for end
of month and do what ever needs doing, including entering the value in
your LocalParameterVariable table.

For the timer control on that form, set the timer to an hour. It
should do the same things your Load event code did. I assume you'll
have a Function procedure that you call in each case.

Be sure that table is in the BackEnd so that everyone sees and sets
the same parameter.

HTH
 
Hi Douglas,
Thanks for your post! The thing is I know how to get the last day of the
month - my problem is getting the code to run automatically if it the last
day of the month...
Thanks again
Claire
The last day of the current month will be:

DateSerial(Year(Date()), Month(Date()) + 1, 0)
Hi!
Click to expand...
[quoted text clipped - 7 lines]
Thanks for any help in advance!
Claire
Click to expand...
Click to expand...
 
Simply compare today's date to the computed last day of the month, and run
your code if they're the same:

If Date() = DateSerial(Year(Date()), Month(Date()) + 1, 0) Then
' Run your code
End If

Where you put that code, of course, depends on when you want to check
whether or not to run your code.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Claire Rohan via AccessMonster.com said:
Hi Douglas,
Thanks for your post! The thing is I know how to get the last day of the
month - my problem is getting the code to run automatically if it the last
day of the month...
Thanks again
Claire
The last day of the current month will be:

DateSerial(Year(Date()), Month(Date()) + 1, 0)
Hi!
Click to expand...
[quoted text clipped - 7 lines]
Thanks for any help in advance!
Claire
Click to expand...
Click to expand...
Click to expand...
 
Simply compare today's date to the computed last day of the month, and
run your code if they're the same:

If Date() = DateSerial(Year(Date()), Month(Date()) + 1, 0) Then
Click to expand...

or check that tomorrow is the first

If Day(Date()+1) = 1 Then...


All the best

Tim F
 
Hi Larry - thanks for your post.. Ive been attempting to get it to work for a
while now and this is what Ive come up with.. a function to find if the code
should be run which I call on the open procedure of the Switchboard form that
opens when the database is opened. this is the function code:

the problem is I get an error at the second "set recMonthInterest = db.
OpenRecordset(strSQL)" line which says Object variable or With block variable
not set............ Any ideas? Thanks, Claire

Public Function MonthInterestCall()
Dim Today As Date
Dim Tomorrow As Date
Dim strSQL As String
Dim db As Database
Dim recMonthInterest As Recordset

Tomorrow = Today + 1

If DatePart("d", Tomorrow) = 1 Then ' check if today is a month end date,
if yes
'check if its been run already today
strSQL = "SELECT MonthEnd FROM TotalMonthlyInterest"
Set recMonthInterest = db.OpenRecordset(strSQL)
Do While Not recMonthInterest.EOF
With recMonthInterest
If !MonthEnd = Today Then 'by seeing if there is already an
entry in the interest table with that date
Exit Function
Else ' if not, run the code using todays date
Call MonthInterestProcedure(Today)
End If
End With
Loop
Set recMonthInterest = Nothing
Else
Do Until DatePart("d", Today) = 1'finds the most recent month end
date
Today = Today - 1
Loop
Today = Today - 1
'check if its been run already that day,
strSQL = "SELECT MonthEnd FROM TotalMonthlyInterest"
Set recMonthInterest = db.OpenRecordset(strSQL)
Do While Not recMonthInterest.EOF
With recMonthInterest
If !MonthEnd = Today Then
Exit Function
Else
Call MonthInterestProcedure(Today)
End If
End With
Loop
End If
End Function
 
Hi Clare,

I've done something quite similar recently for the Monthly calculation
of depreciation for an Asset database.

I found I had to maintain a table which kept a track of the months
where the month end process had been applied successfully. I had to do
this because the application which tests for month end and runs the
month end process is not necessarily run every day or even every month.

Just my tuppenceworth and not helping your current problem.

Noel M
 
Back
Top