How to sort table with VBA 
July 17th, 2006, 10:15 PM
| | | |
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 | 
July 18th, 2006, 12:35 AM
| | | | re: How to sort table with VBA
"Troy" <TroyDDaniels@gmail.comwrote in
news:1153171240.846503.122570@b28g2000cwb.googlegr oups.com: Quote:
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 | 
July 18th, 2006, 12:45 AM
| | | | re: How to sort table with VBA
Troy wrote: Quote:
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. | 
July 18th, 2006, 08:35 AM
| | | | re: How to sort table with VBA
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" <TroyDDaniels@gmail.comwrote in message
news:1153171240.846503.122570@b28g2000cwb.googlegr oups.com... Quote:
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
>
| | 
July 18th, 2006, 10:45 AM
| | | | re: How to sort table with VBA
Terry Kreft wrote: Quote: |
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. Quote: |
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. Quote:
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. Quote:
"Troy" <TroyDDaniels@gmail.comwrote in message
news:1153171240.846503.122570@b28g2000cwb.googlegr oups.com... Quote:
>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
| | | 
July 18th, 2006, 01:25 PM
| | | | re: How to sort table with VBA
"Terry Kreft" <terry.kreft@mps.co.ukwrote in
news:AYacnV-qS7-lDCHZSa8jmw@karoo.co.uk: Quote:
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/ | 
July 18th, 2006, 01:35 PM
| | | | re: How to sort table with VBA
DFS wrote: Quote:
Terry Kreft wrote: Quote: |
>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 | 
July 18th, 2006, 02:05 PM
| | | | re: How to sort table with VBA
David W. Fenton wrote: Quote:
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 | 
July 18th, 2006, 02:45 PM
| | | | re: How to sort table with VBA
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:X62vg.7545$IB.2736@bignews1.bellsouth.net... Quote:
Terry Kreft wrote: Quote: |
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 Quote: |
no need to create a form and report for every table in your system, eg
| just Quote:
to edit a small lookup table (or even a large transaction table).
Especially if you "know" your data.
>
>
> Quote: |
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 Quote:
no panacea, and building good validation rules and proper data typing can
aid in entering data directly in tables.
>
>
> Quote:
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 Quote:
the table directly.
>
>
>
> Quote:
"Troy" <TroyDDaniels@gmail.comwrote in message
news:1153171240.846503.122570@b28g2000cwb.googlegr oups.com... Quote:
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
| | >
>
| | 
July 19th, 2006, 12:35 AM
| | | | re: How to sort table with VBA
Tim Marshall <TIMMY!@PurplePandaChasers.Moertheriumwrote in
news:e9ili2$1a0$1@coranto.ucs.mun.ca: Quote:
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/ |  | | | | /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 225,662 network members.
|