• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 273
  • Last Modified:

A report creation problem

Hi

I have been tearing my hair out trying to produce a complex report in MSAccess.

My problem is that I need to add a second page to an existing Timesheet report.  The existing report contains several headers and footers and may contain several pages.  To complicate matters there is a page break at the bottom of the Order Number footer, so that the Timesheet for each order is on a separate page. (I work for an employment agency, and several people work on one site.)

The new page contains terms and conditions of the contract.  We have a duplex capable printer, and the new page needs to be printed on the back of each Timesheet page.

How do I suppress the header and footer for the front of the page(s) when I print the reverse side(s)?, and how do I interleave the pages?

Any help gratefully received.

John
0
jdthedj
Asked:
jdthedj
  • 6
  • 5
  • 2
  • +1
1 Solution
 
peroveCommented:
Hm, interesting problem, if I understand you correctly, you want to print the contitions of the contract on each "back" page of the timesheet, even if the timesheet are on serveral pages.

So every second page should be terms of contract. Yes?

Is the terms of the contract a "standard text" or is this something you get from a table, where the text varies for each customer/contract?

Let us know.

perove



0
 
peroveCommented:
I find this an interesting problem so'ive been cheking around.

If the terms and conditions of the contract are a standard text, then you can do the following.

Create grouping level so that the "deepest" (exept for the detail section) are the unique record, then move all the fields from detail to this "next deepest" section. In that way you get a empty detail section.


IN the detail section add a text box (or a subreport)containing the text you want.And a pagebreak under the textbox.

In the detali section add this code,:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.Page Mod 2 = 1 Then 'pages 2,4,6,8...
Me.Detail.Visible = False
Cancel = True
Else
Me.Detail.Visible = True
Cancel = False
End If

End Sub

which will make the detail section pront on every second page (and only the detail section, not the rest.

Are I on the right track here?

perove
0
 
AngieBCommented:
Hi John,

Perove : I've also given this some thought and came up with a slightly different idea.

You have the main page of your report which I presume get's repeated depending on the number of contracts you're printing, underneath this information - don't insert a page break but position the terms so they start printing on the second page.  Then include the following

Private Sub Report_Page()
   
    If Me.Section(acDetail).WillContinue = True Then
        Me.Section(acPageHeader).Visible = False
    Else
        Me.Section(acPageHeader).Visible = True
    End If

End Sub


It works like a treat but you mustn't use page breaks or it isn't continued...

Rgds, Angela
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
jdthedjAuthor Commented:
Hi Perove and Angela

Thanks for replying.  Unfortunately I cannot test what you have suggested for a couple of days as it is the weekend here in New Zealand.

Angela: The Timesheet is basically a list of the names of the people who are working, grouped by company and Order No.  The front page is only as long as the list of names for that site, so I have no way of telling where the second page should begin, and that is why I have used a page break.

Perove: The Contract details is standard wording, so it does not have to come from a table.

Your answer seems as though it should work - I will check it out on Monday and let you know.

John
0
 
jdthedjAuthor Commented:
Perove, Angela

I have tried working with your ideas, but so far without success.

Perove:  I may be missing your point here, but when I tried your suggestion I found that I was missing every second Timesheet because it was replaced by the Terms and Conditions.

Angela:  As I mentioned in my previous comment, the page break needs to be there because not all job orders have the same number of employees.  If there was a way to put the Terms and Conditions page somewhere consistent it would work.  I just wish there was an event OnPageBreak!

Any more ideas?

John
0
 
AngieBCommented:
John,

Is the problem that the number of records may go beyond a single page?  because if not with my test I just made the body of the report greater than 30cm - it didn't take much fiddling to get it printing in the right place on page 2.

rgds, Angela
0
 
brewdogCommented:
"I just wish there was an event OnPageBreak! "

can you not use the PageFooter's format event for this?
0
 
jdthedjAuthor Commented:
Hi Angela & Brewdog - thanks for the replies

Angela: One Job Order can span up to 3 pages.

Brewdog: There is a page break in the Order number footer so that each new order starts on a new page.  The PageFooter format event would trigger after every page, not just for a new order.  If I print the back page when the order number changes using that event, how will I be able to print the following page with order details (without missing a page)?  My experiments have not worked.

John
0
 
AngieBCommented:
Hi John,

Another idea for you...

You gave me the clue in the page break on order number footer - here's what I did.

Insert the Terms and Conditions after the page break on the order number footer, then put in another page break after the Terms and Conditions.

Then do something similar to this....
Private Sub Report_Page()
   
    Select Case Me.Page
        Case 2, 4, 6, 8, 10
            Me.Section(acPageHeader).Visible = True
            Me.Section(acPageFooter).Visible = True
        Case 1, 3, 5, 7, 9
            Me.Section(acPageHeader).Visible = False
            Me.Section(acPageFooter).Visible = False
    End Select

End Sub

This will print the Order on odd pages with Header/Footer & terms on even pages without Header/Footer.

Let me know if it's even close to what you want because this is now driving me nuts too :)

Rgds, Angela
0
 
jdthedjAuthor Commented:
Hi Angela

We are getting very close to the answer.

The only problem after trying your latest suggestion is that I now get one blank page (apart from header/footer) between the order details and the Terms and Conditions page.  I have experimented with/without the second page break, but I get the same result.

Thanks for your continued patience.  

John
0
 
AngieBCommented:
I'm sure that's going to be to do with the page length somewhere...

If you'd like to send me a copy of the database I'll have a play with it too, see if we can't crack this problem.

Rgds, Angela
angela.a.berkowitz@aexp.com
0
 
jdthedjAuthor Commented:
Hi again Angela

I have sent you the MDB - thanks! (Pity I can't drop it on here)

John
0
 
jdthedjAuthor Commented:
Thanks Angela!

WOW!! I am very impressed.  It works very well.  You certainly deserve the points.

John
0
 
AngieBCommented:
John: Thanks for some fun formatting <s>, it was certainly an interesting problem...

For anyone following this thread, we had to break down the number of records in a group to print no more than ten per page, then print each page individually.

If anyone is interested I have a copy of the code.

Rgds, Angela
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 6
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now