Results 1 to 6 of 6
  1. #1
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    MS Hierarchical FlexGrid

    Is anyone familiar with this control? For some reason, even though I requery my form and recreate the hierarchical recordset and requery the flexgrid, I'm not NECESSARILY seeing the changed data without first going to another parent record or running through the whole process again. I've tried requerying the flexgrid twice, requerying the form itself (it's unbound, by the way), and everything else I can think of. Does anyone else have any ideas? Here's the code that gets called. CreateShapedRecSet actually generates the SQL and opens the new shaped recordset after closing and destroying the old one.

    <pre>Private Sub UpdateForm()

    'collapse the flexgrid
    hfgLabor.CollapseAll

    ' Me.Requery

    'recreate the flexgrid recordset
    CreateShapedRecset

    If Not mrst.RecordCount = 0 Then

    ' Set the HflexGrid control's DataSource property to the
    ' new recordset
    Set hfgLabor.DataSource = mrst

    End If 'Not mrst.RecordCount = 0

    mrst.Filter = ""

    Me.Requery

    'requery the flexgrid
    hfgLabor.CollapseAll
    hfgLabor.Requery
    Me.Repaint
    End Sub</pre>

    Charlotte

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: MS Hierarchical FlexGrid

    Does it help if you remove the filter from mrst before assigning it as the recordsource of the flexgrid? Can't see anything obviously wrong with that code but then I wouldn't claim to be an expert on hierarchical flexgrids or even hierarchical recordsets!
    Would you mind posting the code for CreateShapedRecset? (I admit it's more because I'm interested than because I think I might be able to help! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: MS Hierarchical FlexGrid

    Actually, the filter was removed in the routine that calls UpdateForm. I was just desparate enough to try it again. Here's the code for CreateShapedRecSet, although it may not enlighten you much since the SQL is highly specific for this form and database. There may also be too many continuation characters in the SQL since I broke it to fit in the post.
    <pre>Private Sub CreateShapedRecset()
    'created a shaped recordset query

    Dim strPath As String 'holds the full path to the data source
    Dim strSQL As String 'holds the SQL for the recordset
    Dim objDlg As clsOpenSaveDialog 'used to retrieve path to backend
    Dim errCurr As ADODB.Error 'holds each ADO error
    Dim intYr As Integer 'holds current year
    Dim varWeek As Variant 'holds week beginning date
    Dim strEmplID As String 'holds employee id for SQL use

    'get the current calendar year
    intYr = Year(Date)

    'get the week if one has been set
    varWeek = Format([txtLWkBeginDt], "short date")

    'get the back end path
    'if the module level variable has been set, use that
    If mstrDataPath <> "" Then
    strPath = mstrDataPath

    'otherwise, use the default
    Else
    strPath = CurrentProject.Path & "GISLaborData.mdb"
    End If 'mstrDataPath <> ""

    'test for a valid file location
    If Dir(strPath) = "" Then
    'if not a valid path, open the fileopen
    'dialog to get the location
    Set objDlg = New clsOpenSaveDialog
    With objDlg
    .Title = "Select the new path to the " _
    & "GIS Labor database"
    .Show
    strPath = .ReturnFilePath
    End With 'objDlg

    'if the Cancel button was clicked on
    'the dialog, exit the sub
    If strPath = "" Then
    MsgBox "You must specify the GISLaborData.mdb " _
    & "path to continue"
    Exit Sub

    'otherwise, update the module level variable
    Else
    mstrDataPath = strPath
    End If 'strPath = ""
    End If 'Dir(strPath) = "" Then

    'get the employee id from cboEmployee
    strEmplID = Nz([cboEmployee].Column(0), 0)

    'create the SQL string
    If [cboMonth].Column(1) = "*" Or _
    IsNull([cboMonth].Column(0)) _
    And varWeek = "" Then

    'return all the records
    strSQL = "SHAPE " _
    & "(SHAPE {SELECT E.ELastName AS LastName, " _
    & "E.EFirstName AS FirstName, " _
    & "P.ProjName AS Project, L.LWkBeginDt as " _
    & "WeekBegins, L.LWkEndDt as WeekEnds, " _
    & "L.LaborHours AS Hours, L.LConfirmed AS " _
    & "Confirmed FROM (tblEmployee AS E INNER JOIN " _
    & "tblLaborData AS L ON E.EmplID = L.LEmplID) " _
    & "INNER JOIN tlkpProjName AS P ON L.LProjID = " _
    & "P.ProjNameID} AS rstLabor COMPUTE rstLabor, " _
    & "SUM(rstLabor.[Hours]) AS ProjectHours BY " _
    & "LastName, FirstName, Project ) AS rstSummary " _
    & "COMPUTE rstSummary, SUM(rstSummary.ProjectHours) " _
    & "AS TotalHours BY LastName, FirstName "

    Else
    'return only the records for the period

    If varWeek = "" Then
    strSQL = "SHAPE " _
    & "(SHAPE {SELECT E.ELastName AS LastName, " _
    & "E.EFirstName AS FirstName, P.ProjName AS Project, " _
    & "L.LWkBeginDt as WeekBegins, L.LWkEndDt as WeekEnds, " _
    & "L.LaborHours AS Hours, L.LConfirmed AS Confirmed " _
    & "FROM (tblEmployee AS E INNER JOIN tblLaborData " _
    & "AS L ON E.EmplID = L.LEmplID) INNER JOIN " _
    & "tlkpProjName AS P ON L.LProjID = P.ProjNameID " _
    & "WHERE ([LWkBeginDt]>=#" & [cboMonth].Column(0) _
    & "/1/" & intYr & "# AND [LWkBeginDt]<#" _
    & (Val([cboMonth].Column(0)) + 1) & "/1/" & intYr & "#"

    If strEmplID <> "0" Then
    strSQL = strSQL & " AND E.EmplID Like " & strEmplID
    End If 'strEmplID <> "0"

    strSQL = strSQL & ")} AS rstLabor " _
    & "COMPUTE rstLabor, SUM(rstLabor.[Hours]) AS " _
    & "ProjectHours BY LastName, FirstName, Project) AS rstSummary " _
    & "COMPUTE rstSummary, SUM(rstSummary.ProjectHours) " _
    & "AS TotalHours BY LastName, FirstName "

    Else 'varWeek = ""
    strSQL = "SHAPE " _
    & "(SHAPE {SELECT E.ELastName AS LastName, " _
    & "E.EFirstName AS FirstName, " _
    & "P.ProjName AS Project, L.LWkBeginDt as " _
    & "WeekBegins, L.LWkEndDt as WeekEnds, " _
    & "L.LaborHours AS Hours, L.LConfirmed AS Confirmed " _
    & "FROM (tblEmployee AS E INNER JOIN tblLaborData " _
    & AS L ON E.EmplID = L.LEmplID) INNER JOIN " _
    & "tlkpProjName AS P ON L.LProjID = P.ProjNameID " _
    & "WHERE ([LWkBeginDt]>=#" & varWeek _
    & "# AND [LWkBeginDt]<#" & DateAdd("ww", 1, varWeek) & "# "

    If strEmplID <> "0" Then
    strSQL = strSQL & " AND E.EmplID Like " & strEmplID
    End If 'strEmplID <> "0"

    strSQL = strSQL & ")} AS rstLabor " _
    & "COMPUTE rstLabor, SUM(rstLabor.[Hours]) AS " _
    & "ProjectHours BY LastName, FirstName, Project) " _
    & "AS rstSummary COMPUTE rstSummary, " _
    & "SUM(rstSummary.ProjectHours) AS TotalHours " _
    & "BY LastName, FirstName "

    End If 'varWeek = ""
    End If '[cboMonth].Column(1) = "*" Or _
    IsNull([cboMonth].Column(0)) _
    And varWeek = ""

    'initialize the connection and recordset objects
    If mcnn Is Nothing Then
    'initialize the connection
    Set mcnn = New ADODB.Connection
    'set the connection object provider for
    'a shaped recordset
    mcnn.Provider = "MSDataShape"

    'open the connection to the data source
    mcnn.Open "Data Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
    & strPath & ";"
    End If 'mcnn Is Nothing

    On Error Resume Next
    'destroy and recreate any existing recordset
    Set mrst = Nothing
    Set mrst = New ADODB.Recordset

    'open the recordset
    With mrst
    .Source = strSQL
    .ActiveConnection = mcnn
    .CursorType = adOpenStatic
    .LockType = adLockReadOnly
    .Open , , , , adCmdText
    End With 'mrst

    If mcnn.Errors.Count <> 0 Then
    For Each errCurr In mcnn.Errors
    MsgBox errCurr.Number & "--" & errCurr.Description
    Next errCurr
    mcnn.Errors.Clear
    Err.Clear
    End If

    'report any error opening the recordset
    If Err Then MsgBox Err.Number & "--" & Err.Description

    'destroy any object variables
    Set objDlg = Nothing
    Set errCurr = Nothing
    End Sub 'CreateShapedRecset()</pre>

    Charlotte

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: MS Hierarchical FlexGrid

    Thanks for that - I'll read and try to digest when I have a bit more time!
    The only thing I can say is that the code in the MSDN library specifies the connection's CursorLocation = adUseNone but I'll admit I have no idea if that would affect your situation.
    It's a little humbling to see the level the professionals work at! <img src=/S/bow.gif border=0 alt=bow width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: MS Hierarchical FlexGrid

    I don't see how the cursorlocation would affect anything and that is probably for a SQL Server connection, anyhow, since adUseNone doesn't appear as a choice for cursorlocation in this Jet recordset. The recordset works and populates the flexgrid, it just doesn't grab the most current value of the confirmed field after I update it and recreate the recordset. If you recreate it again with different parameters, you get the most recent values. <img src=/S/scream.gif border=0 alt=scream width=15 height=15>

    As for humble, I feel the same way. I'm just a working programmer struggling to keep up with the new technology. So I bow before the guys who are way out there ahead of me.
    Charlotte

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: MS Hierarchical FlexGrid

    Yes, I discovered shortly afterward that adUseNone was provided for backward compatibility and now seems to have disappeared. I only mentioned it as I've had a few oddities according to whether I used a client-side or server-side cursor, but that's mainly because I'm still stumbling along with a lot of this stuff!
    I'd be very interested as and when you do sort this glitch out though!
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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