Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2011
    Posts
    6

    Question Unanswered: how to run a query in this context?

    Hi,

    I have a form that generates a letter for mailing. The original code to do so is shown below, where qryReviewLetter extracts the data for the letterbased on the IDnumber as shown in the form for the report. However, the problem that I have to solve next is described after this piece of code.

    Private Sub Command645_Click()
    'prints the letter
    On Error GoTo Err_Command645_Click

    Dim stDocName As String

    stDocName = "myLetter"

    DoCmd.OpenReport stDocName, acViewPreview, "qryReviewLetter"

    Exit_Command645_Click:
    Exit Sub

    Err_Command645_Click:
    MsgBox Err.Description
    Resume Exit_Command645_Click

    End Sub


    Now, since we have moved to a printer with 4 trays, I needed to change this code to one where the first page goes into the letterhead tray and the rest into the plainpaper tray. So I found some code to do that on this site.

    And so I changed the above code to this


    Private Sub Command645_Click()
    'Prints letter
    On Error GoTo Err_Command645_Click

    Dim stDocName As String

    stDocName = "myLetter"


    'Calling TwoTrayPrinting function from SelectPrinter Module
    ' to select letter head tray for first page of report plain paper tray for remaining pages
    TwoTrayPrinting (stDocName)

    Exit_Command645_Click:
    Exit Sub

    Err_Command645_Click:
    MsgBox Err.Description
    Resume Exit_Command645_Click



    End Sub



    and it works, but I don't know how to run the qryReviewLetter against that code so that only one specific report is printed and not all in the database, which is happening so far.


    The code for selecting the two relevant trays I use is shown below:

    Option Compare Database
    Option Explicit

    Const R_LETTERHEAD_TRAY = 258 'letter head tray
    Const R_PLAINPAPER_TRAY = 259 'subsequent pages tray

    '** user defined types
    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


    Sub SetReportTray(r As Report, traynumber As Integer)
    '
    ' Changes the tray number of a report that is opened in DESIGN mode
    '
    Dim dm As str_DEVMODE 'R_DevModeStr
    Dim DevMode As type_DEVMODE
    'MsgBox "test"PrtDevModePrtDevMode
    If Not IsNull(r.PrtDevMode) Then
    dm.RGB = r.PrtDevMode
    LSet DevMode = dm
    DevMode.intDefaultSource = traynumber
    LSet dm = DevMode
    r.PrtDevMode = dm.RGB
    End If
    End Sub

    Function TwoTrayPrinting(ReportName As String) As Integer
    '
    ' Prints a report's page 1 from the letterhead tray
    ' and the remaining pages from the plain paper tray.
    ' NOTE: The report gets run twice, once for each tray.
    '
    ' Assumes: that the report has 999 or fewer pages.
    '
    ' Returns: TRUE = Success; FALSE = Error
    '
    Const MAX_PAGES = 999

    ' Open the report in DESIGN view
    On Error GoTo TTP_Error
    DoCmd.Echo False
    DoCmd.OpenReport ReportName, A_DESIGN

    ' Switch to letterhead tray and print first page
    SetReportTray Reports(ReportName), R_LETTERHEAD_TRAY
    DoCmd.PrintOut A_PAGES, 1, 1

    ' Switch to plain paper tray and print remaining pages
    SetReportTray Reports(ReportName), R_PLAINPAPER_TRAY
    DoCmd.PrintOut A_PAGES, 2, MAX_PAGES

    ' Close the report
    DoCmd.SetWarnings False
    DoCmd.Close A_REPORT, ReportName
    DoCmd.SetWarnings True
    DoCmd.Echo True
    'TwoTrayPrinting = True

    TTP_Exit:
    Exit Function

    TTP_Error:
    ' TwoTrayPrinting = False
    DoCmd.Echo False ' Restore screen echo
    Resume TTP_Exit

    End Function


    I hope I managed to explain my problem and what I am trying to achieve. Any help will be greatly appreciated. Thank you!
    Last edited by Platonic; 12-12-11 at 23:18.

  2. #2
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    183
    If you want to print only a specific Report using both Trays then test for that Report Name in the Code and use both trays otherwise switch the printing to the plain paper tray. The Code segment that needs the change is given below.

    Code:
    If ReportName="myLetter" then
       ' Switch to letterhead tray and print first page
       SetReportTray Reports(ReportName), R_LETTERHEAD_TRAY
       DoCmd.PrintOut A_PAGES, 1, 1
    
       ' Switch to plain paper tray and print remaining pages
       SetReportTray Reports(ReportName), R_PLAINPAPER_TRAY
       DoCmd.PrintOut A_PAGES, 2, MAX_PAGES
    Else
       ' Switch to plain paper tray and print All pages
       SetReportTray Reports(ReportName), R_PLAINPAPER_TRAY
       DoCmd.PrintOut A_PAGES, 1, MAX_PAGES
    End if
    Complete Code with change is given below:
    Code:
    Function TwoTrayPrinting(ReportName As String) As Integer
    '
    ' Prints a report's page 1 from the letterhead tray
    ' and the remaining pages from the plain paper tray.
    ' NOTE: The report gets run twice, once for each tray.
    '
    ' Assumes: that the report has 999 or fewer pages.
    '
    ' Returns: TRUE = Success; FALSE = Error
    '
    Const MAX_PAGES = 999
    
    ' Open the report in DESIGN view
    On Error GoTo TTP_Error
    DoCmd.Echo False
    DoCmd.OpenReport ReportName, A_DESIGN
    
    If ReportName="myLetter" then
       ' Switch to letterhead tray and print first page
       SetReportTray Reports(ReportName), R_LETTERHEAD_TRAY
       DoCmd.PrintOut A_PAGES, 1, 1
    
       ' Switch to plain paper tray and print remaining pages
       SetReportTray Reports(ReportName), R_PLAINPAPER_TRAY
       DoCmd.PrintOut A_PAGES, 2, MAX_PAGES
    Else
       ' Switch to plain paper tray and print All pages
       SetReportTray Reports(ReportName), R_PLAINPAPER_TRAY
       DoCmd.PrintOut A_PAGES, 1, MAX_PAGES
    End if
    
    ' Close the report
    DoCmd.SetWarnings False
    DoCmd.Close A_REPORT, ReportName
    DoCmd.SetWarnings True
    DoCmd.Echo True
    'TwoTrayPrinting = True
    
    TTP_Exit:
    Exit Function
    
    TTP_Error:
    ' TwoTrayPrinting = False
    DoCmd.Echo False ' Restore screen echo
    Resume TTP_Exit
    
    End Function
    www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    to limit the report to just the one item set parameters through the openargs property
    ms access docmd.openreport example - Google Search

    particularly: DoCmd OpenReport in Microsoft Access
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Dec 2011
    Posts
    6
    Unfortunately, the proposed code changes have no effect whatsoever. It simply prints all reports in the database, as before. I need to FILTER for the report that is currently open in the form and from which the print button was pressed. However, the filter is not executed, as I explained in my OP.

    Thanks for trying to help, though ;-)

    Quote Originally Posted by apr pillai View Post
    If you want to print only a specific Report using both Trays then test for that Report Name in the Code and use both trays otherwise switch the printing to the plain paper tray. The Code segment that needs the change is given below.

    Code:
    If ReportName="myLetter" then
       ' Switch to letterhead tray and print first page
       SetReportTray Reports(ReportName), R_LETTERHEAD_TRAY
       DoCmd.PrintOut A_PAGES, 1, 1
    
       ' Switch to plain paper tray and print remaining pages
       SetReportTray Reports(ReportName), R_PLAINPAPER_TRAY
       DoCmd.PrintOut A_PAGES, 2, MAX_PAGES
    Else
       ' Switch to plain paper tray and print All pages
       SetReportTray Reports(ReportName), R_PLAINPAPER_TRAY
       DoCmd.PrintOut A_PAGES, 1, MAX_PAGES
    End if

  5. #5
    Join Date
    Dec 2011
    Posts
    6
    Alas, this is of no help either

    If I change the code to

    Private Sub Command645_Click()
    'Prints letter
    On Error GoTo Err_Command645_Click

    Dim stDocName As String
    stDocName = "myLetter"


    DoCmd.OpenReport "myLetter", acViewPreview, "qryReviewLetter"

    'Calling TwoTrayPrinting function from SelectPrinterTray Module
    ' to select letter head tray for first page of report plain paper tray for remaining pages
    TwoTrayPrinting (stDocName)

    Exit_Command645_Click:
    Exit Sub

    Err_Command645_Click:
    MsgBox Err.Description
    Resume Exit_Command645_Click

    End Sub

    The letter generated by way of the applied filter qryReviewLetter never ends up as that selected letter in the TwoTrayPrinting function, but instead all the records are printed ie. multiple reports are printed.

    What to do?

    Quote Originally Posted by healdem View Post
    to limit the report to just the one item set parameters through the openargs property
    ms access docmd.openreport example - Google Search

    particularly: DoCmd OpenReport in Microsoft Access
    Last edited by Platonic; 12-12-11 at 23:29.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Platonic View Post
    What to do?
    I'd probably read
    DoCmd OpenReport in Microsoft Access
    especially the bit about wherecondition

    if I couldn't glean anything relevant from that I'd need to go away make a brew and decide if I was made out to be a developer. If I did decide I wanted to then I'd possibly want to do
    ms access docmd.openreport example - Google Search
    and see if there was anything there that might help me

    I'd then make an effort to see if I can resolve the problem. and if I need fine tuning or guidance then I'd come back to a place like dbforums and ask for assistance. but I'd want to make an effort to resolve the problem myself first as that's one of the best ways to learn. not just the immediate problem but how to develop my own strategies to develop software

    in short make an effort and Im sure you will get help
    throw your hands up in horror at the first step, then for one I'm less likely to help. the message you put out is I'm too important, I'm to pressed for time to learn when I can get some mug to do it for me. getting some mug to do it for you cheats you yourself. you don't learn from the experience, you don't develop the confidence to express the problem in code.
    Last edited by healdem; 12-13-11 at 05:40.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Dec 2011
    Posts
    6
    I have worked out the problem without your 'helpful and encouraging advice'.


    Quote Originally Posted by healdem View Post
    I'd probably read
    DoCmd OpenReport in Microsoft Access
    especially the bit about wherecondition

    if I couldn't glean anything relevant from that I'd need to go away make a brew and decide if I was made out to be a developer. If I did decide I wanted to then I'd possibly want to do
    ms access docmd.openreport example - Google Search
    and see if there was anything there that might help me

    I'd then make an effort to see if I can resolve the problem. and if I need fine tuning or guidance then I'd come back to a place like dbforums and ask for assistance. but I'd want to make an effort to resolve the problem myself first as that's one of the best ways to learn. not just the immediate problem but how to develop my own strategies to develop software

    in short make an effort and Im sure you will get help
    throw your hands up in horror at the first step, then for one I'm less likely to help. the message you put out is I'm too important, I'm to pressed for time to learn when I can get some mug to do it for me. getting some mug to do it for you cheats you yourself. you don't learn from the experience, you don't develop the confidence to express the problem in code.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Platonic View Post
    I have worked out the problem without your 'helpful and encouraging advice'.

    to paraphrase the age old adage
    you can lead a horse to water, but you can't make it drink
    so should that be
    you can lead a donkey to the references, but you can't make them think or read

    I'll help once someone's made an effort to comprehend a solution to their problem, but I'll not do it for that someone
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Dec 2011
    Posts
    6
    Quote Originally Posted by healdem View Post
    to paraphrase the age old adage
    you can lead a horse to water, but you can't make it drink
    so should that be
    you can lead a donkey to the references, but you can't make them think or read

    I'll help once someone's made an effort to comprehend a solution to their problem, but I'll not do it for that someone
    If you are implying that I have not read the references out there, you are incorrect. I would also say the following: never assume, always get the facts ;-)

    Topic closed.

Posting Permissions

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