Grouping and Sorting a Report On Open Event

  • Thread starter Thread starter NEWER USER
  • Start date Start date
N

NEWER USER

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
 
Does it work if you set the ControlSource to a zero-length string?

Me.GroupLevel(1).ControlSource = vbNullString
Me.GroupLevel(2).ControlSource = vbNullString
 
Thanks for teaching me "How To Suspend" a Group Level; BIG HELP!

Marshall Barton said:
NEWER said:
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
Click to expand...


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.
Click to expand...
 
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 said:
NEWER said:
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
Click to expand...


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.
Click to expand...
 
Back
Top