Add new record based on day of week

[ddikeht]

First, thanks for all the help over the years, everyone here is invaluable!

Issue -

I have a database that tracks projects. These consist of both new and recurring projects. For the recurring (which can be daily, weekly or monthly) I would like to automatically add these to the database, so that they can be tracked via a report that shows projects due within the next 3 days.

So, lets say the database opens on monday. It would look out for the next 7 days and see that there is a recurring report that gets done every wed. The record should be automatically added with some details like due date and project name. I would now be able to see the project when I open up a report that queries on projects due in the next 4 days.

I am thinking a hidden form with a timer, but thats as far as I can go. Does anyone have some sample databases with the functionality that I can tear apart? Not looking for code, but just a push in the right direction, as this has me stumped. As always, thanks!

 

[pbaldy]

Personally, I wouldn't use a timer. I would create an mdb that did the desired process when it opened, then quit. Then I'd call that mdb from Windows Scheduled Tasks on the appropriate schedule.

 

[ddikeht]

pbaldy - Thanks for the tip. I just need to make sure this opens everyday, which I know how to do. This is getting really complicated, which I love. In looking over the data, I would have to insert a record, on say, (best case) once per day, (harder) every Wed, and (way hard!) 15th of month following quarter. As always, input is appreciated...

 

[pbaldy]

As you say, a lot depends on the data. Every Wed wouldn't be tough if the data was structured appropriately. For the 15th of the month following the end of a quarter, my first thought is using the Mod() function to test the previous month. If it's evenly divisible by 3, it was the end of a quarter.

 

[ddikeht]

Paul,

Another great suggestion. I think I will have to have a couple of tables, one with project, another with frequency. I can then loop through them and set a flag for due. Due would then be the criteria for creating the record. Am I on the right track?

 

[pbaldy]

It sounds like it, yes. Post back with more specifics if you get stuck.