By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,986 Members | 1,487 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,986 IT Pros & Developers. It's quick & easy.

Problem sorting tables in visual basic

P: 2
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
Nov 11 '06 #1
Share this Question
Share on Google+
3 Replies


100+
P: 1,646
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 :)
Nov 11 '06 #2

P: 2
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
Nov 11 '06 #3

P: 9
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
Nov 12 '06 #4

Post your reply

Sign in to post your reply or Sign up for a free account.