Calculate Due Date from Previous Record Close Date

So you can "order" the records by that "process number"?

Each task has a start date, end date and due date and a process order number.

Are those fields filled in when you import? That would be somewhat odd, but it's hard to tell from your question.

Instead, it's common that you would import Tasks associated with a Project (and relate them to that Project). Then, as you complete a Task (and enter an End Date value) you could then stamp the "next" Task with the Start Date, based on that End Date.

This is most easily done in the Form where you modify the Task. You could run code like this, perhaps in the AfterUpdate event of the Form:

'/ make sure an End Date is available
If Nz(Me.YourEndDate,0) <>0 Then
  '/ get the next ProcessNumber
  Dim ProcessNum As Double
  ProcessNum = DMin("ProcessNumber", "TaskTables", "EndDate IS NOT NULL")
  Currentdb.Execute "UPDATE TasksTable SET StartDate=#" & Me.YourEndDate & "# WHERE TaskID="         & Me.TaskID & " AND ProcessNumber=" & ProcessNum
End If

Obviously this is air-code, and you'd have to change the Table, Field and Control names to match your own.

Many thanks!

You're correct, the common tasks that are Appended to the task table have no dates associated at the point of import and are just updated as tasks are progressed.

Also, how would I set the lowest process number that's imported  to have today's date? I can't see that from the code unless I'm missing something?

I'll give it a shot though and feedback, currently 00:45 here in the UK so will be in the morning.

Thanks again!

Brendan

Brendan,

Scott's code is for a form's after update event for an end date to set teh next task's start date.

I am not sure how you import the records ,  One solution would be to run code after the import is competed to add today's date to each record that has the smallest process number.

Difficult to give you more specific details without knowing more about the database and the import process.

Hi Boyd,

Essentially it's the access desktop project management with a couple of extra columns added to the tasks and common tasks tables.

To give you an idea though..

There is a common tasks table/sub form, a tasks table/sub form and a project table/form.

The user opens the project form and selects a button which opens the common tasks sub form, the user can then select the relevant common tasks to add to the task table, essentially there's a Boolean yes/no column they can select for each task they want to import. Once selected they then press the add selected tasks button which runs an append query to add the common tasks details to the task table along with the current project ID.

Hope that makes sense.

Best regards

Brendan

To make things more complicated the user can go in at a later date and append more common tasks so by doing that it would need to make sure it doesn't affect the already imported tasks.

Best regards

Brendan

Hi,

I've tried the code as above in my Form, however i'm getting a type mismatch on the "Date Completed" is not null, i've also tried is not blank with no luck. Any suggestions? Many thanks Brendan

Private Sub Form_AfterUpdate()
'/ make sure an End Date is available
If Nz(Me.[Date Completed], 0) <> 0 Then
  '/ get the next ProcessNumber
  Dim ProcessNum As Double
  ProcessNum = DMin("Tasks", "Process Order", "Date Completed" Is Not Null)
  CurrentDb.Execute "UPDATE Tasks SET [Start Date]=#" & Me.[Date Completed] & "# WHERE [ID]=" & Me.[ID] & " AND [Process Order]=" & ProcessNum
End If
End Sub

Select all Open in new window

The DMin line is incorrect. Assuming "Tasks" is the name of the Table, and "Process Order" is the name of the Field you want to return:

ProcessNum = DMin("Process Order", "Tasks", "[Date Completed] Is Not Null")

You could also try:

ProcessNum = DMin("Process Order", "Tasks", "Nz([Date Completed],0) <> 0")

Basically, that line looks for the "minimum" value in Tasks.[Process Order] where there is a value in [Date Completed]

user can go in at a later date and append more common tasks so by doing that it would need to make sure it doesn't affect the already imported tasks.

Assuming the new tasks would be added on to the "end" of the other Tasks, you shouldn't have a problem. The Form's After_Update code would still work (if that's what you end up using).

If you try to intermingle tasks you'll have no end of trouble. For example, lets say you have have Tasks 1, 2, 5, 8 and 9, and you've "completed" up to 8. You then try to add Task 3 and 4 between two completed tasks - which would mean that the Dates for those would very likely be off in consideration of the previous Tasks.

So as Boyd said, you really need to give us more information as to exactly what you're trying to do, and let us know the different scenarios you could run into. We could give much better advice from there.

Hi Scott,

That line fixed the issue although it just seems to be updating the start date of the current record rather than the next process number in the line.

Essentially the user will create a project and select common tasks that are apporiate for the project, they are linear so they should never jump from say 1 - 3,4 down to 2, however because some are optional in later processes they need the ability to decide if they need to add common task process order 10 instead of 11 etc.

I'm hoping that clarifies it a little? :/

Cheers

Brendan

Hi,

I think thats the issue, its returning '1', presumably it should be returning '2' as the next process number?

Realised why it was returning '1' because the is NOT null needed to be 'is null'

just to work out the adding to the next start date now, will report back :)

Many thanks

Brendan

Perfect!