Multi Select with Subform

[mtompkins]

Gents I am trying to use an unbound to filter a subform. It works rather well. I need, however to move from a single SQL condition (1 column on the multiselect) to a 2 column match on the multiselect.

The difficulty lies in my ability to pull the proper condition from the multiselect and pull from the table.

Code follows

Private Sub FilterListbox_Click()

Dim frm As Form
Dim ctl As Control
Dim varItm As Variant
Dim intI As Integer
Dim ListRow, Col, FldName, FldName2, Quote, StrOR, strWhere, SQL, strAND As String

On Error GoTo Handler
Set frm = Forms("Form1")
Set ctl = frm.List0

FldName = " (Sheet3.Field3)= "
FldName2 = " (Sheet3.Field7) = "
Quote = """"
StrOR = " or "
strAND = " and "

   For Each varItm In ctl.ItemsSelected
        For intI = 0 To ctl.ColumnCount - 1
            ListRow = ctl.Column(intI, varItm)
            If Not IsNumeric(ListRow) Then
                Col = Col & FldName & Quote & ListRow & Quote & strAND & Quote & FldName2 & Quote & StrOR
            End If
        Next intI
    Next varItm
    
strWhere = "WHERE " & Left(Col, Len(Col) - 3)
SQL = "SELECT * FROM Sheet3 " & strWhere
Me!subform.Form.RecordSource = SQL

Debug.Print strWhere ' testing only

exit_handler:
    Exit Sub
Handler:
    If Err.Number = 5 Then MsgBox "You must select a list box item before filtering the subform"
    Resume exit_handler

End Sub

Problem is, I can't get it to properly pull the AND condition which is column2 in the multiselect.

In short column0 = sheet3.field3 & column1 = sheet3.field7 should filter the resulting subform.


Please help.

Many thanks

 

[PHV]

Anyway, when mixing AND and OR operators, proper bracketting is mandatory.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mtompkins]

Hi PHV -

I'm not sure if part of your response was missed.

It starts with "anyway ..." and refers to some brocketting I may have goofed.

Any help would be appreciated.

 

[PHV]

I just said that mixing AND and OR boolean operators without parenthesis will end up with unexpected results.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886