Choose sort order with hyperlink

[Guest]

I want to be able to sort a list in a form by clicking the field name at the
top (each subsequent click sorts the list either ascending or descending). I
was thinking of using a hyperlink as the field name, but could also use an
invisible button. Either way, I am not sure how to program this. Any help?
Thanks!

 

[Guest]

K,

Labels have an OnClick event. For each label, add the following code:

Private Sub lblNumber1_Click()
Me.OrderBy = "fldNumber1"
End Sub

Private Sub lblNumber2_Click()
Me.OrderBy = "fldNumber2"
End Sub

etc., etc., etc., for each of the labels/sorts you need.

If you want to get fancy, you can call a Sub that not only changes the sort
but also bolds the label so that the user knows which sort order is being
shown.

HTH,
Bruce

 

[Guest]

K,

Labels have an OnClick event. For each one, add code like the following:

Private Sub lblNumber1_Click()
Me.OrderBy = "fldNumber1"
End Sub

HTH,
Bruce

 

[Guest]

K,

Sorry about the earlier double-post.. The newsgroup told me that the first
one did not post properly, so I re-typed.

Looking back, I realize that I didn't answer your "ascending/descending"
question. Try this:

Private Sub lblNumber1_Click()
If InStr(Me.OrderBy, "fldNumber1 DESC") > 0 Then
Me.OrderBy = "fldNumber1"
Else
If InStr(Me.OrderBy, "fldNumber1") > 0 Then
Me.OrderBy = "fldNumber1 DESC"
Else
Me.OrderBy = "fldNumber1"
End If
End If
End Sub

If you have more than 2 or 3 column headings, put all of that in a
Sub/Function and call it from each OnClick:

Private Sub lblNumber1_Click()

'Changes sort order and shows/hides down arrow.
'lblNumber1Down would be a label that has only a "down arrow".
'I use Wingdings3 character 071 - Chr(113).

Me.lblNumber1Down.Visible = SetSort ("fldNumber1")

End Sub
....

Private Function SetSort (myFld as String) As Boolean

'Returns True if Descending sort, False if Ascending
'Bolds heading for called field. Turns off all Down arrows.

SetSort = False
If InStr(Me.OrderBy, myFld & " DESC") > 0 Then
Me.OrderBy = myFld
Else
If InStr(Me.OrderBy, myFld) > 0 Then
Me.OrderBy = myFld & " DESC"
SetSort = False
Else
Me.OrderBy = myFld
End If
End If

Me.lblNumber1.FontBold = myFld = "fldNumber1"
Me.lblNumber1Down.Visible = False 'This will be changed by calling
'event, if
necessary.
Me.lblNumber2.FontBold = myFld = "fldNumber2"
Me.lblNumber2Down.Visible = False
....
'Continue for each different heading.
End Function

Bruce

 

[Guest]

I can't thank you enough for the suggestion. I am having trouble, however,
getting to work. I've started with just one label and used your suggested
code below from the first part of your reply. I get no errors, but nothing
happens. In place of fldNumber1, I tried both the field name of the
underlying query and the name of the text box. Should I be using the ""'s in
every instance in your example or just in the first reference? Thanks again,
Bruce.

Kevin C.

 

[Guest]

K,

If you were already using your label and field names, the sort not working
is probably because you have not set OrderByOn to True. I usually do this in
the Form_Load:

Private Sub Form_Load()
....
Qty_Lbl_Click 'Use whatever label you want for the "default" sort
order.
Me.OrderByOn = True
....
End Sub

The code I sent earlier is actually a little more complex than it needs to
be beacuse I use it for multiple-field sorts. (That's the reason for the
InStr.) If you're only sorting on one field, you can use something like what
is below:

Private Sub Qty_Lbl_Click()
If Me.OrderBy = "Qty DESC" Then
'Sort is currently Descending. Set to regular.
Me.OrderBy = "Qty"
Else
If Me.OrderBy = "Qty" Then
'Sort is currently regular. Set to descending.
Me.OrderBy = "Qty DESC"
Else
'This field is not the current sort key. Make sort regular on this field.
Me.OrderBy = "Qty"
End If
End If
End Sub

Sorry if I confused you earlier.

Bruce