CODEWITHSUNDEEP
ms-accessvba
SP6
SP6

Reputation: 119

Formatting of Dynamic Reports in MS-Access

I have a question regarding MS-Access. I am creating a report based on inputs from the user. So a new report is created every time the query is run (because the input might change). I want to format my report such that I can easily export it out without editing a whole lot.

The things I want to achieve are (among other things)

  1. Resize the report to A3/A4, Horizontal/Vertical
  2. Rename the Auto_Title
  3. Margins/Borders
  4. Column Width Auto-size.

I did find the following link But I don't have access to the report prior to it being created. So I am at a loss. I creating the report using the acCmdNewObjectAutoReport function. Is there a different approach I should take?

Right now my solution is to format individual reports after they are created, which is cumbersome (there are over 20 possible combinations..so 20 possible reports to be generated).

Upvotes: 0

Views: 1226

Answers (1)

Parfait
Parfait

Reputation: 107567

Look into the VBA Printer Object. You will need to dynamically design the report on the fly after user input to adjust parameters. Add conditional if/then or select/case for adjustment.

Below opens report in design view in hidden mode:

DoCmd.OpenReport "reportName", acViewDesign, , , acHidden

With Reports("reportName").Printer

   .TopMargin = 1440       '1 inch = 1 * 1440
   .BottomMargin = 1440 
   .LeftMargin = 1440 
   .RightMargin = 1440

   .ColumnSpacing = 360

   .Orientation = acPRORLandscape    'or acPRORPortrait
   .PaperSize = acPRPSA4             'or acPRPSA3

End With

'Adjust to AutoTitle's control name
Reports("reportName").LabelName.Caption = "New Title"    

'Saves new design
DoCmd.Close acReport, "reportName", acSaveYes

'Prints report
DoCmd.OpenReport "reportName", acViewNormal

Upvotes: 1

Related Questions