Sorting columns in a list box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a list box with four columns (div, horse name, first name, last name)
with rowsource set to a query qryEntriesList. I can sort the list box any
way I want by sorting the query, but I want to provide the user with the
ability to sort the list box using either a drop down list, or a set of
command buttons. My command button always fails by blanking out the list
box.

This must be a well used application. Can you give me some guidance or send
me to an explanation.
 
In Access ListBox, the sorting comes from the the RowSource and not the
ListBox itself. If you want a different sort order, you need to change the
RowSource, i.e. the query or the SQL String.

Alternatively, you may want to manipulate the Recordset being used as the
rowsource for the ListBox. However, Access Help indicates that it may be
faster to open a new RowSource / RecordSource that includes the sorting
criteria rather than trying to manipulate the existing Recordset.
 
Here is how you do it.
Create a query that has all the fields you want in your list box, but do not
define a sort order.
Create the list box using the wizard. When it asks you what to base it on,
use the query you built. Then after you have saved it, you will have the SQL
as the rowsource for the list box. Edit the SQL (not the saved query) to
include the defalut sort order you want by adding the ORDER BY clause at the
end of the SQL.

Now, for sorting, use an Option Group. Create a button for each sort order
you want. Be sure the default option is the default oder by for the list
box. That is, If your defalut sort for the list box is div, then make option
button 1 div with a value of 1.

Now, in the After Update event of the Option Group:
(note that the values you put in strNewOder for each option should be the
field name you want to sort on)

Private Sub opgOrder_AfterUpdate()
Dim strSQL As String
Dim strNewOrder As String

strSQL = CurrentDb.QueryDefs("_ordertest").SQL
Select Case Me.opgOrder
Case 1
strNewOrder = "div"
Case 2
strNewOrder = "horsename"
Case 3
strNewOrder = "firstname"
Case 4
strNewOrder = "lastname"
End Select
strSQL = Replace(strSQL, ";", " ORDER BY " & strNewOrder & ";")
Me.lstActivity.RowSource = strSQL
End Sub
 
Thankyou for the help. I woke up this morning and realized that I needed to
re-sort the query. So that works great. Here is the code I used:

Private Sub cmdSortHorse_Click()
Dim strSQL As String

strSQL = "SELECT * from qryEntriesList ORDER BY tblHorses.Horsename"

With Me.EntriesList
.RowSource = strSQL
.Requery
End With

End Sub

In this case, I used a named query from the query list. Can you tell me
what the SQL would look like if I had used an internally generated query?
Which is more desireable?
 
Beto
See my earlier post. It shows the easiest way to do what you want. BTW, the
requery is not necessary. Once you set a new rowsource, it requeries.
 
I tried this same code and keep getting blank boxes after update. It does
not appear to be writing any code to the SQL for the list box. Could you
please give me some insight? Thanks!

Here is my code:

'***For the list box:

SELECT [Select City Query].NAME, [Select City Query].IND_IDTE, [Select City
Query].PNAME1
FROM [Select City Query]
GROUP BY [Select City Query].NAME, [Select City Query].IND_IDTE, [Select
City Query].PNAME1;

'*** I tried adding the default ORDER BY and it did seem to help. No
changes were made to this code either way...

Here is the code for the option group:

Private Sub Frame40_AfterUpdate()
'*Frame40 is the name of my option group

Dim strSQL As String
Dim strNewOrder As String

strSQL = CurrentDb.QueryDefs("Select City Query").SQL
'***"Select City Query" is the name of my query the list is based on

Select Case Me.Frame40
Case 1
strNewOrder = "NAME"
Case 2
strNewOrder = "PNAME1"
Case 3
strNewOrder = "IND_IDTE"
'***These are my fields in the query that I want sorted

End Select
strSQL = Replace(strSQL, ";", " ORDER BY " & strNewOrder & ";")
Me.FirstCategory.RowSource = strSQL
'***FirstCategory is the name of my list box

End Sub

'*** I made the option group using the wizard. Do I need to do anything
else
'*** to the settings or the buttons within the option group? They all
show the
'*** correct default values. Any help would be greatly appreciated!
 
Susan,

Right after your select statement, insert either a debug.print or a
messagebox to display the variable strNewOrder. The code looks like it
should work to me, that is basically what I do when I want to give my user
the ability to sort a list.

Dale

SusanForson said:
I tried this same code and keep getting blank boxes after update. It does
not appear to be writing any code to the SQL for the list box. Could you
please give me some insight? Thanks!

Here is my code:

'***For the list box:

SELECT [Select City Query].NAME, [Select City Query].IND_IDTE, [Select
City
Query].PNAME1
FROM [Select City Query]
GROUP BY [Select City Query].NAME, [Select City Query].IND_IDTE, [Select
City Query].PNAME1;

'*** I tried adding the default ORDER BY and it did seem to help. No
changes were made to this code either way...

Here is the code for the option group:

Private Sub Frame40_AfterUpdate()
'*Frame40 is the name of my option group

Dim strSQL As String
Dim strNewOrder As String

strSQL = CurrentDb.QueryDefs("Select City Query").SQL
'***"Select City Query" is the name of my query the list is based
on

Select Case Me.Frame40
Case 1
strNewOrder = "NAME"
Case 2
strNewOrder = "PNAME1"
Case 3
strNewOrder = "IND_IDTE"
'***These are my fields in the query that I want sorted

End Select
strSQL = Replace(strSQL, ";", " ORDER BY " & strNewOrder & ";")
Me.FirstCategory.RowSource = strSQL
'***FirstCategory is the name of my list box

End Sub

'*** I made the option group using the wizard. Do I need to do anything
else
'*** to the settings or the buttons within the option group? They all
show the
'*** correct default values. Any help would be greatly appreciated!




Klatuu said:
Here is how you do it.
Create a query that has all the fields you want in your list box, but do
not
define a sort order.
Create the list box using the wizard. When it asks you what to base it
on,
use the query you built. Then after you have saved it, you will have the
SQL
as the rowsource for the list box. Edit the SQL (not the saved query) to
include the defalut sort order you want by adding the ORDER BY clause at
the
end of the SQL.

Now, for sorting, use an Option Group. Create a button for each sort
order
you want. Be sure the default option is the default oder by for the list
box. That is, If your defalut sort for the list box is div, then make
option
button 1 div with a value of 1.

Now, in the After Update event of the Option Group:
(note that the values you put in strNewOder for each option should be the
field name you want to sort on)

Private Sub opgOrder_AfterUpdate()
Dim strSQL As String
Dim strNewOrder As String

strSQL = CurrentDb.QueryDefs("_ordertest").SQL
Select Case Me.opgOrder
Case 1
strNewOrder = "div"
Case 2
strNewOrder = "horsename"
Case 3
strNewOrder = "firstname"
Case 4
strNewOrder = "lastname"
End Select
strSQL = Replace(strSQL, ";", " ORDER BY " & strNewOrder & ";")
Me.lstActivity.RowSource = strSQL
End Sub
Click to expand...
Click to expand...
 
I really appreciate you looking at this for me. I am a novice at this and,
unfortunately, I am still not doing something right. Do I use Debug.Print
strSQL or Debug.Print strNew Order (or something else)?

Where exactly does it go? I tried several variations with no luck. Please
copy my code and show me - THANK YOU!

Susan


Dale Fye said:
Susan,

Right after your select statement, insert either a debug.print or a
messagebox to display the variable strNewOrder. The code looks like it
should work to me, that is basically what I do when I want to give my user
the ability to sort a list.

Dale

SusanForson said:
I tried this same code and keep getting blank boxes after update. It does
not appear to be writing any code to the SQL for the list box. Could you
please give me some insight? Thanks!

Here is my code:

'***For the list box:

SELECT [Select City Query].NAME, [Select City Query].IND_IDTE, [Select
City
Query].PNAME1
FROM [Select City Query]
GROUP BY [Select City Query].NAME, [Select City Query].IND_IDTE, [Select
City Query].PNAME1;

'*** I tried adding the default ORDER BY and it did seem to help. No
changes were made to this code either way...

Here is the code for the option group:

Private Sub Frame40_AfterUpdate()
'*Frame40 is the name of my option group

Dim strSQL As String
Dim strNewOrder As String

strSQL = CurrentDb.QueryDefs("Select City Query").SQL
'***"Select City Query" is the name of my query the list is based
on

Select Case Me.Frame40
Case 1
strNewOrder = "NAME"
Case 2
strNewOrder = "PNAME1"
Case 3
strNewOrder = "IND_IDTE"
'***These are my fields in the query that I want sorted

End Select
strSQL = Replace(strSQL, ";", " ORDER BY " & strNewOrder & ";")
Me.FirstCategory.RowSource = strSQL
'***FirstCategory is the name of my list box

End Sub

'*** I made the option group using the wizard. Do I need to do anything
else
'*** to the settings or the buttons within the option group? They all
show the
'*** correct default values. Any help would be greatly appreciated!




Klatuu said:
Here is how you do it.
Create a query that has all the fields you want in your list box, but do
not
define a sort order.
Create the list box using the wizard. When it asks you what to base it
on,
use the query you built. Then after you have saved it, you will have the
SQL
as the rowsource for the list box. Edit the SQL (not the saved query) to
include the defalut sort order you want by adding the ORDER BY clause at
the
end of the SQL.

Now, for sorting, use an Option Group. Create a button for each sort
order
you want. Be sure the default option is the default oder by for the list
box. That is, If your defalut sort for the list box is div, then make
option
button 1 div with a value of 1.

Now, in the After Update event of the Option Group:
(note that the values you put in strNewOder for each option should be the
field name you want to sort on)

Private Sub opgOrder_AfterUpdate()
Dim strSQL As String
Dim strNewOrder As String

strSQL = CurrentDb.QueryDefs("_ordertest").SQL
Select Case Me.opgOrder
Case 1
strNewOrder = "div"
Case 2
strNewOrder = "horsename"
Case 3
strNewOrder = "firstname"
Case 4
strNewOrder = "lastname"
End Select
strSQL = Replace(strSQL, ";", " ORDER BY " & strNewOrder & ";")
Me.lstActivity.RowSource = strSQL
End Sub


:

I have a list box with four columns (div, horse name, first name, last
name)
with rowsource set to a query qryEntriesList. I can sort the list box
any
way I want by sorting the query, but I want to provide the user with
the
ability to sort the list box using either a drop down list, or a set of
command buttons. My command button always fails by blanking out the
list
box.

This must be a well used application. Can you give me some guidance or
send
me to an explanation.
Click to expand...
Click to expand...
Click to expand...
 
Where is the data coming from? It appears from you code you are using a
query that calls a query.
SELECT [Select City Query].NAME,... is callina query named "Select City
Query", but I don't see any code or SQL that indicates you are pulling from a
table. In fact, you code says you are using that query plus the Order By you
add as the row source. The code for the list box looks good other than that.

SusanForson said:
I tried this same code and keep getting blank boxes after update. It does
not appear to be writing any code to the SQL for the list box. Could you
please give me some insight? Thanks!

Here is my code:

'***For the list box:

SELECT [Select City Query].NAME, [Select City Query].IND_IDTE, [Select City
Query].PNAME1
FROM [Select City Query]
GROUP BY [Select City Query].NAME, [Select City Query].IND_IDTE, [Select
City Query].PNAME1;

'*** I tried adding the default ORDER BY and it did seem to help. No
changes were made to this code either way...

Here is the code for the option group:

Private Sub Frame40_AfterUpdate()
'*Frame40 is the name of my option group

Dim strSQL As String
Dim strNewOrder As String

strSQL = CurrentDb.QueryDefs("Select City Query").SQL
'***"Select City Query" is the name of my query the list is based on

Select Case Me.Frame40
Case 1
strNewOrder = "NAME"
Case 2
strNewOrder = "PNAME1"
Case 3
strNewOrder = "IND_IDTE"
'***These are my fields in the query that I want sorted

End Select
strSQL = Replace(strSQL, ";", " ORDER BY " & strNewOrder & ";")
Me.FirstCategory.RowSource = strSQL
'***FirstCategory is the name of my list box

End Sub

'*** I made the option group using the wizard. Do I need to do anything
else
'*** to the settings or the buttons within the option group? They all
show the
'*** correct default values. Any help would be greatly appreciated!




Klatuu said:
Here is how you do it.
Create a query that has all the fields you want in your list box, but do not
define a sort order.
Create the list box using the wizard. When it asks you what to base it on,
use the query you built. Then after you have saved it, you will have the SQL
as the rowsource for the list box. Edit the SQL (not the saved query) to
include the defalut sort order you want by adding the ORDER BY clause at the
end of the SQL.

Now, for sorting, use an Option Group. Create a button for each sort order
you want. Be sure the default option is the default oder by for the list
box. That is, If your defalut sort for the list box is div, then make option
button 1 div with a value of 1.

Now, in the After Update event of the Option Group:
(note that the values you put in strNewOder for each option should be the
field name you want to sort on)

Private Sub opgOrder_AfterUpdate()
Dim strSQL As String
Dim strNewOrder As String

strSQL = CurrentDb.QueryDefs("_ordertest").SQL
Select Case Me.opgOrder
Case 1
strNewOrder = "div"
Case 2
strNewOrder = "horsename"
Case 3
strNewOrder = "firstname"
Case 4
strNewOrder = "lastname"
End Select
strSQL = Replace(strSQL, ";", " ORDER BY " & strNewOrder & ";")
Me.lstActivity.RowSource = strSQL
End Sub
Click to expand...
Click to expand...
 
Here is the the code I used to make a command button work for sorting the
last name:
Dim strSQL As String

strSQL = "SELECT * from qryEntriesList ORDER BY tblRiders.Lastname"

With Me.lstEntries
.RowSource = strSQL
.Requery
End With

I do the same with Division and Horsename.

Thanks for the help.
--
BobWingmeister


Klatuu said:
Where is the data coming from? It appears from you code you are using a
query that calls a query.
SELECT [Select City Query].NAME,... is callina query named "Select City
Query", but I don't see any code or SQL that indicates you are pulling from a
table. In fact, you code says you are using that query plus the Order By you
add as the row source. The code for the list box looks good other than that.

SusanForson said:
I tried this same code and keep getting blank boxes after update. It does
not appear to be writing any code to the SQL for the list box. Could you
please give me some insight? Thanks!

Here is my code:

'***For the list box:

SELECT [Select City Query].NAME, [Select City Query].IND_IDTE, [Select City
Query].PNAME1
FROM [Select City Query]
GROUP BY [Select City Query].NAME, [Select City Query].IND_IDTE, [Select
City Query].PNAME1;

'*** I tried adding the default ORDER BY and it did seem to help. No
changes were made to this code either way...

Here is the code for the option group:

Private Sub Frame40_AfterUpdate()
'*Frame40 is the name of my option group

Dim strSQL As String
Dim strNewOrder As String

strSQL = CurrentDb.QueryDefs("Select City Query").SQL
'***"Select City Query" is the name of my query the list is based on

Select Case Me.Frame40
Case 1
strNewOrder = "NAME"
Case 2
strNewOrder = "PNAME1"
Case 3
strNewOrder = "IND_IDTE"
'***These are my fields in the query that I want sorted

End Select
strSQL = Replace(strSQL, ";", " ORDER BY " & strNewOrder & ";")
Me.FirstCategory.RowSource = strSQL
'***FirstCategory is the name of my list box

End Sub

'*** I made the option group using the wizard. Do I need to do anything
else
'*** to the settings or the buttons within the option group? They all
show the
'*** correct default values. Any help would be greatly appreciated!




Klatuu said:
Here is how you do it.
Create a query that has all the fields you want in your list box, but do not
define a sort order.
Create the list box using the wizard. When it asks you what to base it on,
use the query you built. Then after you have saved it, you will have the SQL
as the rowsource for the list box. Edit the SQL (not the saved query) to
include the defalut sort order you want by adding the ORDER BY clause at the
end of the SQL.

Now, for sorting, use an Option Group. Create a button for each sort order
you want. Be sure the default option is the default oder by for the list
box. That is, If your defalut sort for the list box is div, then make option
button 1 div with a value of 1.

Now, in the After Update event of the Option Group:
(note that the values you put in strNewOder for each option should be the
field name you want to sort on)

Private Sub opgOrder_AfterUpdate()
Dim strSQL As String
Dim strNewOrder As String

strSQL = CurrentDb.QueryDefs("_ordertest").SQL
Select Case Me.opgOrder
Case 1
strNewOrder = "div"
Case 2
strNewOrder = "horsename"
Case 3
strNewOrder = "firstname"
Case 4
strNewOrder = "lastname"
End Select
strSQL = Replace(strSQL, ";", " ORDER BY " & strNewOrder & ";")
Me.lstActivity.RowSource = strSQL
End Sub


:

I have a list box with four columns (div, horse name, first name, last name)
with rowsource set to a query qryEntriesList. I can sort the list box any
way I want by sorting the query, but I want to provide the user with the
ability to sort the list box using either a drop down list, or a set of
command buttons. My command button always fails by blanking out the list
box.

This must be a well used application. Can you give me some guidance or send
me to an explanation.
Click to expand...
Click to expand...
Click to expand...
 
That looks good, but I don't think the Requery is necessary.

BetoWing said:
Here is the the code I used to make a command button work for sorting the
last name:
Dim strSQL As String

strSQL = "SELECT * from qryEntriesList ORDER BY tblRiders.Lastname"

With Me.lstEntries
.RowSource = strSQL
.Requery
End With

I do the same with Division and Horsename.

Thanks for the help.
--
BobWingmeister


Klatuu said:
Where is the data coming from? It appears from you code you are using a
query that calls a query.
SELECT [Select City Query].NAME,... is callina query named "Select City
Query", but I don't see any code or SQL that indicates you are pulling from a
table. In fact, you code says you are using that query plus the Order By you
add as the row source. The code for the list box looks good other than that.

SusanForson said:
I tried this same code and keep getting blank boxes after update. It does
not appear to be writing any code to the SQL for the list box. Could you
please give me some insight? Thanks!

Here is my code:

'***For the list box:

SELECT [Select City Query].NAME, [Select City Query].IND_IDTE, [Select City
Query].PNAME1
FROM [Select City Query]
GROUP BY [Select City Query].NAME, [Select City Query].IND_IDTE, [Select
City Query].PNAME1;

'*** I tried adding the default ORDER BY and it did seem to help. No
changes were made to this code either way...

Here is the code for the option group:

Private Sub Frame40_AfterUpdate()
'*Frame40 is the name of my option group

Dim strSQL As String
Dim strNewOrder As String

strSQL = CurrentDb.QueryDefs("Select City Query").SQL
'***"Select City Query" is the name of my query the list is based on

Select Case Me.Frame40
Case 1
strNewOrder = "NAME"
Case 2
strNewOrder = "PNAME1"
Case 3
strNewOrder = "IND_IDTE"
'***These are my fields in the query that I want sorted

End Select
strSQL = Replace(strSQL, ";", " ORDER BY " & strNewOrder & ";")
Me.FirstCategory.RowSource = strSQL
'***FirstCategory is the name of my list box

End Sub

'*** I made the option group using the wizard. Do I need to do anything
else
'*** to the settings or the buttons within the option group? They all
show the
'*** correct default values. Any help would be greatly appreciated!




:

Here is how you do it.
Create a query that has all the fields you want in your list box, but do not
define a sort order.
Create the list box using the wizard. When it asks you what to base it on,
use the query you built. Then after you have saved it, you will have the SQL
as the rowsource for the list box. Edit the SQL (not the saved query) to
include the defalut sort order you want by adding the ORDER BY clause at the
end of the SQL.

Now, for sorting, use an Option Group. Create a button for each sort order
you want. Be sure the default option is the default oder by for the list
box. That is, If your defalut sort for the list box is div, then make option
button 1 div with a value of 1.

Now, in the After Update event of the Option Group:
(note that the values you put in strNewOder for each option should be the
field name you want to sort on)

Private Sub opgOrder_AfterUpdate()
Dim strSQL As String
Dim strNewOrder As String

strSQL = CurrentDb.QueryDefs("_ordertest").SQL
Select Case Me.opgOrder
Case 1
strNewOrder = "div"
Case 2
strNewOrder = "horsename"
Case 3
strNewOrder = "firstname"
Case 4
strNewOrder = "lastname"
End Select
strSQL = Replace(strSQL, ";", " ORDER BY " & strNewOrder & ";")
Me.lstActivity.RowSource = strSQL
End Sub


:

I have a list box with four columns (div, horse name, first name, last name)
with rowsource set to a query qryEntriesList. I can sort the list box any
way I want by sorting the query, but I want to provide the user with the
ability to sort the list box using either a drop down list, or a set of
command buttons. My command button always fails by blanking out the list
box.

This must be a well used application. Can you give me some guidance or send
me to an explanation.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top