RE: Dynamic Sorting

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
.