Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 914
  • Last Modified:

VBA Programmatically change query field sort order and criteria

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?
0
João serras-pereira
Asked:
João serras-pereira
1 Solution
 
peter57rCommented:
How do you establish what the requirements are for any particular execution of this query?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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.

Jim.
0
 
João serras-pereiraAuthor Commented:
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
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
0
 
João serras-pereiraAuthor Commented:
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##"
    Else
        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



error
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now