Using Report_open Event To Filter Report

[TimTDP]

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

 

[vbaInet]

Tim,

Perhaps I should redirect you to the thread from which you got and were given this idea:

http://www.access-programmers.co.uk/forums/showthread.php?t=204478

The comparison used in the IF block that enables the FILTER is not the same as yours. Follow what was written.

 

[TimTDP]

I have change the If statement from:
If Wclause <> "" Then
to
If Len(Wclause) <> 0 Then

and still have the same problem!

 

[vbaInet]

Change it to the Load event.

When it errors, what line does it highlight?

 

[TimTDP]

Changed to On Load event
Same error, line in red

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

 

[vbaInet]

How many times did you declare that global variable? I remember mentioning that you only needed it declared once.

 

[TimTDP]

once

Public Wclause As String

 

[vbaInet]

Have you got error handling in your code? If you do, comment it out to see if you get a more descriptive message.

It could also be that your db is corrupt. Run Compact & Repair.

 

[TimTDP]

I have been advised by some that I should create a temp table, put the InvoiceId into this table and include this table in the reports underlying query.

I have done this. When I look at the data in the reports underlying query, the correct "filtered" data is returned.
I have removed the code form the reports "On Load" and "On Open" events

The same error returns when I open the report.

So it can't be a programming fault!

Could the report have become corrupt when I upgraded from Access 2000 to 2010?

 

[vbaInet]

I imagine it to be corruption in the data (being filtered) and not the report.

Follow these steps:

http://www.stoneedge.com/help/OM_DOC/troubleshooting/error/error3000.html