none
Using Report_open Event To Filter Report

    Question

  • I am upgrading a database from Access 2000 to Access 2010

    In Access 2000, to filter a report I used the following:

    Wclause = "InvoiceId = " & Me.lstSelectInvoice.Column(1)
    DoCmd.OpenReport "rptCustomerTaxInvoice", acViewPreview, , WClause

    This worked well!

    I now want to use the Access 2010 feature of printing to pdf.
    To do this I need to use the following:

    Wclause = "InvoiceId = " & Me.lstSelectInvoice.Column(1)
    DoCmd.OutputTo acOutputReport, "rptCustomerTaxInvoice", acFormatPDF, "C:\CustomerTaxInvoice.pdf", False, , , acExportQualityPrint

    but the DoCmd line does not allow me to include the "Wclause" as a "where" statement!

    to overcome this I have added the following event to my report:

    Private Sub Report_Open(Cancel As Integer)

    If Wclause <> "" Then
    Me.Filter = Wclause
    Me.FilterOn = True
    Else
    Me.FilterOn = False
    End If

    End Sub

    However when I run the code, Access returns an error:
    Run Time Error '3000'
    Reserved error (-1524), there is no message for this error

    If I change Wclause to Wclause = "" then the report runs. This is good news because it tells me that there is nothing wrong with the report or its data!

    What am I doing wrong!!!!

    Thanks in advance
    Tuesday, February 15, 2011 11:52 AM

Answers

  • "TimDTP" wrote in message
    news:90066c5a-d602-4cf8-9c05-ab7d7781b18e@communitybridge.codeplex.com...
    >I am upgrading a database from Access 2000 to Access 2010
    >
    > In Access 2000, to filter a report I used the following:
    >
    > Wclause = "InvoiceId = " & Me.lstSelectInvoice.Column(1)
    > DoCmd.OpenReport "rptCustomerTaxInvoice", acViewPreview, , WClause
    >
    > This worked well!
    >
    > I now want to use the Access 2010 feature of printing to pdf.
    > To do this I need to use the following:
    >
    > Wclause = "InvoiceId = " & Me.lstSelectInvoice.Column(1)
    > DoCmd.OutputTo acOutputReport, "rptCustomerTaxInvoice", acFormatPDF,
    > "C:\CustomerTaxInvoice.pdf", False, , , acExportQualityPrint
    >
    > but the DoCmd line does not allow me to include the "Wclause" as a "where"
    > statement!
    >
    > to overcome this I have added the following event to my report:
    >
    > Private Sub Report_Open(Cancel As Integer)
    >
    > If Wclause <> "" Then
    > Me.Filter = Wclause
    > Me.FilterOn = True
    > Else
    > Me.FilterOn = False
    > End If
    >
    > End Sub
    >
    > However when I run the code, Access returns an error:
    > Run Time Error '3000'
    > Reserved error (-1524), there is no message for this error
    >
    > If I change Wclause to Wclause = "" then the report runs. This is good
    > news because it tells me that there is nothing wrong with the report or
    > its data!
    >
     
    I'm not sure what's causing the error -- it works in Access 2003, in a
    simple test -- but this sort of thing can be handled without adding code to
    the report at all, by first opening the report, hidden, in print preview
    mode, and then using DoCmd.OutputTo to write the report out.  Like this:
    '
    '------ start of example code ------
       Wclause = "InvoiceId = " & Me.lstSelectInvoice.Column(1)
        DoCmd.OpenReport "rptCustomerTaxInvoice", acViewPreview, _
           WhereCondition:=WClause, _
           WindowMode:=acHidden
        DoCmd.OutputTo acOutputReport, "rptCustomerTaxInvoice", _
           acFormatPDF, _
           "C:\CustomerTaxInvoice.pdf", _
           False, , , acExportQualityPrint
        DoCmd.Close acReport, "rptCustomerTaxInvoice", acSaveNo
    '------ end of example code ------
     

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    • Proposed as answer by JohnLute Tuesday, February 15, 2011 8:49 PM
    • Marked as answer by Bessie Zhao Tuesday, February 22, 2011 9:27 AM
    Tuesday, February 15, 2011 4:27 PM

All replies

  • Hallo Tim,

    I'm handling such a situation with a query.

    First I would create a parameter table
    Next step craete a function which write into the table
    Next step is writing a function which read parameters from the table (e.g. fn_app_ReadParameterValue)

    Use this function in the query you want to use for the reports

    1. Write the parameter into the table
    2. Open the reports which will use the parameter in the query

    HTH ;-)


    Uwe Ricken
    MC IT Database Administrator 2005
    MC IT Database Administrator 2008
    MC TS SQL Server 2005
    MC TS SQL Server 2008, Implementation and Maintenance
    db Berater GmbH
    http://www-db-berater.de
    Tuesday, February 15, 2011 12:01 PM
  • That can work, but what is wrong with my method?

    something is, just can't figure it out!

    Tuesday, February 15, 2011 12:51 PM
  • Hallo Tim,

    Error -1524 points to corrupt data. Try to repair the backend and test again!
    If you will search with google, you'll find many problem descriptions and all had problems with "corrupted data"


    Uwe Ricken

    MCIT Database Administrator 2005
    MCIT Database Administrator 2008
    MCTS SQL Server 2005
    MCTS SQL Server 2008, Implementation and Maintenance
    db Berater GmbH
    http://www-db-berater.de
    Tuesday, February 15, 2011 1:46 PM
  • Tim,

    I think you are proceeding from the assumption that the OutPutTo method is similar to the Openreport method.  That is not the case.  OutputTo is a way to Export and OpenReport is a way to Print/Preview.  As you have discovered, unfortunately exporting does not support the Where clause.

    So when you go to outPut the report to PDF, you will need to already have the data ready.  There are probably a few ways to approach this but one way to do it is to have a temporary table that holds the data for the report.  Base the RecordSource of the report on that table.  Then, before you use the OutPutTo, you would clear and then populate the table with your desired data.

    Hope that helps.

    Bob Oxford


    Bob Oxford Software Wizards, Inc.
    Tuesday, February 15, 2011 3:39 PM
  • "TimDTP" wrote in message
    news:90066c5a-d602-4cf8-9c05-ab7d7781b18e@communitybridge.codeplex.com...
    >I am upgrading a database from Access 2000 to Access 2010
    >
    > In Access 2000, to filter a report I used the following:
    >
    > Wclause = "InvoiceId = " & Me.lstSelectInvoice.Column(1)
    > DoCmd.OpenReport "rptCustomerTaxInvoice", acViewPreview, , WClause
    >
    > This worked well!
    >
    > I now want to use the Access 2010 feature of printing to pdf.
    > To do this I need to use the following:
    >
    > Wclause = "InvoiceId = " & Me.lstSelectInvoice.Column(1)
    > DoCmd.OutputTo acOutputReport, "rptCustomerTaxInvoice", acFormatPDF,
    > "C:\CustomerTaxInvoice.pdf", False, , , acExportQualityPrint
    >
    > but the DoCmd line does not allow me to include the "Wclause" as a "where"
    > statement!
    >
    > to overcome this I have added the following event to my report:
    >
    > Private Sub Report_Open(Cancel As Integer)
    >
    > If Wclause <> "" Then
    > Me.Filter = Wclause
    > Me.FilterOn = True
    > Else
    > Me.FilterOn = False
    > End If
    >
    > End Sub
    >
    > However when I run the code, Access returns an error:
    > Run Time Error '3000'
    > Reserved error (-1524), there is no message for this error
    >
    > If I change Wclause to Wclause = "" then the report runs. This is good
    > news because it tells me that there is nothing wrong with the report or
    > its data!
    >
     
    I'm not sure what's causing the error -- it works in Access 2003, in a
    simple test -- but this sort of thing can be handled without adding code to
    the report at all, by first opening the report, hidden, in print preview
    mode, and then using DoCmd.OutputTo to write the report out.  Like this:
    '
    '------ start of example code ------
       Wclause = "InvoiceId = " & Me.lstSelectInvoice.Column(1)
        DoCmd.OpenReport "rptCustomerTaxInvoice", acViewPreview, _
           WhereCondition:=WClause, _
           WindowMode:=acHidden
        DoCmd.OutputTo acOutputReport, "rptCustomerTaxInvoice", _
           acFormatPDF, _
           "C:\CustomerTaxInvoice.pdf", _
           False, , , acExportQualityPrint
        DoCmd.Close acReport, "rptCustomerTaxInvoice", acSaveNo
    '------ end of example code ------
     

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    • Proposed as answer by JohnLute Tuesday, February 15, 2011 8:49 PM
    • Marked as answer by Bessie Zhao Tuesday, February 22, 2011 9:27 AM
    Tuesday, February 15, 2011 4:27 PM
  • Dirk,

    You have got to be F$%^&ing kidding me!  That is unfreaking believable!  I have been doing this stuff for YEARS and never knew that.  When I think of where I could have used that little tip...Well, it makes me kinda sick!  That is awesome!  Thank you!

    Where on earth did you find that?

    Bob Oxford


    Bob Oxford Software Wizards, Inc.
    Tuesday, February 15, 2011 6:25 PM
  • "Bob Oxford" wrote in message
    news:674305d1-0629-4cd2-9753-83eefaa1b311@communitybridge.codeplex.com...
    > Dirk,
    >
    > You have got to be F$%^&ing kidding me!  That is unfreaking believable!  I
    > have been doing this stuff for YEARS and never knew that.  When I think of
    > where I could have used that little tip...Well, it makes me kinda sick!
    > That is awesome!  Thank you!
    >
     
    <G>  I don't know when I last got such a good reaction to a post.  You're
    welcome.
     
    > Where on earth did you find that?
    >
     
    I couldn't tell you.  I picked it up somewhere along the way -- probably
    from a newsgroup post.
     
     

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    Tuesday, February 15, 2011 6:51 PM
  • "Bob Oxford" wrote in message
    news:674305d1-0629-4cd2-9753-83eefaa1b311@communitybridge.codeplex.com...
    > Dirk,
    >
    > You have got to be F$%^&ing kidding me!  That is unfreaking believable!  I
    > have been doing this stuff for YEARS and never knew that.  When I think of
    > where I could have used that little tip...Well, it makes me kinda sick!
    > That is awesome!  Thank you!
    >
     
    <G>  I don't know when I last got such a good reaction to a post.  You're
    welcome.
     
    > Where on earth did you find that?
    >
     
    I couldn't tell you.  I picked it up somewhere along the way -- probably
    from a newsgroup post.
     
     

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Dirk - you taught me this trick a few years ago! I just used this thread to illustrate to some people exactly why you're a MVP! Cheers!
    Tuesday, February 15, 2011 8:47 PM
  • Thanks Dirk, I did not know this trick. Works well for my situation at hand!

    -Tom. Microsoft Access MVP

    Monday, August 27, 2012 11:12 PM