Subtasks

[Mrnorth]

I've had a nifty idea whereby I download an MS Project template from a blob in my SQL server DB. That bit works fine. I have a one-to-many Projects/tasks view that, as you can see creates the overall project and takes the child records and makes tasks out of them.



The problem is the outlineindent. Each subtask becomes the parent of the next rather than clustering under each parent project like a Russian Doll.Does anyone kow what I'm missing?

 
Dim rs As New ADODB.Recordset
    Dim prjApp As MSProject.Application
    Dim prjProject As MSProject.Project
    
    Dim intTask As Long
    Dim lngCPDID    Dim strTempFolder As String
    Dim rst As New ADODB.Recordset
    Dim strFolder As String
    Dim ADST As ADODB.Stream
    Dim bytData() As Byte
    Dim i As Long
    

    If Nz(txtStart, "") = "" Or Nz(txtEnd, "") = "" Then
        MsgBox "Enter some dates, dumbass!"
        Exit Sub
    End If
    strTempFolder = fGetSpecialFolderLocation(CSIDL_PERSONAL) & "\CPD2\"
    
    If FileOrDirExists(strTempFolder) = False Then
        MkDir strTempFolder
    End If

    rst.Open "select * from tbl_CPD_sourcefiles where filename='project1.mpp'", CurrentProject.Connection, adOpenStatic, adLockOptimistic
    Set ADST = New ADODB.Stream
    ADST.Type = adTypeBinary
    ADST.Open
    bytData() = rst.Fields("document").Value
    ADST.Write bytData
    ADST.SaveToFile strTempFolder & "project1.mpp", adSaveCreateOverWrite
    rst.Close
  
    Set prjApp = CreateObject("Msproject.Application")
    
    prjApp.FileOpen strTempFolder & "Project1.mpp", ReadOnly:=True
    prjApp.Visible = True
   
    Set prjProject = prjApp.ActiveProject
    
    If ynOpen = True Then
        strsql = "SELECT * FROM qry_CPD_workload where startdate > '" & Format(Me![txtStart], "yyyy-mm-dd") & "' and enddate<'" & Format(Me![txtEnd], "yyyy-mm-dd") & "' and status='open'"
    Else
        strsql = "SELECT * FROM qry_CPD_workload where startdate > '" & Format(Me![txtStart], "yyyy-mm-dd") & "' and enddate<'" & Format(Me![txtEnd], "yyyy-mm-dd") & "'"
    End If
    rs.Open strsql, CurrentProject.Connection, adOpenStatic, adLockOptimistic
    rs.MoveFirst
    For i = 0 To rs.RecordCount
        lngCPDID = rs.Fields("CPDID").Value
        n = rs.Fields("CPDID").Value & " - " & rs.Fields("description").Value
        prjProject.Tasks.Add Name:=n
        prjProject.Tasks.Item(rs.Fields("CPDID").Value & " - " & rs.Fields("description").Value).Start = CDate(DMin("startdate", "qry_CPD_workload", "cpdid=" & lngCPDID))
        prjProject.Tasks.Item(rs.Fields("CPDID").Value & " - " & rs.Fields("description").Value).Finish = CDate(DMax("enddate", "qry_CPD_workload", "cpdid=" & lngCPDID))
        prjProject.Tasks.Item(rs.Fields("CPDID").Value & " - " & rs.Fields("description").Value).ResourceNames = rs.Fields("teamleader").Value
        While rs.Fields("CPDID").Value = lngCPDID
            prjProject.Tasks.Add Name:=rs.Fields("CPDID").Value & " - " & rs.Fields("descriptions").Value
            prjProject.Tasks.Item(rs.Fields("CPDID").Value & " - " & rs.Fields("descriptions").Value).OutlineIndent
            prjProject.Tasks.Item(rs.Fields("CPDID").Value & " - " & rs.Fields("descriptions").Value).Start = CDate(rs.Fields("startdate").Value)
            prjProject.Tasks.Item(rs.Fields("CPDID").Value & " - " & rs.Fields("descriptions").Value).Finish = CDate(rs.Fields("enddate").Value)
            prjProject.Tasks.Item(rs.Fields("CPDID").Value & " - " & rs.Fields("descriptions").Value).ResourceNames = rs.Fields("teamleader").Value
            
            If rs.EOF = True Then
                rs.Close
                Set prjProject = Nothing
                Set prjApp = Nothing
            Else
                rs.MoveNext
            End If
        Wend
    Next i

[JulieS]

Sorry, I don't know anything about the code you've written other than to point out that when you create a task indent it, and create another task - the newly added task 'inherits' the outline level of the task above it. In your code you're adding a task and indenting and adding the next task and indenting. You may be better served by adding the tasks, and then creating the outline level after all tasks are added.

[Mrnorth]

I don't don't know how to do that.

[JulieS]

I'm not a coder but I would set the Outline Level property of the task versus just indenting. Or, you'll need to stop the OutlineIndent after the first inserted task - assuming all tasks after that are to be at the same outline level.