Order of records on a forn

[Dave Eliot]

I have a two-part question.

First, On the Form Properties I set the Data Properties -- Order By to
LastName; however the records are not alphabetical -- they're somewhat
in the order that they were entered. How can I get them to be alpha by
the field called LastName?

Then, is there any way on the form to select the order that the records
display; ie. LastName or Company?

Thanks for any assistance. I am a beginner so take it slow and easy,
please!

 

[John W. Vinson]

I have a two-part question.

First, On the Form Properties I set the Data Properties -- Order By to
LastName; however the records are not alphabetical -- they're somewhat
in the order that they were entered. How can I get them to be alpha by
the field called LastName?

Base the form on a Query - not directly on your table; and sort that Query by
LastName (or better LastName then FirstName).

Then, is there any way on the form to select the order that the records
display; ie. LastName or Company?

Yes, but you'll need some VBA code to do it. See below...

Thanks for any assistance. I am a beginner so take it slow and easy,
please!

You could have a command button named cmdSort on the form with a Caption
property

&Sort By Company

The ampersand will cause the S to be underlined and make it function as a
hotkey - typing Alt-S will "press" the button. Find the button's Click event
in its properties, click the ... icon by it, and select Code Builder; edit the
code to:

Private Sub cmdSort_Click()
If Me.cmdSort.Caption = "&Sort By Company" Then
Me.cmdSort.Caption = "&Sort By Name"
Me.OrderBy = "[Company]"
Else
Me.cmdSort.Caption = "&Sort By Company"
Me.OrderBy = "[LastName], [FirstName]"
End If
Me.OrderByOn = True
End Sub

This will make the button toggle back and forth - if the form is currently
sorted by name it will let you change to company and vice versa.

Oddly enough, the OrderByOn property of the form must be set to True for the
OrderBy property to take effect, but you can't set it in the properties
window, only in code.

 

[Dave Eliot]

I have a two-part question.

First, On the Form Properties I set the Data Properties -- Order By to
LastName; however the records are not alphabetical -- they're somewhat
in the order that they were entered. How can I get them to be alpha by
the field called LastName?

Base the form on a Query - not directly on your table; and sort that Query by
LastName (or better LastName then FirstName).

Then, is there any way on the form to select the order that the records
display; ie. LastName or Company?

Yes, but you'll need some VBA code to do it. See below...

Thanks for any assistance. I am a beginner so take it slow and easy,
please!

You could have a command button named cmdSort on the form with a Caption
property

&Sort By Company

The ampersand will cause the S to be underlined and make it function as a
hotkey - typing Alt-S will "press" the button. Find the button's Click event
in its properties, click the ... icon by it, and select Code Builder; edit the
code to:

Private Sub cmdSort_Click()
If Me.cmdSort.Caption = "&Sort By Company" Then
Me.cmdSort.Caption = "&Sort By Name"
Me.OrderBy = "[Company]"
Else
Me.cmdSort.Caption = "&Sort By Company"
Me.OrderBy = "[LastName], [FirstName]"
End If
Me.OrderByOn = True
End Sub

This will make the button toggle back and forth - if the form is currently
sorted by name it will let you change to company and vice versa.

Oddly enough, the OrderByOn property of the form must be set to True for the
OrderBy property to take effect, but you can't set it in the properties
window, only in code.

Thanks for such a clear explanation. First the Query did the trick, and
the cmd button with your code was just what I needed. Thank you.

 

[Dave Eliot]

Dave,

Your post is saying that you've set the OrderBy in the forms property
sheet, but that the form doesn't load sorted accordingly????

To cause a resorting of your form, one way I use with some regularity
is to click on the column heading (I'm assuming you're talking about
a continuous form) and have an OnClick event that sorts on that
column. That's sort of a "standard" way Windows accomplishes the
task and the general user is accustom to that.........more-or-less.

Your event code would look something like this:

Private Sub Last_Name_Label_Click()
Me.OrderBy = "LastName"
Me.OrderByOn = True
Me.Requery
End Sub

Private Sub Company_Name_Label_Click()
Me.OrderBy = "Company"
Me.OrderByOn = True
Me.Requery
End Sub

With these two subs, you can easily switch back
and forth between how that form is sorted. (Note
that when providing multi-column sorting, one
would not code as you see here. But since
you're somewhat new to Access, this approach
is more easily understood.)

Bill

I should have mentioned that the form was not continuous -- one record
on the screen at a time. However, your code has been saved for when I do
have column headings and just want to sort on one of them. Thank you.