How can I add a filter to my form based upon a joined table's columns that don't appear in the Select clause?

Question

I have a form which displays companies and a subform which displays contacts. There are multiple contacts per company.

I have a filter which works off of a combo box, which selects companies only where they have contacts with the selected responsibility;

Sub SetFilter()    
    Dim ASQL As String        
    If IsNull(Me.cboshowcat) Then
    ' If the combo and all check boxes are Null, use the whole table as the 
    ' RecordSource.
        Me.RecordSource = "SELECT company.* FROM company"        
    Else        
        ASQL = "SELECT DISTINCTROW company.* " & _
                "FROM company INNER JOIN Contacts " & _
                    "ON company.company_id = Contacts.company_id " & _
                "WHERE Contacts.responsibility= '" & cboshowcat & "' " & _
                "ORDER BY Company.company_id"
        Me.RecordSource = ASQL        
    End If        
End Sub

I also have 3 checkboxes which further refine the records which can only be used once a job responsibility has been selected. The filter and checkboxes are ran after clicking a button;

Private Sub Command201_Click()

If Nz(Me.cboshowcat) = "" _
        And Me.Check194 = True _
    Or Nz(Me.cboshowcat) = "" _
        And Me.Check199 = True _
    Or Nz(Me.cboshowcat) = "" _
        And Me.Check205 = True _
    Then
        MsgBox "Please Select a Job Responsibility"
        Cancel = True
    Else    
        SetFilter
        If Me.Check194 = True _
            And Me.Check199 = True _
            And Me.Check205 = True _
            Then
            Me.Filter = "[contacts].[edit] <=Date()-90 " & _
                "and [contact].[opt out]='No' " & _
                "and [company].[exclude site] is null"
            Me.FilterOn = True
        Else
            If Me.Check194 = True _
                And Me.Check199 = True _
                And Me.Check205 = False _
                Then
                Me.Filter = "[contacts].[edit] <=Date()-90 " & _
                    "and [contact].[opt out]='No'"
                Me.FilterOn = True
            Else
'................(repeated for each combination)
                                    Me.Filter = ""
                                    Me.FilterOn = False
                                End If
                            End If
                        End If
                    End If
                End If
            End If
        End If
    End If
    Me.Repaint
End Sub

The above query does not work as it does not find the field name that I am referencing in the contact table. If I include the field in the filter query select statement it does work, however it shows me multiple instances of each company depending on how many contacts are returned for each company.

I need to filter the companies based on information in contact table without duplicating the company information.

If someone knows how to get around this problem I would be very grateful.

BIBD BIBD · Accepted Answer · 2011-08-17T18:33:24

First some style things:

1) You probably have less nesting if you use ElseIf instead of

Else
    If '...

2) It would be good form to re-name the check boxes something more meaningful that Check194 (at least for the next developer who touches this code - even if that is you 4 years down the line). That is, of course, assuming that this is not a contrived example to anonymise the code a little bit.

3) Like wise having space in column and table names can be a pain in the butt. Likewise "Edit" looks like a reserved word (it may not be), and can lead to heart break.

4) I'm not sure of the logic of your 1st IF statement. You may not have the order of operation may not be what you expect. It's basically the equivalent of

If Nz(Me.cboshowcat) = "" _
    And (Me.Check194 = True _
        Or Me.Check199 = True _
        Or Me.Check205 = True) _
    then

If that's what you wanted, then it is fine.

5) If you build up the filter string on the fly, you won't have to go through al eight combinations (and perhaps miss one). Imagine if you had 4, 5 or 10 check boxes. Typically, I would do something like this

dim strFilter as string

strFilter = "(1 = 1) " ' so we don't have to decide whether to put `and` or not.
If Nz(Me.cboshowcat) = "" then
    if Me.Check194 = then
        strFilter = strFilter & "and [contacts].[edit] <=Date()-90 "
    end if
    if Me.Check199 = then
        strFilter = strFilter & "and [contacts].[opt out]='No' "
    end if
    if Me.Check199 = then
        strFilter = strFilter & "and [company].[exclude site] is null "
    end if
    me.filter = strFilter
    me.filteron = true
else
    me.filter = ""
    me.filteron = false
end if

Second the solution to your problem:

The multiple rows for each company are because of the join, and the query is now forced to show a row for each contact with a different combination of values for "Edit" or "Opt out".

Since you are already updating the record source on the fly, I'd just shove the filter into the where clause of the record source and be done with it. That's effectively what the filter is doing anyway (only on the result of the query, that's why it can't see into the contacts table).

Again, build up the where clause on the fly, instead of doing 8 different combinations.

How can I add a filter to my form based upon a joined table's columns that don't appear in the Select clause?

1 Answers