Increment date field (by a predefined number) to indicate the milestones

[GDimaya]

I am trying to create a date range timeline process in Access the same way that Excel can increment a Date in a cell by dragging that value down to the rows. How it works is the user enters a start date (the length of days is predefined), and then a timeline is automatically created after the user clicks on the timeline tab. I have no problem incrementing the date value by 1 for the start date by using the dateAdd function. However, the EndDate, which is a seperate field in the table, must get prepopulated with only the milestone dates as defined by the task. The table should look like this: StartDate EndDate 10/1/12 10/2/12 10/3/12 10/3/12 'increment by 3 10/4/12 10/5/12 10/6/12 10/6/12 'increment by 2 10/7/12 10/8/12 10/8/12 'increment by 1...etc The code below works however it skips rows (3 times) only for the first entry and fails to skip the desired number of rows (or days) and stops incrementing by the predefined fieldvalue of rs!Days: 'rs!Days is a field in the table that specifies the number of days each task should take. For int = 1 to 45 (total number of days in a timeline) StartDate = DateAdd("d", rs!Days, StartDate) with rs .Edit !EndDate = StartDate .Update end with Next int Please help. I've been looking at this for several weeks now and have come up with so many code scenarios that have been unsuccessful. Any input would be greatly appreciated.Thank you.

[orange]

Perhaps, you could step back and give us an overview of what your database is about. Your post has lots of detail but not much context to help the reader understand your situation. Could you tell us what tables and relationships you have?
There may be other ways to accomplish what you're trying to do.

[recyan]

Just check if below rough draft gives some guidelines :

It is based on tblTasks & the StartDate & not on number of Timeline days specified.

Assumes you have 2 tables :

tblTasks
TaskID - Autonumber - PK
TaskNoOffDays - Number

tblTimeLine
TimeLineID - Autonumber - PK
StartDate - Date
EndDate - Date

The function to run :

Function populateMyTable()
    Dim StartDate As Date
    Dim dbs As Database
    Dim i As Integer
    Dim j As Integer
    Dim rsta As DAO.Recordset
    
    StartDate = #6/1/2012#
    
    Set dbs = CurrentDb
    
    ' Empty the tblTimeLine if any data exists
    strsqlDelete = "delete * from tblTimeLine"
    dbs.Execute (strsqlDelete)

    TheNewStartDate = StartDate

    strsqlTasks = "select TaskID, TaskNoOffDays from tblTasks order by TaskID"
    Set rsta = dbs.OpenRecordset(strsqlTasks, dbOpenDynaset)
        
    Do While Not rsta.EOF
            
        TheNoOffDays = rsta![TaskNoOffDays]

        For j = 1 To TheNoOffDays - 1
    
            strsqlTimeLine = "insert into tblTimeLine (StartDate) values (#" & TheNewStartDate & "#)"
            Debug.Print strsqlTimeLine
            
            dbs.Execute (strsqlTimeLine)
            TheNewStartDate = DateAdd("d", 1, TheNewStartDate)
        
        Next j

        TheNewStartDate = TheNewStartDate
    
        strsqlTimeLineA = "insert into tblTimeLine (StartDate, EndDate) values (#" & TheNewStartDate & "#, #" & TheNewStartDate & "#)"
        Debug.Print strsqlTimeLineA
        
        dbs.Execute (strsqlTimeLineA)
        
        TheNewStartDate = DateAdd("d", 1, TheNewStartDate)
    rsta.MoveNext
    Loop
    
    MsgBox ("Successful")
End Function 

Note : My VBA is on a negative scale.

Edit : Sorry Orange. Was typing while you were posting.

Thanks

[GDimaya]

The overall gist of the program is to create a tracking and alerts system for predefined (global) tasks that require completion within a 45 day period. The documents are all in Word format and since it is known that access memo fields have corruption issues, I decided to allow users to complete their forms in Word, click on a button (macro) and upload the data into access fields. Once the data is in access, all that is needed is to output the reports in PDF form. The only requirement prior to completing the forms is the user must enter a case name, dob, and start date. The end date is a fixed time frame and calculated once the start date is entered. Once this information is entered a timeline is automatically created when the user clicks on the "timeline" tab. The timeline view must have a start date and end date for all tasks without the user entering any tasks, although at one point they will have the ability to add tasks. All tasks have a number of days associated with it.

I have a 1:M table for 1 incident to many tasks. A junction table with compound keys for both tasks and incidents. One task can branch out into different processes and trigger another timeline and alert process. I have attached the database so that you could see the relationships.

I am wondering if the relationships are the issue.

any advice would be appreciated. thank you in advance.