A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Grouping and Sorting a Report On Open Event



 
 
Thread Tools Display Modes
  #1  
Old May 3rd, 2010, 09:05 PM posted to microsoft.public.access.reports
NEWER USER
external usenet poster
 
Posts: 68
Default 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  
Old May 3rd, 2010, 10:02 PM posted to microsoft.public.access.reports
Douglas J. Steele[_3_]
external usenet poster
 
Posts: 3,143
Default 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  
Old May 4th, 2010, 12:58 AM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default 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  
Old May 4th, 2010, 06:49 PM posted to microsoft.public.access.reports
NEWER USER
external usenet poster
 
Posts: 68
Default 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  
Old May 4th, 2010, 07:57 PM posted to microsoft.public.access.reports
NEWER USER
external usenet poster
 
Posts: 68
Default 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  
Old May 5th, 2010, 12:00 AM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:00 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.