Requery sort order on a Form

[Sorting in a Graph]

I want to add a command button to a form to resort the records in the form. I
do many command buttons, but don't know how to associate the button to the
query assigned to the form. How do I link the button to the form to reorder
or requery the records up?

 

[Tim Johnson]

I want to add a command button to a form to resort the records in the form. I
do many command buttons, but don't know how to associate the button to the
query assigned to the form. How do I link the button to the form to reorder
or requery the records up?

Are you using the form's OrderBy property to sort, or the query that the
form is based on?

Also, is there an issue with using the toolbar/right click methods to sort?

Are you looking to alter which field you are sorting by (e.g. a combo box
for the fields and a button to perform the action on the selected field)?

There are several different methods by which to code the button; depending
on how you have your record source set up for the form and what exactly you
are trying to do here. Is the RecordSource a SQL statement, a query, or
<eek> the table itself?

Are you looking to dynamically sort by a variety of field names, or to sort
by one field only? A little more detail might make it easier to answer your
question, but here's some sample code if you are currently using the form's
Order By property to sort and simply want to reorder (this will also work to
quickly reverse the order if the toolbar/right click sort methods have been
used to select the sort field):

If InStr(1, Me.OrderBy, "DESC") = 0 Then 'Is the current sort descending?
Me.OrderBy = Me.OrderBy & " DESC" 'if not, add the ' DESC'
Else
Me.OrderBy = Replace(Me.OrderBy, " DESC", "") 'if so, remove the 'DESC'
End If

 

[Sorting in a Graph]

I am not using the OrderyBy property or toolbar. I want to be able to sort
the records in the form by multiple fields in the query attached/assigned to
the form.

So I want to be able to sort by another query assigned to a command button.
How can that be done? Just don't know how to associated the query running in
the button to the form records.

 

[Tim Johnson]

Gotcha. Is the record source of your form based on a saved query, or does
the form have its own SQL statement for the record source? If the former, I
would suggest using the latter for your purpose.

You can then use the sub for your command button to rearrange the SQL
statement; without seeing the statement that your form is based on, it is
difficult to show you an example of the code that will work for your form in
specific; but something like this should work:

Private Sub cmdSortButton()
Dim strSQL as String

strSQL = <SQL statement prior to your sort> & " ORDER BY " & <your sort
criteria; this can be dynamic, but without knowing your current design, I
can't give a valid example> (NOTE: if you have anything that should be in
your SQL statement after the sort you should add it here; using the ampersand
(&) and then the rest of your statement in quotes

Me.RecordSource = strSQL

End Sub

If you know the structure of an SQL statement, this should work pretty well
for you, if not, you can either google it or post more specific information
for further help.

 

[Tim Johnson]

Forgot to mention, if you would still like to use the saved query, you can do
essentially the same thing by using something similar to this:

Private Sub cmdSort_Click()
Dim qdf as QueryDef, strSQL as String

Set qdf = Currentdb.QueryDefs("<QueryName>")

strSQL = "<your SQL statement, complete with ORDER BY clause>"

qdf.SQL = strSQL
me.requery
end sub

 

[Sorting in a Graph]

I'm not a programmer, but I have done simple stuff in VB with command
buttons. So please be patient. Is this put into VB for a command button or
where? Maybe in a macro? Looks like I could place this in VB on a button. I
am familiar with the DoCmd.Sql Statements in VB using the command buttons.

 

[Tim Johnson]

Sorry for my belated response. You should put this code into the OnClick
event for your command button (in which case, the 'Private Sub...' and 'End
Sub' portions will be provided for you). Remember, if you are
copying/pasting this, to replace the <QueryName> with the name of your query
and the <your SQL statement, complete with ORDER BY clause> with the SQL
statement from that query and your defined ORDER BY clause.

If you need to allow users to change the order by clauses frequently, I
would suggest incorporating another control by which they can do so (e.g.
list box, series of combo boxes, etc.) and include the dynamic data into the
strSQL string.