Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Apr 2001
    Location
    Naperville, Illinois, USA
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting Recs (Access 2002/03)

    I can change the sort order of an Access Report by using the orderby code. However when the detail section in the report is a subreport, I can't make it work. Apparantly the report needs to be open to use the sample code in the SortReport sample database. But even if I use the subreports name, it doesn't reconize the subreport with the main report open.

    Reports!srptRetProposal.OrderBy = "Totaldollars Desc"
    Reports!srptRetProposal.OrderByOn = True

    Can you tel me what I missed?

    Thanks in advance,

    Alan

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sorting Recs (Access 2002/03)

    Are you referring to the SortReports routine in <post:=299,773>post 299,773</post:>? You can't do it that way with subreports because they aren't open in the same sense as the parent report. Perhaps if you describe in more detail what you are trying to do and why you would want to change the sort order of a subform (you wouldn't ordinarily do this), someone can help you.
    Charlotte

  3. #3
    Star Lounger
    Join Date
    Apr 2001
    Location
    Naperville, Illinois, USA
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Recs (Access 2002/03)

    Hi,

    Its a subreport, not a subform if that makes a difference. The main report has the basic customer info. The detail section is a sub-report that has a one to many listing of item records for that customer. Some times, I want the details to be in a descending order by dollar amount, sometimes in an ascending order by dollar amount and sometimes sorted by a sort field that I have numbered.

    I use the subreport because I can control how many records are in a temp table thereby printing lines at the left and right sides to look like Quickbooks estimates. Could I just sort he temp table underneath the subreport?

    Thanks for your help.

    Alan

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

    Re: Sorting Recs (Access 2002/03)

    Reply edited by HansV

    I don't think you can change the sort order of a subreport on the fly, so you'll have to create a separate version of the main report and subreport for each sort order.

  5. #5
    Star Lounger
    Join Date
    Apr 2001
    Location
    Naperville, Illinois, USA
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Recs (Access 2002/03)

    First, thanks for your feedback. It looks like I can't do what I am trying. However, let me just restate what I am trying to achieve, I may be on the wrong path all together.

    1. The client likes the looks of Quickbooks invoices / estimates. Basically boxes for name, ship to and then the details in a big box over the middle of the page plus some boxes at the bottom.

    2. To achieve this look, I used the 'Lines' in my report for the detail section. I forced a certain size of the detail section by creating a temp table with each record being a row on the report , printing the left and right lines on the sides. I would create enough blank records to make my detail section the size I wanted on the report. Then I used the report footer to print the things I needed at the bottom of the report. I can do this if I use a subreport for my detail section. If I use a regular report with no subreport, I can control the sorting of the detail section very easily by passing a openargs to the report and having a select case statement in the open record procedure for the Orderby selection but I can't control the size of the detail section box. If there are only 2 records, the box is only 2 records long and I don't know how to pad it.

    3. Actually what I am printing is an estimate that could have 1 to n items. Sometimes I want the items in descending order, sometimes in ascending order and sometimes in a defined order. There could be different iterations in different orders for the same customer / project.

    What I didn't try, was just sorting the underlying table for the subreport or using a query for the subreport. Access would just print this in order of the underlying table if I have no groupings?

    Again, thanks

    Alan

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sorting Recs (Access 2002/03)

    Since you're using a temporary table to populate the subreport, you can populate it in the order you want the records to appear in the subreport. You can't just set the sort on the table, you have to append the records in the order you want in the report. As for padding, the easiest way to do that is by adding blank records to the table in order to give you the number of rows you need.
    Charlotte

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

    Re: Sorting Recs (Access 2002/03)

    Reports tend to ignore the sort order of the record source; you must either set the sort order in the Sorting and Grouping window, or using code.

    Take a look at the MSKB article ACC2000: How to Print a Constant Number of Lines Per Group (also applies to Access 2002/2003). It shows how to pad the number of "lines" per group. You can download a demo database with a working example.

Posting Permissions

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