Sorting a listbox using command buttons
I always thought it was cool to be able to sort a list by clicking the column heading once, for ascending order then once more for descending order. A while ago I decided to tackle this feature in an Access form.
For this project you will need a database with two or three columns of data. For example State; Suburb Customer another example could be Department;Employee;DateHired. Of course you could have as many columns as you want.
Below is a basic participant form. It displays the name of an applicant and the test this person will be assessed on. It is a simplified version of a form I used in a real application.
Note the Command button with a binocular at the upper right corner. Because there will eventually be dozens, perhaps hundreds of applicants we need a quick way to find one record. It is also possible for one applicant to do more than one test.
Clicking the binocular button opens a form with all the test along with useful information. Below is the form.
This form is pretty simple to do. By default the data is not sorted in the list. This is a very important thing because it will be sorted using a bit of VBA code when the form opens.
The buttons above the field names are used to sort the list. The triangle pointing down means the column is in descending order. Clicking on it again will sort that column in ascending order and the triangle will be pointing up. Like you are used to.
It is important to remember that the Listbox in the form is not originally sorted. When the form opens, the form's event procedure will sort it automatically.
The Basic Technique Used
This project required a basic understanding of Access SQL and concatenating strings. When you configure the Listbox using the Row Source property to specify the data that will appear in the list you are in fact creating a SQL statement.
You can see the whole statement by clicking the Row Source property and hit Shift+F2 (Zoom window) to view it. Because it is not sorted the ORDER BY [Field] is not there (and it should not be there remember).
So when the user clicks on a sort button the string ORDER BY [Field Name] is appended as the end of the Row Source SQL string. For example the name of the first field in the Listbox above is TestDate. So appending:
| ORDER BY TestDate | will sort on TestDate in Ascending order |
| ORDER BY TestDate DESC | will sort on TestDate in Descending order |
Note that ommiting the order means 'ascending'. Now you know why you should not sort the list yourself right. Because if you sort it then the ORDER BY [Field Name] clause will be present and when a sort button will be clicked the code will append another ORDER BY clause. This will cause an error in the SQL instruction.
Coding The Find Participant Form
Because the form can be sorted up to four different ways (Date, Participant, Test Name and On Open event procedure) we will use a Sub to do the job.
Private Sub SortColumn(strField As String, strOrder As String)
Dim strSQL As String
Dim strSorted As String
strSQL = Replace(Left(Me!lstFind.RowSource, Me.ListLength - 1), ";", " ")
strSorted = strSQL & " ORDER BY " & strField & strOrder & ";"
Me!lstFindParticipant.RowSource = strSorted
End Sub
As you look at this procedure you may notice a property you are not aware of: The ListLength property. It is really a custom property assigned to the form. It is not something I use often but I do feel it sometimes simplifies my coding. The Me.ListLength property returns the length of the SQL string in the RowSource property of the Listbox. Since I need this value in my code I decided to store this value in a custom property. There are of course many other ways to do the same thing.
At the top of the module you will find the following declaration and two small procedures:
Dim intLenghtSQL As Integer
Property Let ListLength(intValue As Integer)
intLenghtSQL = intValue
End Property
Property Get ListLength() As Integer
ListLength = intLenghtSQL
End Property
The Property Let procedure will store in the ListLength custom form property the length of the SQL string in the RowSource property of the Listbox. On the other hand the Property Get procedure will allow you to retrieve this value by using such a statement like: Me.ListLength. It is all a matter of taste but I like to be able to assign / retrieve a value I need often this way.
Assigning a value to the custom property
When the Form_Open procedure is fired you have a good occasion to assign a value to the ListLength custom property:
Private Sub Form_Open(Cancel As Integer)
Me.ListLength = Len(Me.lstFind.RowSource)
SortColumn "[TestDate]", " DESC"
End Sub
Upon opening the Me.ListLength is assign a value (VBA knows that this means to run the Property Let procedure). And the SortColumn Sub is executed to sort the list by TestDate in descending order (most recent test at the top).
Retrieving a value from a custom property
To retrieve the value from a custom property is the same a reading the value from any properties. In this case the property is to the right of the equal character. For example:
intNumber = Me.ListLength - 1
Assigns to the variable intNumber the value stored in the property minus 1.
Coding The Buttons
The time has come to code the Command buttons. The button above the date column is named cmdSortDate. Below is the code for the cmdSortDate Command button.
Private Sub cmdSortDate_Click()
Me!cmdSortPartic.Caption = ""
Me!cmdSortTest.Caption = ""
If Me.cmdSortDate.Caption = "" Or Me.cmdSortDate.Caption = "q" Then
Me!cmdSortDate.Caption = "p"
SortColumn "[TestDate]", ""
Else
Me!cmdSortDate.Caption = "q"
SortColumn "[TestDate]", " DESC"
End If
End Sub
Let's start with the "p" and the "q" business (lowercase p and q by the way). The font used for the buttons is Wingdings 3 (a standard font with Windows). In this font a lowercase p is arrow up and q is arrow down. That is how the button toggles between up and down arrow (between p and q).
 |
 |
Ascending |
Descending |
After clearing all other Command buttons the code checks the state of the button when it was clicked. If it is blank or descending order then change the direction of the arrow and sort the TestDate column to ascending order. If not make the arrow pointing down and sort the column in descending.
A Last Example
You noted that the second column of the list box contains the full name of the participant. Because this database is well normalised the name is divided in two fields: ParticFName and ParticLName. In the Record Source the SQL string concatenates the two field as follow: [ParticLName] & ", " & [ParticFName] AS Participant.
When sorting on the Participant column you need only to send the first field in the concatenation. In this example ParticLName. The whole name will be sorted. Here's the code behind the cmdSortPartic Command button.
Private Sub cmdSortPartic_Click()
Me!cmdSortDate.Caption = ""
Me!cmdSortTest.Caption = ""
If Me.cmdSortPartic.Caption = "" Or Me!cmdSortPartic.Caption = "q" Then
Me!cmdSortPartic.Caption = "p"
SortColumn "[ParticLName]", ""
Else
Me!cmdSortPartic.Caption = "q"
SortColumn "[ParticLName]", " DESC"
End If
End Sub
The Final Task
Once the data in the list is found (selected) you want to see it in the previous form. Let's not forget the purpose of all this: Finding a row and display in the more detailed form. Below is the code for the cmdShow Command button.
Private Sub cmdShow_Click()
Dim rs As Recordset
If IsNull(Me.lstFind) Then
Beep
Else
Set rs = Forms!frmManageTestsSimple.RecordsetClone
rs.FindFirst "[TestID] = " & Me!lstFind
Forms!frmManageTestsSimple.Bookmark = rs.Bookmark
Forms!frmManageTestsSimple!ParticID.SetFocus
DoCmd.Close acForm, Me.Name
End If
Set rs = Nothing
End Sub
After declaring a recordset variable (rs) the code checks if an item is selected in the Listbox. If not then you hear a Beep and execution goes past the End If statement.
In the contrary the code sets the recordset variable to the whole underlying data in the form that called the Find form. This type of recordset is called RecordsetClone.
It finds the first entry where the primary key matches the bound field of the Listbox (using FindFirst). Once found the data on the form is synchronised with its RecordsetClone's Bookmark property. The form now shows the same record as the one selected in the Listbox.
The rest is a breeze. The form and the code in the supplied database can easily be customised to your need with a minimum of effort.
Please download the sample Access 2003 database.
Published on: 6 Nov 2009
Home | List of Articles