By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,762 Members | 1,838 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,762 IT Pros & Developers. It's quick & easy.

Ability to Print a User Filtered Sub-Report

P: 5
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.
Aug 22 '17 #1

✓ answered by 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 :
Expand|Select|Wrap|Line Numbers
  1. With Me
  2.     Call DoCmd.OpenReport(ReportName:=.Name, WhereCondition:=.Filter)
  3. End With

Share this Question
Share on Google+
9 Replies


PhilOfWalton
Expert 100+
P: 1,430
Have you created a Report based on the data in your Subform?

Phil
Aug 22 '17 #2

P: 5
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.
Aug 23 '17 #3

PhilOfWalton
Expert 100+
P: 1,430
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub CmdPrint_Click()
  2.  
  3.     Dim strWhere As String
  4.  
  5.     strWhere = ' This is the bit that needs sorting out
  6.     DoCmd.OpenReport "MyReport", acViewPreview, , strWhere
  7.     End If
  8.  
  9. End Sub
  10.  
So we need to know how the filter for the Subform is obtained, and the strWhere will probably be the same.

Phil
Aug 23 '17 #4

P: 5
Sorry. Slightly confused. Both the report and form were built from a query named "Orientation_Info_qry". Is that what you are asking?
Aug 24 '17 #5

NeoPa
Expert Mod 15k+
P: 31,489
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.
Aug 24 '17 #6

P: 5
Yes I got that. I currently have a button on the Report that prints the report:
Expand|Select|Wrap|Line Numbers
  1. 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.
Aug 28 '17 #7

NeoPa
Expert Mod 15k+
P: 31,489
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 :
Expand|Select|Wrap|Line Numbers
  1. With Me
  2.     Call DoCmd.OpenReport(ReportName:=.Name, WhereCondition:=.Filter)
  3. End With
Aug 28 '17 #8

P: 5
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.
Aug 28 '17 #9

NeoPa
Expert Mod 15k+
P: 31,489
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.
Aug 28 '17 #10

Post your reply

Sign in to post your reply or Sign up for a free account.