How to sort table with VBA

[Troy]

Public Sub test()

DoCmd.OpenTable ("OSM Table 1")

End Sub

Amazingly, I've gotten this far, now I am wondering how to sort the
database in ascending order according to field called [Meal Total] and
then select the first record. It's all got to be in VBA, can anyone
help?

Thanks

[Bob Quintal]

"Troy" <Tr**********@gmail.comwrote in
news:11**********************@b28g2000cwb.googlegr oups.com:

Public Sub test()

DoCmd.OpenTable ("OSM Table 1")

End Sub

Amazingly, I've gotten this far, now I am wondering how to
sort the database in ascending order according to field called
[Meal Total] and then select the first record. It's all got
to be in VBA, can anyone help?

Thanks

You can't sort a table. You make a query on the table and use that
query instead of the table.
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[DFS]

Troy wrote:

Public Sub test()

DoCmd.OpenTable ("OSM Table 1")

End Sub

Amazingly, I've gotten this far, now I am wondering how to sort the
database in ascending order according to field called [Meal Total] and
then select the first record. It's all got to be in VBA, can anyone
help?

Thanks

Do
You can mess around and do it like this:

DoCmd.OpenTable "Table1"
DoCmd.GoToControl "field name"
DoCmd.FindRecord "VALUE", acAnywhere, False, acSearchAll, True, acCurrent
DoCmd.RunCommand acCmdSortAscending
DoCmd.RunCommand acCmdSelectRecord
But like B Quintal says, you should probably just run and open a query on
the table.

[Terry Kreft]

You should not open tables.

You should use a form for data entry and a report for data review.

Either way, you use a query as the recordsource as you can then sort the
data in any way you want.
--

Terry Kreft
"Troy" <Tr**********@gmail.comwrote in message
news:11**********************@b28g2000cwb.googlegr oups.com...

Public Sub test()

DoCmd.OpenTable ("OSM Table 1")

End Sub

Amazingly, I've gotten this far, now I am wondering how to sort the
database in ascending order according to field called [Meal Total] and
then select the first record. It's all got to be in VBA, can anyone
help?

Thanks

[DFS]

Terry Kreft wrote:

You should not open tables.

This is bogus advice, I think.

You should open tables if you need to or if you want to. There's certainly
no need to create a form and report for every table in your system, eg just
to edit a small lookup table (or even a large transaction table).
Especially if you "know" your data.

You should use a form for data entry and a report for data review.

Why? Though a well-defined form can give you more control and safety, it's
no panacea, and building good validation rules and proper data typing can
aid in entering data directly in tables.

Either way, you use a query as the recordsource as you can then sort
the data in any way you want.

Yes. Or you can filter and sort it pretty much any way you want if you open
the table directly.

"Troy" <Tr**********@gmail.comwrote in message
news:11**********************@b28g2000cwb.googlegr oups.com...

>Public Sub test()

DoCmd.OpenTable ("OSM Table 1")

End Sub

Amazingly, I've gotten this far, now I am wondering how to sort the
database in ascending order according to field called [Meal Total]
and then select the first record. It's all got to be in VBA, can
anyone help?

Thanks

[David W. Fenton]

"Terry Kreft" <te*********@mps.co.ukwrote in
news:AY********************@karoo.co.uk:

You should not open tables.

You should use a form for data entry and a report for data review.

Either way, you use a query as the recordsource as you can then
sort the data in any way you want.

In a report, don't sort your underlying recordsource, but use the
report's grouping and sorting.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Rick Brandt]

DFS wrote:

Terry Kreft wrote:

>You should not open tables.

This is bogus advice, I think.

You should open tables if you need to or if you want to. There's
certainly no need to create a form and report for every table in your
system, eg just to edit a small lookup table (or even a large
transaction table). Especially if you "know" your data.

The advice assumes that the user of a database is not the developer of the
database. No one would dispute that the developer can make entries into
utility tables and/or make administrative corrections to other data directly
in the tables.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Tim Marshall]

David W. Fenton wrote:

In a report, don't sort your underlying recordsource, but use the
report's grouping and sorting.

That's my approach and I prefer to base reports on unordered queries to
cut back on the unnecessary processing I assume takes place with the
order by clause.

But I vaguely recall at some point wasn't/isn't there some report
property one can turn on to accept the sorting done by a query?

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Terry Kreft]

The OP was talking about writing code to open the table.

This implies that they are writing an app for someone else to use.

Only an idiot or a grossly inexperienced developer would allow users direct
access to base tables.

I believe the OP to be inexperienced.

--

Terry Kreft
"DFS" <nospam@dfs_.comwrote in message
news:X6****************@bignews1.bellsouth.net...

Terry Kreft wrote:

You should not open tables.

This is bogus advice, I think.

You should open tables if you need to or if you want to. There's

certainly

no need to create a form and report for every table in your system, eg

just

to edit a small lookup table (or even a large transaction table).
Especially if you "know" your data.

You should use a form for data entry and a report for data review.

Why? Though a well-defined form can give you more control and safety,

it's

no panacea, and building good validation rules and proper data typing can
aid in entering data directly in tables.

Either way, you use a query as the recordsource as you can then sort
the data in any way you want.

Yes. Or you can filter and sort it pretty much any way you want if you

open

the table directly.

"Troy" <Tr**********@gmail.comwrote in message
news:11**********************@b28g2000cwb.googlegr oups.com...

Public Sub test()

DoCmd.OpenTable ("OSM Table 1")

End Sub

Amazingly, I've gotten this far, now I am wondering how to sort the
database in ascending order according to field called [Meal Total]
and then select the first record. It's all got to be in VBA, can
anyone help?

Thanks

[David W. Fenton]

Tim Marshall <TI****@PurplePandaChasers.Moertheriumwrote in
news:e9**********@coranto.ucs.mun.ca:

But I vaguely recall at some point wasn't/isn't there some report
property one can turn on to accept the sorting done by a query?

I don't know. I just learned a long time ago that sorting the
recordsource was so often a waste of time that I just stopped
trying!

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/