Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #10829

Re: Pass In Sort Order to Report

Show all headers | View raw

On 15/06/2015 23:31:20,  wrote:
> On Monday, June 15, 2015 at 2:35:10 PM UTC-7, mfl...@scu.edu wrote:
>> On Monday, June 15, 2015 at 2:21:45 PM UTC-7, mfl...@scu.edu wrote:
>> > On Friday, June 12, 2015 at 1:01:04 PM UTC-7, Patrick Finucane wrote:
>> > > On Friday, June 12, 2015 at 2:17:17 PM UTC-4, mfl...@scu.edu wrote:
>> > > > Hi,
>> > > > I have two reports that are in fact identical apart from the sort o
> rder.
>> > > >
>> > > > To run them I or the user clicks one of two buttons on a form, one
> button for one sort order, a second button for the other sort order. In th
> > e function for the Click event of the button I have this code:
>> > > >
> > e function for the Click event of the button I have this code:
>> > > > DoCmd.OpenReport "rptCMinusReport", acViewPreview
> > e function for the Click event of the button I have this code:
>> > > >
> > e function for the Click event of the button I have this code:
>> > > > (That's not all the code - I have error handling etc)
> > e function for the Click event of the button I have this code:
>> > > >
> > e function for the Click event of the button I have this code:
>> > > > Unfortunately, I have two copies of the report, one with one sort o
> e function for the Click event of the button I have this code: rder, the
> > other with the second sort order.
>> > > > This is bad design because it means if I make a change to the first
> other with the second sort order. report I have to make the same change to
> the second report. Is there a wa y I can pass the sort order I want into
> the report when I do the DoCmd so t hat that I can use one report for both
> buttons. Alternatively, have one bu tton but prompt for the sort order I
> > want to use?
>> > > >
> > want to use?
>> > > > Any help appreciated,
> > want to use?
>> > > >
> > want to use?
>> > > > Thanks,
> > want to use?
>> > > >
> > want to use?
>> > > > Michael
> > want to use?
>> > >
> > want to use?
>> > > I created a simple report and this seems to work.  In the report's On
> want to use? Load event I entered
>> > >   Private Sub Report_Load()
>> > >     Me.OrderBy = Me.OpenArgs
>> > >     Me.OrderByOn = True
>> > >   End Sub
>> > >
>> > > To open/call the report, I entered
>> > > Docmd.openreport "Test",acViewPreview,,,,"TestID"
>> > >
>> > > TestID is the field I want to sort.  It is an OpenArgs value.  I coul
> d do a "TestID Desc" or "CustName, DateAdded" if I wanted to.
>> > >
>> > > See if this will work for you.
>> >
>> > Patrick,
>> > I did that, but when I click on the button, I get this error:
>> >
>> > "Run-time error '94':
>> > Invalid use of Null
>> >
>> > at the line Me.OrderBy = Me.OpenArgs
>>
>> Sorry.  Ignore that post.  Somehow I trashed an edit.  I think it's worki
> ng.   I'll play a bit more and let you know.
> 
> Hmm. So it runs ok when I click the button, but the order by specified by
> OpenArgs is ignored. When I look at the report in Design view, there is a
> window at the bottom labelled "Group, Sort, and Total." In there this is
> specified: Sort on GPA
> Group on Last_First
> Sort by Term
> Sort by Class Descr
> 
> No matter what I put as the sort order string in the OpenReport line, such
> as this:
> 
> DoCmd.OpenReport "rptCMinusReport", acViewPreview, , , , "Last_First,
> Term"
> 
> the sort order at the bottom of the design view window: "GPA, Last_First,
> T erm, Class Descr" is applied. Also, the behavior is unaffected by the
> valu e in the property sheet for the report of 'order by on Load", it can
> be Yes or No, it does not seem to have an effect.
> 
> I need the Group by on Last_First so that I can have a header.
> 
> 
> 
> 

Don't know if this helps. It runs on the OnOpen of the report

PrintOption:
    Msg = "Select Print order" & vbCrLf
    Msg = Msg & "A for Alphabetical, D for Date, T for Type of donation"
    Letter = InputBox(Msg, "Print Order", "A")
    If Letter = "" Then                 ' Cancel
        DoCmd.CancelEvent
        Exit Sub
    End If

PrintIt:
    If Letter = "A" Then                                    ' Alphabetical
Me.GroupLevel(0).ControlSource olSource = "=[MemSurname]+[MemFirstName]" '
Surname order         Me.Names.ControlSource = "Names"
        Me.ItemDesc.ControlSource = "ItemDesc"
        Me.LabelType.Caption = "Type of Donation"
        Me.GroupLevel(1).ControlSource = "PromiseID"
        Me.GroupLevel(1).SortOrder = True                   ' Ascending
        Me.GroupLevel(1).GroupOn = 0                        ' Each Value
        Me.GroupLevel(1).KeepTogether = 1                   ' Whole Group
        Me.GroupLevel(1).SortOrder = False                  ' Ascending
        Me.GroupLevel(1).GroupOn = 0                        ' Each Value
    ElseIf Letter = "D" Then                                ' Date order
        Me.GroupLevel(0).ControlSource = "PromiseDate"
        Me.GroupLevel(0).SortOrder = True                   ' Decending
        Me.GroupLevel(1).ControlSource = "=[MemSurname]+[MemFirstName]"
        Me.Names.ControlSource = "ItemDesc"
        Me.ItemDesc.ControlSource = "Names"
        Me.LabelType.Caption = "Name"
    Else                                                    ' Category Order
        Me.GroupLevel(0).ControlSource = "ItemDesc"         ' Donated Item
        Me.GroupLevel(0).SortOrder = False                  ' Ascending
        Me.GroupLevel(0).GroupOn = 0                        ' Each Value
        Me.GroupLevel(0).KeepTogether = 1                   ' Whole Group
        Me.Names.ControlSource = "ItemDesc"
        Me.ItemDesc.ControlSource = "Names"
        Me.LabelType.Caption = "Name"
        Me.GroupLevel(1).ControlSource = "=[MemSurname]+[MemFirstName]"
        Me.GroupLevel(1).SortOrder = False                  ' Ascending
        Me.GroupLevel(1).GroupOn = 0                        ' Each Value
        Me.GroupLevel(1).KeepTogether = 0                   ' No
    End If

The report itself has 2 Group on's defined. Don't think it matters
particularly what they are, as the OnOpen Routine overwrites them

Phil

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Pass In Sort Order to Report mflynn@scu.edu - 2015-06-12 11:17 -0700
  Re: Pass In Sort Order to Report Patrick Finucane <patrickfinucanetx@gmail.com> - 2015-06-12 13:01 -0700
    Re: Pass In Sort Order to Report mflynn@scu.edu - 2015-06-15 14:21 -0700
      Re: Pass In Sort Order to Report mflynn@scu.edu - 2015-06-15 14:35 -0700
        Re: Pass In Sort Order to Report mflynn@scu.edu - 2015-06-15 15:31 -0700
          Re: Pass In Sort Order to Report "Phil" <phil@stantonfamily.co.uk> - 2015-06-16 09:03 +0100
  Re: Pass In Sort Order to Report mflynn@scu.edu - 2015-06-17 10:22 -0700

csiph-web