[RESOLVED] Report Designer ForcePageBreak

[DebbieInFlorida]

Hi, I am having a problem forcing a new page. I need to break whenever a certain field changes. I have tried the break before / break after. But nothing happens. Hopefully someone can help, I am desperate to deliver this!!!! kinda in a bind.. THanks

With rptDemoGraphics_Area.Sections("Details").Controls
 
While Not (RS.EOF)
   If SaveRadius <> RS(FIELD_RADIUS) Then
       rptDemoGraphics_Loc.Sections("Title").ForcePageBreak = rptPageBreakBefore
       SaveRadius = RS(FIELD_RADIUS)
   End If
      
      If RS(FIELD_RADIUS) = 0 Then
         strRptType = "TRADE AREA"
      Else
         strRptType = "RING RADIUS - " & RS(FIELD_RADIUS) & " Mile"
      End If
     '---  create heading and new page for each report type change
     With rptDemoGraphics_Area.Sections("Title").Controls
        For i = 1 To .Count
            If .Item(i).Name Like "lblArea" Then
               .Item(i).Caption = strRptAreaType & ":"
            ElseIf .Item(i).Name Like "lblAreaArea" Then
               .Item(i).Caption = strRptArea
            ElseIf .Item(i).Name Like "lblReportType" Then
               .Item(i).Caption = strRptType
            ElseIf .Item(i).Name Like "lblReportYr" Then
               .Item(i).Caption = strRptYear
            End If
        Next
    End With
  
    For i = 1 To .Count
        If .Item(i).Name Like "txtSTNO" Then
           .Item(i).DataField = RS(FIELD_STNO).Name
        ElseIf .Item(i).Name Like "txtAddress" Then
           .Item(i).DataField = RS(FIELD_INTERSECTION).Name
        ElseIf .Item(i).Name Like "txtCity" Then
           .Item(i).DataField = RS(FIELD_CITY).Name
        ElseIf .Item(i).Name Like "txtSt" Then
           .Item(i).DataField = RS(FIELD_STATE).Name
        End If
    Next
  RS.MoveNext
Wend
 
End With

[Hack]

Moved to reporting section

[brucevde]

The DataReport has no processing capabilities on a per record basis.

Looping through the recordset and setting the control properties based on the current record just overwrites the previous setting.

You need to add a Group Header/Footer and then bind the report to a Hierarchical Recordset. Then set the ForcePageBreak property of the Group Header or Group Footer (once in design mode).

[DebbieInFlorida]

thanks Bruce for your fast reply. I just don't seem to understand how the report designer knows how to skip page by record. I want them to jump to new page on field change, ie. "Dept", so how does it know??? I do have a footer, that I fill in with accumulated totals, they are .caption fields not bound. Please give me a little more info or an example on this. THanks so much.

[brucevde]

I just don't seem to understand how the report designer knows how to skip page by record.

That's the problem, you cannot start a new page when the Dept changes, unless you have a hierarchical recordset.

You may have a Page Footer and/or Report Footer section but do you have a Group Footer section. They are not the same.

The DataReport does not have an "Unbound Mode" (despite what the controls indicate in design mode). You cannot, using code, set the values of controls in sections which will repeat (such as the Details and Page Footer sections).

Before I can offer more help you will need to post the DataReport form, the SQL Statement and the code that opens the report. But see the sample application.

[DebbieInFlorida]

BRuce,
I really appreciate your help, and don't want to take up too much of your time. I looked at your code, and I have never seen a report made like that, all the ones I have created had each field bound to the .datafield. I understand what you mean that I can't force a new page unless it is heirachical. I googled the SHAPE command, that was a new one too. But I am totally lost how your records get set on the report page. I have attached my report section, and if you are too busy I understand, I just need to understand how to link the data to the report. I really only need the report to page break on a field change, like Orders, in my case it is the Radius. Don't let all the wonky fields fool you, just the schema.
I didn't see anywhere on your report in the group header where it says to break, how does it know what field to break on??
THanks again. If this doesn't work, I will have to create the report in Excel, I guess.

[brucevde]

The report fields were linked to the recordset fields at design time. In my datareport click on a field and hit F4. The DataField property has been specified and in cases where the data is from a Child recordset within the hierarchy, the DataMember field has been specified.

I didn't see anywhere on your report in the group header where it says to break, how does it know what field to break on??

Again, it was set at design time. Click on the Group Header and hit F4 to bring up the properties window. I set the PageBreak option there.

[DebbieInFlorida]

You have been very helpful, I think I am close now. I understand the datafield and pagebreak in the design mode now. I am trying to mimic a small part of your data to create a small report, I just need to get the pagebreak to work once and I will be fine.
Just to clarify, is it the last line of "Relate Customer ID to Customer ID " that sets that field for page breaking.?
I did try to do that query in SQL and it didn't like the SHAPE field??? Could you please just help me once more... what would be the query to create a report of just Customer ID, Company Name, Orders, with the page breaking on Customer ID??
If I could mimic that, then I know I can keep adding and get whole thing to work.
I really appreciate this, it is due tomorrow!!!
Thanks

[brucevde]

The Shape command would be

SHAPE {SELECT CustomerID, CompanyName FROM Customers}
APPEND ({SELECT OrderID, OrderDate, CustomerID FROM Orders}
RELATE CustomerId To CustomerId) As Orders

Set the DataField property of a Group Header to CustomerId.

Note the difference in the connection string when using Data Shaping. You need to include a "data provider" argument. Some examples

provider=msdatashape;data provider=microsoft.jet.oledb.4.0;data source=...
provider=msdatashape;data provider=sqloledb;data source=...

[DebbieInFlorida]

Bruce,
You are a god!! I finally got the page break, I only used 2 fields, but that is a start, I can now move in baby steps to get the whole report done. I never would've noticed that "provider" statement, or figured it out..
THanks again.
D

[DebbieInFlorida]

Bruce,
OK, I thot I could do it, and I came close. I get the page break, and the right number of records, but the same data repeats in the details??? I know I have something wrong in the query, I am really confused on all the relates and "as" clauses. I hate to bother you again, I will mail you beer!!!!. if you could look at my sample.. I made it really simple, just 3 records, if I get the details out for that, then the rest is easy.. I got an extension over the weekend.
I really appreciate your help
Thanks

[brucevde]

The DataMember property indicates which Recordset within the Shape/Hierarchy the DataField is located.

If the DataMember property is blank, the DataField property refers to the Parent/Master recordset.

You named your Child/Detail recordset RadiusBreak, so put that in the DataMember property for the controls in the Detail section.

[DebbieInFlorida]

Bruce,
You have been very kind and I appreciate your help. If it helps any, I am a fellow Canadian from Ontario, here on contract.. ANyway,, I am understanding the DataMember thing in the report, but it still isn't quite right yet.. I have 3 tables like yours.. Location, Rings, Trade ARea. Need to page break when Radius changes (Which is in Rings and Trade ARea tables). THat works, BUT the details don't break, the line repeats. I need to assign Parent to this group, which is my problem. The following is what I have so far, but errors with the RadiusBreak??



SHAPE {SELECT radius FROM ns_ring}
APPEND

((SHAPE{SELECT Location_ID, population_cy FROM RadiusBreak}
APPEND
({SELECT NS_LOCATION.LOCATION_ID, NS_RING.RADIUS, NS_RING.POPULATION_CY
FROM NS_LOCATION INNER JOIN NS_RING ON NS_LOCATION.LOCATION_ID = NS_RING.LOCATION_ID where radius > 0}
as Details

Relate ns_location.location_ID to location_ID)) as RadiusBreak

Relate Radius to Radius)


YOu will save my butt, thanks so much

[DebbieInFlorida]

Me again, I think I have the query right now.. at least it doesn't blow up..

strQuery = "SHAPE {SELECT Radius FROM ns_ring}
Append ((SHAPE{SELECT Location_ID, Radius, Population_cy FROM ns_Ring Append({SELECT NS_LOCATION.LOCATION_ID as Location_ID, RADIUS, POPULATION_CY FROM NS_LOCATION INNER JOIN NS_RING ON NS_LOCATION.LOCATION_ID = NS_RING.LOCATION_ID} as Details
Relate Location_ID to Location_ID)) as PageBreak
Relate Radius to Radius)"

But I am getting errors - "Report sections do not match data source". I checked the online help and it didn't explain well, something about the Data Environment, which I am not using..
Also, when I check my returned data, I only have 2 records; Radius and PageBreak, is this correct??? is all the data grouped like that????

Really sorry to keep bothering you, it is very stressful here.
Thanks

[DebbieInFlorida]

Ok, I have now become an official stalker.. I shouldve attached the report. It is so small, just 3 records, I shouldn'be be having this much trouble..
THanks again

[brucevde]

Always, willing to help a fellow Canuck (even if they might be upper canadians)...

"Report sections do not match data source".

How many Groups do you have in your report? You must have two because your hierarchical recordset has two.

You might already be aware of this but I am not sure if you understand exactly what is a hierarchical recordset. Based on the last query you posted - the first select generates the Parent records. The first Append, adds a Field called PageBreak (which is actually another Recordset - Microsoft calls them Chapters) to the first recordset . The second Append adds the "Details Chapter" to this second recordset.

Radius, PageBreak
               |-> Location_ID, Radius, Population_cy, Details
                                                  |-> LOCATION_ID, RADIUS, POPULATION_CY

Based on the above,

The Report.Details section should contain information from the Details Chapter. A Report TextBox control in the Details section would have DataMember = Details, DataField = Location_Id. The value of any control in the "Details Section" whose DataMember is PageBreak will be repeated, once for each record in the "Details Chapter"

The DataMember property for controls in the second Group Header would be set to PageBreak. DataMember for controls in the first Group Header would be left blank.

However having said all that, it likes like you have two many Chapters/Groups in your query - you only seem to need 1.

SHAPE {SELECT Radius, Location_Id FROM ns_ring}
Append ({SELECT Location_ID, Population_cy FROM ns_Location 
            Relate Location_ID to Location_ID) as PageBreak

The above is no really no different than executing your 3rd Select statement the normal way.

SELECT NS_LOCATION.LOCATION_ID as Location_ID, RADIUS, POPULATION_CY 
FROM NS_LOCATION 
     INNER JOIN NS_RING ON NS_LOCATION.LOCATION_ID = NS_RING.LOCATION_ID

But both should return the same data, just in a different format.

I really hope this makes sense...

[DebbieInFlorida]

WOW! That was fantastic help. I am still a little fuzzy on all the relationships, BUT it was enuf for me to get the report to work. THank you so much..
Debbie

[DebbieInFlorida]

Bruce,
Have you ever used Oracle with Report Designer, is it the same connection command. I am using both that and Access ???
Thanks Debbie

[DebbieInFlorida]

Bruce,


One final question if you could, I would really appreciate it.. I finally got the report to work, after much hair pulling and work extensions. BUT I have now noticed, that some of the info repeats oddly..Basically there are 2 tables, location and ring. There could be from 1 - 5 rings for each location. I want the report to break on the Ring radius, then print all the locations for it. But I get a lot of repeating pages..

Location Table Ring Table
1 .25
1 .5
1 3
2 .25
2 .5
4 .5
4 3
4 5

instead of getting 3 pages with 2 - 3 lines each I get 8 pages, some are perfect and some repeat??

strQuery = "SHAPE {SELECT Location_ID FROM ns_location} " & _
"APPEND (( Shape{SELECT Radius, Location_id, 'Radius: ' & Radius as AreaField FROM NS_RING} " & _
" Append({Select Radius, R.POPULATION_CY, L.City as City, L.Intersection as Address, L.State_Abbv as State, " & _
" S.Store_nbr as Store_Nbr, Radius, R.Population_CY as Population_cy, " & _
" R.Population_PY as Population_py,
" FROM NS_LOCATION L, NS_RING R, NS_STORES S WHERE LOCATION_STATUS_ID = 1 AND " & _
" DISTRICT_NBR = 1 and L.STORE_NBR = S.STORE_NBR and R.LOCATION_ID = L.LOCATION_ID ORDER BY RADIUS} as Details " & _
" Relate Radius To Radius)) As PageBreak " & _
"Relate Location_ID To Location_Id)"