Multiple search criteria on one junction table field

**INJacqui** · Jun 28 '18, 01:47 PM

OK...I used your suggestions to build individual queries outside of VBA. I believe I know understand what you are doing with the COUNT function. I've got some good ideas of how to move forward.

I very much appreciate all the help! I'll get back to you and let you know if I'm able to get it working or if I need more help.

Thanks again.

**Rabbit** · Jun 28 '18, 04:37 PM

One thing you should be aware of is that twinny's code works by filtering the form. Which takes the dynamically created SQL and plugs it into the filter property of the form. The form will then parse the SQL in that property.

This does not work if you want to plug that string into a query. To get it to work in a query, you would have to either have the code modify the query definition directly or switch the filtering criteria from using the IN operator to using the LIKE operator.

**INJacqui** · Jun 29 '18, 01:44 PM

Rabbit - thanks for the clarification. I thought that was what it was doing, but it's always good to get confirmation.

I am using twinny's code exactly as written, and it runs until line 36

.FilterOn = True

Then I get the following error:
Run-time error '2176'
The setting for this property is too long.

Should strFilter set in line 106 actually be the list of PartIDs that are the results from the SQL statement? Right now, when I debug, strFilter is st to the SQL statement built from lines 93 - 104.

I've read and re-read the code trying to figure out what might be wrong, but I'm at a loss.

Thanks

**twinnyfo** · Jun 29 '18, 02:05 PM

I guess the SQL is too long to include as a filter string. I guess you could open a record set using the SQL statement then build the filter that way.

**NeoPa** · Jun 29 '18, 03:31 PM

Hi. Are you sure you have the correct line reported, as that one is pretty simple and straightforward .

If, as I suspect, it's actually the line setting the .Filter property, then could you post the actual value here for us to review for you please.

**Rabbit** · Jun 29 '18, 04:22 PM

It's possible it doesn't try to parse it until you try to turn the filter on.

If there's an upper limit on the length of the filter property, then you may have to return the search results a different way, such as by using a query and opening that up in a different form or requerying a subform.

**INJacqui** · Jul 2 '18, 01:28 PM

Sorry, I've been away from the project for a couple of days.

Here's the code that I copied from Twinny's suggestion. Again, it runs until I apply the filter.

Code:

Option Compare Database
Option Explicit

Private strFilter As String

Private Sub lstEngineType_AfterUpdate()
On Error GoTo EH

    strFilter = ""
    
    CheckListBoxes
    
    With Me
        .Filter = strFilter
        .FilterOn = True
    End With
    
    Exit Sub

EH:
    MsgBox "There was an error with the list box."
    Exit Sub
    
End Sub

Private Sub lstModelYear_AfterUpdate()
On Error GoTo EH
    
    strFilter = ""
    
    CheckListBoxes
    
    With Me
        .Filter = strFilter
        .FilterOn = True
    End With
    
    Exit Sub

EH:
    MsgBox "There was an error with the list box."
    Exit Sub

End Sub


Private Sub CheckListBoxes()
On Error GoTo EH

    Dim db As Database
    Dim rsResults As Recordset

    Dim frm As Form
    Dim ctl As Control
    Dim varItem As Variant
    Dim strCats As String
    Dim strCats2 As String
    Dim intX As Integer
    Dim strSQL As String
    Dim strSQL_ET As String
    Dim strSQL_MY
    
    
    
' Engine Type
    Set frm = Forms!frm_BuildCart_Bytes2
    Set ctl = frm!lstEngineType
    intX = 0
    
    For Each varItem In ctl.ItemsSelected
        intX = intX + 1
        If strCats = "" Then
            strCats = ctl.ItemData(varItem)
        Else
            strCats = strCats & ", " & ctl.ItemData(varItem)
        End If
    Next varItem
    
    strSQL = "SELECT tbl_Parts.PartID " & _
        "FROM (SELECT join_ParttoTag.PartID, " & _
        "Count(join_ParttoTag.PartID) " & _
        "FROM join_ParttoTag " & _
        "WHERE join_ParttoTag.PartID " & _
        "IN (" & strCats & ")) " & _
        "GROUP BY join_ParttoTag.PartID " & _
        "HAVING Count(join_ParttoTag.PartID)=" & intX & ") AS Cat1 "
            
' Model Year
    Set ctl = frm!lstModelYear
    intX = 0
    
    For Each varItem In ctl.ItemsSelected
        intX = intX + 1
        If strFilter = "" Then
            strFilter = ctl.ItemData(varItem)
        Else
            strFilter = strFilter & ", " & ctl.ItemData(varItem)
        End If
    Next varItem
    Set ctl = Nothing
    Set frm = Nothing
    
    
    strSQL = strSQL & _
        "INNER JOIN " & _
        "((SELECT join_ParttoTag.PartID, " & _
        "Count(join_ParttoTag.PartID) " & _
        "FROM join_ParttoTag " & _
        "WHERE join_ParttoTag.PartID " & _
        "IN (" & strCats & ")) " & _
        "GROUP BY join_ParttoTag.PartID " & _
        "HAVING Count(join_ParttoTag.PartID)=" & intX & ") AS Cat2 " & _
        "INNER JOIN tbl_Parts " & _
        "ON Cat2 = tbl_Parts.PartID) " & _
        "ON Cat1.PartID = tbl_Parts.PartID;"
    
        strFilter = "PartID IN (" & strSQL & ")"
    
    Exit Sub

EH:
    MsgBox "There was an error building the filter."
    Exit Sub

End Sub

Here's snip of the error.

One question I have is about the strCats and strFilter.
When building the first strSQL, we are using strCats to store the selected items from the list as well as for the IN statement. However, when building the second strSQL, we are using strFilter to store the selected items from the list but strCats for the IN statement. Can you help me understand what I'm missing? I would think that we would have to either use strFilter for the IN statement or strCats to store the selected items.

Thanks!

**twinnyfo** · Jul 2 '18, 03:18 PM

Use this as your new CheckListBoxes Sub:

Please note there are a few changes. We now use the SQL statement to open a recordset and build the filter itself.

Code:

Private Sub CheckListBoxes()
On Error GoTo EH
    Dim strETs      As String
    Dim strMYs      As String
    Dim frm         As Form
    Dim ctl         As Control
    Dim intX        As Integer
    Dim varItem     As Variant
    Dim strSQL      As String
    Dim db          As DAO.Database
    Dim rst         As DAO.Recordset
    Dim strPartIDs  As String
 
    strETs = ""
    strMYs = ""

' Engine Type
    Set frm = Forms!frm_BuildCart_Bytes2
    Set ctl = frm!lstEngineType
    intX = 0
 
    For Each varItem In ctl.ItemsSelected
        intX = intX + 1
        If strETs = "" Then
            strETs = ctl.ItemData(varItem)
        Else
            strETs = strETs & ", " & ctl.ItemData(varItem)
        End If
    Next varItem

    strSQL = "SELECT tbl_Parts.PartID " & _
        "FROM (SELECT join_ParttoTag.PartID, " & _
        "Count(join_ParttoTag.PartID) " & _
        "FROM join_ParttoTag " & _
        "WHERE join_ParttoTag.PartID " & _
        "IN (" & strETs & ")) " & _
        "GROUP BY join_ParttoTag.PartID " & _
        "HAVING Count(join_ParttoTag.PartID)=" & intX & ") AS Cat1 "

' Model Year
    Set ctl = frm!lstModelYear
    intX = 0
 
    For Each varItem In ctl.ItemsSelected
        intX = intX + 1
        If strMYs = "" Then
            strMYs = ctl.ItemData(varItem)
        Else
            strMYs = strMYs & ", " & ctl.ItemData(varItem)
        End If
    Next varItem
    Set ctl = Nothing
    Set frm = Nothing

    strSQL = strSQL & _
        "INNER JOIN " & _
        "((SELECT join_ParttoTag.PartID, " & _
        "Count(join_ParttoTag.PartID) " & _
        "FROM join_ParttoTag " & _
        "WHERE join_ParttoTag.PartID " & _
        "IN (" & strMYs & ")) " & _
        "GROUP BY join_ParttoTag.PartID " & _
        "HAVING Count(join_ParttoTag.PartID)=" & intX & ") AS Cat2 " & _
        "INNER JOIN tbl_Parts " & _
        "ON Cat2 = tbl_Parts.PartID) " & _
        "ON Cat1.PartID = tbl_Parts.PartID;"

    Debug.Print strSQL

    strFilter = ""
    Set db = CurrentDb()
    Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
    With rst
        If ((Not .BOF) And (Not .EOF)) Then
            .MoveFirst
            Do While Not .EOF
                If strFilter = "" Then
                    strFilter = !PartID
                Else
                    strFilter = strFilter & ", " & !PartID
                End If
                .MoveNext
            Loop
        End If
        .Close
    End With

    Debug.Print strFilter

    Exit Sub
EH:
    MsgBox "There was an error building the filter!" & vbCrLf & vbCrLf & _
        "Error: " & Err.Number & vbCrLf & _
        "Description: " & Err.Description & vbCrLf & vbCrLf & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Sub
End Sub

Hope this hepps!

**INJacqui** · Jul 4 '18, 03:39 PM

Thanks for the new approach. I'm still having some trouble with the SQL statement, but I'm working through it a bit at a time. I guess it's about time for me to really understand SQL :)

**twinnyfo** · Jul 5 '18, 10:28 AM

What "troubles" are you having with the SQL statement?

That is very vague and we can't offer any advice if we don't know the issue. Is there an error popping up? Or, do you just not understand the approach?

**INJacqui** · Jul 5 '18, 12:48 PM

Hey, twinnyfo,

I was trying to see if I could psych it out first. I really am trying to improve my SQL skills. I've put it off long enough :)

Anyway, the error I am receiving is

Run-time error 3075:
Extra ) in query expression 'Count(join_Par ttoTag.PartID)= 1) AS Cat1
INNER JOIN ((SELECT join_ParttoTag. PartID, Count(join_Part toTag.PartID) FROM join_ParttoTag WHERE join_ParttoTag. PartID IN ()) "GROUP BY join_ParttoTag. PartID HAVING Count(join_Part toTag.PartID)=0 ) AS Cat2 INNER JOIN tbl_Parts ON Cat2 = tbl_Parts.PartI D) ON Cat1.PartID = tbl_Parts.PartI D;

When I debug, the error is thrown on this line:
Set rst = db.OpenRecordse t(strSQL, dbOpenDynaset)

This happens no matter if I have items selected in both lists or only in one or the other.

Any insight would be greatly appreciated. I've tried to parse out the subqueries and figure out where the extra ) is, but I've had no luck.

Thanks!

**twinnyfo** · Jul 5 '18, 12:51 PM

So, that is officailly called, in Access-speak, "fat-fingering", which often happens when we, ourselves, don't have your database to play with.

Let me take a look at the SQL we created and see if I can't find that extra ")".

**twinnyfo** · Jul 5 '18, 12:55 PM

Line 38:

Code:

        "HAVING (Count(join_ParttoTag.PartID)=" & intX & ") AS Cat1 "

Line 63:

Code:

        "HAVING (Count(join_ParttoTag.PartID)=" & intX & ") AS Cat2 "

Let me know how that works for you!

**NeoPa** · Jul 5 '18, 01:23 PM

This is why it's so important when building SQL using VBA to put it into a string and use Dubug.Print on that string while you're working on it. Twinny does this in line #68 - showing he's at least journeyman level (If not expert) - so you're well covered.

Posting the value from line #68 would have been the better idea, but I can assure you he found and fixed the problem anyway ;-) Working remotely on another's database is always very difficult as you can't see the info you need directly.

**INJacqui** · Jul 5 '18, 01:26 PM

AHA! I should have caught that one. However, now we've got:

Run-time error 3075:

Syntax error (missing operator) in query expression '(Count(join_Pa rttoTag.PartID) =1) AS Cat1
INNER JOIN ((SELECT join_ParttoTag. PartID, Count(join_Part toTag.PartID) FROM join_ParttoTag WHERE join_ParttoTag. PartID IN ()) "GROUP BY join_ParttoTag. PartID HAVING (Count(join_Par ttoTag.PartID)= 1) AS Cat2 INNER JOIN tbl_Parts ON Cat2 = tbl_Parts.PartI D) ON Cat1.PartID = tbl_Parts.PartI D;

Multiple search criteria on one junction table field

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment