MS Heirarchial Flexgrid Help (1 Viewer)

[Tanner65]

I'm working on a form that has a MSHFlexgrid located in it and have two questions. A preface, the sort events are occurring on a the single click event (shown below).

1)Once it pulls the information, the recordset connected to the database creates a clone and populates the MSHFlexgrid recordset property.

So, after the recordset has been populated, the recordset is sorted and after its sorted, it drops the entire recordset. As in, I can no longer access it unless I reopen the first recordset and clone it again. The only line it is clearing on is the line containing MSHFlexgrid.RecordSet.Sort = "blah". If I skip the line the recordset is still there, only that line changes.

How do I keep the recordset from being cleared?

2) When I step through the code the sort column is bolded. But when I run the code, I see the column become bold and return to the original non-bolded font. How do I keep it bolded? It's a necessity for them to be bolded.

Below is the code to populate set the recordsets and sort the columns.

Dim rsSearch As New ADODB.Recordset
Dim rsConn As New ADODB.Connection
Dim sSQL As String, sWhere As String
Dim bRecordSet As Boolean
Dim m_SortColumn As Integer
Dim sSortType As String
'flgrdOrderLookup is the MSHFlexgrid on the form
Private Sub ProcessSearch()
    Dim lrsRecordCount As Long, lCurrentColumn As Long
    Dim sRowData As String
    With rsConn
        .CursorLocation = adUseClient
        .ConnectionString = "DSN=Database"
        .CommandTimeout = 20
        .ConnectionTimeout = 15
    End With
    
    
    sSQL = "Select * From ""Database Table"""
    sWhere = "Where FieldName ='FieldValue'"
    
    On Error Resume Next
    If rsSearch.State > 0 Then rsSearch.Close
    If Me.flgrdOrderLookup.Recordset.State > 0 Then Me.flgrdOrderLookup.Recordset.Close
    If rsConn.State < 1 Then rsConn.Open
    On Error GoTo 0
    rsSearch.CursorLocation = adUseClient
    rsSearch.Open sSQL & sWhere, rsConn, adOpenDynamic, adLockReadOnly, adCmdText
    Set rsSearch.ActiveConnection = Nothing
    rsConn.Close
    Set Me.flgrdOrderLookup.Recordset = rsSearch.Clone
    rsSearch.Close
    bRecordSet = True
    
    Me.flgrdOrderLookup.Rows = Me.flgrdOrderLookup.Recordset.RecordCount + 2
    Me.flgrdOrderLookup.FixedRows = 1
    
    SortByColumn m_SortColumn
    flgrdOrderLookupColumnHeaderBold
    
End Sub

' Sort by the indicated column.
Private Sub SortByColumn(ByVal sort_column As Integer)
        
    ' Restore the previous sort column's name.
    If m_SortColumn <> sort_column Then
        With Me.flgrdOrderLookup
            .Row = 0
            .col = m_SortColumn
            .CellFontBold = False
        End With
        sSortType = " ASC"
    ElseIf m_SortColumn = sort_column Then
        If sSortType = " DESC" Then sSortType = " ASC" Else sSortType = " DESC"
    Else
        sSortType = " DESC"
    End If
    
    'On Error Resume Next
'        Set Me.flgrdOrderLookup.Recordset = Nothing
'        Set Me.flgrdOrderLookup.Recordset = rsSearch
    Me.flgrdOrderLookup.Recordset.Sort = CStr(Chr(91) & _ 
Me.flgrdOrderLookup.TextMatrix(0, sort_column) & Chr(93) & sSortType)
'    ^--This line is where the recordset is removed from the FlexGrid

    On Error GoTo 0
    
    m_SortColumn = sort_column
    
End Sub

Private Sub flgrdOrderLookupColumnHeaderBold()
    With Me.flgrdOrderLookup
        .Row = 0
        .col = m_SortColumn
        If .CellFontBold = False Then .CellFontBold = True
    End With
End Sub

Private Sub flgrdOrderLookup_MouseDown(ByVal Button As Integer, _ 
ByVal Shift As Integer, ByVal X As Long, ByVal Y As Long)
    If Me.flgrdOrderLookup.MouseRow > 0 Then Exit Sub
    'DoCmd.Echo False
    SortByColumn Me.flgrdOrderLookup.MouseCol
    flgrdOrderLookupColumnHeaderBold
    DoCmd.Echo True
End Sub

I hope this all makes sense, I'm a little bit distracted today. Please ask questions as necessary and I'l hopefully be able to clarify some things.

Stephen

 

[Tanner65]

No one can help or at least lend some direction?

 

[Pat Hartman]

Non-Access controls are rarely used so we don't know much about them. I am going to guess that you should move the sort to the query which would be more efficient anyway.

 

[Tanner65]

Non-Access controls are rarely used so we don't know much about them. I am going to guess that you should move the sort to the query which would be more efficient anyway.

Thanks for the help Pat.

I've tried moving the sort to the query and it has no impact on either one of those problems.

I'm also trying to cut down on the interactions to the database so, I'm attempting to eliminate the need to requery.

 

[Pat Hartman]

That's why the sort should be in the query. That way it happens on the server before the grid is populated. You should have no need to resort the grid after it is loaded although the user may want the option.