Filtered Report from filtered datasheet form

[gemadan96]

I have a form users use to select criteria for opening a datasheet form of the filtered data. I would like to have a button on the form of filtered data to produce a report of that filtered data. I'm trying to figure out who to pass on the where condition used to open the form to the report.

There are over 100,000 records in the table being used for the form and the report. The table itself is linked to a SQL server. The users will use the first form to filter the data by date range, category, and facility. Now I just need to make it so they can generate a report for printing or saving as a PDF.

[June7]

Options:

1. dynamic parameterized query that references form controls for input, review http://datapigtechnologies.com/flash...mtoreport.html

2. VBA to construct filter criteria string, review http://allenbrowne.com/ser-62code.html
Instead of setting the Filter property, pass the string with OpenReport
DoCmd.OpenReport "report name", , , strWHERE

[ItsMe]

Maybe something like this

Dim strWhere As String
strWhere = ""
if me.filteron = true then
strWhere = Me.Filter
End If
Debug.Print strWhere
DoCmd.OpenReport "ReportName", acViewPreview, , strWhere, acHidden
DoCmd.SelectObject acReport, "ReportName", False
DoCmd.OutputTo acOutputReport, "rReportName, acFormatPDF
DoCmd.Close acReport, "ReportName"

[June7]

DOH! Of course! If you want the exact same filter criteria that was applied to the form, just use its Filter property to grab the criteria string. Excellent!

[gemadan96]

Thanks. Works great. Just had to modify to suit my needs...

Dim strWhere As String
strWhere = ""
If Me.FilterOn = True Then
strWhere = Me.Filter
End If
Debug.Print strWhere
DoCmd.OpenReport "StudyList", acViewPreview, , strWhere, acHidden
DoCmd.SelectObject acReport, "StudyList", False
DoCmd.OutputTo acOutputReport, "StudyList", acFormatPDF
DoCmd.Close acReport, "StudyList"
End Sub

The only issue is if I cancel on the OutputTo window I get a MS Visual Basic error: Runtime error '2501': The OutputTo action was canceled. It then wants me to click end or debug. If I select debug it highlights the DoCmd.OutputTo line.

Any suggestions to eliminate/resolve are appreciated.

[ItsMe]

You can do an 'on error resume next' or build a custom error trap to handle the error number '2501' for cancel.

If it is the last code within the sub probably just an 'on error resume next'

[gemadan96]

That did it. Thanks.

[ItsMe]

You should also be able to attach that same PDF directly to an email without saving it. Just replace the DoCmd.OutputTo acOutputReport, "StudyList", acFormatPDF

with


.

DoCmd.SendObject acReport, "rptNameofReport", acFormatPDF, "ToEmailAddress", "CcEmailAddress", "BccEmailAddress", "Email Address Subject Line", "Text to Include within Email Body", True

The True at the very end will pause to allow the user to view and edit the email where False will send it without any user interaction.