Dynamic Sorting

Hello;

I created a form with a list box of clients. There are about 50 clients
within the list box and when you click on one the rest of the fields in the
form are populated with that client's information. (Name, Address........)


Above the list box I created an Option Group which allows the user to filer
between the 50 clients. (All, New, Old). Upon clicking the option the
'AferUpdate' property setting runs a new SQL Statement to populate the list
box.

What I am trying to do now is add a filter to the form. So another option
group, which will allow the user to sort (Based upon the current lists boxes
recordset) by Name, ID, or Date.

How can I retain the current record set and simply sort by that field. OR
do I need to requery the list box adding an ORDER BY ?

Any help would be much appreciated, I'm looking for the best way to go about
this.

thanks
ACase

W

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "ListboxSorting.mdb" which illustrates how to do this with
buttons above each column in the listbox. It recreates the SQL of the
RowSource. It also shows how to do this with a subform.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L


"ACase" wrote in message
...
Hello;

I created a form with a list box of clients. There are about 50 clients
within the list box and when you click on one the rest of the fields in
the
form are populated with that client's information. (Name,
Address........)


Above the list box I created an Option Group which allows the user to
filer
between the 50 clients. (All, New, Old). Upon clicking the option the
'AferUpdate' property setting runs a new SQL Statement to populate the
list
box.

What I am trying to do now is add a filter to the form. So another option
group, which will allow the user to sort (Based upon the current lists
boxes
recordset) by Name, ID, or Date.

How can I retain the current record set and simply sort by that field. OR
do I need to requery the list box adding an ORDER BY ?

Any help would be much appreciated, I'm looking for the best way to go
about
this.

thanks
ACase

W

ACase,

Reassign the RowSource and requery. You need to check if there is an
existing ORDER BY clause, or a trailing semi-colon. The code below assumes
that the ORDER BY clause is the last clause of the SQL statement:

Dim strRowSource As String
Dim strTableName As String
Dim strOrderBy As String
Dim intStripPosition As Integer

strTableName = "YourTable"
strRowSource = Me![YourComboBox].RowSource

' Obtain bare bones SELECT statement

' Strip current ORDER BY clause
intStripPosition = InStr(strRowSource, "ORDER BY")

If intStripPosition 0 Then
strRowSource = Trim(Left(strRowSource, intStripPosition - 1))
Else
'No Order By clause, strip trailing semi-colon
intStripPosition = InStr(strRowSource, ";")
If intStripPosition 0 Then
strRowSource = Trim(Left(strRowSource, intStripPosition - 1))
End If
End If

' Assign field name string.
Select Case Me![YourOptionGroup]
Case 1
strOrderBy = "YourFirstSelection"
Case 2
strOrderBy = "YourSecondSelection"
Case Else
strOrderBy = “YourDefaultSelection�
End Select

' Build ORDER BY clause, assign new RowSource, and Requery
strOrderBy = " ORDER BY " & strTableName & "." & strOrderBy
Me![YourComboBox].RowSource = strRowSource & strOrderBy
Me![YourComboBox].Requery

Hope that helps.
Sprinks

"ACase" wrote:

Hello;

I created a form with a list box of clients. There are about 50 clients
within the list box and when you click on one the rest of the fields in the
form are populated with that client's information. (Name, Address........)


Above the list box I created an Option Group which allows the user to filer
between the 50 clients. (All, New, Old). Upon clicking the option the
'AferUpdate' property setting runs a new SQL Statement to populate the list
box.

What I am trying to do now is add a filter to the form. So another option
group, which will allow the user to sort (Based upon the current lists boxes
recordset) by Name, ID, or Date.

How can I retain the current record set and simply sort by that field. OR
do I need to requery the list box adding an ORDER BY ?

Any help would be much appreciated, I'm looking for the best way to go about
this.

thanks
ACase

W

Roger, Sprinks,

Thank you - it works.

"Sprinks" wrote:

ACase,

Reassign the RowSource and requery. You need to check if there is an
existing ORDER BY clause, or a trailing semi-colon. The code below assumes
that the ORDER BY clause is the last clause of the SQL statement:

Dim strRowSource As String
Dim strTableName As String
Dim strOrderBy As String
Dim intStripPosition As Integer

strTableName = "YourTable"
strRowSource = Me![YourComboBox].RowSource

' Obtain bare bones SELECT statement

' Strip current ORDER BY clause
intStripPosition = InStr(strRowSource, "ORDER BY")

If intStripPosition 0 Then
strRowSource = Trim(Left(strRowSource, intStripPosition - 1))
Else
'No Order By clause, strip trailing semi-colon
intStripPosition = InStr(strRowSource, ";")
If intStripPosition 0 Then
strRowSource = Trim(Left(strRowSource, intStripPosition - 1))
End If
End If

' Assign field name string.
Select Case Me![YourOptionGroup]
Case 1
strOrderBy = "YourFirstSelection"
Case 2
strOrderBy = "YourSecondSelection"
Case Else
strOrderBy = “YourDefaultSelection�
End Select

' Build ORDER BY clause, assign new RowSource, and Requery
strOrderBy = " ORDER BY " & strTableName & "." & strOrderBy
Me![YourComboBox].RowSource = strRowSource & strOrderBy
Me![YourComboBox].Requery

Hope that helps.
Sprinks

"ACase" wrote:

Hello;

I created a form with a list box of clients. There are about 50 clients
within the list box and when you click on one the rest of the fields in the
form are populated with that client's information. (Name, Address........)


Above the list box I created an Option Group which allows the user to filer
between the 50 clients. (All, New, Old). Upon clicking the option the
'AferUpdate' property setting runs a new SQL Statement to populate the list
box.

What I am trying to do now is add a filter to the form. So another option
group, which will allow the user to sort (Based upon the current lists boxes
recordset) by Name, ID, or Date.

How can I retain the current record set and simply sort by that field. OR
do I need to requery the list box adding an ORDER BY ?

Any help would be much appreciated, I'm looking for the best way to go about
this.

thanks
ACase

W