sorting & grouping for access reports

[Elsie]

hi all, I am designing a report using access. I need to sum up the total
sales for a month, total for each customer, total for each ship-to country
for each customer and each category of product....so I have 4 footer to do
the summing... now that I got the totals, I got another problem: I need to
separate the ship-to country for each customer..

e.g.:
customer A has ship-to countries as Belgium, Germany, Italy.
my report is giving the printout as Belgium sales in 1 page, followed by
Germany sales in same page if there is space.

I need to separate them such that Belgium sales in one page, Germany sales
in next page.
how do I go about doing so?

 

[Poh]

Hi
Try this
Insert a page break in the ship-to country's footer.

 

[Wayne Morgan]

It appears that one of your groupings is on the country. If so, right click
the group header bar and choose properties or just left click the bar if the
Properties sheet is already open. On the Format tab, try the Force New Page
option.

 

[Elsie]

is using page break the same as going to the properties of ship-to country's
footer --> under force new page-->'yes' ?

cos i tried using the force new page property for this footer, but this
results in my customer total going to the next page.... i need to have the
customer total in the same page if there is space on that page.

i tried to insert a page break, but nothing seems to happen...

 

[Wayne Morgan]

Just checking to see if I have the request correct, you want to start a new
page after the SMN footer unless the current SMN group is the last in its
groups, then you want the SDC footer to be on the same page as the SMN
footer. Is this correct? If so, could you create an SMN Header and do a page
break there? You wouldn't need to put any controls in it.

 

[Elsie]

that sort of solves the problem.... because for some customers, they have
many SMN, and i need the data for each SMN to start on different pages, but
the SDC (customer total) should be on the same page if there is enough
space.

tried the page break at SMN header, this method will give an additional page
with the page header only and then the next page will give the desired
output....

is there any way i can continue with this but do not print preview those
pages with the page header only? hope i am making sense..

 

[Wayne Morgan]

The attachment has a picture, but no explanation. The only thing I see is a
( in the wrong spot in a couple of the textboxes and a little of the text
being cut off. Is this the problem?

 

[Elsie]

ya... if i put a page break at SMN header, the first page will be those
textboxes with no other data....but the rest of the pages are the way i
wanted them to be... just the first page...
perhaps i should just get the user not to print the first page?

 

[Wayne Morgan]

What happens if you turn the page break of, but in the Format event of the
detail section add,

Me.Section(acGroupLevel1Header).ForceNewPage = 1

to turn it on. This way you get through it once before it starts.

 

[Wayne Morgan]

No, it is a VBA command. Instead of opening the Builder, set the Detail
Section's Format event to [Event Procedure], then click the ... Insert the
code in the VBA event.

Example:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.Section(acGroupLevel1Header).ForceNewPage = 1
End Sub

The first and last lines will be automatically created for you if you use
the method mentioned above to get to the editor.

 

[Elsie]

ok, I took out the page break at SMN header but leave the header in and
tried 'Me.Section(acGroupLevel1Header).ForceNewPage = 1' under the on format
event for detail section, but got error message that 'section number you
entered is invalid'. so I tried Me.Section(acGroupLevel2Header).ForceNewPage
= 1 and was back to square one - where the different SMN are on the same
page.

No, it is a VBA command. Instead of opening the Builder, set the Detail
Section's Format event to [Event Procedure], then click the ... Insert the
code in the VBA event.

Example:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.Section(acGroupLevel1Header).ForceNewPage = 1
End Sub

The first and last lines will be automatically created for you if you use
the method mentioned above to get to the editor.

--
Wayne Morgan
MS Access MVP

hi, I am not sure where is the place to change....
anyway, I tried adding 'Me.Section(acGroupLevel1Header).ForceNewPage = 1',
but got a message that say 'The expression On Format you entered as the
event property setting produced the following error: The object doesn't
contain the Automation object 'Me.'.

so I tried to use the expression builder and got
Section(9).ForceNewPage=1... but I got all data printing continuously
....instead of a break at each customer....

way

i

 

[Elsie]

hi, I tried 'Me.Section(9).ForceNewPage = 1', which seems to work fine,
giving exactly what I wanted. but I don't understand the meaning of
Me.Section(9).ForceNewPage = 1 or
Me.Section(acGroupLevel1Header).ForceNewPage = 1

thanks very much for your help, Wayne

ok, I took out the page break at SMN header but leave the header in and
tried 'Me.Section(acGroupLevel1Header).ForceNewPage = 1' under the on format
event for detail section, but got error message that 'section number you
entered is invalid'. so I tried Me.Section(acGroupLevel2Header).ForceNewPage
= 1 and was back to square one - where the different SMN are on the same
page.

No, it is a VBA command. Instead of opening the Builder, set the Detail
Section's Format event to [Event Procedure], then click the ... Insert the
code in the VBA event.

Example:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.Section(acGroupLevel1Header).ForceNewPage = 1
End Sub

The first and last lines will be automatically created for you if you use
the method mentioned above to get to the editor.

--
Wayne Morgan
MS Access MVP

hi, I am not sure where is the place to change....
anyway, I tried adding 'Me.Section(acGroupLevel1Header).ForceNewPage = 1',
but got a message that say 'The expression On Format you entered as the
event property setting produced the following error: The object doesn't
contain the Automation object 'Me.'.

so I tried to use the expression builder and got
Section(9).ForceNewPage=1... but I got all data printing continuously
....instead of a break at each customer....





What happens if you turn the page break of, but in the Format event

of
the

detail section add,

Me.Section(acGroupLevel1Header).ForceNewPage = 1

to turn it on. This way you get through it once before it starts.

--
Wayne Morgan
MS Access MVP


ya... if i put a page break at SMN header, the first page will be those
textboxes with no other data....but the rest of the pages are the

way

i
wanted them to be... just the first page...
perhaps i should just get the user not to print the first page?


message
The attachment has a picture, but no explanation. The only thing

I
see

is
a
( in the wrong spot in a couple of the textboxes and a little of the
text
being cut off. Is this the problem?

--
Wayne Morgan
MS Access MVP


sorry, accidentally put a force new page at SDC header....took it
away....
now just 1 small problem ... pls refer to attachment...

 

[Wayne Morgan]

Well, first things first, it is working, that's good. acGroupLevel1Header is
a "built-in" constant in Access, its value is 5. By using 9, you have placed
the page bread in a different location. The sections are actually numbered,
its just that the built-in constants are easier to remember because of their
descriptive names. If you open the report in design mode then go to the
Immediate window (Ctrl+G) and type

?Reports!ReportName.Section(9).Name

and press Enter, it will tell you which section you placed this in.

--
Wayne Morgan
Microsoft Access MVP

hi, I tried 'Me.Section(9).ForceNewPage = 1', which seems to work fine,
giving exactly what I wanted. but I don't understand the meaning of
Me.Section(9).ForceNewPage = 1 or
Me.Section(acGroupLevel1Header).ForceNewPage = 1

thanks very much for your help, Wayne

ok, I took out the page break at SMN header but leave the header in and
tried 'Me.Section(acGroupLevel1Header).ForceNewPage = 1' under the on format
event for detail section, but got error message that 'section number you
entered is invalid'. so I tried Me.Section(acGroupLevel2Header).ForceNewPage
= 1 and was back to square one - where the different SMN are on the same
page.

No, it is a VBA command. Instead of opening the Builder, set the Detail
Section's Format event to [Event Procedure], then click the ... Insert the
code in the VBA event.

Example:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.Section(acGroupLevel1Header).ForceNewPage = 1
End Sub

The first and last lines will be automatically created for you if you use
the method mentioned above to get to the editor.

--
Wayne Morgan
MS Access MVP


hi, I am not sure where is the place to change....
anyway, I tried adding 'Me.Section(acGroupLevel1Header).ForceNewPage
= 1',
but got a message that say 'The expression On Format you entered as the
event property setting produced the following error: The object doesn't
contain the Automation object 'Me.'.

so I tried to use the expression builder and got
Section(9).ForceNewPage=1... but I got all data printing continuously
....instead of a break at each customer....





message
What happens if you turn the page break of, but in the Format event of
the
detail section add,

Me.Section(acGroupLevel1Header).ForceNewPage = 1

to turn it on. This way you get through it once before it starts.

--
Wayne Morgan
MS Access MVP


ya... if i put a page break at SMN header, the first page will be
those
textboxes with no other data....but the rest of the pages are the way
i
wanted them to be... just the first page...
perhaps i should just get the user not to print the first page?


message
The attachment has a picture, but no explanation. The only
thing I
see
is
a
( in the wrong spot in a couple of the textboxes and a little
of the
text
being cut off. Is this the problem?

--
Wayne Morgan
MS Access MVP


sorry, accidentally put a force new page at SDC
header....took it
away....
now just 1 small problem ... pls refer to attachment...

 

[Elsie]

tried ?Reports!R_Sales_Report_M_No_Parts.Section(9).Name, it gave
GroupHeader1, while
?Reports!R_Sales_Report_M_No_Parts.Section(5).Name gives an error,
?Reports!R_Sales_Report_M_No_Parts.Section(7).Name gives GroupHeader0.

does this mean that GroupLevel1Header is not = GroupHeader1?

Well, first things first, it is working, that's good. acGroupLevel1Header is
a "built-in" constant in Access, its value is 5. By using 9, you have placed
the page bread in a different location. The sections are actually numbered,
its just that the built-in constants are easier to remember because of their
descriptive names. If you open the report in design mode then go to the
Immediate window (Ctrl+G) and type

?Reports!ReportName.Section(9).Name

and press Enter, it will tell you which section you placed this in.

--
Wayne Morgan
Microsoft Access MVP

hi, I tried 'Me.Section(9).ForceNewPage = 1', which seems to work fine,
giving exactly what I wanted. but I don't understand the meaning of
Me.Section(9).ForceNewPage = 1 or
Me.Section(acGroupLevel1Header).ForceNewPage = 1

thanks very much for your help, Wayne

ok, I took out the page break at SMN header but leave the header in and
tried 'Me.Section(acGroupLevel1Header).ForceNewPage = 1' under the on format
event for detail section, but got error message that 'section number you
entered is invalid'. so I tried Me.Section(acGroupLevel2Header).ForceNewPage
= 1 and was back to square one - where the different SMN are on the same
page.



No, it is a VBA command. Instead of opening the Builder, set the Detail
Section's Format event to [Event Procedure], then click the ...

Insert
the

code in the VBA event.

Example:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.Section(acGroupLevel1Header).ForceNewPage = 1
End Sub

The first and last lines will be automatically created for you if you use
the method mentioned above to get to the editor.

--
Wayne Morgan
MS Access MVP


hi, I am not sure where is the place to change....
anyway, I tried adding 'Me.Section(acGroupLevel1Header).ForceNewPage
=
1',
but got a message that say 'The expression On Format you entered as the
event property setting produced the following error: The object doesn't
contain the Automation object 'Me.'.

so I tried to use the expression builder and got
Section(9).ForceNewPage=1... but I got all data printing continuously
....instead of a break at each customer....





message
What happens if you turn the page break of, but in the Format

event
of

the
detail section add,

Me.Section(acGroupLevel1Header).ForceNewPage = 1

to turn it on. This way you get through it once before it starts.

--
Wayne Morgan
MS Access MVP


ya... if i put a page break at SMN header, the first page will be
those
textboxes with no other data....but the rest of the pages are the
way
i
wanted them to be... just the first page...
perhaps i should just get the user not to print the first page?


"Wayne Morgan" <[email protected]>

wrote
in

message
The attachment has a picture, but no explanation. The only
thing I
see
is
a
( in the wrong spot in a couple of the textboxes and a little
of
the
text
being cut off. Is this the problem?

--
Wayne Morgan
MS Access MVP


sorry, accidentally put a force new page at SDC
header....took
it
away....
now just 1 small problem ... pls refer to attachment...

 

[Wayne Morgan]

Yes, it does. With all of the modifications that have been made, it may be
that something got out of synch. I don't know if once a number is assigned,
if you then delete another section, if the numbers will roll or if the
assigned number will stay put. It would be interesting to play with it and
see. If the numbers rolled, it would cause problems with code you've already
written, but if the don't, then it would make you think that a section isn't
the section it really is. The latter is probably the safer.