Pass Subform filter to subform in report

[camftm]

Hi,

I cant seem to figure out how to pass a filter from a subform to a subform in my report so I can open the report in print preview to print.

I have a Main form (Summary) that includes a subform (SummaryQuerySubfrm) which are linked together by ID.

I have a combo box on the main form which filters the subform by using the code below in the after update event of the combo box.

Private Sub SummaryMonth_AfterUpdate()
SummaryQuerySubfrm.Form.Filter = "[SubformField]= '" & ComboBox & "'"


SummaryQuerySubfrm.Form.FilterOn = True
End Sub

I want to be able to print the current main form and the filtered subform the user has selected via a report (Print preview).

I am not sure how to pass the filter from my subform on my main form to the subform on my report.

At the moment when I select the print button on my main form, it brings up the print preview of the report however it does not filter the subform within the report.

I have been searching the forums but cant seem to find an answer. I have been stuck on this part of my database for ages, any help would be greatly appreciated.

Thanks

Ross

[pbaldy]

This is an untested shot in the dark, but you could try similar code in the load event of the subreport that sets the subreport filter equal to the subform's filter (either using the same type filter text or simply referring to the subform's filter property).

[camftm]

I am not sure how the syntax would work with that. On my report I have put a subform on there instead of a subreport because it is displayed better, is that not good?

Would the syntax on the on open event of my subform in the report be along the lines of

Private Sub Form_Open(Cancel As Integer)

SummaryQuerySubfrmReport.Report.Filter = "[SubformReportField]= '" & Forms!Summary.ComboBox & "'"
SummaryQuerySubfrmReport.Form.FilterOn = True

End Sub

I am very new to access and have not really done much VBA so my coding is very novice.

Any help would be great.

Thanks

[pbaldy]

To be honest I've never mixed forms and reports like that. I would assume that you'd have to use "Form" instead of "Report" here:

SummaryQuerySubfrmReport.Report.Filter = "[SubformReportField]= '" & Forms!Summary.ComboBox & "'"

But since the code is running in the load event of that subform, you should be able to use:

Me.Filter = ...

[camftm]

Thanks for the info.

I have tried the below code in the on open event of the subform in my report.

Me.Filter = "[Expr1]= '" & Forms!Summary.ComboBox & "'"
Me.FilterOn = True

When I try and open my form I get a Run-time error 2465 Application-defined or object-defined error.

When I try and debug the error it seems to be pointing to me not referencing the field that filters the subform. "Microsoft Office Access can't find the field '|' referred to in your expression You may have misspelled the field name, or the field may have been renamed or deleted."

Does this reference seem correct? Forms!Summary.ComboBox (This should be a reference to the combobox on my main form that intialy filters the subform.

[pbaldy]

Have you tried the load event as I suggested? I don't believe the data is available yet in the open event.

[camftm]

Tried it in the On Load event and the same error.

Can you think of anything else to try?

[pbaldy]

Can you post the db?

[camftm]

Hi,

I have attached my database. I have stripped out all the unnecessay forms and queries from it.

If you open the summary form you can filter the subform by the month combo box on the main form. When you select the print button it does not carry over the filter to the subform in my report.

Any help would be great.

Thanks

[dinorbaccess]

thats also my problem buddy. Did you get it to solve this problem.

If yes, please tell me how. I have the exactly same problem

[Callahan]

I am having a similar problem with Access 2010. Using Access 2003, I could use a form filter in a report. With Access 2010, this does not work, and I cannot find a fix.

[rpeare]

Have you tried copying the filter from the suform to the subreport?
I never use filters for this kind of thing I always use queries but I would think you could do something like:

ReportName.filter = form.subform.filter

Again, I'm not sure of the exact syntax for referencing the subform this way but the concept is sound. I would imagine you would have to put it in the ON OPEN event of the report for it to work.

[Callahan]

Thanks for the reply.

Yes I've tried using the on open report to no avail, I've tried every thing I can think of every which way but loose, all to no avail. I've pasted the filter into the filter box in the Report Properties, and sometimes it will filter the way it should, but most of the time it ignores the filter. Somthing is wrong with this Access 2010 report writer, in my humble opinion.

[rpeare]

Dim sFilter As String
sFilter = Me.Filter

DoCmd.OpenReport "Rpt_testfilter", acViewDesign
Reports!rpt_testfilter.Filter = sFilter
Reports!rpt_testfilter.FilterOn = True
DoCmd.OpenReport "Rpt_testfilter", acViewPreview

This is what I ran. I gave my form a filter that worked then passed the filter and turned the filter on the report. I based both the form and the report on the same data as well (obviously) and it worked.

[Callahan]

Tried that and got a quick glance at correct report with filtering in place, a short pause and the report preview re-displays without filtering.

Could be that because I'm trying to use a complex database developed with 2003 Access in the 2010 environment, the Access god's are against me. Maybe, I'll have to painfully rebuild the database one little bit at a time? I'm going to give that theory a shot.

Thanks rpeare.