Automatic reordering of Records in Fields

[Guest]

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?

 

[Rick Brandt]

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?

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.

 

[Guest]

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

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

 

[Rick Brandt]

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

It does not matter whether the order you see when you look directly at the table
is the order you want or the order you don't want. The ONLY way to guarantee a
form or report or ListBox/ComboBox will display rows in a certain order is to
use an explicit sort order to force it. For the RowSource of a ComboBox use a
query and apply the sorting you want in that query.

 

[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

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]

 

[Guest]

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,

You solved my problem.
Bryan