Automating Entries Based on Dates

[curlydog]

I'm developing a database that stores data concerning overtime in a department. I have some (limited) knowledge of programming but I'm new to VBA.

On the 26th of each month, a fixed amount of overtime hours are added to the department's overtime budget. I'm trying to automate the process of adding these hours into my database.

I plan on using the onOPen event of a form that automatically opens when the database opens to trigger the check. The problem I have is figuring out how to programatically check if the hours have been added on the 26th of each month up to the present day.

The main difficulty is taking into account previous months and years if the current day of the month is less than 26, especially if the database hasn't been opened for a few weeks.

I'm wondering if anyone else has found a solution to this problem in the past. I'm not looking for code to solve my problem (although I'll take it if you are offering), but simply the processes that I need to go through to cover all eventualities.

Thanks in advance
Jason

 

[SarahG]

You have two options.

1. Create the queries to add the overtime and create a macro which will run this query. Then using Windows AT (a Task scheduler) set up a command which will launch Microsoft Access on the 26th of each month and run this macro only. You need to set this up on a machine which will always be switched on - perhaps your Network Administrator can set this task up for yu on a server. This is the easiest of the two, and if you know that it did not run correctly, you can always run the macro automatically. You could also set up an audit trail table in the application and set up this macro to write to it every time it runs, for your own information.

2.Create an audit trail table which will capture the following:

AuditID Autonumber
UserName text (represents user = CurrentUser())
AuditDesc text (e.g. Update of overtime)
AuditDate datetime (date & time of update)
OT_Date datetime (date of overtime this applies to, e.g. 26 April 2004)

and every time you add the overtime hours successfully, add an entry to this table. Then when you are running your check to see if overtime has been added, write a routine which adds the overtime for the 26th of each month which fell between the last OT_Date and the currentdate.

Hope this helps.