Sort Recordset

[Manny Somarriba]

Hello everyone how are you?

I wonder is anybody had a Sort sample to sort recordset in a database? I need to build a program to sort my recordset in diferent orders.

Thank you, and have nice day!

[IROY55]

"Select F1,F2,F3 from Table1 where F2=5 Order By F1"

[Manny Somarriba]

May be you misunderstand me I need to build a code in visual basic in order that the user can use it any time

[IROY55]

So, I just showed how tio do that. The only difference will be that you will need to replace F1, etc , Table1 with your real field/table name and open recordset using your SQL statement:
rstMyRecordset.Open strMySQL, ...

[James Stanich]

You can display the data in a grid or listview, and let the user sort the data at will...

[-=SC@RF@C3=-]

select statement order by would do

[moinkhan]

if you insist on sorting a RECORDSET... then

MyRec.Recordsets(0).Sort="MyField"

But
1. It's slow
2. It has a lot of limitations and considerations to be kept in view.. (like doesn't work on Forward-only recordset)

[IROY55]

What the heck are talking about?! You just have to send SQL statement with Order By clause as I and SC@RF@C3 pointed out.

[Edneeis]

I agree with moinkhan there is no need to make another trip to the db when the recordset object has a sort method.

[techgnome]

Originally posted by moinkhan
if you insist on sorting a RECORDSET... then

MyRec.Recordsets(0).Sort="MyField"

But
1. It's slow
2. It has a lot of limitations and considerations to be kept in view.. (like doesn't work on Forward-only recordset)

3. Can't sort on more than one field.

[IROY55]

I agree with moinkhan there is no need to make another trip to the db when the recordset object has a sort method.

Sure, but it only works on 1 (one) field. Using Order By clause will give you flexibilty of sorting on the multiple fields and it's much faster anyway.

[Edneeis]

Says who?

rs.Sort = "Zip, City"

http://www.devguru.com/Technologies/...dset_sort.html

http://vbtechniques.com/content.asp?a=co&cID=924

[Manny Somarriba]

I got this example from microsoft, bu this is not working, it does not replace my fisical table.

Dim dbs As Database
Dim rs As Recordset
Dim rsSort As Recordset

Set dbs = OpenDatabase("C:\Address\Address.mdb")
Set rs = dbs.OpenRecordset("A", dbOpenDynaset)

With rs
SortOutput "Original Recordset:", rs
.Sort = "LastName, FirstName"
' Print report showing Sort property and record order.
SortOutput _
"Recordset after changing Sort property:", rs

' Open new Recordset from current one.
Set rsSort = .OpenRecordset
Set rs = rstSort

' Print report showing Sort property and record order.
SortOutput "New Recordset:", rsSort


rsSort.Close
.Close
End With




dbs.Close

End Sub

Function SortOutput(strTemp As String, rstTemp As Recordset)

With rstTemp
Debug.Print strTemp
Debug.Print " Sort = " & _
IIf(.Sort <> "", .Sort, "[Empty]")

.MoveFirst

' Enumerate Recordset.
Do While Not .EOF
Debug.Print " " & !LastName & _
", " & !FirstName
.MoveNext
Loop

End With

End Function