RE: Dynamic Sorting

Tech-Archive recommends: Fix windows errors by optimizing your registry

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
.

Relevant Pages

  • Re: Another @ Get command reference
    ... > automatically generates the commands that create lists. ... > FoxPro window, ... > on the current font. ... > If SHOW GETS is issued, the RANGE clause is re-evaluated. ...
    (microsoft.public.fox.helpwanted)
  • Parsing file format to ensure file meets criteria
    ... There is no limit on the number of clause lines. ... Header lines must precede clause lines. ... elements of the list are strings, even integers are converted to strings ... Question - how are nested lists indexed? ...
    (comp.lang.python)
  • RE: Dynamic Sorting
    ... > existing ORDER BY clause, ... > Dim strRowSource As String ... > ' Build ORDER BY clause, assign new RowSource, and Requery ... which will allow the user to sort (Based upon the current lists boxes ...
    (microsoft.public.access.forms)
  • List comprehensions and pattern matching
    ... list comprehensions and pattern matching are ... lists, ... to the right of the binding clause ...
    (comp.lang.scheme)
  • Re: which field to choose
    ... For example, we'll write a COND macro, which will transform lists like: ... (second clause) ... (cons 'progn (rest clause)))) ...
    (comp.lang.lisp)