title: Microsoft Access Report - Sub Report carries over 2 pages, but access thinks it is one?
body: <p>I've reworded the title and this description because the last one didn't make much sense.</p>
  
  <p>Basically, I have a report that displays business details. Some of these details include:</p>
  
  <ul>
  <li><strong>Business Name</strong></li>
  <li><strong>Address</strong></li>
  <li><strong>Documents</strong></li>
  <li><strong>Clients</strong></li>
  </ul>
  
  <p>When the report is formatted (Print View), the business name and it's corresponding page number are added to a table(<strong>BusinessPage</strong>). The code for this is placed in the 'On Format' event of the first group header, I have listed the code below:</p>
  
  <pre><code>Dim RST As DAO.Recordset
  
  Set RST = CurrentDb.OpenRecordset("BusinessPage", dbOpenTable)
  RST.AddNew
  RST![Business Name] = BUSName
  RST![Business Page] = Me.Page
  RST.Update
  RST.Close
  
  CurrentDb.Execute "SELECT DISTINCT * INTO t_temp FROM BusinessPage"
  CurrentDb.Execute "DELETE FROM BusinessPage"
  CurrentDb.Execute "INSERT INTO BusinessPage SELECT * FROM t_temp"
  CurrentDb.Execute "DROP TABLE t_temp"
  </code></pre>
  
  <p>The table will look something like this:</p>
  
  <pre><code>Business Name        Page No
  Business 1           3
  Business 2           4
  Business 3           6 'This indicates that Business 2 spans over two pages as page 5 is skipped'
  Business 4           7
  </code></pre>
  
  <p>In the report header, I have two things:</p>
  
  <ul>
  <li><strong>A cover page</strong></li>
  <li><strong>An Index Sub Report/Page</strong></li>
  </ul>
  
  <p>The index page (Sub Report) takes the information from the table(<strong>BusinessPage</strong>) and lists all of the businesses in the report, along with the page numbers that they start on.</p>
  
  <p>Now that you have an idea of how this works, here is my problem:
  The sub report works fine if it only takes up one page. The issue I have is when the sub report lists too many records and it carries over onto the second page. For some reason, access still thinks that the sub report is on one page, even though it's created another page. Despite it thinking that, the footer that contains the code ([Page]) displays correctly. However, the code that displays the total amount of pages ([Pages]) is wrong. At the end of the report, the footer will say something like this:
      Page 50 of 49
  <br></p>
  
  <p>The other issue I have is that the information that is added to the table is wrong when the sub report exceeds one page. The information added is in assumption that the sub report is still on one page.
  <br></p>
  
  <p>For example, when the sub report takes up one page:</p>
  
  <pre><code>Page 1: Cover Page
  Page 2: Sub Report/Index
  Page 3: Business 1
  </code></pre>
  
  <p>Sub report will correctly display:</p>
  
  <pre><code>Business 1                                    Page 3
  </code></pre>
  
  <p><br></p>
  
  <p>When the sub report takes up 2 pages:</p>
  
  <pre><code>Page 1: Cover Page
  Page 2: Sub Report/Index
  Page 3: Sub Report/Index
  Page 4: Business 1
  </code></pre>
  
  <p>The sub report/index page will still display:</p>
  
  <pre><code>Business 1                                    Page 3
  </code></pre>
  
  <p>Whereas it should be displaying it as:</p>
  
  <pre><code>Business 1                                    Page 4
  </code></pre>
  
  <p><br>
  <br></p>
  
  <p>Does anybody have any idea of how I can correct this? I have a feeling it may be because the sub report is in the report header (But I need it there because it will only ever be displayed once at the start of the report). Is there another way to create a secondary report header to test this theory?</p>
  
  <p>Let me know if anybody thinks of any other solutions!</p>
  
  <p><strong>Edit:</strong>
  I moved the sub report into it's own group/header, away from the Report Header.
  This didn't change anything, access still thinks it's displaying on one page even though it's not. I have another hunch that access thinks it's on one page due to the size of the sub report in design view? The sub report in design view will fit on one page before bringing in the data, could this be the issue? How would I go about fixing that? 
  <br></p>
  
  <p>I was thinking, maybe an if statement to decide how big the sub report turns out, if it's the length of 2 pages, then alter the code to become:</p>
  
  <pre><code>RST![Business page] = Me.Page + 1
  </code></pre>
  
  <p>This is still quite a shoddy fix though, and I'm not sure how I'd go about writing the if statement to calculate the length of the page</p>