Sort (then Re-sort) by form header

[Big Pat]

Hi,

I've got part way with this but need some help to finish it.

I've got a contuinuous form and want the users to be able to sort by the column headings. Double-click the first time should sort ascending, the next double-click should sort descending.

So far I've got

Private Sub Recruit_Tracker_Study_Name_Label_DblClick(Cancel As Integer)
Me.OrderBy = "[Recruit Tracker Study Name] ASC"
End Sub

I'd rather not use two separate command buttons, so presumably I need an IF statement that says something like

IF me.orderby=asc then
me.orderby=desc
end if

But I can't work out the syntax! Can anyone help?

 

[MStef]

Look at "DemoSortADA2002.mdb" (attachment, zip).
Open Form and see.

 

[poduska]

Excellent.
Just Change the non "Order by" query source in the module header and the field names in the On_Click and it works!

Absolutely Brilliant.
Thanks

 

[rohnds]

Here is who I did it:

Dim sortFlag as Boolean

Private Sub Form_Load()
sortFlag=True
Me.OrderBy = "name of field"
Me.Refresh
End Sub

Private Sub name of field_Label_Click()
Me.OrderByOn = True
If f1 Then
    Me.OrderBy = "name of field DESC"
    f1 = False
    MsgBox "Sorted Decending Order"
Else
    Me.OrderBy = "name of field ASC"
    f1 = True
    MsgBox "Sorted Ascending Order"
End If
Me.Refresh
End Sub

I simply created a boolean variable sortFlag to keep track when in which direction sort is applied. You can remove the msgbox if you like.

 

[poduska]

I want to try this as it appears simpler than what I am using from the sample database. but I do not see where "F1" is declared.

What is F1?
Is F1 Global, how would it effect a different column label's code?

Where is the Boolean sortFlag utilized? Is sortFlag the same as F1?

Maybe I just am missing something, sounds like it will be easier code to move around than what I am using.

Thanks for the post.

 

[poduska]

Re: Sort (then Re-sort) by form header, Access

Ok, I have it working and it is much easier than that what I was using.

I use F1 to F5 (have five Column Labels) and I declare them globally.
I then reset all the F? to False for all the other filters when I click sort. This ensures that when I return to different header it starts with ASC.

Here is the Code (showing only 2 Column Labels for clarity)

Option Compare Database
Option Explicit

Dim sortFlag As Boolean
Dim F1 As Boolean
Dim F2 As Boolean
Dim F3 As Boolean
Dim F4 As Boolean
Dim F5 As Boolean
'---------------------------------------------
Private Sub Form_Load()
     sortFlag = True
     Me.OrderBy = "model"
     Me.Refresh
End Sub

'---------------------------------------------
Private Sub lblModel_Click()

Me.OrderByOn = True

F2 = False
F3 = False
F4 = False
F5 = False

If F1 Then
    Me.OrderBy = "Model DESC"
    F1 = False
    'MsgBox "Sorted Decending Order"
Else
    Me.OrderBy = "Model ASC"
    F1 = True
    'MsgBox "Sorted Ascending Order"
End If
Me.Refresh

End Sub

'---------------------------------------------
Private Sub lblLot_Click()

Me.OrderByOn = True

F1 = False
F3 = False
F4 = False
F5 = False

If F2 Then
    Me.OrderBy = "[Timing of prototype] DESC"
    F2 = False
    'MsgBox "Sorted Decending Order"
Else
    Me.OrderBy = "[Timing of prototype] ASC"
    F2 = True
    'MsgBox "Sorted Ascending Order"
End If
Me.Refresh

End Sub

 

[deputy herb]

I realize this is old but thought someone might come across as I did with the Google. It far easier to just check the current orderby

Private Sub lblModel_Click()

Me.OrderByOn = True

If Me.OrderBy = "[Model] DESC" then
    Me.OrderBy = "[Model] ASC"
Else
    Me.OrderBy = "[Model] DESC"
End If

Me.Refresh

End Sub

 

[vbaInet]

Good job deputy.

But you need to Requery instead of Refresh.

 

[deputy herb]

Good job deputy.

But you need to Requery instead of Refresh.

I just tried that. Seems either one will work. Maybe there is an advantage to requery that I'm not aware of, I'm a bit of a noob with Access.

 

[vbaInet]

Requery will pull the entire recordset for you, which is what you want.

 

[ncraigg]

OK, I just tried deputy herb's method, but on a report rather than a form. It works beautifully. However, when I call the report from a button on another form (the form is designed to filter the results that are returned in the report), I get the following error:

The expression On Click you entered as the event property setting produced the following error: Member already exists in an object module from which this object module derives.

Does anybody have any thoughts on what would cause this?

Thanks!