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

Sorting Table Data (Yes, I know I should have used a query!)

P: n/a
Hi:

At the outset let me admit that I screwed up!

I have built a rather elaborate set of forms and sub forms starting with a client table,
and going down to PO and Line item. This works very well EXCEPT that when I started the
design I failed to consider that client records would be added to the client table in
random order. The table is indexed on client ID which is an auto number field, and as a
result the table is in random order, and the way the records are presented in the form is
random by client too.

I have been able to put the table in the right order on the form by doing a sort on the
name of the client in the form, I would like to be able to SORT the client table
programmatically by client name (a single field) before I go into the form WITHOUT having
to change to the use of a query, and I don't know how to do it. I know a query is what I
should have used at the outset, but moving to a query now would require significant
modification of the entire system, and frankly I would rather not since it would open
complications and would take considerable time that I do not have right now.!

My question is basically, is there a command ( or set of commands) that I could invoke
when the form is opened which would sequence the table by Client.

The table is named : tblClient
and the client name is : Client Name

Your help would be much appreciated

Thanks

John Baker
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On Sun, 08 Aug 2004 20:51:19 GMT, John Baker <Ba******@Verizon.net>
wrote:
Hi:

At the outset let me admit that I screwed up!

I have built a rather elaborate set of forms and sub forms starting with a client table,
and going down to PO and Line item. This works very well EXCEPT that when I started the
design I failed to consider that client records would be added to the client table in
random order. The table is indexed on client ID which is an auto number field, and as a
result the table is in random order, and the way the records are presented in the form is
random by client too.

I have been able to put the table in the right order on the form by doing a sort on the
name of the client in the form, I would like to be able to SORT the client table
programmatically by client name (a single field) before I go into the form WITHOUT having
to change to the use of a query, and I don't know how to do it. I know a query is what I
should have used at the outset, but moving to a query now would require significant
modification of the entire system, and frankly I would rather not since it would open
complications and would take considerable time that I do not have right now.!

My question is basically, is there a command ( or set of commands) that I could invoke
when the form is opened which would sequence the table by Client.

The table is named : tblClient
and the client name is : Client Name

Your help would be much appreciated


It really shouldn't be much to create a query in forms RecordSource.
Just click the build button [...] next to the recordsource property
and proceed.

However, it is good to know how to sort (or Order) the data in a form.
Use the forms On Open event with code like ...

Private Sub Form_Open(Cancel As Integer)
Me.OrderBy = "[Client Name]"
Me.OrderByOn = True
End Sub

Note that Ascending is the default. To descend by the field add DESC;
to the field name.
Me.OrderBy = "[Client Name] DESC;"

- Jim
Nov 13 '05 #2

P: n/a
Hi, John.

You may be in luck, because believe it or not, a query might be able to save
you. Here's how:

1.) Close all database objects until you only have the database window
showing the list of tables in your database.

2.) Turn off Auto-name correct. (Unless you're using Access '97 or
earlier, in which case, you can skip this step.)
a.) Open the Tools -> Options menu to display the "Options" dialog
window.
b.) Select the "General" tab.
c.) Uncheck the "Track name AutoCorrect info" check box.
d.) Select the "OK" button to close the "Options" dialog window.

3.) Change the name of your "tblClient" table to something like
"tblClientSource."

4.) Create a new query based upon this "tblClientSource" table. This query
will contain all fields in the "tblClientSource" table, and will sort
ascending on the "Client Name" field. Save this new query as "tblClient."

All of your database objects that reference the object "tblClient" as a
record source are unlikely to discern whether it's a table or a query. But
if you've got customized VBA code that references DAO TableDefs and such,
then this will mean you've got some programming maintenance work to do, and
that may be what you were referring to when you stated, "would require
significant modification of the entire system." If it _is_ a significant
modification and you've got fewer than 25 forms and subforms that need the
sorted record source, then Jim Allensworth's advice on sorting for each form
would be the way to go.

If this query substitution works for you, you'll remember the mantra, "I
will _always_ use a query for a record source, because queries provide
extreme flexibility, especially when time is extremely short!"

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.
"Jim Allensworth" <Ji****@datacentricsolutions.com> wrote in message
news:41***************@netnews.comcast.net...
On Sun, 08 Aug 2004 20:51:19 GMT, John Baker <Ba******@Verizon.net>
wrote:
Hi:

At the outset let me admit that I screwed up!

I have built a rather elaborate set of forms and sub forms starting with a client table,and going down to PO and Line item. This works very well EXCEPT that when I started thedesign I failed to consider that client records would be added to the client table inrandom order. The table is indexed on client ID which is an auto number field, and as aresult the table is in random order, and the way the records are presented in the form israndom by client too.

I have been able to put the table in the right order on the form by doing a sort on thename of the client in the form, I would like to be able to SORT the client tableprogrammatically by client name (a single field) before I go into the form WITHOUT havingto change to the use of a query, and I don't know how to do it. I know a query is what Ishould have used at the outset, but moving to a query now would require significantmodification of the entire system, and frankly I would rather not since it would opencomplications and would take considerable time that I do not have right now.!
My question is basically, is there a command ( or set of commands) that I could invokewhen the form is opened which would sequence the table by Client.

The table is named : tblClient
and the client name is : Client Name

Your help would be much appreciated


It really shouldn't be much to create a query in forms RecordSource.
Just click the build button [...] next to the recordsource property
and proceed.

However, it is good to know how to sort (or Order) the data in a form.
Use the forms On Open event with code like ...

Private Sub Form_Open(Cancel As Integer)
Me.OrderBy = "[Client Name]"
Me.OrderByOn = True
End Sub

Note that Ascending is the default. To descend by the field add DESC;
to the field name.
Me.OrderBy = "[Client Name] DESC;"

- Jim

Nov 13 '05 #3

P: n/a
Thank you both very much.

I appreciate the advice . Yes it is a sharp lesson learned!!!

Best regards

John Baker

John Baker <Ba******@Verizon.net> wrote:
Hi:

At the outset let me admit that I screwed up!

I have built a rather elaborate set of forms and sub forms starting with a client table,
and going down to PO and Line item. This works very well EXCEPT that when I started the
design I failed to consider that client records would be added to the client table in
random order. The table is indexed on client ID which is an auto number field, and as a
result the table is in random order, and the way the records are presented in the form is
random by client too.

I have been able to put the table in the right order on the form by doing a sort on the
name of the client in the form, I would like to be able to SORT the client table
programmatically by client name (a single field) before I go into the form WITHOUT having
to change to the use of a query, and I don't know how to do it. I know a query is what I
should have used at the outset, but moving to a query now would require significant
modification of the entire system, and frankly I would rather not since it would open
complications and would take considerable time that I do not have right now.!

My question is basically, is there a command ( or set of commands) that I could invoke
when the form is opened which would sequence the table by Client.

The table is named : tblClient
and the client name is : Client Name

Your help would be much appreciated

Thanks

John Baker


Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.