Set page size with VBA

[TG_W]

Here's an interesting one.



I set a report page size to Ledger (11x17), but when I issued to users in the UK, the page size changed to A4. When I changed to A3 (via the user's shared desktop), I was getting an error that the page size was too big even though it wasn't, but I cannot replicate the error on my end when I change to A3.

So now, I am trying to figure out the following -
- What caused the change? (I am suspecting possible different versions for US/UK)
- If different versions, how can I, through VBA, change the page size?
- Will I need to make US/Int'l versions of the Access 2010 database moving forward? (I don't think I had this problem with the 2007 version I had to build since our lovely IT department hasn't gotten everyone on the same version yet)

Any help/insight is appreciated.

[June7]

Might have to open report in Design view to change page setup. Review http://en.allexperts.com/q/Using-MS-...Page-setup.htm

I use VBA to modify printer settings to direct output to specific paper tray and/or set duplexing. This requires opening the report in Preview before outputting to printer so I don't have to save report with altered setup properties.

[TG_W]

Sorry to take so long to reply to the thread, but I've been all over lately.

I have reorganized all of the reports to be ready for A3 printing rather than 11x17, and what I've found to help the page size thus far is the code below with an On Load event.

Dim p As Printer
Set p = Me.Printer
p.PaperSize = acPRPSA3
Set p = Nothing

The problem I am now encountering is that I allow the user to preview the report as a subreport in a form, and Access no likey that (Run-time error '2596': Printer object is not available on subforms and subreports.). I am considering creating two versions of each report - one with code and one without - if there is not an If...Then that can help identify when the report is being opened in a sub window. This would be a little extra work but not that bad since I would just make the code a function and call it to each, but either way, I am going to make the code a function to call.

[June7]

Maybe use code that checks if the main form is open, like:

If Not CurrentProject.AllForms("main form name").IsLoaded Then
'set printer
...
End If

[TG_W]

Brilliant idea, June. Thanks!

The way this database is set up, I am utilizing a main Navigation Form layout, and I give the users two options for getting a report out of the database - and export button (for Excel or PDF) and a button that opens the report in print preview. Because of the second option, I modified the code to check if the report is opened, and it worked. I think the problem is arising when I .zip the database to email to end users for Alpha testing. I've only been with this company for a couple of months, and I haven't been given a breakdown of how the network drive work so that will hopefully further alleviate issues.

Thanks, again!

[TG_W]

The code for anyone else with a similar issue

Private Sub Report_Load()
    If Not CurrentProject.AllReports("rptOverdue").IsLoaded Then
    PrRpt
    Else: End If
End Sub

'In a separate module for use less code throughout the database

Public Sub PrRpt()
    Dim p As Printer
    Set p = Printer
    p.PaperSize = acPRPSA3
    Set p = Nothing
End Sub