Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2006
    Posts
    37

    Unanswered: Reports & Sorting & VBA

    Ok guys, You have helped me before and I am turning to you again.

    I have created a report that will be used to show the results of different queries. I have figured out how to use VBA methods to open the report, change the recordsource and any of the corresponding label headings to what I would need to match the changing queries, save the report, and finally open it in preview mode for viewing.

    However, I have now run into the problem of how the report sorts the data. It does not maintain the sorting options specified in my queries. To be specific, one of the fields changes from a date, to a week, to a month, etc. depending on what the underlying query changes to. I need to sort on this. The underlying queries themselves are sorted on these fields but that seems to be lost when it is brought into the form.

    I can not use the sorting & grouping options in the reports design view because the field name changes (Date, week, etc.).

    I guess the root question is: 1) Can I get the report to keep the sorting as the query or 2) is there a way to set the sorting for the report in VBA?

    Thanks guys!

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    In vb, type a search on the SortOrder Property and the GroupOn Property.

    Basically, you can do everything via the Sorting and Grouping box, but in your case, you need to do it via vb. So here are some examples of how to do what the box does via vb.
    Code:
        ' Set SortOrder property to ascending order.
        Me.GroupLevel(0).SortOrder = False
        ' Set GroupOn property.
        Me.GroupLevel(0).GroupOn = 1
        ' Set GroupInterval property to 1.
        Me.GroupLevel(0).GroupInterval = 1
        ' Set KeepTogether property to With First Detail.
        Me.GroupLevel(0).KeepTogether = 2
    Good luck!
    Me.Geek = True

  3. #3
    Join Date
    Feb 2006
    Posts
    37
    Quote Originally Posted by nckdryr
    In vb, type a search on the SortOrder Property and the GroupOn Property.

    Basically, you can do everything via the Sorting and Grouping box, but in your case, you need to do it via vb. So here are some examples of how to do what the box does via vb.
    Code:
        ' Set SortOrder property to ascending order.
        Me.GroupLevel(0).SortOrder = False
        ' Set GroupOn property.
        Me.GroupLevel(0).GroupOn = 1
        ' Set GroupInterval property to 1.
        Me.GroupLevel(0).GroupInterval = 1
        ' Set KeepTogether property to With First Detail.
        Me.GroupLevel(0).KeepTogether = 2
    Good luck!
    Nick,

    Thanks that looks exactly like what I was looking for. I knew it had to be available but my searches just didn't seem to turn up the appropriate properties. I will give it a shot now.

  4. #4
    Join Date
    Feb 2006
    Posts
    37
    Ok well that seems to be working just the way I was hoping. I think I got all of my loose ends tied up aroudn that. Now I have one more question.

    Let's say I want to close the report now without making any changes. Unlike, a form I can not place a button on the report that will allow me to use the DoCmd.Close procedure and stack on the no save option. How would I go about getting this report to close so that it does not save any of the changes that are being made to it each time it opens.

    I just really don't want that pop-up menu to come when the hit the close button in the upper right hand corner and someone accidentally save which could be a big problem for me.

    Any ideas anyone?

  5. #5
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    Not that I'm very good at remembering such, but I didn't think that forms and reports save such settings on close, or give such prompt (unless you open it in design view and do changes). Though, I think that if you switch from preview to design view, then you might get prompted whether to save changes or not - but the users shouldn't/wouldn't do that?
    Roy-Vidar

  6. #6
    Join Date
    Feb 2006
    Posts
    37
    Roy,

    Thanks for your reply. Your are mostly correct that the user shouldn't / wouldn't do it but the problem as most of us know is that someone eventually will and this could potentially mess up soem of the code. The report is being opened in design view and having changes made but it only prompts for the save on close.

    I am hoping someone else will come up with an idea... anyone...

Posting Permissions

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