Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    New Lounger
    Join Date
    Feb 2001
    Location
    ms
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    landscape printer setting in access report

    Is there an easy way to set a report to print in landscape (without user intervention) from access? If there is a simple way, it's escaped me. The only code examples I found in VBA help were for the (I believe I'm spelling this right) PrtDevMode property and I cannot get it to work right from the example presented in the help files. Any help would be greatly appreciated.

    PS Access 2000 and Win98SE

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: landscape printer setting in access report

    The simplest way is to use Page Setup with the report in design view, set it the way you want it, and then save the report. This will work as long as all users have the same printer name and path. However, there's a bug in the initial release of Access 2000 that causes it to drop printer settings. Make sure you have SR1a loaded, since it corrected that problem.
    Charlotte

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: landscape printer setting in access report

    Just with Access 2000! Every version of Access since 2.0 I have met will drop printer settings at various times. Long ago I made a table where I store margins etc and have a module which will 'repair' all reports when I see Access has corrupted things again.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: landscape printer setting in access report

    Sounds like a good idea, especially since opening the report on a computer with a different printer causes the settings to default to portrait. That, at least, has been consistent in all the versions. However in Access 2000, there was a problem (corrected in SR-1a) with it happening on the same machine. I never encountered that with the other versions.
    Charlotte

  5. #5
    New Lounger
    Join Date
    Apr 2002
    Location
    Sydney, NSW, Australia
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: landscape printer setting in access report

    I saw this post and thought what a great idea, but couldn't see how to change the properties from code. Can you let me know what report properties match up with the margins and orientation of the page

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: landscape printer setting in access report

    To set print properties in code, you can use the PrtMip and PrtDevMode properties of a report. It ain't pretty - Microsoft didn't bother to create a user-friendly interface for these Windows structures.

    See this article for a good overview of how to use them.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: landscape printer setting in access report

    The is also code to manipulate them in all the various versions of the Access Developers Handbook.
    Charlotte

  8. #8
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: landscape printer setting in access report

    Hi Andy

    Is this table and module available to this forum?

    Thanks, John

  9. #9
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: landscape printer setting in access report

    Hi John,

    Sorry for the delay in responding, I'll leave it for you to reconstruct a suitable table - it's pretty obvious what it requires.


    Option Compare Database
    Option Explicit


    Type ty_szPRTMIP
    szRGB As String * 28
    End Type

    Type ty_PRTMIP
    lLeftMargin As Long
    lTopMargin As Long
    lRightMargin As Long
    lBottomMargin As Long
    lDataOnly As Long
    lWidth As Long
    lHeight As Long
    lDefaultSize As Long
    lColumns As Long
    lColumnSpacing As Long
    lRowSpacing As Long
    lItemLayout As Long
    lFastPrint As Long
    lDatasheet As Long
    End Type




    Type ty_szDevModeStr
    szRGB As String * 94
    End Type

    Type ty_DeviceMode
    dmDeviceName As String * 16
    dmSpecVersion As Integer
    dmDriverVersion As Integer
    dmSize As Integer
    dmDriverExtra As Integer
    dmFields As Long
    dmOrientation As Integer
    dmPaperSize As Integer
    dmPaperlength As Integer
    dmPaperWidth As Integer
    dmScale As Integer
    dmCopies As Integer
    dmDefaultSource As Integer
    dmPrintQuality As Integer
    dmColor As Integer
    dmDuplex As Integer
    dmResolution As Integer
    dmTTOption As Integer
    dmCollate As Integer
    dmFormName As String * 16
    dmPad As Long
    dmBits As Long
    dmPW As Long
    dmDFI As Long
    dmDRr As Long
    End Type


    Private Sub FillTableWithReports()
    Dim DB As Database, RS As Recordset
    Dim doc As Document
    Set DB = CurrentDb

    For Each doc In DB.Containers!Reports.Documents
    Set RS = DB.OpenRecordset("SELECT * FROM [PatchReportMargins] WHERE (ReportName='" & doc.Name & "')")

    If RS.BOF And RS.EOF Then
    RS.AddNew
    RS![ReportName] = doc.Name
    RS.Update
    End If

    RS.Close
    Next
    Set DB = Nothing
    Set RS = Nothing

    End Sub



    Private Sub PatchReportMargins()
    Const cTWIP = 567

    Dim DB As Database, RS As Recordset
    Dim rpt As Report, doc As Document

    Dim PrtMipString As ty_szPRTMIP
    Dim PrtMipStringORG As ty_szPRTMIP
    Dim PM As ty_PRTMIP

    Dim DM As ty_DeviceMode
    Dim DevString As ty_szDevModeStr
    Dim DevStringORG As ty_szDevModeStr
    Dim DevModeExtra As String

    Set DB = CurrentDb
    Set RS = DB.OpenRecordset("PatchReportMargins")
    If Not (RS.BOF And RS.EOF) Then
    RS.MoveFirst
    Do
    On Error Resume Next
    If RS!Process Then

    DoCmd.OpenReport RS!ReportName, acViewDesign

    If Err Then
    Err.Clear
    Debug.Print "Can't open report '"; RS!ReportName; "'"
    Else
    Set rpt = Reports(RS!ReportName)
    PrtMipString.szRGB = rpt.PrtMip
    PrtMipStringORG.szRGB = rpt.PrtMip
    LSet PM = PrtMipString
    PM.lLeftMargin = CInt(RS!MarginLeft * cTWIP)
    PM.lTopMargin = CInt(RS!MarginTop * cTWIP)
    PM.lRightMargin = CInt(RS!MarginRight * cTWIP)
    PM.lBottomMargin = CInt(RS!MarginBottom * cTWIP)
    LSet PrtMipString = PM ' Update property.



    'Copy the PrtDevMode property to a string.
    DevModeExtra = rpt.PrtDevMode
    DevString.szRGB = DevModeExtra
    DevStringORG.szRGB = DevModeExtra
    LSet DM = DevString

    If RS!LandscapeMode Then
    DM.dmOrientation = 2
    Else
    DM.dmOrientation = 1
    End If

    'Update the first 68 bytes of the PrtDevMode property.
    LSet DevString = DM
    Mid$(DevModeExtra, 1, 68) = DevString.szRGB

    rpt.PrtMip = PrtMipString.szRGB
    rpt.PrtDevMode = DevModeExtra
    DoCmd.Close acReport, RS!ReportName, acSaveYes

    Set rpt = Nothing

    End If
    End If
    RS.MoveNext
    Loop Until RS.EOF
    End If

    RS.Close

    For Each doc In DB.Containers!Reports.Documents
    Set RS = DB.OpenRecordset("SELECT * FROM [PatchReportMargins] WHERE (ReportName='" & doc.Name & "')")

    If RS.BOF And RS.EOF Then
    Debug.Print "Report '"; doc.Name; "' not in the table"
    End If

    RS.Close
    Next
    Set DB = Nothing
    Set RS = Nothing

    MsgBox "Finished"

    End Sub

  10. #10
    2 Star Lounger
    Join Date
    Nov 2002
    Posts
    160
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: landscape printer setting in access report

    it is a good idea but if u have any example for it i think it is better to understand well .
    Thanks advanced
    ashraf

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: landscape printer setting in access report

    This is not the sort of thing where sample databases are particularly appropriate. You will learn more from creating your own database and trying to apply the code yourself.
    Charlotte

  12. #12
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: landscape printer setting in access report

    The table contains a list of all the reports in the database (or those you want to handle). Along with the margins you want (in cms) and a flag as to normal/landscape mode.
    All it does is loop through the reports and reset the margins to he sizes you have specified.
    What else requires clarification?

  13. #13
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: landscape printer setting in access report

    Hi Andy

    Great code!

    FillTableWithReports works OK

    I have three reports in container:

    CustomersCol
    CustomersTab
    CustomersJus

    I have two reports in PatchReportMargins:

    CustomersCol
    CustomersTab

    When I run PatchReportMargins I get:

    MsgBox Err.Description reveals "Item not found in this collection" on the following 2 lines:
    Can't open report 'CustomersCol'
    Can't open report 'CustomersTab'
    Report 'CustomersJus' not in the table

    When I close the Module I can see both CustomersCol & CustomersTab Reports in Design view.

    What am I doing wrong?

    John

    PS: PatchReportMargins Table looks like this:

    ReportName text
    MarginLeft text
    MarginTop text
    MarginRight text
    MarginBottom text
    LandscapeMode yes/no

  14. #14
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: landscape printer setting in access report

    Hi John,

    Sorry I forgot a field in the table which is called process. This is a y/n flag to tell if the report should actually be processed in the routine. (Sub reports don't require processing)
    The first will raise an error which is 'Item not found...'
    This is then getting trapped after the report is opened in design mode, hence the reports being opened after the code has run.

    I wanted to trap the error internally to keep the loop going if a report had been renamed/removed.

    It's years since I did this code and I didn't look closely enough at what I had in the table before typing. Sorry.

    I hope it runs without a problem now.
    Andy.


    ps. The margin fields I have as singles (could be eg. 2.5 cm).

  15. #15
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: landscape printer setting in access report

    John,

    If you want margins in inchs just change the const cTWIPS to 1440 (I think this is correct but it is in the help somewhere search for TWIP).

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •