Access Form showing data in wrong order after update

[Himmahan]

Hi,
I am new to Access.

I have a simple database and a form connected to that database. I have made
simple buttons on the form to add or delete records. The trouble is that
after I add or delete a record using the form, the records are displayed on
the form in wrong order! For instance the last 2 records are displayed first.

Please Help!

 

[JulieS]

Hi,
I am new to Access.

I have a simple database and a form connected to that database. I have
made
simple buttons on the form to add or delete records. The trouble is
that
after I add or delete a record using the form, the records are
displayed on
the form in wrong order! For instance the last 2 records are displayed
first.

Please Help!

The order records are displayed in a form is usually based upon the sort
order in the underlying table. Tables are usually sorted by the primary
key field. If you wish to show the records in a different order in the
when you view them through the form, you can always sort the records in
the form. Click in the field on the form you wish to sort by and click
the Sort Ascending (A to Z) or Sort Descending (Z to A) button on the
toolbar. You can also use the Records | Sort menu command.

I hope this helps.
Julie

 

[John W. Vinson]

Hi,
I am new to Access.

I have a simple database and a form connected to that database. I have made
simple buttons on the form to add or delete records. The trouble is that
after I add or delete a record using the form, the records are displayed on
the form in wrong order! For instance the last 2 records are displayed first.

Please Help!

A Table HAS NO order. If you want to see records in some particular order, you
must - no options! - include a field in the table which defines the order, and
either base your form on a Query sorting the records by that field; or setting
the Form's OrderBy property to that field.

 

[nomadk]

Base your form on a query of the source table. Sort the query by whatever
field you wish.

 

[JulieS]

A Table HAS NO order. If you want to see records in some particular
order, you
must - no options! - include a field in the table which defines the
order, and
either base your form on a Query sorting the records by that field; or
setting
the Form's OrderBy property to that field.

John,
I'm quite new to Access so I'm a bit puzzled by your comment. I
understand the order that records in a table is presented is irrelevant
and attempting to find a record by its record number is meaningless, but
in both table and form properties I see the Order By property. Can you
point me to some information explaining further?

Thank you,
Julie

 

[John W. Vinson]

John,
I'm quite new to Access so I'm a bit puzzled by your comment. I
understand the order that records in a table is presented is irrelevant
and attempting to find a record by its record number is meaningless, but
in both table and form properties I see the Order By property. Can you
point me to some information explaining further?

The OrderBy property simply is a way of creating a concealed Query, which
sorts the records in order. The table datasheet is *presented* in primary key
order, or in its OrderBy order if there is one, as a convenience; but this has
no bearing on the actual disk storage order. New records will be stored
wherever Access finds convenient, and the records will be displayed in
whatever order Access finds convenient, unless you specify the sort order
somewhere.

You could have a Table with one sort specified in its order by property, a
Form with a different order by, and a Report with yet a third sort order
specified in its Sorting and Grouping property. They'd all be independent.

 

[JulieS]

The OrderBy property simply is a way of creating a concealed Query,
which
sorts the records in order. The table datasheet is *presented* in
primary key
order, or in its OrderBy order if there is one, as a convenience; but
this has
no bearing on the actual disk storage order. New records will be
stored
wherever Access finds convenient, and the records will be displayed in
whatever order Access finds convenient, unless you specify the sort
order
somewhere.

You could have a Table with one sort specified in its order by
property, a
Form with a different order by, and a Report with yet a third sort
order
specified in its Sorting and Grouping property. They'd all be
independent.

Thank you for the information John. I appreciate it.
Julie

 

[Ken Sheridan]

Julie:

Its interesting to note that queries also have an OrderBy property. This
seems quite bizarre to me at least, as the result table of a query is
generally sorted by an ORDER BY clause in the underlying SQL statement; this
is the equivalent of the 'sort' row in the grid in query design view.
Consequently a query can have conflicting sort orders, one by virtue of its
ORDER BY clause, the other by virtue of its OrderBy property. If you create
a query and sort it by one column, either in the design grid or in SQL view,
then open the query in datasheet view and sort it on another column by
clicking on the A-Z button on the toolbar, which sets its OrderBy property,
and then save the query, when your open it again the OrderBy property will
govern the order of the result table, not its ORDER BY clause. While I can
understand the mechanics of this, in essence a sorted query is being
re-sorted, any good reason for having an OrderBy property for a query at all
escapes me!

BTW if you are using a query as the RecordSource of a report don't sort the
query; the report will generally (though not invariably as is sometimes said)
ignore the sort order. Use the reports internal sorting and grouping
mechanism instead. Forms on the other hand do respect a query's sort order.

Ken Sheridan
Stafford, England

 

[JulieS]

Julie:

Its interesting to note that queries also have an OrderBy property.
This
seems quite bizarre to me at least, as the result table of a query is
generally sorted by an ORDER BY clause in the underlying SQL
statement; this
is the equivalent of the 'sort' row in the grid in query design view.
Consequently a query can have conflicting sort orders, one by virtue
of its
ORDER BY clause, the other by virtue of its OrderBy property. If you
create
a query and sort it by one column, either in the design grid or in SQL
view,
then open the query in datasheet view and sort it on another column by
clicking on the A-Z button on the toolbar, which sets its OrderBy
property,
and then save the query, when your open it again the OrderBy property
will
govern the order of the result table, not its ORDER BY clause. While
I can
understand the mechanics of this, in essence a sorted query is being
re-sorted, any good reason for having an OrderBy property for a query
at all
escapes me!

BTW if you are using a query as the RecordSource of a report don't
sort the
query; the report will generally (though not invariably as is
sometimes said)
ignore the sort order. Use the reports internal sorting and grouping
mechanism instead. Forms on the other hand do respect a query's sort
order.

Ken Sheridan
Stafford, England

<snip>

Ken,
Thanks so much for your valuable comments as well. It's clear to me
that I can spend an inordinate amount of time reading the posts here as
well as the wealth of information on websites and still have volumes to
discover.

Julie