473,320 Members | 2,164 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Ability to Create/Print Report from User Filtered Sub-form

3 2Bits
My end user is using a filter on a sub-form (SearchQSubform) then pressing the "PrintPDF" button that is located on the form (SearchForms). However, all the searched data in the sub-form is printing. I only wish to print the user's filtered data. I tried putting the following code in the "On Click" event procedure for the "PrintPDF" button:

Private Sub PrintPDF_Click()

DoCmd.OpenReport ReportName:="Report", View:=acViewReport, WhereCondition:="SearchQSubform.Filter"

Here are the details of the database:

Main Form Name: "SearchForms:
Sub-form Name: "SearchQSubform"
The Query the sub-form is based on: "SearchQ"
Name of the report to be viewed/printed: "Report"

What am I doing wrong? Please help.
Feb 22 '22 #1

✓ answered by NeoPa

Nothing obvious that I can see. Oh yes there is. Almost missed it. Your WhereCondition parameter has quotes around it. This means instead of getting the value that's already set in the .Filter property you simply get the string "SearchQSubform.Filter" as your filter. Essentially a no-effect filter.

Also, remember that there is a difference between the SubForm Control and the Form that is currently set as being used by that Control. See Referring to Items on a Sub-Form for more on that.

Try instead ("SearchQSubform" needs to be the name of the SubForm Control) :
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport ReportName:="Report" _
  2.                , View:=acViewReport _
  3.                , WhereCondition:=SearchQSubform.Form.Filter

5 9323
NeoPa
32,556 Expert Mod 16PB
Nothing obvious that I can see. Oh yes there is. Almost missed it. Your WhereCondition parameter has quotes around it. This means instead of getting the value that's already set in the .Filter property you simply get the string "SearchQSubform.Filter" as your filter. Essentially a no-effect filter.

Also, remember that there is a difference between the SubForm Control and the Form that is currently set as being used by that Control. See Referring to Items on a Sub-Form for more on that.

Try instead ("SearchQSubform" needs to be the name of the SubForm Control) :
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport ReportName:="Report" _
  2.                , View:=acViewReport _
  3.                , WhereCondition:=SearchQSubform.Form.Filter
Feb 22 '22 #2
Tamille2020
3 2Bits
Hi NeoPa. The SubForm Control is SearchQSubform. I tried the suggested code, I get the "Enter Parameter Value" popup box. It is asking for "SearchQSubform.Recipients" input. The column that is being filtered by the user is "Recipients".
Feb 22 '22 #3
NeoPa
32,556 Expert Mod 16PB
That generally means that a reference is not recognised. For someone other than you to determine what & why would need a fuller set of information.

What is being filtered and what is the actual (string) value of SearchQSubform.Form.Filter at the time this is attempted. The following line of code will show this value in the Immediate Pane (See Debugging in VBA) :
Expand|Select|Wrap|Line Numbers
  1. Debug.Print SearchQSubform.Form.Filter;
Put it on the line prior to the DoCmd that runs the Report.

Another bit of information required to compare with is the Table, Query or SQL used as the RecordSource of the report. If it's a Query then we'll need to see the SQL of the Query and if it's a Table then we'll need a list of the Fields it has.
Feb 23 '22 #4
Tamille2020
3 2Bits
Great suggestion NeoPa. I was able to get the sub-form filter to transfer over to the report via the following code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub PrintPDF_Click()
  2.     Dim strCriteria As String
  3.         strCriteria = Trim$(Me![SearchQSubform].[Form].[Filter] & "")
  4.     If Len(strCriteria) Then
  5.         strCriteria = Replace$(Replace$(strCriteria, "[SearchQSubform].", ""), "SearchQSubform.", "")
  6.     Else
  7.         strCriteria = "(1 = 1)"
  8.     End If
  9.  
  10.     DoCmd.OpenReport ReportName:="Report", View:=acViewReport, WhereCondition:=strCriteria
  11. End Sub
But I'm wondering, is there a more simple method of doing this? I'm working with database administrators who may find this code hard to understand.
Feb 23 '22 #5
NeoPa
32,556 Expert Mod 16PB
Here's a very slightly tidier version. In reality you're pretty close to what you need :
Expand|Select|Wrap|Line Numbers
  1. Private Sub PrintPDF_Click()
  2.     Dim strCriteria As String
  3.  
  4.     With Me.SearchQSubform.Form
  5.         strCriteria = Trim(Nz(.Filter, ""))
  6.         If strCriteria > "" Then
  7.             strCriteria = Replace(Replace(strCriteria) _
  8.                                         , "[SearchQSubform].", "") _
  9.                                         , "SearchQSubform.", "")
  10.         Else
  11.             strCriteria = "True"
  12.         End If
  13.     End With
  14.     DoCmd.OpenReport ReportName:="Report" _
  15.                    , View:=acViewReport _
  16.                    , WhereCondition:=strCriteria
  17. End Sub
In reality the .Filter value set for you should use either one of the formats so you could simplify further by removing the Replace() call that isn't used if you wanted to. Otherwise it's pretty close to doing just what it says on the tin and further simplification is impossible.
Feb 25 '22 #6

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

Similar topics

1
by: laddie1997 | last post by:
When I try to create a report in Access 2003, the error message "the wizard is unable to create your report" comes up. My printer is a network printer and no local printer is attached. I can get...
2
by: ChadDiesel | last post by:
Hello, I have a form and subform with their tables linked by a field called Load_ID. I have a button on my subform that prints just the items listed on that particular subform (looking at...
2
by: ghat12 | last post by:
Hi, I am trying to print a subform containing approx 50 columns in datasheet view. The printout cannot fit more than 8-10 columns. Does anyone know of any way to print all columns; i don't care...
3
by: creative1 | last post by:
Here is how you create a complex data report that involves parent and child commands and you can update information at runtime. Its pretty straight forward to work with simple queries; however,...
3
by: sconard | last post by:
I have a subform in datasheet view that is the result of a complex vba custom query with a number of parameters. I would like to place a button on the parent form to print the datasheet view as a...
1
by: Crombam | last post by:
Hi to all, After searching the net for a couple of hours I just have to ask the question. How can I close a report with a subform (Pivot Chart) when this Pivot Chart is having no data? ...
3
AccessQuestion
by: AccessQuestion | last post by:
Not sure how to word this as I am somewhat new to Access 2007. What I am trying to do is the user fills out a form, when he hits the "save record" button I want it to save the record and print a...
1
yosiro
by: yosiro | last post by:
First, sorry for my english so bad. I want to create print button from a form that not working when i use navigation control. Step 1. http://i47.tinypic.com/2wp1d9e.jpg I want to print data...
6
by: Ever Improving | last post by:
I have created a report from a subform. However, there is one nuance I have not been able to formulate. To give a little background. I have a table (Managers) and table (Staff). The main form...
9
by: Brohagen | last post by:
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...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.