Automatic reordering of Records in Fields

B

Bryan

If I set up a Table containing Records then in another Table wish to use
that list of records and set the Data Type as Lookup Wizard, the order of the
records in the original Table has been automatically set to Alphebetical or
the reverse. How can I prevent reordering of the input records?
 
B

Bryan

Rick Brandt said:
Tables in database tables have no inherent order that can be relied upon. Use a
query for your RowSource and set an explicit sort order in the query.

If you want "order of entry" then you need to add a field to your table that
records either an incrementing number or the Date and Time of entry so that can
be sorted on.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com


Rick
Click to expand...
Thank you for your answer which probably answers my question except that I'm
a new user and don't fully understand it. Although I appreciate that there is
no inherent order in the tables nevertheless the originalk table is used only
as as a drop down table and yet the records IN THE ORIGINAL TABLE all become
reordered permanatly alphabetically subsequently. The Lookup Wizard asks if I
want the table ordered and it's default is Alphabetical and although I leave
it blank could this be the reason for reordering the records and if so how do
I stop that?
Bryan
 
J

John Vinson

Thank you for your answer which probably answers my question except that I'm
a new user and don't fully understand it. Although I appreciate that there is
no inherent order in the tables nevertheless the originalk table is used only
as as a drop down table and yet the records IN THE ORIGINAL TABLE all become
reordered permanatly alphabetically subsequently. The Lookup Wizard asks if I
want the table ordered and it's default is Alphabetical and although I leave
it blank could this be the reason for reordering the records and if so how do
I stop that?
Bryan
Click to expand...

You stop it *BY NOT USING A TABLE*.

A table is an unordered "heap" of records. Access will
(inconsistantly!) display it in Primary Key order, so if you've made
the alphanumeric field the primary key that's the order you'll get.

If you want a different order for the records, create a Query based on
the table; sort the query by the field which defines the desired
order. If there is no such field you will have to create one.

Use this Query for the lookup field - or, perhaps better, don't use
the misdesigned, misleading, unnecessary Lookup Wizard *at all*:

http://www.mvps.org/access/lookupfields.htm

John W. Vinson[MVP]
 
B

Bryan

John Vinson said:
You stop it *BY NOT USING A TABLE*.

A table is an unordered "heap" of records. Access will
(inconsistantly!) display it in Primary Key order, so if you've made
the alphanumeric field the primary key that's the order you'll get.

If you want a different order for the records, create a Query based on
the table; sort the query by the field which defines the desired
order. If there is no such field you will have to create one.

Use this Query for the lookup field - or, perhaps better, don't use
the misdesigned, misleading, unnecessary Lookup Wizard *at all*:

http://www.mvps.org/access/lookupfields.htm

John W. Vinson[MVP]
Thanks John and Rick,
Click to expand...
You solved my problem.
Bryan
 
Top