Subform filtering

[abcrf]

I'm getting no error messages here, but I'm not getting any results visible when the filter is applied. There are 2 unbound combo boxes in the main form, SetStudy and SetCDB. The only possible values for the column [ROLE] are the 9 reflected in the code below. (The form and table for [ROLE] have the Row Source: "CEC Chair";"CEC Co-Chair";"CEC Member";"DSMB Chair";"DSMB Co-Chair";"DSMB Member";"CEC/DSMB Chair";"CEC/DSMB Co-Chair";"CEC/DSMB Member")

Private Sub SetCDB_AfterUpdate()
If Me.SetCDB.Selected(0) Then
    Me.SubR.Form.Filter = "[ROLE] = " & Chr(34) & "CEC Chair" & Chr(34) & " Or [ROLE] = " & Chr(34) & "CEC Co-Chair" & Chr(34) & " Or [ROLE] = " & Chr(34) & "CEC Member" & Chr(34) & " And [STUDY NAME] = " & Chr(34) & Me.SetStudy.Value & Chr(34)
ElseIf Me.SetCDB.Selected(1) Then
    Me.SubR.Form.Filter = "[ROLE] = " & Chr(34) & "DSMB Chair" & Chr(34) & " Or [ROLE] = " & Chr(34) & "DSMB Co-Chair" & Chr(34) & " Or [ROLE] = " & Chr(34) & "DSMB Member" & Chr(34) & " And [STUDY NAME] = " & Chr(34) & Me.SetStudy.Value & Chr(34)
ElseIf Me.SetCDB.Selected(2) Then
    Me.SubR.Form.Filter = "[ROLE] = " & Chr(34) & "CEC/DSMB Chair" & Chr(34) & " Or [ROLE] = " & Chr(34) & "CEC/DSMB Co-Chair" & Chr(34) & " Or [ROLE] = " & Chr(34) & "CEC/DSMB Member" & Chr(34) & " And [STUDY NAME] = " & Chr(34) & Me.SetStudy.Value & Chr(34)
Else
    Me.SubR.Form.Filter = "[ROLE ID] = 0"
End If
Me.SubR.Form.FilterOn = True
SubR.Requery
End Sub

Any ideas what's going wrong with the filtering?

[zmbd]

line 12: Me!SubR.Form.Refresh

you shouldn't need a new query if all you are doing is applying a filter to the form's current record-set; however,

line 12: Me!SubR.Form.Requery

[abcrf]

Thanks, Z, but that wasn't it. It's performing the same with both syntaxes.

[zmbd]

The most common issue is a malformed string which is why I always build my strings first and then set to the form or the called function.

Give me a moment to double check things....

[zmbd]

Ok, a little re-arrangement on the code

Private Sub SetCDB_AfterUpdate()
    Dim zSQL As String
    Dim zSelected as Integer
'
    zSelected = Me.setcdb.value
'
    Select Case zSelected
        Case 1
            zSQL = "[ROLE] = " & Chr(34) & "CEC Chair" & Chr(34) & _
                " Or [ROLE] = " & Chr(34) & "CEC Co-Chair" & Chr(34) & _
                " Or [ROLE] = " & Chr(34) & "CEC Member" & Chr(34) & _
                " And [STUDY NAME] = " & Chr(34) & Me.SetStudy.Value & Chr(34)
        Case 2
            zSQL = "[ROLE] = " & Chr(34) & "DSMB Chair" & Chr(34) & _
                " Or [ROLE] = " & Chr(34) & "DSMB Co-Chair" & Chr(34) & _
                " Or [ROLE] = " & Chr(34) & "DSMB Member" & Chr(34) & _
                " And [STUDY NAME] = " & Chr(34) & Me.SetStudy.Value & Chr(34)
        Case 3
            zSQL = "[ROLE] = " & Chr(34) & "CEC/DSMB Chair" & Chr(34) & _
                " Or [ROLE] = " & Chr(34) & "CEC/DSMB Co-Chair" & Chr(34) & _
                " Or [ROLE] = " & Chr(34) & "CEC/DSMB Member" & _
                Chr(34) & " And [STUDY NAME] = " & Chr(34) & Me.SetStudy.Value & Chr(34)
         Case Else
            zSQL = "[ROLE ID] = 0"
    End Select
'
Debug.Print zSQL
'
    Me!SubR.Form.Filter = zSQL
'
    Me!SubR.Form.FilterOn = True
'
    Me!SubR.Form.Refresh
'    Me!SubR.Requery
End Sub

Open the VBE, <CTRL><G> to open the immediate panel

Run your form

Check the immediate panel for properly formatted string for the filter

((another option along the line of the OP code))

Private Sub SetCDB_AfterUpdate()
    Dim zSQL As String
'
    Select Case  Me.setcdb.ListIndex
        Case 0
            zSQL = "[ROLE] = " & Chr(34) & "CEC Chair" & Chr(34) & _
                " Or [ROLE] = " & Chr(34) & "CEC Co-Chair" & Chr(34) & _
                " Or [ROLE] = " & Chr(34) & "CEC Member" & Chr(34) & _
                " And [STUDY NAME] = " & Chr(34) & Me.SetStudy.Value & Chr(34)
        Case 1
            zSQL = "[ROLE] = " & Chr(34) & "DSMB Chair" & Chr(34) & _
                " Or [ROLE] = " & Chr(34) & "DSMB Co-Chair" & Chr(34) & _
                " Or [ROLE] = " & Chr(34) & "DSMB Member" & Chr(34) & _
                " And [STUDY NAME] = " & Chr(34) & Me.SetStudy.Value & Chr(34)
        Case 2
            zSQL = "[ROLE] = " & Chr(34) & "CEC/DSMB Chair" & Chr(34) & _
                " Or [ROLE] = " & Chr(34) & "CEC/DSMB Co-Chair" & Chr(34) & _
                " Or [ROLE] = " & Chr(34) & "CEC/DSMB Member" & _
                Chr(34) & " And [STUDY NAME] = " & Chr(34) & Me.SetStudy.Value & Chr(34)
         Case Else
            zSQL = "[ROLE ID] = 0"
    End Select
'
Debug.Print zSQL
'
    Me!SubR.Form.Filter = zSQL
'
    Me!SubR.Form.FilterOn = True
'
    Me!SubR.Form.Refresh
'    Me!SubR.Requery
End Sub

[abcrf]

I'm getting an "argument not optional" error for .Selected using the "Case" structure above, but I'll try using that string technique with my original code.

[abcrf]

Interesting result, though it fits with the initial problem.

Private Sub SetCDB_AfterUpdate()
Dim zSQL As String
Forms!FRM_ROLES!SubR!SubStudy.DefaultValue = Chr(34) & Me.SetStudy.Value & Chr(34)
If Me.SetCDB.Selected(0) Then
    zSQL = "[ROLE] = " & Chr(34) & "CEC Chair" & Chr(34) & " Or [ROLE] = " & Chr(34) & "CEC Co-Chair" & Chr(34) & " Or [ROLE] = " & Chr(34) & "CEC Member" & Chr(34) & " And [STUDY NAME] = " & Chr(34) & Me.SetStudy.Value & Chr(34)
ElseIf Me.SetCDB.Selected(1) Then
    zSQL = "[ROLE] = " & Chr(34) & "DSMB Chair" & Chr(34) & " Or [ROLE] = " & Chr(34) & "DSMB Co-Chair" & Chr(34) & " Or [ROLE] = " & Chr(34) & "DSMB Member" & Chr(34) & " And [STUDY NAME] = " & Chr(34) & Me.SetStudy.Value & Chr(34)
ElseIf Me.SetCDB.Selected(2) Then
    zSQL = "[ROLE] = " & Chr(34) & "CEC/DSMB Chair" & Chr(34) & " Or [ROLE] = " & Chr(34) & "CEC/DSMB Co-Chair" & Chr(34) & " Or [ROLE] = " & Chr(34) & "CEC/DSMB Member" & Chr(34) & " And [STUDY NAME] = " & Chr(34) & Me.SetStudy.Value & Chr(34)
Else
    zSQL = "[ROLE ID] = 0"
End If
Debug.Print zSQL
Me!SubR.Form.Filter = zSQL
Me!SubR.Form.FilterOn = True
Me!SubR.Form.Refresh
End Sub

Gives the response:

[ROLE ID] = 0

[zmbd]

I was afraid of that... I've not used the selected property like that before... I'll revise the post

The selected property is usually used to set a selection in a list box.

I've re-written the code to use the value of the cbox instead; however, this is assuming that the cbox has the bound column set so that the first selection returns 1, the second 2, etc...

[abcrf]

Turns out I was using the wrong syntax for .Selected; it needs to be in the format Me.SetCDB.Selected(0) = True.

But with it corrected in the code above, I get the same string printed, so maybe that's not the location of the error?

[zmbd]

The selected property isn't used to retrieve the row selected, it is used to set the selected row.
(This will not however, set the value of the cbox)

Please see the revised code in Post#5

Two options
First Option
The revision now will return the value of the bound column for the cbox in Line 5 and the compare against the select...case starting on line 7.

Of course, you may have to modify the Case comparisons based upon what the actual return is for the cbox's bound column.

Second Option:
Just thought of this: We could potentially us the listindex property... and this more closely matches your original logic. Not really how I would approach the use of a combobox...

you got me "out of the box" on this one ;-)

[abcrf]

The listindex approach works, thanks! Of course, I also needed to change the syntax of the filter to include parentheses as per below.

zSQL = "([ROLE] = " & Chr(34) & "CEC Chair" & Chr(34) & _
            " OR [ROLE] = " & Chr(34) & "CEC Co-Chair" & Chr(34) & _
            " OR [ROLE] = " & Chr(34) & "CEC Member" & Chr(34) & _
            ") AND [STUDY NAME] = " & Chr(34) & Me.SetStudy.Value & Chr(34)

[zmbd]

good deal, looks like we solved two potential snags here :)

Like I said earlier, using the cbo.ListIndex isn't the most common method employed with this control, I'd actually forgotten that it was available. The most common usage I've ran across is to help the end-user select the appropriate value from a related table for the record at hand by showing the human-friendly text in the list/box while actually setting the related foreign/primary-key based on the row-source "behind the scenes."

[NeoPa]

Hi there. This isn't directly related to the question but simply some advice about putting together strings which contain quotes in them. The existing solution is all good.

However, using quotes needn't be so complicated. Chr() should never be required, even when using quotes that aren't the most appropriate (See Quotes (') and Double-Quotes (") - Where and When to use them). As we're looking at strings for SQL here (Whether a full SQL string or just a filter in SQL format is immaterial.) we would probably prefer to use the quote character ('). This can be written out simply within a VBA string that uses double-quotes (") without any need for Chr().

Whenever we need to use quote characters of the same style as the string they're enclosed within though, we can simply double them up to be interpreted properly. This is a coding convention that goes back many decades and is supported consistently across all languages I've ever used that I recall (Some I last used many decades ago myself so may not recall all such details).

Thus, using option A can be always be replaced by option B :

strName = "Fairholme"
 
A.  strExample = "Their house was named " & Chr(34) & strName & Chr(34) & "."
B.  strExample = "Their house was named """ & strName & """."

My personal preference, for easiest readability and interpretation, would be to use Replace() as in :

C.  strExample = Replace("Their house was named ""%N"".", "%N", strName)

With very little imagination or understanding you can see what it is the code is trying to achieve. It does have a function call in the code, but not within the text you're trying to formulate. There it simply has a replacement token which is easy to process visually for easy understanding.