Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Change report sort order on the fly (97)

    I have a report in Ac97 that shows fuel consumption for a fleet of trucks. Presently, the report shows subtotals for TruckName and DriverName. In addition, the report is sorted by TripDate, so that the trips are printed chronologically. The grouping of the report is by TruckName, DriverName, and TripDate. So far, so good.

    I want to modify the report on-the-fly using VBA in the report's Open event to show just a single subtotal i.e. TruckName. It's relatively easy to hide the group section for the DriverName, but how do I handle the report's sorting? If I don't do anything, then the report continues to be sorted as TruckName, DriverName, and TripDate, which results in a confusing printed order. What I really want to do is delete DriverName altogether from the sorting or to change the order of DriverName and TripDate. If I was to open the report manually in design mode, it would be easy to drag-and-drop the sort order, but I need to do it programmatically.

    I've tried putting this into the code:
    Me.GroupLevel(1).ControlSource = "TripDate"
    Me.GroupLevel(2).ControlSource = "DriverName"

    but it doesn't accomplish what I need. BTW, I use the same report to subtotal the data several other ways by changing GroupLevel(0).ControlSource, so the brute-force method of using two versions of the report is not really an option. Any ideas?
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  2. #2
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Change report sort order on the fly (97)

    look at the help for the orderby property. You can either make a message box that offers two choices and based on the response change the orderby property, or make a dialog form that gives you list box of choices and opens the report based on the choice.
    There also a property that controls how the group is sorted that you can set.
    I hope this helps

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change report sort order on the fly (97)

    Close... but still no joy!

    I've written the report with the "driver" section Visibility set to false, and with the Grouping and Sorting defined as "TruckName,DriverName,TripDate". When I open the report in the mode where I want to ignore the Driver information, it works correctly. Using a parameter passed to the report's Open event, I execute the following code when I want to reveal the driver info:

    Private Sub RevealDriverSubtotals(psPrimarySortFieldname As String)
    Me.Section(9).Visible = True
    Me.Section(10).Visible = True
    Me.GroupLevel(1).ControlSource = "DriverName"
    Me.GroupLevel(2).ControlSource = "TripDate"
    Me.OrderBy = psPrimarySortFieldname & ",DriverName,TripDate"
    Me.OrderByOn = True
    End Sub

    A temporary textbox on the report reveals that the OrderBy property is being set correctly, and inspection of the report's data confirms that the proper order is being used. The problem comes with the Grouping that is used for subtotals: Access continues to print a subtotal for every day even if the same driver is used for consecutive trips. The only time when the report shows more than one trip in a subtotal is when the same driver makes more than one trip on a single day.

    It's as if the OrderBy has properly changed the sorting order of the records, but it hasn't changed the grouping order. I am baffled, and any help would be appreciated.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

Posting Permissions

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