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
