Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
Calculate Due Date from Previous Record Close Date
Hi,
I have an access database whereby i import common tasks into a tasks table where they are linked to the current project.
Each task has a start date, end date and due date and a process order number.
What i want to do is create a macro/query/vba?! so that as the tasks are imported the lowest process number gets a start date of today i.e. now () and when that task is closed the next process order number in line gets a start date of the previous process number's end date.
As the common tasks are optional, it could be that the process number increments jump e.g. 1,2,4,5,6,8,10 etc.
Any ideas
Many thanks
Brendan
I have an access database whereby i import common tasks into a tasks table where they are linked to the current project.
Each task has a start date, end date and due date and a process order number.
What i want to do is create a macro/query/vba?! so that as the tasks are imported the lowest process number gets a start date of today i.e. now () and when that task is closed the next process order number in line gets a start date of the previous process number's end date.
As the common tasks are optional, it could be that the process number increments jump e.g. 1,2,4,5,6,8,10 etc.
Any ideas
Many thanks
Brendan
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
So you can "order" the records by that "process number"?
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.
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
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.
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
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
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
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
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]
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.
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
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
You'd have to modify this line to point to the "next" record:
CurrentDb.Execute "UPDATE Tasks SET [Start Date]=#" & Me.[Date Completed] & "# WHERE [ID]=" & Me.[ID] & " AND [Process Order]=" & ProcessNum
Without knowing your table structure it's hard to say exactly what needs to be modified, but I'd think you should point to the "TaskID" (i.e. the PARENT of that record) and not the ID value (which generally defines the specific record you're pointing to in that dataset).
So if my "parent" record field is named TaskID, the statement would look like:
CurrentDb.Execute "UPDATE Tasks SET [Start Date]=#" & Me.[Date Completed] & "# WHERE [ID]=" & Me.[TaskID] & " AND [Process Order]=" & ProcessNum
This assumes the DMIN process is getting the correct value, of course. You can verify that by putting this line immediately after the DMIN line:
Msgbox ProcessNum
You should then see a message box that shows the value retrieved by the DMIN process.
CurrentDb.Execute "UPDATE Tasks SET [Start Date]=#" & Me.[Date Completed] & "# WHERE [ID]=" & Me.[ID] & " AND [Process Order]=" & ProcessNum
Without knowing your table structure it's hard to say exactly what needs to be modified, but I'd think you should point to the "TaskID" (i.e. the PARENT of that record) and not the ID value (which generally defines the specific record you're pointing to in that dataset).
So if my "parent" record field is named TaskID, the statement would look like:
CurrentDb.Execute "UPDATE Tasks SET [Start Date]=#" & Me.[Date Completed] & "# WHERE [ID]=" & Me.[TaskID] & " AND [Process Order]=" & ProcessNum
This assumes the DMIN process is getting the correct value, of course. You can verify that by putting this line immediately after the DMIN line:
Msgbox ProcessNum
You should then see a message box that shows the value retrieved by the DMIN process.
Hi,
I think thats the issue, its returning '1', presumably it should be returning '2' as the next process number?
I think thats the issue, its returning '1', presumably it should be returning '2' as the next process number?
Premium Content
You need an Expert Office subscription to comment.Start Free Trial