Solved FILTERING MAIN REPORT AND SUBREPORTS FROM A FORM

[gregoryhills]

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]

I don't understand the purpose of the second table. The purchase price should be in the order details table bound to subreport1

 

[gregoryhills]

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]

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...

DoCmd.OpenReport "YourReport", acViewPreview, , "[FieldInReport]=" & Me![ControlOnForm]

If your field is text…

DoCmd.OpenReport "YourReport", acViewPreview, , "[FieldInReport]='" & Me![ControlOnForm] & "'"

 

[gregoryhills]

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...

DoCmd.OpenReport "YourReport", acViewPreview, , "[FieldInReport]=" & Me![ControlOnForm]

If your field is text…

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]

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]

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...

DoCmd.OpenReport "YourReport", acViewPreview, , "[FieldInReport]=" & Me![ControlOnForm]

If your field is text…

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]

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]

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]

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]

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]

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]

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]

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]

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]

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:

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.