asked on

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?

peter57r

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

Jim Dettman (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.

Jim.

João serras-pereira

ASKER

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

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

João serras-pereira

ASKER

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

Scott McDaniel (EE MVE )

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.