Ability to Print a User Filtered Sub-Report

[Brohagen]

I have built a kiosk type setup within MS Access 2013 using VBA to Maximize the screen so users cannot click on the All Access Objects, ribbon, tools, INCLUDING the print button.

They exclusively fill out sub-forms created within a tabbed Form. These sub-forms feed to reports that I use VBA buttons to print out, however it prints out the ENTIRE form. Users are using right-click on forms, using a text filter to get the info they need from the report, and pressing my print button that is printing the ENTIRE form. What VBA code do I need to use to print EXACTLY what they are seeing in the sub-form after they have filtered it.

[PhilOfWalton]

Have you created a Report based on the data in your Subform?

Phil

[Brohagen]

Yes. One tab contains all the Subforms the users completes. Another tab contains all the Reports with data from the Subforms. We use it for an Orientation class. The forms allow the users to add students to classes on certain dates. The user then click on a tab that contains the Report with all the students grouped by the date of orientation. Users are right clicking on the dates, and using a text filter so the report only shows students for a certain date. I need a button they can click to print that view or filtered report. My current VBA button only prints the ENTIRE unfiltered report.

[PhilOfWalton]

So what is the Record Source for the subform and what is the filter and how are you applying it.

Basically the Record Source for the Report and Subform should be the same, and then you apply the same filter to it.

On your button you will have some code like

Private Sub CmdPrint_Click()
 
    Dim strWhere As String
 
    strWhere = ' This is the bit that needs sorting out
    DoCmd.OpenReport "MyReport", acViewPreview, , strWhere
    End If
 
End Sub
 

So we need to know how the filter for the Subform is obtained, and the strWhere will probably be the same.

Phil

[Brohagen]

Sorry. Slightly confused. Both the report and form were built from a query named "Orientation_Info_qry". Is that what you are asking?

[NeoPa]

Hi Brohagen.

You may notice, when working with Access and other Office applications too, that many items have properties. Forms, Reports, etc, and even the Controls and Sections on these objects, have a whole bunch of properties that can be seen when the Properties Pane is open (It's a must to have the Properties Pane open while doing any designing).

When you look at these objects that you're discussing, one of the properties, that's common to both, is called Record Source. From the code it's referred to as .RecordSource. The value in there is what Phil's asking about.

[Brohagen]

Yes I got that. I currently have a button on the Report that prints the report:

DoCmd.OpenReport "rpt_Orientation_Report1", acViewNormal

I understand I can update the button VBA to print out a filtered view of the Report.

However, users are looking at the entire report, right clicking on a certain date, and applying their own Text Filter directly to the Report. After they have done that they click the button and it prints out the entire report.

I am not applying the filters. Users are directly on the Report. If you open a Report in MS ACCESS you can right click on date or string, and apply a filter to it. For example, again with the Orientation classes; there are students grouped by class date. There may be 5 dates with up to 30 students under each date. Users are right clicking on a date and using a "Equals '15 Sept 17'" string filter. This causes the report to ONLY show that date with those students under it. I need a button now to print that.

[NeoPa]

Brohagen:
This causes the report to ONLY show that date with those students under it. I need a button now to print that.

In that case it may help to know that when a filter is applied to a Form or Report object then that object has its .Filter property updated to reflect the filtering.

Also, when using the DoCmd.OpenReport() procedure there is a parameter called WhereCondition that can be used to filter the Report. This property expects a string of the same format as found in the .Filter property.

Thus, your code would probably look something like :

With Me
    Call DoCmd.OpenReport(ReportName:=.Name, WhereCondition:=.Filter)
End With

[Brohagen]

YES! This is the answer! Thank you NeoPa. My VBA is novice at best but you took my situation and delivered. I've been Googling for months for the answer. Much appreciated.

[NeoPa]

Always a pleasure.

A very similar situation happened to me on 19th October 2006 (No I didn't need to look it up). I've been posting here ever since.