VBA Programmatically change query field sort order and criteria

Posted on 2012-09-12
Last Modified: 2012-09-21
I have an application that runs a query  "Q01" and a list form that presents the query results and so, its control field is linked to "Q01". The query results in a series of fields "F01", "F02, "F03", etc. (the list of fiels is always the same)

However, depending on conditions, I need to present "Q01" both filtered by specific field values, e.g. F01 > 1234 or, mostly for dates, "F03 > Date1 and F03 < Date2"  and sorted either ascendetly or descendently by another field, e.g. F07.

I need to do this programmatically in VBA

how do I do it?
Question by:jirdeaid
    LVL 77

    Expert Comment

    How do you establish what the requirements are for any particular execution of this query?
    LVL 56

    Expert Comment

    by:Jim Dettman (Microsoft MVP/ EE MVE)
    <<I need to do this programmatically in VBA

    how do I do it?

    One simple way to do that is with the query alone; use it with this form alone and have the query refer to controls on the form (these can be hidden) for the criteria:

    >=Forms![myFormName]![mycontrolName] or is null Forms![myFormName]![mycontrolName]

    If you populate the control, the criteria is checked.  If not, the criteria is ignored.

    If you really want to do it with VBA for some reason, you need to open the querydef object, modify the SQL property, save it, then requery the form.

    another way would be to use the form filter and orderby properties.


    Author Comment

    I reckon that the most flexible and straightforward manner even for future documentation will be to try to follow the very last suggestion and setup, programatically the form filter and the orderby properties.

    In my code. I click on a field in a form and the click event should
    a) show a small sign in the clicked field (up/down/nill) for what is the order that the user selects (the code is below, showing that, in the form header (e.g. in the below snippet  the user selected the "F0001_CurrentOwnerID" field and then by requering the Q01 query the new order should appear.
    I still do not have the code for filtering as I do not know the syntax. I suppose that it is something like

    strFilter = "[Q01.FieldName ] = " & Value
    Me.Filter =  strFilter
    Me.Orderby "[Q01.FieldName] ascending"?

    My current code is like below:

    Private Sub F0001_SortOrderCurrentOwnerID_Click()
        Dim IBool As Boolean, _
            strUserID As String, _
            strFieldName As String
        strUserID = Forms![F91_LoggedUser]![F91_F0001_DiasFaltam]
        IBool = InsertLog("F0001_SortOrderCurrentOwnerID_Click", "Click", "", "", "", 0, 0, strUserID, "")
        strFieldName = "F0001_CurrentOwnerID"
        F001_FieldSortOrder (strFieldName)
        Me.F0001_SortOrderPriority = Forms![F91_LoggedUser]![F91_F0001_ToDoPriority]
        Me.F0001_SortOrderDiasFaltam = Forms![F91_LoggedUser]![F91_F0001_DiasFaltam]
        Me.F0001_SortOrderCurrentOwnerID = Forms![F91_LoggedUser]![F91_F0001_CurrentOwnerID]
        Me.F0001_SortOrderToDoRisk = Forms![F91_LoggedUser]![F91_F0001_ToDoRisk]
        Me.F0001_SortOrderToDoStatusDate = Forms![F91_LoggedUser]![F91_F0001_ToDoStatusDate]
        Me.F0001_SortOrderToDoCurrentStatus = Forms![F91_LoggedUser]![F91_F0001_ToDoCurrentStatus]
        Me.F0001_SortOrderToDoDateFinit = Forms![F91_LoggedUser]![F91_F0001_ToDoDateFinit]
        Me.F0001_SortOrderToDoPercentDone = Forms![F91_LoggedUser]![F91_F0001_ToDoPercentDone]
        Me.F0001_SortOrderToDoID = Forms![F91_LoggedUser]![F91_F0001_ToDoID]
    End Sub
    LVL 84

    Accepted Solution

    I'm not sure what all of your "Me.F0001 etc etc" settings are for, but it seems your question is about filtering/sorting on a form?

    If so, your code here:

    strFilter = "[Q01.FieldName ] = " & Value
    Me.Filter =  strFilter
    Me.Orderby "[Q01.FieldName] ascending"?

    Will work (with mods to the Orderby portion), but you also need to toggle those to "ON" after you set them:

    Me.FilterOn = True
    Me.OrderbyOn = True

    Note too that "ASCending" is the default sort order, and you don't need to define it. You define DESCending like this:

    Me.OrderBy = "[YourFieldName] DESC"
    Me.OrderByOn = True

    If you want to sort ASCending, you do this:

    Me.OrderBy = "[YourFieldName]"
    Me.OrderByOn = True

    Although you can include the ASC qualifier if you wish:

    Me.OrderBy = "[YourFieldName] ASC"
    Me.OrderByOn = True

    Author Comment

    Thanks a lot.
    Its almost working right now, I just have a small question. Please feel free not to answer and I'll start a new question.
    On my code I am have a crash the moment I am using the

    Me.FilterOn = True

    My code is below

    Private Sub Form_Load()
        Dim IBool As Boolean
        Dim strUserID As String, _
            strFilter As String

        Me.Filter = ""
        If IsNull(Forms![F91_LoggedUser]![CurrentUserID]) Then
            strUserID = "##NA##"
            strUserID = Forms![F91_LoggedUser]![CurrentUserID]
        End If
        Me.F0001_CurrentOwnerID = "###Impossible##UserID###"
        '[Q01_UserToDoTasks].[T15_CurrentOwnerID] = Nz([Q01_UserToDoTasks].[T15_CurrentOwnerID], "")
        strFilter = " Me.F0001_CurrentOwnerID = " & "[Q01_UserToDoTasks].[T15_CurrentOwnerID]"
        Me.Filter = strFilter
        MsgBox (strFilter)
        Me.FilterOn = True
        Me.F0001_SortOrderPriority = Forms![F91_LoggedUser]![F91_F0001_ToDoPriority]
        Me.F0001_SortOrderCurrentOwnerID = Forms![F91_LoggedUser]![F91_F0001_CurrentOwnerID]
        Me.F0001_SortOrderRisk = Forms![F91_LoggedUser]![F91_F0001_ToDoRisk]
        Me.F0001_SortOrderToDoStatusDate = Forms![F91_LoggedUser]![F91_F0001_ToDoStatusDate]
        Me.F0001_SortOrderToDoCurrentStatus = Forms![F91_LoggedUser]![F91_F0001_ToDoCurrentStatus]
        Me.F0001_SortOrderToDoDateFinit = Forms![F91_LoggedUser]![F91_F0001_ToDoDateFinit]
        Me.F0001_SortOrderPercent = Forms![F91_LoggedUser]![F91_F0001_ToDoPercentDone]
        Me.F0001_SortOrderToDoID = Forms![F91_LoggedUser]![F91_F0001_ToDoID]
        IBool = InsertLog("F0001 Form_Load", "Load", "", "", "", 0, 0, strUserID, "")
    End Sub

    I am getting an error message that I camnot understand. Any clues?

    I am putting below the snippets

    containing the strFilter text

    place in the code where it stops

    LVL 84
    I'd suspect the form is trying to Save the file at this point, and you haven't set the PK value? Just a guess, of course, but as you mentioned, it would probably be a good idea to open a new question for this.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now