Recurrent Tasks 1

[Eddyoftheyear]

OccuranceID Occurance Type Values
1 Daily 1
2 Weekly 7
3 Bi-Weekly 14
4 Monthly 30
5 Quarterly 90
6 Semi Annually 180
7 Annually 365

I have a task table with a lookup field to the occurance table. when manager select updae weekly, I would like the task to pop in the staff's queue every week. Staff will update the task on the status table and will enter update date. I thought of assigning vlaue to each but I can not think of the formula that will help with this.
Help.....

 

[dhookom]

I would insert a new row into the "status table" when a task has been completed. Running an append query in code would probably be the easiest.

If you need more assistance, you should provide your table and field names, data types, relationships, and business rules.

Duane
Hook'D on Access
MS Access MVP

 

[Eddyoftheyear]

Tbl_Tasks: DueDate (Date/Time), TaskDescription (Text), occuranceID (lookup table (monthly, yearly,...)

Tbl_Status: updatedate (Date/time)short date, Status field (text field)

Tbl_Ocurrance: OccuranceID(Autonumber), OccuranceType (Text), Value (Number). month=30, day=1, week=7

Relationship: one to many Tbl_tasks to Tbl_status

One the task is recurrent monthly, I want the task to show in the staff's queue monthly using the duedate to calculate the recurrence.

Thank you

 

[dhookom]

This is impossible since there are no fields that relate any of the tables. In addition, their are no staff tables or fields and nothing that looks like a "queue".

Duane
Hook'D on Access
MS Access MVP

 

[randy700]

Relationship: one to many Tbl_tasks to Tbl_status
How are these tables related?


Randy

 

[Eddyoftheyear]

The staffID is a field in the task table. The relationship is

TaskID to TaskID between task and status table

I created a popup form with staff name to lookup all tasks assigned to them. after update, the task form opens with all tasks assigned.

 

[dhookom]

I think you should write some code to run in the after update of a record in the tasks assigned form to schedule the next task.

Duane
Hook'D on Access
MS Access MVP

 

[Eddyoftheyear]

so if staff update the status and write the update date for example today and the task is to be updated monthly. would there be a way that I can use the update date and add 30 days to that date so it can show again everymonth?

 

[dhookom]

This would be my plan. You would need to determine what table needs to be appended to (assuming task table) and what fields need values. Then consider the fields in the current form's field list and build a sql statement to execute. It might look something like this:

Dim strSQL as String
strSQL = "INSERT INTO tblTasks (txtFldA, numFldB, datFldC) " & _
   "Values(""" & strFldA & """," & lngFldB & ",#" & datFldC & "#)"
Currentdb.Execute strSQL, dbFailOnError

Duane
Hook'D on Access
MS Access MVP

 

[Eddyoftheyear]

dhookom,

is there an easier way so the same task record will reapear based on the period specified. quartetly, monthly...

I think if we can add value to monthly, quarterly, yearly...and so on to the lastupdate date so it can add to the date 30 days and on....

I am just not an expert in this and I am trying simple way.

 

[dhookom]

Does your business rules state:
[tt][blue]A task should be scheduled x number of days after it has been completed.[/blue][/tt]
or
[tt][blue]A task should be scheduled x number of days after its previous scheduled date.[/blue][/tt]


Duane
Hook'D on Access
MS Access MVP

 

[Eddyoftheyear]

a task such as scheduling monthly meeting based on the start date of 08/12/2010. so on the 12 of everymonth, the meeting must be scheduled.

 

[dhookom]

I would then use something like recurring demo for some tasks.

Duane
Hook'D on Access
MS Access MVP

 

[Eddyoftheyear]

Thank you so much for this nice template. it helped alot. But I am trying to remove the starttime and end time since the task is only with due date. I removed all starttime and end time from the codes to the best of my ability but ran into this ero with the following code:


strSQL = "INSERT INTO tblTempSchedDates (" & _
"tscDate, tscActID, tscLocID, " & _
tscNotes ) " & _
"Values(#" & datThis & "#," & lngActID & ", " & _
lngLocID & ", " & _
IIf(IsNull(varNotes), "Null", """" & varNotes & """") & ")"


I don't know what I left extra to it is giving me erorr.

 

[dhookom]

What is the error message?
Have you tried debugging your code? How about using Debug.Print strSQL?

Duane
Hook'D on Access
MS Access MVP

 

[Eddyoftheyear]

compile error
syntax error and it higlghted the codes

strSQL = "INSERT INTO tblTempSchedDates (" & _
"tscDate, tscActID, tscLocID, " & _
tscNotes ) " & _
"Values(#" & datThis & "#," & lngActID & ", " & _
lngLocID & ", " & _
IIf(IsNull(varNotes), "Null", """" & varNotes & """") & ")

 

[dhookom]

Looks like at least one " missing:

    strSQL = "INSERT INTO tblTempSchedDates (" & _
              "tscDate, tscActID, tscLocID, " & _
              [red]"[/red]tscNotes ) " & _
              "Values(#" & datThis & "#," & lngActID & ", " & _
              lngLocID & ", " & _
              IIf(IsNull(varNotes), "Null", """" & varNotes & """") & ")"

Duane
Hook'D on Access
MS Access MVP

 

[Eddyoftheyear]

Thank you very much dhookom. you are very helpful. this site is great.I am so happy that I found this site.