Form in wrong sequence

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form that displays data from a table. The table is ordered by an autonumber id. The data is last name and first name. I want the form to display names in order by last, first. I updated the form property "order by" to do that. The records are still displayed in id sequence.

The form has two subforms displaying data related to the people on the main form, and a second related to the first subform.

How can I get the form to present the set of related records in lastname,firstname sequence?
 
Create a query that displays the results the way you want, then use that
query as the recordsource for the form.

--
Ken Snell
<MS ACCESS MVP>

Fred said:
I have a form that displays data from a table. The table is ordered by an
Click to expand...
autonumber id. The data is last name and first name. I want the form to
display names in order by last, first. I updated the form property "order
by" to do that. The records are still displayed in id sequence.
The form has two subforms displaying data related to the people on the
Click to expand...
main form, and a second related to the first subform.
How can I get the form to present the set of related records in
Click to expand...
lastname,firstname sequence?
 
Thanks. That works.

However, I am confused about why there would be an order by property in the form if it doesn't do anything...
 
Me too, Fred. It works ok for me. What exactly did you actually put
in the property setting?

- Steve Schapel, Microsoft Access MVP
 
Last_Name,First_Name (the names of the fields in the table)

If there's some syntax to this that I'm missing, fine--but there's no help and no error given...and I can't find anything in CH49 of Office XP Inside and Out (Creating Forms...).
 
Fred,

Unless this is a .adp database project, as against an .mdb, I can't
think of an explanation for your experience. As an experiment, you
could try enclosing the field names in []s, i.e.
[Last_Name], [First_Name]
Other than that, all I can say is... it works for me. Sorry.

- Steve Schapel, Microsoft Access MVP
 
Fred said:
I have a form that displays data from a table. The table is ordered
by an autonumber id. The data is last name and first name. I want
the form to display names in order by last, first. I updated the
form property "order by" to do that. The records are still displayed
in id sequence.

The form has two subforms displaying data related to the people on
the main form, and a second related to the first subform.

How can I get the form to present the set of related records in
lastname,firstname sequence?
Click to expand...

I think the problem you're having is that the form also has an OrderByOn
property, which determines whether the OrderBy property is applied or
not, but that property isn't exposed on the property sheet (unlike a
report's property sheet). I could be wrong, but I think there are only
two ways to set this property:

1. Open the form manually, click on the field you want to sort by, and
click one of the Sort buttons.

2. In VBA code, set both the form's OrderBy and OrderByOn properties.
They will be saved with the form.

However, the most reliable way to get the form to display records in the
order you want is to do as Ken suggested: forget the OrderBy property
and base the form on a query that sorts records the way you want. Even
then, if you allow the user access to the standard menus, the user can
sort the form any way he wants once it's open, and that will stick
unless you clear the OrderByOn property in the form's Open or Close
event.
 
Steve, thanks.

When I went in to make the change you suggested, I noticed that Access had changed the Order by property to "Residents.Last_Name,Residents.First_Name" (Residents is the table name)---I changed the record source property back to the table (ordered by record id, not name) from the query (ordered by name) and it works--the brackets don't seem to make any difference.

So, the problem apprears to be solved, for now. However, I believe this to be usability problem: if the table name is a required prefix, then there yould be an error ir it's omitted. If it's not required, it should work.
 
Back
Top