Just what the title says. In the on click event of a button, I would like it to open a report, considering the filters of the form the user has applied.

For instance, if the end user is viewing the form, and they filter the records to show all records where Technician = Adam, then click on the button, the report should only show what the form shows, records where tech = Adam. There are a few fields, and could be more than one filter, like status = pending, date=today, work order=58943.

I found this with google:

How to Filter a Report Using a Form's Filter (http://support.microsoft.com/kb/208548/en-us)

You had to use google??? I thought you knew basically everything about access. lol.

Thanks for the link! I'll try it out tonight.

You had to use google??? I thought you knew basically everything about access. lol.
The KEY is not to KNOW everything, but to know WHERE to find the answers. :D

Oh, and many times even if you know the answer, it is easier for all involved to post a link to somewhere that does the explaining, or has a picture of how to do it...

Hell thats a simple solution .... (Me.Filter)
I had RecordsetClone's and arrays running around in my mind when I first read this post - thats why I have been sitting back and watching!

Someone should sticky this!;)

DCB,

I agree that this is a great thing to know how to do.

You had to use google??? I thought you knew basically everything about access. lol.

Thanks for the link! I'll try it out tonight.

Glad you liked the link.

I did know how to do this. I use it a lot. That is why I could find it so quickly with Google. As Bob said, I did not want to example it is it had already been done.

This gives me an error that it can't find 'form'. Any ideas?

Private Sub Print_Click()
DoCmd.OpenReport "rptSchedules", acViewPreview, , Me![subHomeSchedules]![Form].Filter
End Sub

This works, but if there is a filter applied, then I get a prompt dialog. If the filter is for date, the dialog asks for a date. Without a filter, there is no prompt.


Private Sub Print_Click()
DoCmd.OpenReport "rptSchedules", acViewPreview, , Me.subHomeSchedules.Form.Filter
End Sub

Is the actual field name available in BOTH the report and the form? It sounds like the subform has a field name that isn't exactly the same in the report and that is why you are getting the prompt.

I think I figured out what it is. The filter needs to be applied to a subreport. Not the report itself. How should I reference the subreport?

Spam reported.

Here is what I've got.


DoCmd.OpenReport "rptSchedules", acViewPreview, , Reports![rptSchedules]![rptSchedulesAppointments].Report.Filter = Me.subHomeSchedules.Form.Filter


The error says it cant find rptSchedules, but it is there. If I take out the where clause, it works.

I changed the report so that the filter is not applied to a subreport. There are no errors. When I click the button while no filters are set, it works.
When I click the button while filters are set, it opens without any filters. ???

Inside the report, I set filter on load to yes.


edit:

I have messed this one up every way possible. The reason it doesn't want to work now is because I am not supposed to be using me.filter. This code should work, but says it cant find 'form'.

Private Sub Print_Click()
DoCmd.OpenReport "rptSchedulesAppointments", acViewPreview, , Me.subHomeSchedules!Form.Filter
End Sub

I think I got it.


Private Sub Print_Click()
DoCmd.OpenReport "rptSchedulesAppointments", acViewPreview, , Me.subHomeSchedules.Form.Filter
End Sub

Here is the next problem. There is one field that is on the subform that is a combo box. It is called resultID. On the report, there is a field called resultid. When I run the report with the resultID filtered, I get a prompt that says lookup_resultid.result. What did I do wrong?

I get a prompt that says lookup_resultid.result. What did I do wrong?
If this is on a report, make sure you don't use combo boxes on them as that can do wacky things.

The combo is on the form, not the report.

Then I'm not quite sure what the problem is, unfortunately. I would probably have to see the database to get this part (I'm more visual), but perhaps someone else wouldn't.

I worked around that problem by removing the field that was giving me a hard time.

hi, i'm also having difficulty with passing the filter from my Items form to an Items report.

i have checked that the relevant fields are in the report and set "Filter on load" to "yes".

when i click my button to open the report, however, it returns all items and no error message.


Dim strFilter As String

strFilter = Me.Filter

DoCmd.OpenReport "rptItems", acViewPreview, strFilter, , acDialog
i tried also placing "Me.Filter" in the filter argument directly, with the same effect.

anyone have any ideas? here's the DB if you want it (SHIFT when open FE, then open frmWAIT to automatically connect BE - note, both FE and BE must be in the same folder for this to work without our server ;)!).

thanks in advance.

Heres how I did it.

Private Sub Print_Click()
DoCmd.OpenReport "rptschedulesappointments", acViewPreview, , Me.subHomeSchedules.Form.Filter
End Sub

oh. thanks speakers - i did see your code before, but i had better look.

it appears as though instead of putting the FILTER string into the FILTER argument of the DoCmd method, it works by putting it into the WHERE argument of the method instead.

this seems to work for me as well. i just moved the filter string to the WHERE argument of the method.

huh. who'd have thunk it?

oh. thanks speakers - i did see your code before, but i had better look.

it appears as though instead of putting the FILTER string into the FILTER argument of the DoCmd method, it works by putting it into the WHERE argument of the method instead.

this seems to work for me as well. i just moved the filter string to the WHERE argument of the method.

huh. who'd have thunk it?

Well, I was going to chime in but I had to leave work when you posted. It is amazing if you hit F1 for help, it actually will tell you that :D

Well, I was going to chime in but I had to leave work when you posted. It is amazing if you hit F1 for help, it actually will tell you that :D

wow. i do normally do F1 as the first step, however, i had my "quick info" on (CTRL+I) for the method, and it had clearly delineated which argument goes where.

but silly me, of course microsoft doesn't do it literally!