Solved FILTERING MAIN REPORT AND SUBREPORTS FROM A FORM (1 Viewer)

gregoryhills

New member
Local time
Today, 18:02
Joined
Feb 14, 2022
Messages
15
Hello everyone. I have main report with two subreports. From the form, when I filter using ID, it gives me all the records including all the records in subreports. The query for subreport A is query 1 and for subreport B is query 2. If i want to see records for customerID 1, on goods he bought which is in subreport A e g RICE, ORANGES and MANGOES with the price list in subreport B how will I filter if I am in a form. Thanks in advance
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:02
Joined
Feb 19, 2002
Messages
42,970
I don't understand the purpose of the second table. The purchase price should be in the order details table bound to subreport1
 

gregoryhills

New member
Local time
Today, 18:02
Joined
Feb 14, 2022
Messages
15
I don't understand the purpose of the second table. The purchase price should be in the order details table bound to subreport1
I have corrected that. Kindly assist me with vba code to call Report and subreports from a form
 

GinaWhipp

AWF VIP
Local time
Today, 11:02
Joined
Jun 21, 2011
Messages
5,901
Are the subreports linked to the main report? Because you would only need to filter the main report from the Form, i.e.

If your field is numeric...
Code:
DoCmd.OpenReport "YourReport", acViewPreview, , "[FieldInReport]=" & Me![ControlOnForm]


If your field is text…
Code:
DoCmd.OpenReport "YourReport", acViewPreview, , "[FieldInReport]='" & Me![ControlOnForm] & "'"
 

gregoryhills

New member
Local time
Today, 18:02
Joined
Feb 14, 2022
Messages
15
Are the subreports linked to the main report? Because you would only need to filter the main report from the Form, i.e.

If your field is numeric...
Code:
DoCmd.OpenReport "YourReport", acViewPreview, , "[FieldInReport]=" & Me![ControlOnForm]


If your field is text…
Code:
DoCmd.OpenReport "YourReport", acViewPreview, , "[FieldInReport]='" & Me![ControlOnForm] & "'"
They are linked but when you filter the main reports it gives all records in subreports. How do I filter records in subreports.
Numeric and
Text
Assist me please
 

GinaWhipp

AWF VIP
Local time
Today, 11:02
Joined
Jun 21, 2011
Messages
5,901
It does not sound like the subreports are linked if they are showing all the records in the subreports. How are they linked? On what field?
 

gregoryhills

New member
Local time
Today, 18:02
Joined
Feb 14, 2022
Messages
15
Ki
Are the subreports linked to the main report? Because you would only need to filter the main report from the Form, i.e.

If your field is numeric...
Code:
DoCmd.OpenReport "YourReport", acViewPreview, , "[FieldInReport]=" & Me![ControlOnForm]


If your field is text…
Code:
DoCmd.OpenReport "YourReport", acViewPreview, , "[FieldInReport]='" & Me![ControlOnForm] & "'"
Kindly assist me on how to filter subreports after filtering the main report. Please i am begging you.
Please.
 

gregoryhills

New member
Local time
Today, 18:02
Joined
Feb 14, 2022
Messages
15
Hello. Kindly please if anyone has a sample database on how to filter subreports whether numeric fields or text fields kindly assist me. I will really appreciate. Please. Please. Please. Kindly assist me please.
 

SHANEMAC51

Active member
Local time
Today, 18:02
Joined
Jan 28, 2022
Messages
310
I have main report with two subreports. From the form, when I filter using ID, it gives me all the records including all the records in subreports. The query for subreport A is query 1 and for subreport B is query 2
lay out an example of the form and reports that you could see
1 how do you open the report
2 and on what data

apparently the problem is in the sampling conditions
 

gregoryhills

New member
Local time
Today, 18:02
Joined
Feb 14, 2022
Messages
15
lay out an example of the form and reports that you could see
1 how do you open the report
2 and on what data

apparently the problem is in the sampling conditions
Thanks. Any sample database. Ir could assist me and other new members a great deal
 

SHANEMAC51

Active member
Local time
Today, 18:02
Joined
Jan 28, 2022
Messages
310
Any sample database. Ir could assist me and other new members a great deal
it is your example that is needed
you are doing something wrong and the sample should help to understand your mistake

usually calling reports from the form does not create problems
 

GinaWhipp

AWF VIP
Local time
Today, 11:02
Joined
Jun 21, 2011
Messages
5,901
Ki
Kindly assist me on how to filter subreports after filtering the main report. Please i am begging you.
Please.
You want assistance but you have not provided an answer to how your subreports are linked to the main report. Without that I can't say why the lines I posted are not working. Better yet, upload your database so we can see what the issue is.
 

gregoryhills

New member
Local time
Today, 18:02
Joined
Feb 14, 2022
Messages
15
Looks like there are no sample databases. If there is one kindly please assist me have a look on how to filter REPORTS and subreports. Thanks
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:02
Joined
Feb 19, 2002
Messages
42,970
I don't think a sample will help you if you can't answer the questions the people trying to help you have asked. Try to answer the questions or post a database with the report you are having trouble with.
 

LarryE

Active member
Local time
Today, 08:02
Joined
Aug 18, 2021
Messages
562
Hello everyone. I have main report with two subreports. From the form, when I filter using ID, it gives me all the records including all the records in subreports. The query for subreport A is query 1 and for subreport B is query 2. If i want to see records for customerID 1, on goods he bought which is in subreport A e g RICE, ORANGES and MANGOES with the price list in subreport B how will I filter if I am in a form. Thanks in advance
To automatically filter subreports with the main report:
  1. Open the main report
  2. Make sure ID is a field on that report
  3. Open query 1 and query 2
  4. Make sure ID is included in those queries. There must be a common field included with the Main Report.
  5. Create a textbox on both subreports and set their ControlSource and Textbox Name to the ID field
  6. On each, subreport, create a Master/Child relationship with ID. That will link each subreport to the main report
If for some reason you cannot create Master/Child relationships with the main report
  1. Open both query 1 and query 2
  2. Make sure there is at least one field in common with the main report. It could be ID, or a text field or other numerical criteria
  3. Use the fields Criteria property box to enter the common criteria name. For example, if the main report name is MainReport and there is a field included in each subreport query called ID that is used as a relationship link to the main report, then the entry in the field criteria property would be:
[Reports]![MainReport]![ID]
That will filter your subreports based upon whatever ID is in MainReport.

I don't believe you can filter a subreport in a main report by using the DoCmd.OpenReport command. You can only filter the Main Report using that command because that command only operates on the named report to be opened. Subreports are a separate issue.
 

LarryE

Active member
Local time
Today, 08:02
Joined
Aug 18, 2021
Messages
562
In addition, you might be able to filter a subreport based on criteria on a form by filtering the subform when you open the main form.
Let's say:
  1. You have a form with a combobox that has criteria options the user selects
  2. The form name is MyForm
  3. The criteria combo box is named MyCriteria based upon a field in SubReportA called MyName. The user selects the criteria.
  4. The subreport is SubReportA with a field in it called MyName.

In the main reports On Load Event enter the following:
Code:
Me.SubReportA.Filter="[MyName]=Forms![MyForm]![MyCriteria]"
Me.SubReportA.FilterOn=True
Me.SubReportA.Requery

I have never had a reason to do what I described, but you can try it.

Reading through the other answers, I have to agree that your original table and relationship design may be flawed if you can't use the normal report Master/Child fields. You should also make sure you have Primary keys and foreign keys properly defined. If you already have Master/Child fields defined, then you can filter your subreports further.
 
Last edited:

Users who are viewing this thread

Top Bottom