473,734 Members | 2,788 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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
programmaticall y 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
3 1872
On Sun, 08 Aug 2004 20:51:19 GMT, John Baker <Ba******@Veriz on.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
programmatical ly 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
complication s 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(Cance l 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
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
"tblClientSourc e."

4.) Create a new query based upon this "tblClientSourc e" table. This query
will contain all fields in the "tblClientSourc e" 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****@datacen tricsolutions.c om> wrote in message
news:41******** *******@netnews .comcast.net...
On Sun, 08 Aug 2004 20:51:19 GMT, John Baker <Ba******@Veriz on.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 tableprogrammatical ly 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 opencomplication s 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(Cance l 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
Thank you both very much.

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

Best regards

John Baker

John Baker <Ba******@Veriz on.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
programmatical ly 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
complication s 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
1504
by: Simon | last post by:
Hi, Can anybody tell me if it is possible to return a table data type (as opposed to a temporary table) from a stored procedure. I am currently studying for 70-229 using the Thomas Moore book but it doesn't really make it clear, as the text suggests you can't return them but some of his sample questions suggest you can. I can't make it work in Query Analyser so I am assuming that you can't
2
6758
by: harry | last post by:
Hi All ! I open a table in Query Analyzer by right clicking on it and selecting 'Open' and when the data is displayed in the view pane I would like to be able to edit it. It seems however that tables marked as read only (as shown on the top bar e.g. SQL Query Analyzer - ) can not be edited whereas the one that do not have that attribute infront of the name I can edit. Is there a way I can change the read only attribute of a table so...
1
1652
by: Ross | last post by:
The following codes originally provided by a kind responder Toby A Inkster in another newsgroup for working on displaying a table on web capable of sorting are modified by me (a newbie). How to disable sorting by say, length and chart (last few codes at the end)? I find the order is important to display so cannot just single them out. <?php function insert_datatable_cmp ($a, $b) { return ($a]<$b]) ? -1 : 1; }
1
2000
by: Learner | last post by:
Hi there, I have installed Sql server 2005 developer on my machine which already has a Sql server 2000 installed on. Now i am trying to query the Sqlserver 2005 data(Ex: from Person.Address located in AdventureWorks database) in Sqlserver 2000 query analyzer: When i try as Select * from Address it said invalid object. But when i explored AdventureWorks database in the Management studio i
17
3464
by: Lag | last post by:
Was wondering if anyone could help me with a PHP, MySQL problem. I am completely new to PHP and MySQL. I have been trying to find a way to list data from a table on a web page and after each individual item have a hyperlink to allow users to delete the information listed. Can anyone help? i.e. Name Age Sex ------------------------------------------ Adam 21 M delete
1
4924
by: stabbert | last post by:
We are on DB2 UDB 8.2.2 on AIX. I know this question has been asked many times on the ng but I am just not finding a real good answer. I need to be able to not only determine existing table data size and index size but also estimate for the future. I keep seeing info regarding using reorgchk and the such however my dba will not allow us developers to have access to the comands necessary such as runstats. How can a lowely developer such...
1
1230
by: rando1000 | last post by:
I'm trying to pull data into a variable from a temp table. ending up with "The column prefix '#NextCenter' does not match with a table name or alias name used in the query." I know this must be easier than I'm making it, but I can't seem to get it. Select Top 1 Table.FieldName into #NextCenter from Table Inner Join Table2 on Table.Key = Table2.key where Table.Completed = 0 and Table.OtherField = '00000' Declare @testvar as...
1
2051
by: parimi | last post by:
hi this is pawanparimi doing mca working on a project "conducting online examination",i have a table in mysql database, the table schema is RANKS=student_id, section1, section2, section3, totalmarks, rank; i want to sort this table based on the field totalmarks and then i have to give them ranks according to the marks the highest marks get the top rank 1 like that can any one help me please
0
1663
by: neweldho982 | last post by:
Hello Thanks a lot for your help. Our DS people would like to remove the table data by calling a function (can be any other objects as well) in a SELECT query. SELECT TRUNCATE_TABLE_PARTITION('p_table_name','p_suffix' ) from sysibm.sysdummy1; This is a partitioned system in DB2 V9.5. We have already tried most of the truncating features and we could not success in this case..
0
8946
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8776
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
9236
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8186
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6735
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6031
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
3261
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2724
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2180
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.