Order By Property

  • Thread starter Thread starter Karen
  • Start date Start date
K

Karen

I have gotten into the habit of never using the OrderBy property on a form
as it seems to do nothing.

I'm using Access 2003, I've set the control source of a form to a table,
e.g. Employees and let's say I want to sort by Last Name so I've tried these
forms in the OrderBy property of the form, all had no effect

"LastName"
"[LastName]"

Is there a bug or am I just missing something really obvious?
 
Karen said:
I have gotten into the habit of never using the OrderBy property on a form
as it seems to do nothing.

I'm using Access 2003, I've set the control source of a form to a table,
e.g. Employees and let's say I want to sort by Last Name so I've tried these
forms in the OrderBy property of the form, all had no effect

"LastName"
"[LastName]"

Is there a bug or am I just missing something really obvious?
Click to expand...

Ready to hit yourself?

OrderByOn needs to be set to True.

(Arrrr...)
 
Hi,

Not sure I can completely help, but I'll give it a shot.
I'm sure there are probably lots of ways to solve this.

1. Do you always want to sort by Last Name?
Why not just base the form on a saved query that sorts by last name?

2. I'm assuming you went to the form's properties and Put LastName on the
"Order By" line. You open the form, but it still is not showing the
employees in alphabetical order by last name. Correct?
Try adding this line to the Form's Open event:

Me.Form.OrderByOn = True

Save the form and see if that solves the problem.

3. On some forms (usually continuous) I give the user the option to sort by
various fields. For example, say I have two fields called Report Group
Number and Report Group Name. I make two small command buttons over each
field in the Form Header and use the little A->Z and Z->A bitmaps.

I code the button Click events like so:

Private Sub cmdSortNameAscending_Click()
Me.txtReportGroupName.SetFocus
DoCmd.RunCommand acCmdSortAscending
End Sub

Private Sub cmdSortNameDescending_Click()
Me.txtReportGroupName.SetFocus
DoCmd.RunCommand acCmdSortDescending
End Sub

Hope that gives you some ideas to try.
Jeff Conrad
Bend, Oregon
 
Jeff and Bas Cost Budde,

OK, so is OrderByOn only set in VBA? It's not a property of the form?

And Jeff, I do end up using queries on the tables to get my data and get it
sorted. It just seemed like I should be able to just use the properties of
the form and not build a query every time for the control source (not that
it takes forever!).

--
Karen

Jeff Conrad said:
Hi,

Not sure I can completely help, but I'll give it a shot.
I'm sure there are probably lots of ways to solve this.

1. Do you always want to sort by Last Name?
Why not just base the form on a saved query that sorts by last name?

2. I'm assuming you went to the form's properties and Put LastName on the
"Order By" line. You open the form, but it still is not showing the
employees in alphabetical order by last name. Correct?
Try adding this line to the Form's Open event:

Me.Form.OrderByOn = True

Save the form and see if that solves the problem.

3. On some forms (usually continuous) I give the user the option to sort by
various fields. For example, say I have two fields called Report Group
Number and Report Group Name. I make two small command buttons over each
field in the Form Header and use the little A->Z and Z->A bitmaps.

I code the button Click events like so:

Private Sub cmdSortNameAscending_Click()
Me.txtReportGroupName.SetFocus
DoCmd.RunCommand acCmdSortAscending
End Sub

Private Sub cmdSortNameDescending_Click()
Me.txtReportGroupName.SetFocus
DoCmd.RunCommand acCmdSortDescending
End Sub

Hope that gives you some ideas to try.
Jeff Conrad
Bend, Oregon

Karen said:
I have gotten into the habit of never using the OrderBy property on a form
as it seems to do nothing.

I'm using Access 2003, I've set the control source of a form to a table,
e.g. Employees and let's say I want to sort by Last Name so I've tried these
forms in the OrderBy property of the form, all had no effect

"LastName"
"[LastName]"

Is there a bug or am I just missing something really obvious?
Click to expand...
Click to expand...
 
Hi Karen,
OK, so is OrderByOn only set in VBA? It's not a property
of the form?
Click to expand...

I *think* so. I'm not completely positive. Bas is the real
expert, I'm just his personal assistant.
And Jeff, I do end up using queries on the tables to get
my data and get it sorted.
Click to expand...

That's good. Most experts believe basing forms on queries
or SQL statements is better than directly on the table
itself.
It just seemed like I should be able to just use the
properties of the form and not build a query every time
for the control source (not that it takes forever!).
Click to expand...

Well remember that you can change the form's record source
at any time as well as apply filters and sort orders.
There are lots of ways to accomplish your goal, my point
was if the ONLY way you're going to display the data in
that form is by Last Name than just base it on a saved
query and not mess with an OrderBy property at all.

If you want to completely change the record source and/or
change sort orders you can do this "on the fly" in VBA.
For example, I have a continuous form list of products.
The form is based on a simple saved query listing all
products. Through command buttons and combo boxes I change
the record source of the form like so:

Me.Form.RecordSource = "SELECT tblProducts.* FROM
tblProducts ORDER BY ProductName;"
Me.Form.Requery

(This shows me all the products in alphabetical order by
name)

Or:

Me.Form.RecordSource = "SELECT tblProducts.* FROM
tblProducts WHERE (((tblProducts.[Active]) = Yes)) ORDER
BY ProductName;"
Me.Form.Requery

(This shows me all Active products in alphabetical order
by name)

Or:

Me.Form.RecordSource = "SELECT tblProducts.* FROM
tblProducts WHERE (((tblProducts.[Active]) = No)) ORDER BY
ProductName;"

(This shows me all Inactive products in alphabetical order
by name)

So by all means you can change the underlying record
source at any time without having to create an additional
saved query.

Hope that helps,
Jeff Conrad
Bend, Oregon
Jeff Conrad said:
Hi,

Not sure I can completely help, but I'll give it a shot.
I'm sure there are probably lots of ways to solve this.

1. Do you always want to sort by Last Name?
Why not just base the form on a saved query that sorts by last name?

2. I'm assuming you went to the form's properties and Put LastName on the
"Order By" line. You open the form, but it still is not showing the
employees in alphabetical order by last name. Correct?
Try adding this line to the Form's Open event:

Me.Form.OrderByOn = True

Save the form and see if that solves the problem.

3. On some forms (usually continuous) I give the user
Click to expand...
the option to sort
by
various fields. For example, say I have two fields called Report Group
Number and Report Group Name. I make two small command buttons over each
field in the Form Header and use the little A->Z and Z- A bitmaps.

I code the button Click events like so:

Private Sub cmdSortNameAscending_Click()
Me.txtReportGroupName.SetFocus
DoCmd.RunCommand acCmdSortAscending
End Sub

Private Sub cmdSortNameDescending_Click()
Me.txtReportGroupName.SetFocus
DoCmd.RunCommand acCmdSortDescending
End Sub

Hope that gives you some ideas to try.
Jeff Conrad
Bend, Oregon
Click to expand...
OrderBy property on a
form
as it seems to do nothing.

I'm using Access 2003, I've set the control source of a form to a table,
e.g. Employees and let's say I want to sort by Last
Click to expand...
Name so I've tried
these
forms in the OrderBy property of the form, all had no effect

"LastName"
"[LastName]"

Is there a bug or am I just missing something really obvious?
Click to expand...


.
Click to expand...
Click to expand...
 
Karen said:
Jeff and Bas Cost Budde,

OK, so is OrderByOn only set in VBA? It's not a property of the form?
Click to expand...

It's just an annoying omission. Note that it >is< present for reports - so
someone must have said "Oops!" at some stage in the product development
cycle!

TC

And Jeff, I do end up using queries on the tables to get my data and get it
sorted. It just seemed like I should be able to just use the properties of
the form and not build a query every time for the control source (not that
it takes forever!).

--
Karen

Jeff Conrad said:
Hi,

Not sure I can completely help, but I'll give it a shot.
I'm sure there are probably lots of ways to solve this.

1. Do you always want to sort by Last Name?
Why not just base the form on a saved query that sorts by last name?

2. I'm assuming you went to the form's properties and Put LastName on the
"Order By" line. You open the form, but it still is not showing the
employees in alphabetical order by last name. Correct?
Try adding this line to the Form's Open event:

Me.Form.OrderByOn = True

Save the form and see if that solves the problem.

3. On some forms (usually continuous) I give the user the option to sort by
various fields. For example, say I have two fields called Report Group
Number and Report Group Name. I make two small command buttons over each
field in the Form Header and use the little A->Z and Z->A bitmaps.

I code the button Click events like so:

Private Sub cmdSortNameAscending_Click()
Me.txtReportGroupName.SetFocus
DoCmd.RunCommand acCmdSortAscending
End Sub

Private Sub cmdSortNameDescending_Click()
Me.txtReportGroupName.SetFocus
DoCmd.RunCommand acCmdSortDescending
End Sub

Hope that gives you some ideas to try.
Jeff Conrad
Bend, Oregon

Karen said:
I have gotten into the habit of never using the OrderBy property on a form
as it seems to do nothing.

I'm using Access 2003, I've set the control source of a form to a table,
e.g. Employees and let's say I want to sort by Last Name so I've tried these
forms in the OrderBy property of the form, all had no effect

"LastName"
"[LastName]"

Is there a bug or am I just missing something really obvious?
Click to expand...
Click to expand...
Click to expand...
 
Thanks Jeff,

I really do use SQL queries and VBA to change the control source of a form
or a control based on other actions/choices.

It really boils down to my curiosity as to why that property seemed to have
no effect. I'll just move back to what works, especially since it gives me
more flexibility.

--
Karen
Jeff Conrad said:
Hi Karen,
OK, so is OrderByOn only set in VBA? It's not a property
of the form?
Click to expand...

I *think* so. I'm not completely positive. Bas is the real
expert, I'm just his personal assistant.
And Jeff, I do end up using queries on the tables to get
my data and get it sorted.
Click to expand...

That's good. Most experts believe basing forms on queries
or SQL statements is better than directly on the table
itself.
It just seemed like I should be able to just use the
properties of the form and not build a query every time
for the control source (not that it takes forever!).
Click to expand...

Well remember that you can change the form's record source
at any time as well as apply filters and sort orders.
There are lots of ways to accomplish your goal, my point
was if the ONLY way you're going to display the data in
that form is by Last Name than just base it on a saved
query and not mess with an OrderBy property at all.

If you want to completely change the record source and/or
change sort orders you can do this "on the fly" in VBA.
For example, I have a continuous form list of products.
The form is based on a simple saved query listing all
products. Through command buttons and combo boxes I change
the record source of the form like so:

Me.Form.RecordSource = "SELECT tblProducts.* FROM
tblProducts ORDER BY ProductName;"
Me.Form.Requery

(This shows me all the products in alphabetical order by
name)

Or:

Me.Form.RecordSource = "SELECT tblProducts.* FROM
tblProducts WHERE (((tblProducts.[Active]) = Yes)) ORDER
BY ProductName;"
Me.Form.Requery

(This shows me all Active products in alphabetical order
by name)

Or:

Me.Form.RecordSource = "SELECT tblProducts.* FROM
tblProducts WHERE (((tblProducts.[Active]) = No)) ORDER BY
ProductName;"

(This shows me all Inactive products in alphabetical order
by name)

So by all means you can change the underlying record
source at any time without having to create an additional
saved query.

Hope that helps,
Jeff Conrad
Bend, Oregon
Jeff Conrad said:
Hi,

Not sure I can completely help, but I'll give it a shot.
I'm sure there are probably lots of ways to solve this.

1. Do you always want to sort by Last Name?
Why not just base the form on a saved query that sorts by last name?

2. I'm assuming you went to the form's properties and Put LastName on the
"Order By" line. You open the form, but it still is not showing the
employees in alphabetical order by last name. Correct?
Try adding this line to the Form's Open event:

Me.Form.OrderByOn = True

Save the form and see if that solves the problem.

3. On some forms (usually continuous) I give the user
Click to expand...
the option to sort
by
various fields. For example, say I have two fields called Report Group
Number and Report Group Name. I make two small command buttons over each
field in the Form Header and use the little A->Z and Z- A bitmaps.

I code the button Click events like so:

Private Sub cmdSortNameAscending_Click()
Me.txtReportGroupName.SetFocus
DoCmd.RunCommand acCmdSortAscending
End Sub

Private Sub cmdSortNameDescending_Click()
Me.txtReportGroupName.SetFocus
DoCmd.RunCommand acCmdSortDescending
End Sub

Hope that gives you some ideas to try.
Jeff Conrad
Bend, Oregon

I have gotten into the habit of never using the
Click to expand...
OrderBy property on a
form
as it seems to do nothing.

I'm using Access 2003, I've set the control source of a form to a table,
e.g. Employees and let's say I want to sort by Last Name so I've tried
these
forms in the OrderBy property of the form, all had no effect

"LastName"
"[LastName]"

Is there a bug or am I just missing something really obvious?
Click to expand...


.
Click to expand...
Click to expand...
 
Hi Karen,

I learned that you want to konw if orderby property worked on a form.
Please try to perform the following steps to order the records in a form.

1. In the Northwind.mdb database, create a form using Form wizard.
2. The data source of the form is customers
3. Choose some fileds (including CompanyName) of the table in a form.
4. Then type "Customers.CompanyName" (without quotation marks) in the
orderby property of the form.
5. Reopen the form and check to see if it worked.

Does it work on your side?

Please feel free to post in the group if this solves your problem or if you
would like further assistance.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
 
Jeff said:
Hi Karen,



I *think* so. I'm not completely positive. Bas is the real
expert, I'm just his personal assistant.
Click to expand...

<G> you cannot set this property (or its collegue, FilterOn) from the
property sheet. It is implied in clicking the QuickFilter or SortAZ
buttons in the interface.
That's good. Most experts believe basing forms on queries
or SQL statements is better than directly on the table
itself.
Click to expand...

I'm one of them :-) Well, better is not necessarily true; but it makes
the forms more portable. And I usually need to sort anyway.
 
Hi Yuan,

No, it didn't work. It did work when I put

OrderByOn = True

in the OnLoad event as suggested by Jim and Bas. After carefully re-reading
the help file on OrderBy

"For reports, the OrderByOn property must be set to Yes to apply the sort
order specified by the object's OrderBy property. For forms, select the
field by which you want to sort the records and either click the appropriate
Sort button on the toolbar, or point to Sort on the Records menu and click
the appropriate command on the submenu. You can also set the OrderByOn
property for either forms or reports by using Visual Basic."

I was obviously making an incorrect assumption as to the value of the
'OrderBy' property on the form. I'll stick to the SQL queries I've been
using for my form record source.
 
Hello Karen,

Thanks for your feedback. Based on my further research, it mostly likes a
known issue of Microsoft Access. You can reproduce it by the following
steps.

1. Open Access database.
2. Design a form and set "OrderBy" property of form to a field.
3. Browse it. notice "OrderBy" doesn't work.
4. Still in browse, sort on a field with the Sort Asc/Desc button.
5. Go back to design mode for this form.
6. Enter a field name in the "OrderBy" property box.
7. Browse it again.

RESULT:
The form is ordered by the "OrderBy" property value.

I hope the explanation is clear. Please let us know if you need further
assistance on this issue.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
 
Thanks Michael,

I don't need further help now. I understand the issue and have a method
that I'm applying when required.
 
Back
Top