Full Version: Sorting by clicking a column
UtterAccess Forums > MicrosoftŪ Access > Access Forms
Is it possible to have my continuous form sort by a particular column if I click the field name at the top, similar to the way Windows does this?
heck this thread.
Add an Order BY clause to the form's recordsource.
Something like:
[color="brown"][Form Code]  [/color]  [color="blue"]
Option Compare Database
Option Explicit
Const baseSQL = "SELECT * FROM Customers"
Private Sub City_Label_Click()
  Dim rsource As String
  rsource = baseSQL _
                   & " Order By City;"
  Me.RecordSource = rsource
End Sub [/color]
[color="brown"][/Form code]  [/color]
That thread you linked to doesn't have a 97 attachment.
Here's the attachment in 97 version....
There is also this example from CyberCow which I use.
Just downloaded the attachment from the link I gave you and I recall it has a file name of ...
orting a listbox or combobox 97.mdb
Open that one and a pop-up form will pop-up and the form name would be Form1 ..
Just looked at your example Jay and that some nice work.
sheesh ... Kept it in one of my archives ... Old samples ... blush.gif
First time I've seen it. I don't use list boxes much. Prefer the functionality combos.
See my post in another thread here
hat's incredible!
Many thanks!
One small thing - would it be possible to move the little up/down arrow "in a bit" (currently it sits to the very right of the label you click, which actually makes it look like it's on the left hand side of the next label along if all your labels, like mine, are aligned left with each other).
Sorted it. I changed the line:
e.lblSortIndicator.Left = ctl.Left + ctl.Width
Me.lblSortIndicator.Left = ctl.Left + ctl.Width - 180
There's probably a more elegant way of doing it, but for my system/db/pc, it works.
Using this method would there be anyway to get me report to sort by whatever sort order I've chosen on the form?
Even better:
e.lblSortIndicator.Left = ctl.Left + ctl.Width - Me.lblSortIndicator.Width
I imagine you could, you'd have to pass the current sort order of the form to the report. I've never tried it, but I think I may play with it a little to see if I can do it. If you come up with a solution, let me know.
I'm not even sure where to start!
I played for a little bit and I found something that works. There might be a more elegant method, but I don't know it. I've attached a db to demo the idea so if anyone has a better one, please chime in.
That I did was create a hidden text box in the form's footer called CurrentSort. On the click event of the label's I added a line that puts the underlyning recordset's field name that is being sorted in this control. Then on the button click that prints the report I put the following code to set the report's recordsource:
Dim db As Database
Dim qd As QueryDef
Dim SortOrder As String
Set db = CurrentDb()
On Error Resume Next
db.QueryDefs.Delete ("ReportQuery")
On Error GoTo 0
If Me.lblSortIndicator.Visible = True Then
    If Me.lblSortIndicator.Caption = "6" Then
        SortOrder = "ORDER BY " & Me.CurrentSort & " DESC"
        SortOrder = "ORDER BY " & Me.CurrentSort
    End If
SortOrder = ""
End If
Set qd = db.CreateQueryDef("ReportQuery", "SELECT * FROM sqryActiveInsPlans " & SortOrder)
DoCmd.OpenReport "rptActiveInsPlans", acViewPreview

I was hoping for a way to use the Where Condition of the OpenReport command, but I couldn't get that to work.
The draw back to this is that you will be deleted and creating the report's recordsource each time you print the report. This can cause db bloat requiring frequent compacting.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.