VBA Code to Sort Table Records

[jt463]

I have gone to great pains to use VBA to create a table - which comes out with exactly the information I need. Now, I want to use VBA to sort the table on two columns that are in the middle of the record.

So, say I have a table (tbl1) with the following columns:

1. Customer
2. Address
3. City
4. State
5. Zip

I want to use VBA to sort the table on Address and then State. I saw there is an option for me to code something like this (where rst is tbl1):

rst.Sort

But it didn't give me any additional options where I could tell it what fields to sort on.

Any help would be greatly appreciated.

 

[PHV]

Open your recordset with the following SQL code:
"SELECT * FROM tbl1 ORDER BY Address, State"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

Are you pulling the table into a recordset? "rst.Sort" is a property of a recordset object. You can do several things

Sort the data with SQL when you populate the recordset

Set rst = CurrentDb.OpenRecordset ( _
          "Select * From tbl1 " & _
          "Order By Address, State" )

Sort the data after populating the recordset

Set rst = CurrentDb.OpenRecordset ("Select * From tbl1")
rst.Sort = "[Address], [State]"
Set rss = rst.OpenRecordset
[COLOR=black cyan]' Recordset rss now contains the sorted table[/color]

 

[jt463]

Thanks PHV and Golom. Specifically, I used the first suggestion of Golom's which is essentially the same as PHV, and it worked brilliantly!

Thanks for your time.