Filtering and sorting using vba or Macro Builder RRS feed

  • Question

  • Hi,

    I have made the attached form and I want to add some functionality.

    I would like to insert buttons above each column (Company / Case ID / the various date columns) such that the onclick event sorts the resulting recordset DESC or ASC by the specific column. If someone has advice where I should start (links to help or direct advice / vba is fine) with this I would appreciate it.

    Also, the All Open button, I want to return a recordset where DateCaseClosed is null and also use this VBA to set the value of the search fields back to null. But again, I am not sure how to filter.

    I know it is something like  Me.Filter = [DateCaseClosed] is null

    Maybe a few simple example filters would assist me with syntax?

    Thanks in advance.


    Clearing search fileds vba code.

        Me.txtCompanyFilter.Value = Null
        Me.cboCaseOfficerFilter.Value = Null
        Me.txtCaseOpenStartDateFilter.Value = Null
        Me.txtCaseOpenEndDateFilter.Value = Null
        Me.txtCaseClosedStartDateFilter.Value = Null
        Me.txtCaseClosedEndDateFilter.Value = Null
        Me.txtInvOpenStartDateFilter.Value = Null
        Me.txtInvOpenEndDateFilter.Value = Null
        Me.txtInvClosedStartDateFilter.Value = Null
        Me.txtInvClosedEndDateFilter.Value = Null


    Friday, July 13, 2018 8:47 AM


  • Hi JG,

    Filtering goes something like this:

    Me.Filter = "[DateCaseClosed] Is Null"
    Me.FilterOn = True

    Unfiltering goes something like this:

    Me.Filter = ""
    Me.FilterOn = False

    Sorting goes something like this:

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

    For descending sort:

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

    Hope it helps...

    Friday, July 13, 2018 2:49 PM