Forcing page break on subreport after 2 records

[Indigo]

I have been working on this for over a week in Access 2010 and I know that I am almost there, but need some help getting over this last hurdle as I have a severe case of tunnel vision on this while I am trying to get it to work.

I have a Main Report based on my Element table and a subreport based on my OTC table. There is a one-to-many relationship between the Element and the OTC table.

My Master / Child Fields between the two reports are as follows:

ElementID;GroupNum

My SQL for the Main report is:

SELECT Val(DCount("*","Element","ElementID=" & [ElementID] & " AND Step <" & [Step]))\2 AS GroupNum, Element.ElementID, Element.ProcessID, Element.ModelID, Element.Step, Element.ElementDescription, Element.RevDate, Element.GPCFS, Element.TrainingGate
FROM Element
GROUP BY Val(DCount("*","Element","ElementID=" & [ElementID] & " AND Step <" & [Step]))\2, Element.ElementID, Element.ProcessID, Element.ModelID, Element.Step, Element.ElementDescription, Element.RevDate, Element.GPCFS, Element.TrainingGate
ORDER BY Element.ElementID;

My SQL for the subreport is:

SELECT Val(DCount("*","OTC","ElementID=" & [ElementID] & " AND OTCID <" & [OTCID]))\2 AS GroupNum, OTC.*
FROM OTC
ORDER BY OTC.ElementID;

As an example in testing this report, I am looking at a specific Element with 33 records and a total of 72 OTC records. Each of the 33 records can have 1 or 2 or 3 or up to 4 OTC records but the report is only 17 pages which tells me that the page break is occuring based on the ElementID and not on the OTC records.... Sorry - severe case of tunnel vision as I can't see where I went wrong. Would appreciate another pair of eyes to look at my SQL and point me in the right direction. Thank you.

 

[Indigo]

Just wanted to share as I resolved this issue. I took out the SQL statements I used above and simply sourced the main report to the Element table and the subreport to the OTC table. Then I took all the controls out of the main report page header and moved them into a new header I created for the Element ID. I set this to Repeat Section = Yes as well as Force New Page = Before Section.


I then went through the Subreport Wizard and created a brand new subreport. I set the columns to 2 and half the width of the main report.


I did not need to force a new page in the VBE for the subreport and my report is the 42 pages I needed it to be.