Apply Filter By Form to Report (1 Viewer)

feddev · May 17, 2017

Hello,

Having some issues and hoping you folks can help me out!

Problem- Users have a single table, single form, single report access 2013 database and they are using the built in Sort and Filter options to filter their form.

Advanced>>Filter by Form is the specific option they are using. Once they filter the form on a Vendor they would like when they open the report for that sample filter to be applied to it.

If i set the Filter On Load option to Yes...what do i want to put in the filter field to make it reference the filter that was just applied to the form?

Or should i be using some kind of OnLoad event on the report?

static · May 17, 2017

You would normally open a report from a button on a form

Code:

Private Sub cmdOpenreport_Click()
    Dim fltr As String
    If Me.FilterOn Then fltr = Me.Filter
    DoCmd.OpenReport "table1", acViewPreview, , fltr
End Sub

feddev · May 18, 2017

the users don't want a button unfortunately so could i use this in an on load event for the report?

isladogs · May 18, 2017

feddev said:
the users don't want a button unfortunately so could i use this in an on load event for the report?

You may not be able to change the report record source after you open it.

If there's no button, how are they going to run the report?
Do you really want users going into it from the navigation pane?

feddev · May 18, 2017

They want to open it from the navigation pane. I've tried talking them into something else but its what they want.

isladogs · May 18, 2017

feddev said:
They want to open it from the navigation pane. I've tried talking them into something else but its what they want.

Well it won't be long before someone alters or deletes something!

Just tell them the report can only be filtered by using code from a form button.
Not actually true but it may convince them!

feddev · May 18, 2017

So how can i do this on the onLoad event? I'm not going to have this debate with you here i've already lost it with the users.

isladogs · May 18, 2017

Try it in report load but it probably won't work

Otherwise possibly open the report HIDDEN with the filter applied on the form.
Set the report properties to filter on = true
Then open the report normally from the nav. pane and see if the filter 'sticks'

I can't test this at the moment as I'm typing on a tablet without Access
If I'm talking nonsense then apologies

feddev · May 18, 2017

makes sense let me give it a try thank you

feddev · May 22, 2017

I got them to break with adding the button yay!

Now they showed me how they filter by FORM and at the bottom of the form in this "mode" there are tabs where they can add OR conditions to the filter. Which is basically opening another Form and allowing them to filter on it again.

Here is the code behind the button i added....any ideas how i can make this work?

Code:

Private Sub Command1649_Click()
    DoCmd.OpenReport "Landscape Data Security Provision Review", acViewReport, , Form.Filter
End Sub

isladogs · May 22, 2017

Great.
So just add the filters to the form BEFORE you open the report:

e.g. Dim strCriteria As String
strCriteria = "whatever you want it to be"

In fact you can have different criteria for different purposes if you want

THEN open the filtered report using your command button
Personally, I'd use acViewPreview as below

Code:

Private Sub Command1649_Click()

    DoCmd.OpenReport "Landscape Data Security Provision Review", acViewPreview, , strCriteria
End Sub

I'd also recommend you give the button a meaningful name!

feddev · May 23, 2017

So this is working great. My issue was i had some code left on the report that i forgot to remove that was causing me all my pain. Removed it and now everything is working great.

Thank you for all your help!

Apply Filter By Form to Report (1 Viewer)

feddev

New member

static

Registered User.

feddev

New member

isladogs

MVP / VIP

feddev

New member

isladogs

MVP / VIP

feddev

New member

isladogs

MVP / VIP

feddev

New member

feddev

New member

isladogs

MVP / VIP

feddev

New member

Similar threads

Users who are viewing this thread