If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Grouping and Sorting a Report On Open Event
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 |
#2
|
|||
|
|||
Grouping and Sorting a Report On Open Event
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" wrote in message ... 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 |
#3
|
|||
|
|||
Grouping and Sorting a Report On Open Event
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] |
#4
|
|||
|
|||
Grouping and Sorting a Report On Open Event
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] . |
#5
|
|||
|
|||
Grouping and Sorting a Report On Open Event
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] . |
#6
|
|||
|
|||
Grouping and Sorting a Report On Open Event
NEWER USER wrote:
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 Nothing in case 2 jumps out at me as being wrong. I would first double check to make sure you have the header/footer section names correct and use a break point to verify that [Select Sales Rankings] = 1 and [Select Grouping] = 2 when you get to case 2. -- Marsh MVP [MS Access] |
Thread Tools | |
Display Modes | |
|
|