Filter by form with multiple options

[Indigo]

I am wondering if this is possible as I can't seem to get it to work.

I am using Select Case to filter a form on open and it works fine when I have this:

 Case Is = 2
         Forms![Open Opportunities List].Form.Filter = "[Employee] = 4"
         Forms![Open Opportunities List].Form.FilterOn = True

But if I want the form to open filtering multiple employee records, like this:

 Case Is = 6
         Forms![Open Opportunities List].Form.Filter = "[Employee] = 2 & " And 9 & " And 10 & " And 11
         Forms![Open Opportunities List].Form.FilterOn = True

I get a type mismatch error and I am stumped as to why and how to overcome. Any advice? Please?

 

[JHB]

First, using "AND" is wrong, because you can't have an Employee there are 2, 9, 10 etc. at the same time, so use "OR" instead.
Next, you need to tell what should be 9, 10 etc., like you do in the first part of the criteria.
I think you've mix up something, then when you want to use hardcoded values in the criteria, there are no need for using "&".

Forms![Open  Opportunities List].Form.Filter = "[Employee] =2 OR [Employee] =9 OR  [Employee] =10 OR [Employee] =11"

"&" is used when you want to insert values from variables in the criteria.

Forms![Open Opportunities List].Form.Filter = "[Employee] =" Variable1 & " OR [Employee] = " & Variable2 & " OR [Employee] =" & Variable3 & " OR [Employee] =" & Variable4

 

[vbaInet]

Can also:

Forms![Open Opportunities List].Form.Filter = "[Employee] [COLOR="blue"]IN (2,9,10,11)[/COLOR]"

 

[Indigo]

Thank you so much! That was very helpful.

 

[Indigo]

Wondering if we can re-visit this as I have run into a snag and I can't quite figure out why this is happening. I am opening the form by the Select Case method as a way of enforcing Security for users. However, there are some users that would require full access to all available records but when I don't have a Case I get no records. I have also tried to set the filter to all records (listing all employees) and I still get no records.

Any troubleshooting advice / suggestions would be appreciated.

Thank you :banghead:

 

[vbaInet]

However, there are some users that would require full access to all available records but when I don't have a Case I get no records.

I don't understand what you mean by this.

I have also tried to set the filter to all records (listing all employees) and I still get no records.

If it's all records then no filter should be applied right?

 

[Indigo]

Sorry I wasn't clear. I had been working on it for 2 hours straight and was over tired when I wrote this. Basically I have 10 cases. Case 1 through 9 have the filter turned for employees. Case 10 is for management has no filer applied but no records are returned. It's very strange.

 

[JHB]

Do you set the filter to "" for Case 10?

Forms![Open Opportunities List].Form.Filter = ""

 

[vbaInet]

... and you turn it off too.

 

[Indigo]

Sigh... no, still not working. Thinking I will re-build the form because I just can't figure this one out.....

 

[JHB]

Show the code you've in the procedure/sub where the Cases is in.
But are you sure that Case 10 gets "fired", have you stepped through your code?
Another approach is to post a stripped version of your database with some sample here, (zip it) + info in where the Cases statement are.

 

[Indigo]

JHB, thank you for your reply. I have been digging deeper and stepping through my code. I am stumped. I have a Security form that opens "hidden" with an AutoExec macro. On it is a security level text box that is populated based on the user login ID. When the Security form opens, I have code that opens another form and a sub that filters the form based on the users security level as follows:

Sub Security()
     Dim dbObject As DAO.Database
    Dim NetworkRS As DAO.Recordset
    Dim HoldUserLevelAssignment As Integer
    Dim HoldWindowsLogin As String
    Dim strQuery As String
     HoldUserLevelAssignment = Forms!frmSecurity!SecurityLevel
     Set dbObject = CurrentDb
     strQuery = "SELECT * FROM Network;"
         Set NetworkRS = dbObject.OpenRecordset(strQuery)
           With NetworkRS
                .MoveFirst
                Do While Not .EOF
                        Select Case HoldUserLevelAssignment

                         Case Is = 1
                            Forms![Open Opportunities List].Form.Filter = "[Sales Representative] = 'John Doe' OR [Sales Representative] = 'Jan Doe'"
                            Forms![Open Opportunities List].Form.FilterOn = True
                            Forms![Open Opportunities List].ProposalNumber.Enabled = False
                            Forms![Open Opportunities List].Customer.Enabled = False
                            Forms![Open Opportunities List].CountryCode.Enabled = False
                            Forms![Open Opportunities List].Employee.Enabled = False
                            Forms![Open Opportunities List].Title.Enabled = False
                            Forms![Open Opportunities List].Category.Enabled = False
                            Forms![Open Opportunities List].[Est Revenue].Enabled = False
                            Forms![Open Opportunities List].[Forecast Value].Enabled = False
                            Forms![Open Opportunities List].[Open Date].Enabled = False
                            Forms![Open Opportunities List].[How Found].Enabled = False
                            Forms![Open Opportunities List].Description.Enabled = False
                            Forms![Open Opportunities List].cmdUpload.Enabled = False
  
 ....
  
                         Case Is = 10
                            ' This is full access no restrictions required at this level
                            Forms![Open Opportunities List].Form.Filter = ""
                            Forms![Open Opportunities List].Form.FilterOn = False
 
                           Exit Sub
                    End Select
                .MoveNext
            Loop
        End With
 End Sub

The form filters fine for Cases 1 - 9 but does not for 10 and I cannot figure out why...

 

[JHB]

You didn't tell if your code goes into case 10?
Else your code looks okay.

 

[Indigo]

Well, this is weird, if I switch Case 10 and Case 1 and try to open giving myself level 1 instead of 10, it opens as if it was case 10 (i.e. filtered for John and Jane Doe) but having switched them, it should be giving me all....

I'm confused.

 

[vbaInet]

Why the loop?

 

[Indigo]

No need? Sorry, always did it that way.... But now I think I see the lack of logic.

 

[vbaInet]

The thing is you're looping through a recordset without even using it so best to get rid of it, sort out the code and re-run your test.

 

[Indigo]

Okay, simplified the code as follows:

Sub Security()
     Dim HoldUserLevelAssignment As Integer
     HoldUserLevelAssignment = Forms!frmSecurity!SecurityLevel
             Select Case HoldUserLevelAssignment
                 Case Is = 1
                    Forms![Open Opportunities List].Form.Filter = "[Sales Representative] = 'John Doe' OR [Sales Representative] = 'Jane Doe'"
                    Forms![Open Opportunities List].Form.FilterOn = True
  
 ...
  
                 Case Is = 10
                     ' This is full access no restrictions required at this level
                    Forms![Open Opportunities List].Form.Filter = ""
                    Forms![Open Opportunities List].Form.FilterOn = True
             End Select
 End Sub

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

 

[vbaInet]

You're running the code from the same form right?

 

[Indigo]

I have a Security form that opens "hidden" with an AutoExec macro. On it is a security level text box that is populated based on the user login ID. When the Security form opens, I have code that opens the [Open Opportunities List] form and this sub that filters the form based on the users security level.