Auto Create New Record

[Lochwood]

I have created a list of scheduled tasks in my database with Job_ID Job_Description scheduled_date and Completed Date.

Each job has a different scheduled date interval E.G Weekly scans run weekly, network scans quarterly, server updates monthly. at the moment I am manually inputting dates when the task is due..

What I would like to ask is if it is possible to auto create a new record when completed date is populated for that task with correct due date. So if I have a weekly scan task that is due 24/08/17 and I add completed date of 24/08/17, a new record is generated based on the Job_Description with correct due date of 31/08/17

Thanks in advance.

 

[arnelgp]

possible using macro or vba.

but i think you need a second table, JobFrequency
where you put the

taskID (add this also to your task table),
taskDescription
taskFrequency (in days)

remove the task description from youre task schedule table
and add the taskID. the textbox for this field on the form
can be changed to combobox with lookup to the new table.

use form when entering data on the task schedule table.
insert a code to the AfterUpdate of the form, something like:

If Trim([Completed Date] & "") <> "" then
DoCmd.RunSQL "Insert Into [task schedule table] ([Job ID], [taskID],[Due Date]) " & _
	"SELECT " & DMax("[Job ID]", "task schedule table")+1 & ", " & [TaskID] & ", #" & _
	[Competed Date]+Dlookup("taskFrequency","JobFrequency","taskID=" & [taskID]) & "#;"
End If