Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jul 2003
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ACCESS 2002 Sort (Access 2002)

    I have a form that has a command button that opens a report; I would like the report to open up with the data that was filtered and sorted by using the Filter and Sort Commands on the Form View. Currently the Report Displays all records in the table; even thought the form only displays filtered records.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ACCESS 2002 Sort (Access 2002)

    You can set the report filter and sort order in the On Open event of the report:

    Private Sub Report_Open(Cancel As Integer)
    ' Test if form is loaded
    If IsFormLoaded("frmSomething") = False Then
    Exit sub
    End If
    ' Test if form has filter
    If Forms!frmSomething.FilterOn = True Then
    Me.Filter = Forms!frmSomething.Filter
    Me.FilterOn = True
    End If
    ' Test if form has sort order
    If Forms!frmSomething.OrderByOn = True Then
    Me.OrderBy = Forms!frmSomething.OrderBy
    Me.OrderByOn = True
    End If
    End Sub

    The procedure uses the following function that should be in a general module, so that it can be used throughout the database.

    Function IsFormLoaded(ByVal strName As String) As Boolean
    ' Returns True if the specified form is open in Form view or Datasheet view.
    If SysCmd(acSysCmdGetObjectState, acForm, strName) Then IsFormLoaded = Forms(strName).CurrentView
    End Function

  3. #3
    2 Star Lounger
    Join Date
    Jul 2003
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ACCESS 2002 Sort (Access 2002)

    I entered the code the On Open event of the desired report and I entered the Function code into the Module; I then opened the form used the Sorting Function on the Form View. When I used the command button to open the report, the report displayed the records in the sort order of the table and not in the sort order of the form. My concern is that when the Function is entered; should it be saved to a specific name? Currently it is save to Module1.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ACCESS 2002 Sort (Access 2002)

    1. I hope you replaced frmSomething by the name of your form, otherwise, the IsFormLoaded test will exit the On Open procedure before setting the sort order and filter.
    2. You should NOT specify a sort order for the report in the Sorting and Grouping window, that will overrule the OrderBy in the code.

    It doesn't matter much what you call the module, as long as you don't give it the same name as a function or procedure. I usually prefix module names with "bas" (for Basic), e.g. basUtilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •