Chang the sort order of a query that drives a form

[Guest]

I'm looking for a way to provide a user with the option of changing the sort
order of the query behind a form. Any thoughts?

 

[Allen Browne]

Set the OrderBy property of the form, remembering to turn OrderByOn on as
well.

Or, just reassign a SQL statement to the RecordSource of the form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

in message
news:[email protected]...

 

[John Welch]

Do you know that you can just put the cursor in a control on the form, go to
the records menu, and choose sort ascending or descending? Do you want
something fancier than that?
-John

 

[John Vinson]

I'm looking for a way to provide a user with the option of changing the sort
order of the query behind a form. Any thoughts?

A bit of context would help...

You would need to actually replace the Recordsource query of the form
by a different Recordsource, presumably using VBA code.

If you just want to change the order of records as seen by the user,
it's probably simpler to set the Form's OrderBy property to the
desired sort field or fields, and set its OrderByOn property to True.
What's the context? What are you trying to accomplish with this?

John W. Vinson[MVP]

 

[Marshall Barton]

I'm looking for a way to provide a user with the option of changing the sort
order of the query behind a form. Any thoughts?

Not without creating a new SQL statement with a different
ORDER BY clause. Not a difficult thing to arrange for with
most queries, but why not use the form's OrderBy and
OrderByOn properties?

 

[Guest]

Sorry, here's some context. I'm building a form to assign exhibit booths at
events. Laying out an exhibit hall is a dynamic process; it's like putting
together a giant jig saw puzzle while keeping things in mind such as
exhibitor seniority, compatibilities between companies and number of booths.

So for the form, I'm looking to create a very easy way for a user to change
the sort order so that in one pass they're looking at the largest sized
booths to the smallest size booths with a subsort of the date by which the
exhibitor contract was received. Then another option would be to just sort
by the date the contract was received. A third sort option would be by areas
requested.

What I'm envisioning is an unbound combo box with these three options, where
the user can select the sort type, and then the query behind the form resorts
by that option.

 

[John Vinson]

What I'm envisioning is an unbound combo box with these three options, where
the user can select the sort type, and then the query behind the form resorts
by that option.

While you could do that, it would really be MUCH simpler to instead
change the Form's OrderBy property, and requery the form, in the
afterupdate event of the combo box.

John W. Vinson[MVP]

 

[Guest]

Can you offer some assistance in how to go about doing this? For example,
several of the posts on this thread have suggested the same thing, and to
"enable" the order by function. I haven't seen a place to do this in the
properties box. I've also played around with listing various field names in
the "Order By" field in the properties box, but it doesn't seem to have an
effect.

Also, in order to link this to the combo box, I'm assuming I'd write an
if-then-else statement? Is that what you're suggesting?

Thanks for your help.

 

[John Vinson]

Can you offer some assistance in how to go about doing this? For example,
several of the posts on this thread have suggested the same thing, and to
"enable" the order by function. I haven't seen a place to do this in the
properties box. I've also played around with listing various field names in
the "Order By" field in the properties box, but it doesn't seem to have an
effect.

Also, in order to link this to the combo box, I'm assuming I'd write an
if-then-else statement? Is that what you're suggesting?

You'ld use code something like this. Bear in mind I have NO idea what
your table or fieldnames might be, so I'll use arbitrary ones.

Create a Combo Box with a List of Values rowsource containing the
names of the fields that you want to offer as sort keys. Include a
"Default" value (to restore the default sort order).

Private Sub cboFieldnames_AfterUpdate()
If Me!cboFieldnames = "Default" Then
Me.OrderBy = ""
Me.OrderByOn = False
Else
If Len(Me.OrderBy) > 0 Then
Me.OrderBy = Me.OrderBy & ";"
End If
Me.OrderBy = Me.OrderBy & Me.cboFieldnames
Me.OrderByOn = True
End If
End Sub

This will clear the form's OrderBy property if the user chooses
Default; otherwise it will add fields in the order that the user
selects them. (There's no protection here from the user making
irrational selections such as sorting by FieldA, then FieldB, then
FieldA again, but this could be added).

John W. Vinson[MVP]