Hello,
How should I write an "on click" code for a cmd button to tell a field in a listbox to order itself in ascending?
There are various techniques to do this.
A Listbox can have X number of columns and you might wish to sort on any one of them 'ascending' or 'descending'. In this simple case ascending could be represented by the value of TRUE conversely descending could be represented by FALSE
If we take the case of a listbox having its "Row Source Type" property set to "Table/Query" then we could have its "Rowsource" set to an SQL statement that gets fed to it dynamically at runtime.
If we take a simple example of a three column listbox where the columns values are represented by the following SQL Statement
"SELECT DISTINCTROW CustomerID, CustomerName, City FROM tblCustomers ORDER BY CustomerID ASC;"
We see here that statement would provide a list sorting the list by the CustomerID field. The idea then is to amend the ORDER BY clause at runtime so that it reflect the order in which we wish to list to be in either ASC for ascending or DESC for descending.
In addition it might not be the CustomerID field we wish to sort on but the CustomerName ...again either ASC or DESC.
The idea then is to manipulate and change the "ORDER BY.....ASC" block string to reference the field we wish to sort on. So how can this be done.
We could set a variable at form level to represent the TRUE or FALSE value of ASC or DESC and then '
switch' the value of the variable each time the button is clicked.
But how do we know
which field to sort on? we can do this by mounting a dropdown (combobox) control on the form and setting its "Row Source Type" to 'Field List'. You might place this combobox above the listbox so that when the user clicks on the combox they see the same fieldnames as shown in the listbox. Immediately to the right of this combobox you could have a small command button the default caption of which says "Sort" on opening the form.
Each time we click the command button then... we want the list to sort dependant on the value of the fieldname selected in the combobox. In addition each 'subsequent' click of the command button we would want it to alternately sort the column value ascending or descending every time.
The code behind the button would be relying on a 'function' to do the work of manipulating the list. (This function COULD be generic by nature if we wanted it to be, but for the purposes of clarity I am going to show you as dealing with one particular SQL statement and displaying the resultant dataset.)
So lets make this happen.....
1) Create a table called tblCustomers with the fieldnames CustomerID,CustomerName,City
2) Populate it with some data
3) Create a form and mount on it a Listbox with the name lstMyList and set the column count property for the listbox to 3
4) In the "row source type" of the listbox set it to "Table/Query"
5) In the "row source" of the listbox paste in the following
"SELECT DISTINCTROW CustomerID, CustomerName, City FROM tblCustomers ORDER BY CustomerID ASC;"
4) Save the Form
5) Create a combobox and place it nearto the listbox name it Combo1
6) In the "row source type" of the combo box set it to "Field List"
7) In the "row source" of the combobox select tblCustomer
8) Mount a small command button immediately to the right of the combobox. Set the Name property of the command button to cmdsort
9) Set the caption property of the command button to "Sort"
In form design go into the code module for the newly created form (menubar...view...code)
Immediately below the statement that says "Option Compare Database" paste the following code:
- Option Explicit
-
Dim SORT_FLAG As Boolean
-
Private Function ListOrder(c As Control, str As String) As Integer
-
On Error Resume Next
-
Dim mystr As String
-
If IsEmpty(SORT_FLAG) Then SORT_FLAG = True
-
mystr = "SELECT DISTINCTROW OrderID, Client, PositionTitle "
-
mystr = mystr & "FROM Orders "
-
mystr = mystr & "ORDER BY " & str & " " & IIf(SORT_FLAG, "ASC", "DESC")
-
c.RowSource = mystr
-
c.Requery
-
Screen.ActiveControl.Caption = IIf(SORT_FLAG, "v", "^")
-
SORT_FLAG = Not SORT_FLAG
-
End Function
-
Private Sub cmdsort_Click()
-
On Error Resume Next
-
Dim x
-
If IsNull(Me!Combo1) Then
-
MsgBox "You must select a fieldname from the list", vbExclamation, "System Message"
-
Exit Sub
-
End If
-
x = ListOrder(Me!List0, Me!Combo1)
-
End Sub
Click on Save and close the module window. and save the form. If you have done this correctly you will see the words "Event procedure" in the onclick properties for the command button cmdsort in design view.
Open the form in form view and you should be seeing a list of customers and a selectable list of fields from the drop down combobox. Each time you click on the command button it should sort the list dependant on the field name value you have selected from the dropdown.
This example is by no means the only way or even the most efficient way of doing things or indeed the most functional way but you can see that for a couple of clicks worth you have a sortable list of all of your selectable fields as opposed to just one hard coded into a single event
Hope this helps you
Regards
Jim :)
8
