page setup changes upon refreshing of table links 1

[THWatson]

Using Access 2000

I have a form in which the user can select names and then print those names and accompanying addresses on #10 envelopes.

All of this works fine...unless the links to BackEnd tables have to be refreshed. When that happens, the report's Page Setup changes from "#10 envelope" to "Letter" size paper and has to be reset manually to #10 envelope.

Is there a way to keep that from happening?

Thanks.

Tom

 

[Remou]

Could it be to do with:

http://support.microsoft.com/?id=240826

 

[THWatson]

Remou
Unfortunately, Name AutoCorrect isn't the issue. I haven't used Name AutoCorrect for a long time.

Tom

 

[THWatson]

When this is occurring is when I update something in the FrontEnd on another user's computer...and their printers are different from mine.

Therefore I am wondering if it happens because the page setup reverts to the default for the particular computer.

If that's the case, is there a work-around, so that the page doesn't have to be reset every time an update is made?

Tom

 

[Knicks]

Have you tried something like this. Use the On Open Event of the report to progmatically set desired page setup

Me.Printer.PaperSize = acPRPSLetter
Me.Printer.BottomMargin = 0.5 * 1440
Me.Printer.TopMargin = 0.5 * 1440
Me.Printer.LeftMargin = 0.5 * 1440
Me.Printer.RightMargin = 0.5 * 1440
Me.Printer.Orientation = acPRORPortrait

 

[THWatson]

Knicks
Are the settings you supplied supposed to work with Access 2000?

VBA seems not to recognize a Me.Printer property.

Tom

 

[Knicks]

I am not sure, I have Access 2002. Does it fail when you attempt to use the code?

 

[THWatson]

Yep, it fails. That's what makes me think that the ability to change some printer settings came with Access 2002.

Thanks for trying.

Tom

 

[Knicks]

Sorry, its a super cool feature - I use it to allow users access to printers and page setup without the worry of them messing up the settings for other users.

I am trying to think if there is some other work around along the same lines? I think I've seen some horribly complex code (never my favorite as I like to understand what I'm doing!).

Have you searched the archives here?

 

[THWatson]

Knicks
I searched the archives before I posted...but didn't find what I was looking for.

I'll do some Internet searching.

If you come up with another work-around, I'd appreciate it.

Thanks again.

Tom

 

[Knicks]

Tom - found this code on a web site, might work?

You can set the margins for a report via code prior to previewing or printing a report. The code below should be placed in a general db module and be called first then call the DoCmd.OpenReport Method. Note that this code works in Access 95 and above. Even though in Access 2002 (Xp) you can access the margin properties directly in design mode rather than using the PrtMip property, this code will also work on that version of Access for compatibility.
'-------------------Module Declarations----------------

Type PRT_MIP_STRING
RGB As String * 28
End Type

Type PRT_MIP
'Typed as longs due to Ansi to Unicode conversion
xLeftMargin As Long
yTopMargin As Long
xRightMargin As Long
yBottomMargin As Long
fDataOnly As Long
xItemSizeWidth As Long
yItemSizeHeight As Long
fDefaultSize As Long
xItemsAcross As Long
yColumnSpacing As Long
xRowSpacing As Long
rItemLayout As Long
rFastPrinting As Long
rDataSheetHeadings As Long
End Type
'------------------------Function-----------------------------
Public Function SetReportMarginDefault(strReportName As String, left!, top!, right!, bottom!)
Dim PrtMipString As PRT_MIP_STRING
Dim PM As PRT_MIP
Dim objRpt As Report
Dim tempPrtMip As String

DoCmd.Echo False
DoCmd.OpenReport strReportName, acDesign
Reports(strReportName).Painting = False
Set objRpt = Reports(strReportName)

PrtMipString.RGB = objRpt.prtmip

LSet PM = PrtMipString
'Use 1440 for US (inches), 567 (rest of the world) (centimeters)
PM.xLeftMargin = left * 1440
PM.yTopMargin = top * 1440
PM.xRightMargin = right * 1440
PM.yBottomMargin = bottom * 1440

LSet PrtMipString = PM

objRpt.prtmip = PrtMipString.RGB

'Make sure report has the focus
DoCmd.SelectObject acReport, strReportName
'Save the Report
DoCmd.DoMenuItem 7, acFile, 4, , acMenuVer70

CloseRpt:
DoCmd.Close acReport, strReportName
DoCmd.Echo True
'You're done.

End Function

 

[THWatson]

Knicks
Thanks. I'll give it a whirl.

Tom

 

[THWatson]

Knicks
Well, this may...or may not work. Don't know for sure.

What is happening is that when I try to run the code from a form, and call the module and then Preview the report, it opens the report quickly and then closes it quickly. If I try to comment out the three lines beginning with CloseRpt: then the report gets partially opened in Design view but then I have to shut Access down to get out of it.

I am going to have to work with this some more.

Tom

 

[THWatson]

Knicks
I got it working...partially.

The problem is that there still has to be some way to set the paper size. I suspect that's in a PrtDevMode function. I'll look further.

Tom

 

[THWatson]

Knicks
I found some other code that works. Here 'tis...

Type str_DEVMODE
   RGB As String * 94
End Type

Type type_DEVMODE
   strDeviceName As String * 16
   intSpecVersion As Integer
   intDriverVersion As Integer
   intSize As Integer
   intDriverExtra As Integer
   lngFields As Long
   intOrientation As Integer
   intPaperSize As Integer
   intPaperLength As Integer
   intPaperWidth As Integer
   intScale As Integer
   intCopies As Integer
   intDefaultSource As Integer
   intPrintQuality As Integer
   intColor As Integer
   intDuplex As Integer
   intResolution As Integer
   intTTOption As Integer
   intCollate As Integer
   strFormName As String * 16
   lngPad As Long
   lngBits As Long
   lngPW As Long
   lngPH As Long
   lngDFI As Long
   lngDFr As Long
End Type

Public Function SetEnvelope(strName As String)
   Dim rpt As Report
   Dim strDevModeExtra As String
   Dim DevString As str_DEVMODE
   Dim DM As type_DEVMODE

DoCmd.OpenReport strName, acDesign 'Opens report in Design view.

Set rpt = Reports(strName)

If Not IsNull(rpt.PrtDevMode) Then
   strDevModeExtra = rpt.PrtDevMode
   DevString.RGB = strDevModeExtra
   LSet DM = DevString
   DM.lngFields = DM.lngFields Or DM.intOrientation 'Initialize fields.
   DM.intPaperSize = 20 '#10 Envelope size
   DM.intOrientation = 2 'Landscape
   LSet DevString = DM 'Update property.
   Mid(strDevModeExtra, 1, 94) = DevString.RGB
   rpt.PrtDevMode = strDevModeExtra
   DoCmd.Save acReport, strName
   DoCmd.Close acReport, strName
End If

End Function

I have to call the function upon opening of the form which drives the report.

Note the line DM.intPaperSize = 20. This was not the original line, but I was fortunate to find, in an Access 2002 book by Getz, Litwin & Baron, called "Access Cookbook" the values for various page sizes.

Seems like a ton of code to do the job, but it seems to do the job.

Thanks for your efforts. I will plug in a start for you.

Tom