Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2011
    Posts
    1

    Unanswered: Task Database Rollforward upon Completion

    I am trying to figure out a command that will "roll forward" a task when I mark the "Status" of the task as "Completed". The roll forward length should be based on a table/associated field called "Cycle". When cycle is defined as "Monthly" and the task is marked as "Completed" then the task will be copied and the "Due Date" of the task will be 30 days from the last due date. I have a table for the cycles with the rollforward days associated. I am working off the standard "Task Database" template provided by Microsoft.

    Any guidance/advice would be much appreciated!!!!

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Access has a terrible habit of naming Controls the same as the Fields whose data they hold, and this can cause trouble, so I always name Textbox Controls with a txt prefix, and I'll use that convention in the following code.

    Where:

    Your Textboxes are named

    txtStatus
    txtCycle
    txtDueDate

    The Table holding the Cycle and Days per Cycle is named CycleTable

    And CycleTable's Fields are named

    Cycle

    Days

    Code:
    Private Sub txtStatus_AfterUpdate()
     Dim NewDueDate As Date
      
     If Me.txtStatus = "Completed" Then
      
      If Nz(Me.txtCycle, "") <> "" And Nz(Me.txtDueDate, "") <> "" Then
    
       NewDueDate = DateAdd("d", DLookup("Days", "CycleTable", "[Cycle] = '" & Me.txtCycle & "'"), Me.txtDueDate)
        
       DoCmd. RunCommand acCmdSelectRecord
       DoCmd. RunCommand acCmdCopy
       DoCmd. RunCommand acCmdPasteAppend
       
        Me.txtDueDate = NewDueDate
     
      End If
     
     End If
    
    End Sub
    Due to a quirk of this site, you cannot post

    DoCmd. followed by RunCommand

    because the site inserts asterisks, so you'd see

    DoCmd****nCommand

    So once you copy the code posted, you need to remove the space between each

    DoCmd.

    and

    RunCommand

    so it forms one continuous string.

    I should add that when you have Controls populated via calculations that depend on one or more other Controls, such as txtDueDate, txtCycle and txtStatus, you should really give thought to making sure the Values in these Controls are entered correctly.

    To accomplish this I would use a Combobox for txtCycle and txtStatus, and a form-based Calendar for the txtDueDate Control.IF you're using Access 2007/2010, of course, this is provided, I believe by default, for all DateTime Fields.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    there is another alternative and to do this using a query,

    add you main table and the table with the stuses into a new query,

    drop all fields in the new query, in an empty column (by the way i'm presuming you are storing the date in there somewhere too)

    you can use a formula sucvh as

    =[completed_date]+[rollover_period]

    providing you drag and rop all fields into the query, a link will be made on the cycle and the actual amount of time!

    Hope it helps

    dan
    sometimes simple is best.... and i'm just a simple fellow.

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by cmartin85 View Post
    When...the task is marked as "Completed" then the task will be copied and the "Due Date" of the task will be 30 days from the last due date.
    How does your example 'copy the task,' i.e. copy the task to a new record, assigning the calculation to the Due Date Field? You're simply placing the calculation in a new field on the existing record. The OP apparently needs to have the old record 'closed' and a new record for the task created, with an incremented Due Date.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •