Subscribe RSS
MS Access Reports and Dynamic Report Headers via VBA Oct 09

Part Two of this entry is here and includes the code from production that works daily for us.  (I only got half the solution with the below)

I have been spending the last few hours trying to figure out how I can take a single report, then update the header information (and the underlying query) as I have a number of reports that have the same layout and information, but just need to have a different heading, and query.

I could have just created a duplicate report with different heading and query, but as I’m still working out the layout of the report I don’t want to have to redo each report.

You think this would have been easy, but nope.  I spent hours looking over material from the internet and just couldn’t figure it out.  I tried global variables, but that didn’t work.  A function to update the titles was a way around it, but also not nice. There had to be a better way.

I ran into problems because you by default you cannot access a reports object unless is been opened.  Then you can access the controls on the report and update them, but you I couldn’t figure out how to refresh the print preview.

Then I stumbled onto something that works perfectly (well at least in the test model I did in 2003 and it will hopefull work in 2007).

What you need to do is open the report in designer.  The look at the report properties and goto the ‘Build Event’ option.  This will transfer the report into an class and let you edit in VBA various values of it.  You don’t actually have add any code!

More on the fold

Category: Uncategorized
You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

2 Responses

  1. 1
    Rob 

    I am trying to do something similar with Access, but I don’t understand your method. Can you be more specific? I don’t know what you mean when you say, “do the above of going into the report and turn it into a Class object, the your refer to it as ‘Report_'”.

    Thank you,

  2. Thanks! This has helped me a lot with creating some reports for a simple access app before we migrate to .Net… Still working on programatically adding labels/textboxes, but at least I’ve got a start!

Leave a Reply » Log in

Better Tag Cloud