Auto create record based on date and criteria in other record

[ringer]

I have a db where each record is a certain task to be done. Each record includes the date the task should be completed. Some of the tasks are one time only but some are recurring. I have been having to create a new record for every task even the recurring ones, but I'd like to try to automate creation of the recurring tasks.

What I'm trying to do is set it up so that when I'm setting up a task I can select from a drop down box that this task will not recur or that it will recur every week, month, etc. Then on the date (or the day after) the first task was due I need it to create the task again, but the new task's due date would be set based on the first tasks due date and recurrence interval selected when setting up the first task.

For example, if the original task was:
Fax weekly expense report
Due 9/26/07
Repeat: weekly

I'd like for it to, on 9/26 or 9/27, automatically create this record
Fax weekly expense report
Due 10/3/07
Repeat: weekly

I need to have a record for each task so I can't change the due date on the existing task, and I need the new task record to be created regardless of the previous task being completed.

[Ian Brown]

I created a CheckList database that we use at work that produces Checksheets based on tasks as you describe.

You need to have an additional column where the next date the task will be active can be stored. Then use a script to workout when the next active date is and store it in the table.

Then using a report, or form, to display all the tasks have the same active date as the current date.

The command you need (DateAdd) to forecast the next active date is:

ActiveDate = DateAdd("d", 7, StartDate)

The above command will add 7 Days to the StartDate.

Hope this helps

Ian

[ringer]

Thanks for your reply.
I see how you're figuring the next date the task will be needed again, but I also need to write another record to the db for the next task, with the new record being the same as the first task record except that the start date should be set to a future date according to the recurrence frequency of the task.

[Ian Brown]

Thanks for your reply.
I see how you're figuring the next date the task will be needed again, but I also need to write another record to the db for the next task, with the new record being the same as the first task record except that the start date should be set to a future date according to the recurrence frequency of the task.

So you want to add the same record to the same table but with the start date in the future, or add this data into a second table? By storing the future date in the same record and then using a form/report that looks at the future date should cover what I think you are after.

Your table could look like:

ID  Discription       StartDate   Days  ActDate
01  Send Fax to ###   1/1/2007    7     1/7/2007

Run the forcast part of the code after the 1/1/2007 it will update to:

ID  Discription       StartDate   Days  ActDate
01  Send Fax to ###   1/1/2007    7     8/7/2007

[ringer]

Thanks again for your help.
If I understand correctly, this wouldn't add a separate (new) record for the new task and I that's what I am trying to do. I need a record for each task, both the old and the new.
To answer your question...I do want the new record to go into the same table as the old one.