Problem sorting tables in visual basic

[RichardJB]

Hi,

I am using visual basic in access 2003 and am having a problem with sorting tables which I can't seem to find a solution to, and was hoping that someone might be able to help.

I have a table containing policy numbers and various bits of information about each policy. It is necessary to go through the table in policy order and do various calculations to each row.

So, I have used an SQL query to create a copy of the table sorted by policy number as follows:

Dim STR As String

STR = " SELECT Policy.* INTO PolicyCopy FROM Policy ORDER BY PolicyNumber; "
DoCmd.RunSQL (STR)


I now open the table PolicyCopy as follows:

Dim db As Database
Dim PolicyRecordset As Recordset

Set db = CurrentDb
Set PolicyRecordset = db.OpenRecordset("PolicyCopy", dbOpenTable)


Then I go through the PolicyRecordset by using Movefirst and Movenext's.

However, sometimes the program will crash because the PolicyCopy table is not in the correct order. Sometimes the top few rows, usually about a couple of hundred, will be dumped into the middle of the table. I wondered if anyone knew how to get around this problem

Many thanks

[willakawill]

Hi,

I am using visual basic in access 2003 and am having a problem with sorting tables which I can't seem to find a solution to, and was hoping that someone might be able to help.

I have a table containing policy numbers and various bits of information about each policy. It is necessary to go through the table in policy order and do various calculations to each row.

So, I have used an SQL query to create a copy of the table sorted by policy number as follows:

Dim STR As String

STR = " SELECT Policy.* INTO PolicyCopy FROM Policy ORDER BY PolicyNumber; "
DoCmd.RunSQL (STR)


I now open the table PolicyCopy as follows:

Dim db As Database
Dim PolicyRecordset As Recordset

Set db = CurrentDb
Set PolicyRecordset = db.OpenRecordset("PolicyCopy", dbOpenTable)


Then I go through the PolicyRecordset by using Movefirst and Movenext's.

However, sometimes the program will crash because the PolicyCopy table is not in the correct order. Sometimes the top few rows, usually about a couple of hundred, will be dumped into the middle of the table. I wondered if anyone knew how to get around this problem

Many thanks

Hi. You have the answer really. Just need to put the two statements together.

Set PolicyRecordset = db.OpenRecordset("SELECT * FROM Policy ORDER BY PolicyNumber")

The table type recordset is default.

And away you go :)

[RichardJB]

Hi. You have the answer really. Just need to put the two statements together.

Set PolicyRecordset = db.OpenRecordset("SELECT * FROM Policy ORDER BY PolicyNumber")

The table type recordset is default.

And away you go :)

Thats wonderful, thanks very much

[jpezhil]

I Think into Statement Does Not Allow Order By Class
So please remove Order by policyNumber statement

STR = " SELECT * INTO PolicyCopy FROM Policy "

Dim db As Database
Dim PolicyRecordset As Recordset
Set db = CurrentDb
Set PolicyRecordset = db.OpenRecordset("select * from policycopy order by policynumber")

Thanking U
R.Jayaprakash