Listbox Sort

[Brightspark98]

Hi,



Is it possible to sort a listbox ascending or descending by either clicking on the header or by use of buttons?

The listbox in question is 4 columns wide with the ID column hidden.

Data comes from two tables.

Many Thanks

[aytee111]

Only manually - by requerying the listbox with a different SQL string (same fields, different sort)

[aytee111]

In case you read that wrong, what I meant by "manually" is thru VBA or a macro when a user clicks on a button.

[Micron]

I haven't done this with a list box, but have with a continuous form. Is your list box based on a query (hopefully) or is it filtered? If a query, you'd alter the sql behind the list box according to the sort as aytee111 says.

Some things you could do to play around:
- hide the column headers and use your own command or toggle buttons above one or more list box columns
- setting a command button back style to transparent, you could place a colored rectangle behind the button and color it if sorted by that column.
- or just use rectangles, coloring as noted, or switching between raised and sunken
or whatever else turns your crank!

[Brightspark98]

Hi,

This is the SQL for the listbox,

Can anyone help on how to assign it to three buttons in order to sort the data in each column.

SELECT tbl_T_Trainee.Surname, tbl_T_Trainee.Forename, GroupRef.GroupRef
FROM GroupRef INNER JOIN tbl_T_Trainee ON GroupRef.GroupRefID = tbl_T_Trainee.GroupRefID
ORDER BY tbl_T_Trainee.Surname, tbl_T_Trainee.Forename, GroupRef.GroupRef;

Once again

Many thanks

[pbaldy]

Behind each button:

Me.ListBoxName.RowSource = "SELECT..."

where the SQL is as you show it, but with the ORDER BY clause adjusted as appropriate to the button. I'd use a variable for the SQL, but it isn't required.

[Micron]

a bit more info is needed. If there are 3 buttons, 1 for each column and I click the leftmost button, is it supposed to sort ascending or descending? Whichever that will be, what if I click it again? Maintain the same sort or switch it to the opposite?
EDIT:
What about the other 2 columns when I sort on just one (default is ascending).

[Micron]

Well, out of curiosity, I went ahead with a routine that would reverse a sort each time the button was clicked. However, it became apparent that to worry about the sort of the remaining columns beyond settling for the default ascending sort would be much more involved. Rather than repeat the sql in each button event, plus the fact that knowing the last sort is required, I opted for a function that managed both.

For this to work as I have coded it, create 3 command buttons but you MUST name each according to the field you want to sort by, such as cmdSurname. On the property sheet for each button (Other tab), find the Tag property and put 'asc' there (without any quotes). To make it easier for me, I will give one example and use cmdBtn1 as my name - substitute the real name for that AND the listbox. Also, at the top of the form module where you (should) see
Option Compare Database
Option Explicit
put this below those lines:

Const strSql = "SELECT tbl_T_Trainee.Surname, tbl_T_Trainee.Forename, GroupRef.GroupRef " & _
"FROM GroupRef INNER JOIN tbl_T_Trainee ON GroupRef.GroupRefID = tbl_T_Trainee.GroupRefID ORDER BY "

For the click event of each button:

Private Sub cmdBtn1_Click

Dim strSort As String
'pass button and button tag property to function and assign function value to strSort variable
strSort = strSql & PassTag (Me.cmdBtn1, Me.cmdBtn1.Tag)
Me.lstMyListbox.Rowsource = strSort
Me.lstMyListbox.Requery

End Sub

This function goes in the form module

Private Function PassTag (ctl As Control, strTag As String) As String

'function receives control as object so that it can affect its tag value
If strTag = "asc" Then 
  ctl.Tag = "desc"
Else
  ctl.Tag = "asc"
End If

'if control name contains the word "name", prepend appropriate table name and part of button name that matches
' name that matches the listbox column and add this to the ORDER BY clause
If InStr(ctl.Name, "name") > 0 Then
  PassTag = "tbl_T_Trainee." & Mid(ctl.Name, 4) & " " & ctl.Tag & ";"
Else
'else it is the GroupRef table we need to include in the ORDER BY clause
  PassTag = "GroupRef.GroupRef " & ctl.Tag & ";"
End If

End Function

The form should open as it does now, and the click of a column button should reverse the sort on that column if you make the necessary changes.
NOTE - this is 'air code' and is not fully tested as I don't have your db to work with. Hope it helps.
If you try it and it doesn't work, you need to post your version of the code along with any error message numbers and text if you want help on this code.

[Brightspark98]

Hi Micron,

Thanks for all of your efforts so far, I think I must be doing something wrong as I cant get the code to work. When I click the button the data in the listbox disappears, if I refresh I get the message shown below. I couldn't find the module with Option Explicit so I entered this manually, was this correct?
All of the code is in the same place please see below also.

Option Compare DatabaseOption Explicit


Const strSql = "SELECT tbl_T_Trainee.Surname, tbl_T_Trainee.Forename, GroupRef.GroupRef " & _
"FROM GroupRef INNER JOIN tbl_T_Trainee ON GroupRef.GroupRefID = tbl_T_Trainee.GroupRefID ORDER BY "




Private Sub cmdForename_Click()
Dim strSort As String
'pass button and button tag property to function and assign function value to strSort variable
strSort = strSql & PassTag(Me.cmdForename, Me.cmdForename.Tag)
Me.lbTrainee.RowSource = strSort
Me.lbTrainee.Requery
End Sub


Private Sub cmdGroupRef_Click()
Dim strSort As String
'pass button and button tag property to function and assign function value to strSort variable
strSort = strSql & PassTag(Me.cmdGroupRef, Me.cmdGroupRef.Tag)
Me.lbTrainee.RowSource = strSort
Me.lbTrainee.Requery
End Sub


Private Sub cmdSurname_Click()
Dim strSort As String
'pass button and button tag property to function and assign function value to strSort variable
strSort = strSql & PassTag(Me.cmdSurname, Me.cmdSurname.Tag)
Me.lbTrainee.RowSource = strSort
Me.lbTrainee.Requery
End Sub
Private Function PassTag(ctl As Control, strTag As String) As String


'function receives control as object so that it can affect its tag value
If strTag = "asc" Then
  ctl.Tag = "desc"
Else
  ctl.Tag = "asc"
End If


'if control name contains the word "name", prepend appropriate table name and part of button name that matches
' name that matches the listbox column and add this to the ORDER BY clause
If InStr(ctl.Name, "name") > 0 Then
  PassTag = "tbl_T_Trainee." & Mid(ctl.Name, 4) & " " & ctl.Tag & ";"
Else
'else it is the GroupRef table we need to include in the ORDER BY clause
  PassTag = "GroupRef.GroupRef " & ctl.Tag & ";"
End If


End Function

[pbaldy]

Try this to see how the finished SQL is coming out:

BaldyWeb-Immediate window

[Micron]

yes, do as pbaldy suggests and post the output if you see nothing wrong with it and/or a compacted, zipped copy of your db, but while I'm here, I'll ask
- this was the original record source for the list box and there is no row source specified for it, yes?

SELECT tbl_T_Trainee.Surname, tbl_T_Trainee.Forename, GroupRef.GroupRef
FROM GroupRef INNER JOIN tbl_T_Trainee ON GroupRef.GroupRefID = tbl_T_Trainee.GroupRefID
ORDER BY tbl_T_Trainee.Surname, tbl_T_Trainee.Forename, GroupRef.GroupRef;

[Brightspark98]

Thanks for all of your help.

I assigned the sql to two cmd buttons above each colums, one for ascending, one for descending. All seems to work as I wanted.

Thank you.

[Micron]

Glad you got it working, but too bad we didn't learn what the problem was. Two of something (buttons) are not always better when one should do.