Report Sorting, Grouping & Page Break Issue

[waltb]

I have a report that lists PRODUCTID (which are assigned starting with 1 until the end of the year) and associated PROJECTID (changes throughout the year, in no specific order). I need the report to sort the PRODUCTID in numerical order, but I've grouped the PROJECTID so that only the first occurrence appears rather than continual repeats. Each new PROJECTID invokes a page break.



I'm attempting to get around the sorting feature of using the GROUP BY function, but I want that visual appearance in my report.

The intended output should look something like this:

ProductID ProjectID
1 B
2
<page break>
3 A
4

What I end up with is this:

ProductID ProjectID
3 A
4
<page break>
1 B
2

Possibly sort by PRODUCTID, then coding to show only the first occurence of PROJECID, and page break when that value changes?

Thanks!

[June7]

Need a larger data sample. Could data be like:

ProductID ProjectID
1 A
2 D
3 A
4 A
5 B
6 C

How many project ID's?

I suspect any solution will involve VBA coding.

[waltb]

In general, any given ProjectID could generate anywhere upwards in the tens of ProductID's. The number of Projects within a given year is variable, though generally 20-40 projects. And yes, once a set of products is produced for a given project, that project may require the production of additional products later in the year, though I'm not interested in combining all products within a given project for purposes of this report. The projects generally come to us in numerical order throughout the year, but of course I have an instance where they are not. The best I can think of is trying to come up with code to show the first occurrence of the Project ID and hide the replicate ProjectID until the value of ProjectID changes.

If there is a way to force a page break (VBA code in the detail format section?) when the ProjectID changes, that would suffice as well, as the report would still sort by ProductID and produce the desired result.

[June7]

Your example appears to already do this page break. Are you using the ForceNewPage property of the ProjectID group section?

[waltb]

Yes, by selecting "Keep whole group together on one page" in the Group options, which is the desired behavior. I had thought of using an OnChange event to trigger a page break (ProjectID grouping would be disabled) but it appears that function is only available for forms? Something along the lines of "PgBreak = ProjectID.OnChange"

[waltb]

I was attempting to follow these Microsoft instructions for inserting a conditional page break in a report:
http://office.microsoft.com/en-ca/ac...005187336.aspx

I placed "PageBreak51" code in the page header as described. Then, using the following code as my condition in the detail format section of my report where the ProjectID field resides:

If Me![ProjectID].OnChange = True Then
Me![PageBreak51].Visible = True
End If

Thinking that as the ProjectID value changed, this would force a page break. However, it does nothing.

[June7]

I was suggesting you try the ForceNewPage property of the Group section and set it to AfterSection. You will find this on the Format tab of the PropertySheet when a section other than Page Header/Footer is selected.

[waltb]

If I create a PROJECTID group, I can set the ForcePageBreak in the group header section. Unfortunately, by creating the Grouping this causes a sort by PRODUCTID which is not desired.

I found a workaround. Since the PRODUCTID is always entered sequentially irregardless of the order of the PROJECTID, I've added a hidden field in the details section which is the key (and sequential) ID for the PROJECTID table. Now I can Group by this ID field, and introduce a Page Break, all of which causes the desired behavior, as follows:

ProductID ProjectID ID
1__________A_____1
2__________A_____1
<page break>
3__________C_____2
4__________C_____2
<page break>
5__________B_____3
6__________B_____3

However, it will require that the data truly be entered in sequential manner, by the PRODUCTID. And with two or more folks potentially entering various Project information it's very possible (but highly unlikely) that someone will forget to enter their information in a timely manner. If you can think of better VBA coding to check for a change in PROJECTID to trigger a page break, let me know, otherwise this is closed. Thanks.