Sorting a DBGrid


I'n writing a VB-application which uses a DBGrid-component (DBGrid32.ocx).

My DbGrid is connected to a MS-Access database.
The recordsource-property is filled with a certain table in the DB.
I don't want to use a query.

I want the grid to be sorted on a certain field of the linked table.
How can I do this ?

Are there other DBGrid-components (freeware) who are even better than the standard DBGrid I'm using ?

Thanx in advance,

The M@yor
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bob LearnedCommented:
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.

Auerelio VasquezETL DeveloperCommented:
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.
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Bob LearnedCommented:
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
End Sub

Private Sub Form_Load()

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

wimmeyvaertAuthor Commented:
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
The irsMembers is a public recordset variable in the Form storing the recordset return from my stored procedure retriving all member from my database.

1st Click -> acsending order
2nd Click -> descending order

Private Sub dgMembers_HeadClick(ByVal ColIndex As Integer)

    If irsMembers.Sort = irsMembers.Fields(ColIndex).Name Then
        irsMembers.Sort = irsMembers.Fields(ColIndex).Name & " desc"
        irsMembers.Sort = irsMembers.Fields(ColIndex).Name
    End If
    Set dgMembers.DataSource = irsMembers
End Sub

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wimmeyvaertAuthor Commented:
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)
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"
      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)

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)

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.