Sorting a DBGrid

You might want to change the DataControl's RecordSource property to an SQL statement.

Such as:

Select * From Table1 Order By Name

Select * From Table1 Order By ProductID

Select * From Table1 Order By DateReceived.

The grid reflects the data source used to populate it, it doesn't know anything about content. You want sorted data, feed it sorted data. TheLearnedOne is correct.

M

i don't think you can use db grid to sort data once it is populated. if the Recordsource is an access query, for instance, lernedone is correct, you can build the query in acces, and just use the order by clause within the query just like you would a view in an RDBMS such as SQL Server. that way the recordsource would just be that query, and you would have to just dropr the query in as  the Recordsource as you would a table.

I put a DBGrid on a form, with a Data control.  I put the following code in the form:

Private Sub DBGrid1_HeadClick(ByVal ColIndex As Integer)

   Data1.RecordSource = "SELECT * FROM Table1 Order By " & DBGrid1.Columns(ColIndex).DataField
   Data1.Refresh
   
End Sub

Private Sub Form_Load()

   Data1.RecordSource = "SELECT * FROM Table1 Order By Field1"
   Data1.Refresh
   
End Sub

Hello Basile,

I appreciate your answer, but I knew this myself already (I mean using a Select-query to fill the grid with sorted data).
But I wondered if there was a way to link a dbgrid to a table (no sql) and set a property of the dbgrid or datasource to sort the data automatically.
But obviously there isn't a way to do that.

Can't you use the indexes of the table in some way to sort the data ?

Best regards
The M@yor ;-)

please sort tha database while adding a record  it self so that when you are in a grid the records will be sorted

try this

all tha best

Hello chnelson,

I've used your code-snippet to make a little application, concerning dbgrid-sorting.
There seems to be an error in your code (RunTime Error 430 - Class doesn't support automation).
The problem has something to do with the last command in the HeadClick-Event
(I suppose it has to be : Set DataSource1.Recordset = irsMembers).

I've worked something out, but I have to use 2 recordset-objects to make the
code work correctly. Am i doing something wrong ?
This is the code I produced, started from your code :

Option Explicit
Dim dbsTest As Database
Dim rstUnsorted As Recordset
Dim rstSorted As Recordset

Private Sub Form_Load()
   Set dbsTest = OpenDatabase("NorthWind.mdb")
   Set rstUnsorted = dbsTest.OpenRecordset("Customers", dbOpenDynaset)
   rstUnsorted.Sort = "ContactName"
   Set rstSorted = rstUnsorted.OpenRecordset
   Set Data1.Recordset = rstSorted
End Sub

Private Sub Form_Unload(Cancel As Integer)
   rstUnsorted.Close
   rstSorted.Close
   dbsTest.Close
End Sub

Private Sub DBGrid1_HeadClick(ByVal ColIndex As Integer)
   If rstSorted.Sort = rstSorted.Fields(ColIndex).Name Then
      rstUnsorted.Sort = rstSorted.Fields(ColIndex).Name & " desc"
   Else
      rstUnsorted.Sort = rstSorted.Fields(ColIndex).Name
   End If
   Set rstSorted = rstUnsorted.OpenRecordset
   rstSorted.Sort = rstUnsorted.Sort
   Set Data1.Recordset = rstSorted
End Sub


Now I just have 1 last question :
Is there a possibility to reduce the number of recordset-objects ?
I just want to use 1 recordset instead of 2.
If you can help me out with this one, you've earned the full 80 points.


Thanx in advance,
The M@yor.

i've already checked by my codes.
i found that i m using MSDATGRD.OCX - Microsoft DataGrid Control 6.0 (SP3) (OLEDB) instead.

i m not sure if this is the reason.

Now, i m trying to use DBGrid32.ocx which u r using now in your codes. To see what's your problem is.

'This solution to the DBGrid sorting problem uses the
'SQL ORDER BY statement as the means to sort the data
'sent to the Grid. The record starts out with a default
'sort field. Then the user has the opportunity to select
'from a list of other possible sort fields. These are
'listed in a combobox (Combo1). When the user selects an
'option in the combobox, the recorsource SQL statement
'is reformulated and the Data Control is refreshed.

Private Sub Combo1_Click()
 'The combobox contains the names of the possible sort fields.
 'When a user clicks a field name then the recorsource for the
 'data control is recalculated to show the new sort option.
 Me![Data1].RecordSource = gswSetSQL(Me![Combo1].Text)
 Me![Data1].Refresh

End Sub


Private Sub Form_Load()

Dim lng As Long

'Add the possible sort fields to the combobox list.
With Me![Combo1]
    .AddItem "ItemID"
    .AddItem "Item"
    .AddItem "PriorityLevel"
    .AddItem "Deadline"
    .AddItem "DateCompleted"
End With

'Set the default sort field.
Me![Combo1].ListIndex = 4

'Calculate the recordsource and refresh the data control.
'The form's default sort is by Date Completed.
Me![Data1].RecordSource = gswSetSQL(Me![Combo1].Text)
Me![Data1].Refresh

End Sub

Private Function gswSetSQL(strSortField) As String
'This function simply recalculates the SQL statement that
'forms the basis of the data control's recordsource.
gswSetSQL = "Select tblActive.StatusIcon, tblActive.ItemID, " & _
"tblActive.Item, tblActive.PriorityLevel, tblActive.Deadline," & _
"tblActive.DateCompleted From tblActive " & _
"Order By " & strSortField

End Function