Order Command for Fields in Listbox

[brsawvel]

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?

[puppydogbuddy]

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?

See this link for a step by step example with source code.

http://www.databasedev.co.uk/sort_listbox_columns.html

[brsawvel]

I attempted the code explained, but for some reason it isn't working with my form.

I also tried this, but the listbox goes blank -

Private Sub CmdButtonSortBy Name_Click
Dim strSQL as String
strSQL = me.listbox.rows ource & "ORDER BY FieldName;"
me.listbox.rows ource = strSQL
End Sub

Any reason you can see?

[puppydogbuddy]

I attempted the code explained, but for some reason it isn't working with my form.

I also tried this, but the listbox goes blank -

Private Sub CmdButtonSortBy Name_Click
Dim strSQL as String
strSQL = me.listbox.rows ource & "ORDER BY FieldName;"
me.listbox.rows ource = strSQL
End Sub

Any reason you can see?

Yes. On this line you need to put the actual name of the field in your application in place of FieldName.

strSQL = me.listbox.rows ource & "ORDER BY FieldName;"

[Jim Doherty]

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,Cust omerName,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 :)

[brsawvel]

Yes. On this line you need to put the actual name of the field in your application in place of FieldName.

strSQL = me.listbox.rows ource & "ORDER BY FieldName;"

The code I showed you was the original. I did change the field name as you suggested, but it doesn't work.

[puppydogbuddy]

The code I showed you was the original. I did change the field name as you suggested, but it doesn't work.

You did not provide much info, but I think your problem is that you did not download the sample database from the link at the bottom of the web page to get the code for the button click event, which is not shown on the web page at all. The button click event contains the code that calls and passes a parameter to the private function code shown in the link. The passed parameter identifies which button was clicked. So you need to download the sample database from the link shown at the bottom of the page and get the code for the button click event.

If you have any problem after downloading the sample database, let me know.

[brsawvel]

You did not provide much info, but I think your problem is that you did not download the sample database from the link at the bottom of the web page to get the code for the button click event, which is not shown on the web page at all. The button click event contains the code that calls and passes a parameter to the private function code shown in the link. The passed parameter identifies which button was clicked. So you need to download the sample database from the link shown at the bottom of the page and get the code for the button click event.

If you have any problem after downloading the sample database, let me know.

Actually I did download the sample database provided in the link. I wasn't able to get that to work on my db.

No worries though. I got it to work this way....

1. Copy the Row Source from the Listbox Properties.

2. Create a command button.

3. Create an "On Click" Event for that button.

4. Enter the following into the module - listboxName.Row Source = "****Paste the Row Source data here (removing the semicolon if there is one)**** Order By [tblName].[fldName]"

5. If you want it in descending order, just add DESC to the end of the script before the quotation.

[puppydogbuddy]

Actually I did download the sample database provided in the link. I wasn't able to get that to work on my db.

No worries though. I got it to work this way....

1. Copy the Row Source from the Listbox Properties.

2. Create a command button.

3. Create an "On Click" Event for that button.

4. Enter the following into the module - listboxName.Row Source = "****Paste the Row Source data here (removing the semicolon if there is one)**** Order By [tblName].[fldName]"

5. If you want it in descending order, just add DESC to the end of the script before the quotation.

Glad you got it resolved and thanks for posting back. If I understand you correctly, the reason you could not get the code from the link (see below) to work was that you did not have anything defined for the row source of the listbox, and because the row source was empty, an empty string was assigned to the strSQL variable.

Private Sub CmdButtonSortByName_Click
Dim strSQL as String
strSQL = me.listbox.rowsource & "ORDER BY FieldName;"
me.listbox.rowsource = strSQL
End Sub

[NeoPa]

Glad you got it resolved and thanks for posting back. If I understand you correctly, the reason you could not get the code from the link (see below) to work was that you did not have anything defined for the row source of the listbox, and because the row source was empty, an empty string was assigned to the strSQL variable.

Private Sub CmdButtonSortByName_Click
Dim strSQL as String
strSQL = me.listbox.rowsource & "ORDER BY FieldName;"
me.listbox.rowsource = strSQL
End Sub

...Not to mention you're not leaving a space before the "ORDER BY..." part. This will tend to muck up the resulting SQL ;)

[Jim Doherty]

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,Cust omerName,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 :)

Correction on my earlier code posting
My apologies The SQL block should NOT read

 
mystr = "SELECT DISTINCTROW OrderID, Client, PositionTitle "
mystr = mystr & "FROM Orders "
mystr = mystr & "ORDER BY " & str & " " & IIf(SORT_FLAG, "ASC", "DESC")

and should be amended to read

 
mystr = "SELECT DISTINCTROW CustomerID, CustomerName, City "
mystr = mystr & "FROM tblCustomers "
mystr = mystr & "ORDER BY " & str & " " & IIf(SORT_FLAG, "ASC", "DESC")