Changing Report Margins from VBA code - Access 2007

[Peter Fletcher]

I am writing an application which needs to print labels to printers on
computers to which I do not have physical access - they are on the other side
of the Atlantic. To add to the fun, my users are running the application
under the Access runtime, so they don't have access to the Page Setup menu
for Reports.

I have found from experience that it is not enough to set the report up to
use a specified label blank - some "tuning" of (particularly) the top and
bottom margin settings is needed to position the printed output squarely on
the labels on a given printer. I would like to give my users a form that
allows them to adjust the margins so that a test Report prints out correctly,
and then programmatically apply the settings to the real Reports. All I
thought I would have to do was to change the relevant Me.Printer.xxxMargin
settings on the basis of saved offset values in code running at the On Open
Event. However, as noted in an earlier post, to which no-one has yet
responded, the Open Event does not seem to fire at all if a Report is opened
in Normal mode by DoCmd.OpenReport. I therefore tried opening the Report in
preview mode, modifying the relevant settings, and saving it, before printing
it. Everything appears to work, the new settings can be seen in the Printer
Setup dialog of the open preview window, and printing from it before closing
the Report produces an appropriately modified printout. However, the changes
are lost when the Report is closed, even with acSaveYes specified. Even
opening the Report (from code) in Design View still does not allow the
changes to be saved. See code example below - txtVOffset and txtHOffset are
the relevant "offset" controls on the Form:

.....
DoCmd.OpenReport "labelsTest", acViewDesign

Set repLabels = Reports("LabelsTest")
With repLabels.Printer
.TopMargin = .TopMargin + (txtVOffset * 1440)
.BottomMargin = .BottomMargin - (txtVOffset * 1440)
.LeftMargin = .LeftMargin + (txtHOffset * 1440)
.RightMargin = .RightMargin - (txtHOffset * 1440)
End With

DoCmd.Close acReport, "labelsTest", acSaveYes

Why is this not working? If the problem results from a known bug, is there a
workaround?

 

[Mark Andrews]

Peter,

Send me an email. I have a friend who is working on a generic label
solution for me and he came up with some good code.
I also need it to work in the runtime. I'm 80% sure his code will work in
the runtime (but haven't tried it yet).

The basic idea is one table holds information for say 100 different label
types. One report and some code allow you to create any of those 100 labels
(you can do 700 labels if you have 700 records in the table etc...).

I can shoot you the code he wrote. See website for contact details. You
helped me out in the past so I owe you one.

Mark
RPT Software
http://www.rptsoftware.com

 

[docskidz]

I'm having the same problem with printer margins. You mentioned a friend had written some code. If that is available, could you please send it to me? I have some code that seemed to work well until I tried it on windows 8. I can't change the margins from that machine. I'm printing reports from various machines on a LAN. Each computer has their own front end (access) and there is one back end on one computer that serves as the server (actually a shared folder). All users are running runtime except for the main computer that has full Access 2010. Sure hope you can help me solve this dilemma. Thanks in advance.

 

[gbkleinr]

... However, the changes are lost when the Report is closed, even with acSaveYes specified ...

I figured out why Access is refusing to save your "changes." The problem is that Access thinks the report has not changed, so there is nothing to save -- apparently changing the margins is not recognized by Access as a design modification.

The solution: make some irrelevant design "modification," so Access will realize that the form has changed. Add this line right before your "DoCmd.Close" line:
Reports(repLabels.Name).Caption = Reports(repLabels.Name).Caption