VB Code to print a report using filtered datasheet from a subform

[asYancey]

I have a form that uses an unbound combo box using a qry for a source as does the subform. This data is for viewing not editing. I filter the datasheet using the column heading dropdown but I need to create a button that will allow the user to print a report using the filtered data from the datasheet. Using the vb statement:

DoCmd.OpenReport "rptSoftwareAndUser", acViewPreview, , "[tblTrackerSysSft]![sw_name]=[Forms]![CopyfrmSoftwareAndUsers]![sw_name]"

will print the data filtered by my combo box but will not filter out the data selected from the datasheet heading drop downs.

Notice the record count in the video below before generating the report and the count in the report. The report doesn't filter out my selections.

View my form here

http://www.screencast.com/t/uWuZafXkh2C

Any suggestions?
Yancey

 

[lameid]

I assumed this code runs from the form in question...
You probably could modify the second example to concatenate in the literals for the combo box value.
There are two examples because I wasn't sure if you wanted the filter and the combo box criteria or just the filter.

DoCmd.OpenReport "rptSoftwareAndUser", acViewPreview, , IIF(me.FilterOn, Me.Filter, "")



DoCmd.OpenReport "rptSoftwareAndUser", acViewPreview, , IIF(me.FilterOn, "[tblTrackerSysSft]![sw_name]=[Forms]![CopyfrmSoftwareAndUsers]![sw_name] AND " &
Me.Filter, "[tblTrackerSysSft]![sw_name]=[Forms]![CopyfrmSoftwareAndUsers]![sw_name]")

 

[MajP]

You have identified what I consider to be two shortfalls in Access (or at least needed enhancements)

1) You can not bind a report to a recordset like you can a form. Not sure why that is. But if you could you would have an easy solution. As the report opens you set the reports recordset to the filtered datasheet recordset. You can do this from form to form. You can open a form and set the recordset to the filtered datasheet and it will show only the filtered records.

2)AKAIK you can not return the filter string from a datasheet filtered using the field filters. Again that would lead to an easy solution and would allow for some really powerful report filters. If you could then when you opened the report you would set the reports filter to the filtered datasheet

So I can only think of one way to do this, and it is not elegant but would work. You could filter your datasheet and then write to a table. Then base the report on the table.