Full Version: Sorting by clicking a column
UtterAccess Forums > MicrosoftŪ Access > Access Forms
markmcrobie
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?
Cheers
JayNoelOlimpo
Hi;
heck this thread.
HTH.
ace
Add an Order BY clause to the form's recordsource.
Something like:
!--c1-->
CODE
[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]
markmcrobie
That thread you linked to doesn't have a 97 attachment.
JayNoelOlimpo
Here's the attachment in 97 version....
dashiellx2000
There is also this example from CyberCow which I use.
TH.
JayNoelOlimpo
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 ..
HTW.
dashiellx2000
Just looked at your example Jay and that some nice work.
JayNoelOlimpo
sheesh ... Kept it in one of my archives ... Old samples ... blush.gif
dashiellx2000
First time I've seen it. I don't use list boxes much. Prefer the functionality combos.
Schizolocal
See my post in another thread here
markmcrobie
Dashiellx2000:
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).
Cheers!
markmcrobie
Sorted it. I changed the line:
e.lblSortIndicator.Left = ctl.Left + ctl.Width
To:
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.
markmcrobie
Using this method would there be anyway to get me report to sort by whatever sort order I've chosen on the form?
markmcrobie
Even better:
e.lblSortIndicator.Left = ctl.Left + ctl.Width - Me.lblSortIndicator.Width
dashiellx2000
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.
markmcrobie
I'm not even sure where to start!
dashiellx2000
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:
CODE
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"
        Else
        SortOrder = "ORDER BY " & Me.CurrentSort
    End If
Else
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.
HTH.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.