Help Please I need to sort a table but this code does not work?

[Manny Somarriba]

I have problems with this sort this does not update my records in my database. I can not find what wrong is on it.

I want to sort a table in my database any time the user clcik on a button and answer

Dim dbs As Database
Dim rst As Recordset
Dim rstSort As Recordset
Dim strName As Variant

strName = _
Trim(InputBox("Enter Last Name first letter eg: [A] for Anderson"))
If strName = "" Then Exit Sub

Set dbs = OpenDatabase("C:\Address\Address.mdb")
Set rst = _
dbs.OpenRecordset(strName, _
dbOpenDynaset)

With rst
SortOutput "Original Recordset:", rst
.Sort = "LastName, FirstName"
' Print report showing Sort property and record order.
SortOutput _
"Recordset after changing Sort property:", _
rst
' Open new Recordset from current one.
Set rstSort = .OpenRecordset
' Print report showing Sort property and record order.
SortOutput "New Recordset:", rstSort
rstSort.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

[COLOR=blue][COLOR=blue][COLOR=darkblue][COLOR=darkblue][PHP]

[IROY55]

Many people are using Sort property of a Recorset object instead of sending SQL statement:
"Select f1, f2, f3 From Table1 Order By f2, f1, f3"

Besides, SQL works much faster then Sort propety.

[Manny Somarriba]

hi Iroy55 how are you?
After read your response I got think about this

What about the values in code for sorting a recordset. I means how can I coding the f1, f2, f3, etc. I suppose every key has a value. How can I codify if I want this recordset in descendent order or if I want sort an specific field? I am sorry about my opinion, but I am new in Visual Basic, before I was a COBOL programmer.

Thank you, Iroy55

[marnitzg]

f1 f2 f3 are attribute names.
If you want to retrieve all data then use the following:
"Select * From Table1 Order By LastName Desc, FirstName Asc"
Desc = descending
Asc = ascending

[Manny Somarriba]

I did something that you say, but now I have another problem. The problem is table name not found or not declarated.!

Can you help me with this please I think I feel a little more close to solve it.


Dim dbs As Database
Dim rst As Recordset
Dim strName as String

strName = _
Trim(InputBox("Enter Last Name first letter eg: [A] for Anderson"))
If strName = "" Then Exit Sub

Set dbs = OpenDatabase("C:\Address\Adddress.mdb")

' Open a Recordset from an SQL statement that specifies a
' sort order.

Set rst = dbs.OpenRecordset("SELECT * " & _
"FROM strName ORDER BY LastName, FirstName", _
dbOpenDynaset)

dbs.Close

End Sub

Thank you for your help

[IROY55]

The "problem" is your SQL statement. Change it to the following:

Set rst = dbs.OpenRecordset("Select * From " & strName & _
" Order By LastName, FirstName Asc", dbOpenDynaset)

[Manny Somarriba]

To many thanks Iroy55.

I do think it will work so good after this, so I think this issue is closed

Thanks everyone!