Datagrid Title sorting 2

[beautieee]

How to do sorting in Datagrid on any title i clicked on?

It was working with MSHFlexgrid but not in Datagrid. Can it be done?

Thank you.

Regards,
Beautieee.

 

[SBerthold]

The column's Datafield is:
DataGrid1.Columns(ColIndex).DataField

So sort the underlying recordset on this field:

A. If the CursorLocation is set to adUseClient, then

DataGrid1.HoldFields
rs.Sort = DataGrid1.Columns(ColIndex).DataField & " ASC"
DataGrid1.Rebind

B. If the CursorLocation is set to adUseServer then
1. add the sort to the ORDER BY clause of the recordset
2. Requery or re-open the recordset
3. Set the grid's DataSource to the recordset again

 

[beautieee]

I was using rs.open ORDER BY command to load data in ordered, but can i sort it according to the title (Header) on datafield? whenver I clicked on Company, it will sorted in Company, or when i click on CustID, it will sort in its order.

Is the pointer in Datagrid flexsible to move to specific Character set when we press a Alphabet? When I click G, it will move the cursor to G recordset.

Thank You.

 

[ChewDoggie]

I used this method to sort a datagrid that's bound to a recordset simply called "rs". I use a variable called "sortord" to toggle between ascending and descending methods. The recordset's cursorlocation is adUseClient.

Private Sub dtGrid_HeadClick(ByVal ColIndex As Integer)
     If ColIndex = 6 And rs.RecordCount > 0 Then
        If sortord = 1 Then
            rs.sort = "lname, fname"
            DoEvents
            sortord = 0 ' change the sortord
        Else
            rs.sort = "lname desc, fname"
            DoEvents
            sortord = 1 ' change the sortord
        End If
    End If
    dtGrid.ClearSelCols
    DoEvents
    Exit Sub
End Sub

HTH

Chewdoggie

10% of your life is what happens to you. 90% of your life is how you deal with it.

 

[beautieee]

Thank you Chew for your reply.

Why is the sortord not a valid property control? Do i need to upgrade any service pack??

 

[ChewDoggie]

I don't believe you do. The flexgrid has a "sort" method. The datagrid was designed to be bound to a recordset, and the sort method is available to ADODB recordsets, so I guess the creators didn't think they were missing anything by omitting that functionality from the grid.

BTW, it's not in my post, but I'd declare sortord as static in the Sub.

Static Sortord as Integer

As for your question:

Is the pointer in Datagrid flexsible to move to specific Character set when we press a Alphabet? When I click G, it will move the cursor to G recordset.


I have a form with an associated textbox (Last Name) that every keystroke the user enters, a subroutine is executed that searches for a matching record in the grid.

It looks like this:

...partial code from sub...
Private Sub txtLName_KeyUp(KeyCode As Integer, Shift As Integer)
    If Trim(txtLname.Text) <> "" Then
        SearchMemberByName
    End If

and the SearchMemberByName Sub looks like this:

...again, this is part of the Sub...
Public Sub SearchMemberByName()
    rs.Find "lname like '" & Trim(txtLname.Text) & "%'", , adSearchForward, 1
    If Not rs.EOF Then
        BKMRK = rs.AbsolutePosition
        racerfound = True
    Else
        LastNameFound = False
        If BKMRK > 0 Then
            rs.AbsolutePosition = BKMRK
        ElseIf rs.RecordCount > 0 Then
            rs.AbsolutePosition = 1
        End If
        racerfound = False
    End If
End Sub

and an intregal part of this feature is the following code:

Private Sub dtGrid_RowColChange(LastRow As Variant, ByVal LastCol As Integer)
    If dtGrid.SelBookmarks.Count > 0 Then
        dtGrid.SelBookmarks.Remove 0
    End If
    If Not rs.EOF Then
        dtGrid.SelBookmarks.Add rs.Bookmark
    End If
End Sub

The BKMRK variable allows the focus to move back to the previously highlighted record if the user entered last name doesn't match.

HTH

Chewdoggie

10% of your life is what happens to you. 90% of your life is how you deal with it.

 

[beautieee]

Thank you Chew for long explanation, but i was searching for column sorting in DATAGRID. No wonder i couldnt find datagrid1.sortord. Anyhow thank you for your reply.

I do have a different method on column sorting for flexgrid on mousedown control, do you want it?

Regards.

 

[ChewDoggie]

I've found that flexgrid is SUBSTANTIALLY easier to code and make user-friendly. I already have code for a keyed letter, but thanks.

Chew


10% of your life is what happens to you. 90% of your life is how you deal with it.

 

[Glasgow]

SortOrd is a variable in ChewDoggie's post, it is not a property. It is just being used to keep track of what the current sort order is so that it can be easily toggled.

By binding the datagrid to a recordset and using the Sort property of the recordset, the datagrid will be refreshed to reflect the recordset's new sequence.

 

[beautieee]

Hi Glasgow,

Noticed with thanks. Just tried out the code and it works great.

Chew, youe code given on headclick is sorting 2 headers only, how to get it done for 3 or more?? Tried with additional IF but did not work.

Thank you very much.

Regards,
Beautieee

 

[Glasgow]

I would have thought that something like:

Select Case ColIndex
  Case 0 :           rs.sort = "lname"
  Case 1 :           rs.sort = "fname"
  Case 2 :           rs.sort = "street"
End Select
DoEevents

would do the trick. Or simply:

rs.sort = dtGrid.Columns(ColIndex).DataField & " asc"
DoEvents

 

[beautieee]

Thanks you Glasgow,it worked.

I was not that dumb thou but just didnt realise there are columns been hide in datagrid. IF and CASE should be working,

BUT

"rs.sort = dtGrid.Columns(ColIndex).DataField & " asc"
DoEvents" worked the best, easy to use.

Regards.