Change the filter or sort order of a form or report
After a form or report is open, you can change the filter or sort order in response to users' actions by setting form and report properties in Visual Basic for Applications (VBA) code. For example, you may want to provide a button or a shortcut menu that users can use to change the records that are displayed. Or you may include an option group control on a form that users can use to select from common sorting options.
To set the filter of a form or report, set its Filter property to the appropriate wherecondition argument, and then set the FilterOn property to True. To set the sort order, set the OrderBy property to the field or fields you want to sort on, and then set the OrderByOn property to True. If a filter or sort order is already applied on a form, you can change it simply by setting the Filter or OrderBy properties.
When you apply or change the filter or sort order by setting these properties, Access automatically requeries the records in the form or report. For example, the following code changes the sort order of a form based on a user's selection in an option group:
Private Sub SortOptionGrp_AfterUpdate()
Const conName = 0
Const conDate = 1
On Error GoTo ErrorHandler
Select Case SortOptionGrp
Case conName
Me.OrderBy = "LastName, FirstName" ' Sort by two fields.
Case conDate
Me.OrderBy = "HireDate DESC" ' Sort by descending date.
End Select
Me.OrderByOn = True ' Apply the sort order.
Exit Sub
ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub
Whether the filter and sort order get set in code or by the user, you can apply or remove them by setting the FilterOn and OrderByOn properties to True or False.
Support and feedback
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for