Grouping and Sorting a Report On Open Event
in [Reports]
|
Prev: display a total value
Next: Charts on a Report
From: NEWER USER on 3 May 2010 16:05 I want to use a single report with pre-defined Group and Sort criteria and then Group/Sort on different Group Levels ( some or all groups). The following code works but gives me an Enter a Parameter False when running report. How do I turn off or suspend levels of Grouping rather than = False? If I remove the ControlSource.False, the reports grouops on three levels and not one as desired. Any help appreciated. Private Sub Report_Open(Cancel As Integer) On Error GoTo Report_Open_Err DoCmd.ShowToolbar "iTurns Command Bar", acToolbarNo Select Case Forms!PrintReportsDialog.[Select Sales Rankings] Case 1 'Part Name/IMC Sales If Forms!PrintReportsDialog.[Select Sales Rankings] = 1 And Forms!PrintReportsDialog.[Select Grouping] = 1 Then Me.GroupLevel(1).ControlSource = False Me.GroupLevel(2).ControlSource = False Me.GroupHeader2.Visible = False Me.GroupHeader1.Visible = False Me.GroupHeader2.Visible = False Me.GroupLevel(0).ControlSource = "PartName" Me.GroupLevel(0).SortOrder = False Me.OrderBy = "Sales DESC" Me.OrderByOn = True End If
From: Douglas J. Steele on 3 May 2010 17:02 Does it work if you set the ControlSource to a zero-length string? Me.GroupLevel(1).ControlSource = vbNullString Me.GroupLevel(2).ControlSource = vbNullString -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "NEWER USER" <NEWERUSER(a)discussions.microsoft.com> wrote in message news:277849A7-7CFB-45F4-A83F-6F6354D55C46(a)microsoft.com... >I want to use a single report with pre-defined Group and Sort criteria and > then Group/Sort on different Group Levels ( some or all groups). The > following code works but gives me an Enter a Parameter False when running > report. How do I turn off or suspend levels of Grouping rather than = > False? > If I remove the ControlSource.False, the reports grouops on three levels > and > not one as desired. Any help appreciated. > > Private Sub Report_Open(Cancel As Integer) > On Error GoTo Report_Open_Err > DoCmd.ShowToolbar "iTurns Command Bar", acToolbarNo > Select Case Forms!PrintReportsDialog.[Select Sales Rankings] > Case 1 'Part Name/IMC Sales > If Forms!PrintReportsDialog.[Select Sales Rankings] = 1 And > Forms!PrintReportsDialog.[Select Grouping] = 1 Then > Me.GroupLevel(1).ControlSource = False > Me.GroupLevel(2).ControlSource = False > Me.GroupHeader2.Visible = False > Me.GroupHeader1.Visible = False > Me.GroupHeader2.Visible = False > Me.GroupLevel(0).ControlSource = "PartName" > Me.GroupLevel(0).SortOrder = False > Me.OrderBy = "Sales DESC" > Me.OrderByOn = True > End If
From: Marshall Barton on 3 May 2010 19:58 NEWER USER wrote: >I want to use a single report with pre-defined Group and Sort criteria and >then Group/Sort on different Group Levels ( some or all groups). The >following code works but gives me an Enter a Parameter False when running >report. How do I turn off or suspend levels of Grouping rather than = False? > If I remove the ControlSource.False, the reports grouops on three levels and >not one as desired. Any help appreciated. > >Private Sub Report_Open(Cancel As Integer) >On Error GoTo Report_Open_Err > DoCmd.ShowToolbar "iTurns Command Bar", acToolbarNo > Select Case Forms!PrintReportsDialog.[Select Sales Rankings] > Case 1 'Part Name/IMC Sales > If Forms!PrintReportsDialog.[Select Sales Rankings] = 1 And >Forms!PrintReportsDialog.[Select Grouping] = 1 Then > Me.GroupLevel(1).ControlSource = False > Me.GroupLevel(2).ControlSource = False > Me.GroupHeader2.Visible = False > Me.GroupHeader1.Visible = False > Me.GroupHeader2.Visible = False > Me.GroupLevel(0).ControlSource = "PartName" > Me.GroupLevel(0).SortOrder = False > Me.OrderBy = "Sales DESC" > Me.OrderByOn = True > End If When an unexpected prompt pops up, you need to make a careful note of the prompt string because it's an important clue to what/where the problem is. You should include an exact copy of the prompt string with your question about what caused it to help us help you track it dpwn. In this case I suspect you were prompted to enter a value for -1. It probably didn't matter what you entered, the report would then run as you designed it to run. A group level's control source must be either a field name or an expression. Expressions are distinguishable because they must start with an = sign. I don't know what you hoped would happen when you set the control source to False (i.e. -1), but you can not group on on that. You can "suspend" a group by setting it's control source to a constant expression: Me.GroupLevel(1).ControlSource = "=1" or Me.GroupLevel(1).ControlSource = "='Suspend' " and setting the corresonding group header/footer section's Visible to False as you did. Another point. If you have anything specified in Sorting and Grouping, then using the report's OrderBy property is just about useless. Instead you should specify the desired sorting as one or more additional group levels. -- Marsh MVP [MS Access]
From: NEWER USER on 4 May 2010 13:49 Thanks for teaching me "How To Suspend" a Group Level; BIG HELP! "Marshall Barton" wrote: > NEWER USER wrote: > > >I want to use a single report with pre-defined Group and Sort criteria and > >then Group/Sort on different Group Levels ( some or all groups). The > >following code works but gives me an Enter a Parameter False when running > >report. How do I turn off or suspend levels of Grouping rather than = False? > > If I remove the ControlSource.False, the reports grouops on three levels and > >not one as desired. Any help appreciated. > > > >Private Sub Report_Open(Cancel As Integer) > >On Error GoTo Report_Open_Err > > DoCmd.ShowToolbar "iTurns Command Bar", acToolbarNo > > Select Case Forms!PrintReportsDialog.[Select Sales Rankings] > > Case 1 'Part Name/IMC Sales > > If Forms!PrintReportsDialog.[Select Sales Rankings] = 1 And > >Forms!PrintReportsDialog.[Select Grouping] = 1 Then > > Me.GroupLevel(1).ControlSource = False > > Me.GroupLevel(2).ControlSource = False > > Me.GroupHeader2.Visible = False > > Me.GroupHeader1.Visible = False > > Me.GroupHeader2.Visible = False > > Me.GroupLevel(0).ControlSource = "PartName" > > Me.GroupLevel(0).SortOrder = False > > Me.OrderBy = "Sales DESC" > > Me.OrderByOn = True > > End If > > > When an unexpected prompt pops up, you need to make a > careful note of the prompt string because it's an important > clue to what/where the problem is. You should include an > exact copy of the prompt string with your question about > what caused it to help us help you track it dpwn. In this > case I suspect you were prompted to enter a value for -1. > It probably didn't matter what you entered, the report would > then run as you designed it to run. > > A group level's control source must be either a field name > or an expression. Expressions are distinguishable because > they must start with an = sign. > > I don't know what you hoped would happen when you set the > control source to False (i.e. -1), but you can not group on > on that. You can "suspend" a group by setting it's control > source to a constant expression: > Me.GroupLevel(1).ControlSource = "=1" > or > Me.GroupLevel(1).ControlSource = "='Suspend' " > and setting the corresonding group header/footer section's > Visible to False as you did. > > Another point. If you have anything specified in Sorting > and Grouping, then using the report's OrderBy property is > just about useless. Instead you should specify the desired > sorting as one or more additional group levels. > > -- > Marsh > MVP [MS Access] > . >
From: NEWER USER on 4 May 2010 14:57
I jumped the gun too early. It didn't work as expected with further testing. Case 1 - Suspends two group levels, hides 2 headers and 1 footer. Works okay. Case 2 - Suspends 1 group level, 1 header and 1 footer. Not working. Case 3 - Shows all; no hidning. Works okay. Case 2 and Case 3 are producing identical reports; the group level is not suspending in Case 2 nor hiding header/footer. Any ideas why? Private Sub Report_Open(Cancel As Integer) On Error GoTo Report_Open_Err DoCmd.ShowToolbar "iTurns Command Bar", acToolbarNo Select Case Forms!PrintReportsDialog.[Select Sales Rankings] Case 1 'IMC Sales/Part Name If Forms!PrintReportsDialog.[Select Sales Rankings] = 1 And Forms!PrintReportsDialog.[Select Grouping] = 1 Then Me.GroupLevel(0).ControlSource = "='Suspend' " Me.GroupLevel(2).ControlSource = "='Suspend' " Me.GroupHeader0.Visible = False Me.GroupHeader2.Visible = False Me.GroupFooter1.Visible = False Me.GroupFooter3.Visible = False Me.GroupLevel(1).ControlSource = "PartName" Me.GroupLevel(1).SortOrder = False Me.OrderBy = "Sales DESC" Me.OrderByOn = True End If Case 2 'IMC Sales/Region/Part Name If Forms!PrintReportsDialog.[Select Sales Rankings] = 1 And Forms!PrintReportsDialog.[Select Grouping] = 2 Then Me.GroupLevel(2).ControlSource = "='Suspend' " Me.GroupHeader2.Visible = False Me.GroupFooter3.Visible = False Me.GroupLevel(0).ControlSource = "tblType.Make" Me.GroupLevel(1).ControlSource = "PartName" Me.GroupLevel(0).SortOrder = False Me.GroupLevel(1).SortOrder = False Me.OrderBy = "Sales DESC" Me.OrderByOn = True End If Case 3 'IMC Sales/Region/Part Name/Make If Forms!PrintReportsDialog.[Select Sales Rankings] = 1 And Forms!PrintReportsDialog.[Select Grouping] = 3 Then Me.GroupLevel(0).ControlSource = "tblType.Make" Me.GroupLevel(1).ControlSource = "PartName" Me.GroupLevel(2).ControlSource = "tblCode.Make" Me.GroupLevel(0).SortOrder = False Me.GroupLevel(1).SortOrder = False Me.GroupLevel(2).SortOrder = False Me.OrderBy = "Sales DESC" Me.OrderByOn = True End If End Select "Marshall Barton" wrote: > NEWER USER wrote: > > >I want to use a single report with pre-defined Group and Sort criteria and > >then Group/Sort on different Group Levels ( some or all groups). The > >following code works but gives me an Enter a Parameter False when running > >report. How do I turn off or suspend levels of Grouping rather than = False? > > If I remove the ControlSource.False, the reports grouops on three levels and > >not one as desired. Any help appreciated. > > > >Private Sub Report_Open(Cancel As Integer) > >On Error GoTo Report_Open_Err > > DoCmd.ShowToolbar "iTurns Command Bar", acToolbarNo > > Select Case Forms!PrintReportsDialog.[Select Sales Rankings] > > Case 1 'Part Name/IMC Sales > > If Forms!PrintReportsDialog.[Select Sales Rankings] = 1 And > >Forms!PrintReportsDialog.[Select Grouping] = 1 Then > > Me.GroupLevel(1).ControlSource = False > > Me.GroupLevel(2).ControlSource = False > > Me.GroupHeader2.Visible = False > > Me.GroupHeader1.Visible = False > > Me.GroupHeader2.Visible = False > > Me.GroupLevel(0).ControlSource = "PartName" > > Me.GroupLevel(0).SortOrder = False > > Me.OrderBy = "Sales DESC" > > Me.OrderByOn = True > > End If > > > When an unexpected prompt pops up, you need to make a > careful note of the prompt string because it's an important > clue to what/where the problem is. You should include an > exact copy of the prompt string with your question about > what caused it to help us help you track it dpwn. In this > case I suspect you were prompted to enter a value for -1. > It probably didn't matter what you entered, the report would > then run as you designed it to run. > > A group level's control source must be either a field name > or an expression. Expressions are distinguishable because > they must start with an = sign. > > I don't know what you hoped would happen when you set the > control source to False (i.e. -1), but you can not group on > on that. You can "suspend" a group by setting it's control > source to a constant expression: > Me.GroupLevel(1).ControlSource = "=1" > or > Me.GroupLevel(1).ControlSource = "='Suspend' " > and setting the corresonding group header/footer section's > Visible to False as you did. > > Another point. If you have anything specified in Sorting > and Grouping, then using the report's OrderBy property is > just about useless. Instead you should specify the desired > sorting as one or more additional group levels. > > -- > Marsh > MVP [MS Access] > . > |