Filter by form with multiple options

[JHB]

Still not working. I step through the code and it goes to Case 10 and steps through but the form is not filtering to show all records.....

Which records does it show then? Like records apply to the last filter setting or ...?
What records does it show when you open the form direct from the Navigation window?
I would create a "temporary/test" form with a button and have the code for clearing the filter on the "[Open Opportunities List] form behind it, where you can play with the code until it clears the filter correct.
Is it possible for you to make a stripped version of you database with some sample code and post the database?

 

[vbaInet]

I'm sure JHB can sort it out if you manage to upload a stripped down db.

But in the meantime, you should be turning it Off not On:

                 Case Is = 10
                     ' This is full access no restrictions required at this level
                    Forms![Open Opportunities List].Form.Filter = [COLOR="Red"]vbNullString[/COLOR]
                    Forms![Open Opportunities List].Form.FilterOn = [COLOR="red"]False[/COLOR]

 

[Indigo]

JHB,I get no records returning when I set my security level to 10. If I set it to any other level (1 through 9) the form filters correctly and returns the records for each level / case.

The other odd thing is that when I open the form directly from the Navigation pane, I get a parameter pop-up looking for the security form... I also created a copy of the form with no code behind it that I can see and when I try and open it from the navigation pane, I get that same parameter pop up.

VBA - that was a typo on my part. I caught it after I submitted my post and changed the "True" to "False". I have also changed the "" to vbNullString to no avail.

 

[JHB]

..
The other odd thing is that when I open the form directly from the Navigation pane, I get a parameter pop-up looking for the security form...

Then we need to see the SQL-string for the form's recordsource, because it think it is here the problem is.
But anyway why do you have a parameter in the query, when you filter the form afterwards?

 

[vbaInet]

Or ...............

Is it possible for you to make a stripped version of you database with some sample code and post the database?

 

[Indigo]

Oh good grief! You just pointed me in the right direction! I am not the original author of this database and would not have built it like this, but I had to work with it and have been trying to get it to work for someone else.....Now I see my problem..... the form was based on one query based on a second query that supports other forms and security level 10 was not included in the criteria..... now I know what to do. Thank you for putting up with me!

 

[JHB]

Oh good grief! You just pointed me in the right direction! I am not the original author of this database and would not have built it like this, but I had to work with it and have been trying to get it to work for someone else.....Now I see my problem..... the form was based on one query based on a second query that supports other forms and security level 10 was not included in the criteria..... now I know what to do. Thank you for putting up with me!

Good you got it a last, yeah that is the evil of queries based on other queries - it can be hard to figure out what is going wrong.