Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How the Access report preview be refreshed after programmatically changing the page setup options?

Posted on 2009-04-14
5
Medium Priority
?
847 Views
Last Modified: 2013-11-28
Hello Experts,

I have a sub routine that I can call on a report's open event to change the default report paper size and margins based on a user's preference stored in a table.  This works fine and changes the report's defaults.  However, it does not update the report's preview after modifying the report's page settings.  The result is that the desired page setup changes are made and correct, but may look wrong to the user because the report may have opened with different initial page settings.  After the report opens, I can confirm that changes have taken place by opening the page setup dialog box and checking that the margins and page selection have been modified.  Once the page setup dialog is open, selecting OK refreshes the print preview and the report then looks correct.  What I need is a way to programmatically simulate clicking the OK button after resetting the page setup options with VBA to get the report to appear correct in print preview.

Any suggestions or is there a better way to do this that will apply the desired page setup changes and then correctly show them in print preview?

Cheers,
Ben
Public Sub SetMarginsToDefault()
'Uses a global string "strOpenreport" set to the name of the currently open report
'Allows use of this sub from any open report
'Set margin defaults for Letter or A4 size paper depending on region
' First, determine what size is the regional default
' looks up and assigns regional default based on user's looked up region, allows for additional defualt types
Dim setRegionalMargin
'setRegionalMargin = "letter" 'comment in/out for testing
'setRegionalMargin = "A4" 'comment in/out for testing
setRegionalMargin = DLookup("PaperSize", "tblRegion", "Region = " & "'" & varRegion & "'")
    'specifies the regional paper size and margin format in twips
     Select Case setRegionalMargin
            Case "A4"
                With Reports(strOpenreport).Printer
                    .PaperSize = acPRPSA4
                    .LeftMargin = 0.65 * 1440
                    .TopMargin = 0.5 * 1440
                    .RightMargin = 0.25 * 1440
                    .BottomMargin = 0.125 * 1440
                End With
             Case "Letter"
                With Reports(strOpenreport).Printer
                    .PaperSize = acPRPSLetter
                    .LeftMargin = 0.3 * 1440
                    .TopMargin = 0.625 * 1440
                    .RightMargin = 0.25 * 1440
                    .BottomMargin = 0.25 * 1440
                End With
     End Select
    End Sub

Open in new window

0
Comment
Question by:benpope
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24142240
When are you running this code?

One option may be to simply close the report, then open it in Print Preview again.

    DoCmd.Close acReport, "YourReport", acSaveYes
    Docmd.OpenReport ""YourReport", acViewPreview

JeffCoachman
0
 

Accepted Solution

by:
benpope earned 0 total points
ID: 24151889
Hi Jeff,
Thanks for the suggestion.  I'm running the code to reset the margins on the open event of the report.  I also added some code to first test if the page setup was already formatted correctly and if not, then run the code.  I thought that closing and re-opening the report might work and conducted some tests and variations on the code.  Simply closing and re-opening does not change the print preview appearance.  The only way I can get the print preview to appear correctly after changing the page setup options programmatically is to open the page setup dialog and click OK. Then, the preview screen refreshes and it looks good.  At this point I think my answer may be to put in a pop-up message box that instructs the user to open page setup dialog and click OK the first time the page options are changed.  This only needs to be done once and then the options stick unless the user manually changes it.

BTW.  This is a for an application that is used through several global regions.  It is able to determine which region it is operating in and whether the regions uses A4 or letter paper. The idea is to set the printing for reports based on the user's regional requirement.

Thanks, and please let me know if you have any idea other than a message box that might resolve this.
Ben
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24154159
Can you post a sample of this database that displays this issue?
0
 
LVL 6

Expert Comment

by:BALMUKUND KESHAV
ID: 24154676
In your above code, you have not refreshed the report after setting the paper size, try to refresh the report after setting the paper size.

thanks,

Bm Keshav
0
 

Author Comment

by:benpope
ID: 24156351
Hi BM Keshav,

Refreshing the report preview after re-setting the page setup optins does seem like the logical programming step.  However, there does not seem to be a VBA action for a report that I know of that can do this.  Refresh works on the data underlying the report and repaint only affects forms.  There did not seem to be a VBA command tool that would the cause the report preview to repaint after a page setup change.  I was hoping that someone on this forum might be able to tell me how to do this.

For Boaq2000,
It wouldn't be practical to post the actual database that generates these reoports.  It's rather large at about 60 meg and rather intricate.  It might be possible to split off a small sample segment that demstrates this problem, but I would have to develop that.

Cheers,
Ben
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question