I’m here to help

James contacted me as part of our Training PLUS service.  This allows people who have attended one of our courses to ask for help with any problems they encounter when trying to put the training into practice. [The Training PLUS service is entirely free!]

In James’s case, he was attempting to create a report in Microsoft Access. He wanted the elements he had put in the Page Footer area to appear only on the first page of the report, and this was proving difficult to achieve.

Hide the Entire Footer

My first stab at solving this problem was to find a method of hiding the entire footer area for every page –  except page one.

Here is a very simplified version of the kind of report James was trying to create:

It turns out that you only need one line of VBA code to achieve the desired effect – but of course it has to be put in the correct place:

  • Open the Design View of the report (as illustrated above)
  • Click on the grey bar which says Page Header
  • Display the Property Sheet and click the drop-down next to On Print
  • Choose [Event Procedure] then click the button at the right edge
  • Enter the following code into the PageHeaderSection_Print stub:

That simple, no fuss entry did the job.  The bottom of page one looked like this…

… the rest of the pages were blank in that area.

Felling quite pleased with myself, I sent the solution to James.

Hide a Single Footer Entry

A couple of days later, James got back to me.  He agreed that this code did indeed work, but explained that the effect wasn’t actually what he wanted !

Rather than hide the entire footer area, he wanted to show the date and page number on every page, and only hide the text entry – (in my example, the bit that says “Created by Base2 Training Limited“) – from page two onward.

Plan B was needed.  (As Ben Drew wasn’t available, I would have to have another stab at it)

The first part of this solution was “get rid of the code I gave you last time“.

Every item in an Access report has a Name property.  You use this when you want to refer to that object in your VBA code.  Here I’ve called the relevant label Thing_to_Hide:

 

This time the code has to be put in a slightly different place:

  • Open the Design View of the report, click on the grey bar which says Page Footer
  • Display the Property Sheet and click the drop-down next to On Print
  • Choose [Event Procedure] then click the button at the right edge
  • Enter the following code into the PageFooterSection_Print stub:

Now the first page looks the illustration from my first attempt, but the other pages look like this:

Job done!