Tricky one ! multiple list boxs with multiple selections as criteria 1

[Chance1234]

firstly whjy is it thjat whjen you hjave one day left to do somethjing your keyboard decides to play up ? gfrrr anyway

what im tryingf to do is use multiple list boxes with multiple criteria to filter another list.

to make more sense, there is a picture of the form here

http://www.5ylac.s5.com/EXP/123.jpg

dont blame me on thje colour schjeme, clients choice not mine.

on the right is a list of clients, thjis is thje list i want to filter, thjis list is called "lstcustom" to thje righjt of that list is a series of othjer list boxes called crit1, crit2 etc respectivly. these lists refer to columns in the query that is beingf used as the row source for lstcustom.

Thjey are all set up as multiple select , and what i want to do is allow thje user to select whjatever values they want in thje list box then by pressingf thje apply button , thje list will filter to thjere options.

now with one listbox it isnt too bad in my code i loop thjrogh the list box to create a where statement, somethingf like this

For i = 0 To crit1.ListCount - 1
If lstCriteria.Selected(i) Then



strWhereIN = strWhereIN & " " & lstCrit1.Column(0, i) & ","

End If
Next i


strWhere = " WHERE " & [Client Sector] & " in (" & Left(strWhereIN, Len(strWhereIN) - 1) & &quot"

which i then add onto the end of "SELECT * FROM qrycustom1 " & strwhere , then i set this as the rowsource for lstcustom

no problemo !

but as you can see from the picture i have four listboxes, what i want to be able to do is set up for any possible combination , i hjad an idea of usingf tix boxes and dependingf on whjichj tickboxes are tick, addingf in the word "AND" to the SQL statement but i cant quite gfet my hjead around it

hjelp!



Chance

 

[Chance1234]

For the record i got it to work with the following code, i cheated with the where statement by already putting a where into the criteria of lstcustom on another control, so all i had to use was the AND. not the most tidest of code BUT IT WORKS!!!!! also my keyboard is now working too! lifes good.

Private Sub Command325_Click()


Dim stRsql, strwhere, strwherein As String

If chjk1.Value = True Then

stRsql = lstcustom.RowSource

stRsql = Left(stRsql, Len(stRsql) - 1)
strwherein = ""
strwhere = ""
For i = 0 To crit1.ListCount - 1
If crit1.Selected(i) Then



strwherein = strwherein & "'" & crit1.Column(0, i) & "',"

End If
Next i


strwhere = " AND qrycustom1.[Client Sector] IN (" & Left(strwherein, Len(strwherein) - 1) & ""

stRsql = stRsql & strwhere


lstcustom.RowSource = stRsql

End If

If chjk2.Value = True Then
strwherein = ""
strwhere = ""
stRsql = lstcustom.RowSource

stRsql = Left(stRsql, Len(stRsql) - 1)

For i = 0 To crit2.ListCount - 1
If crit2.Selected(i) Then



strwherein = strwherein & "'" & crit2.Column(0, i) & "',"

End If
Next i


strwhere = " AND qrycustom1.[Owner] IN (" & Left(strwherein, Len(strwherein) - 1) & ""

stRsql = stRsql & strwhere


lstcustom.RowSource = stRsql

End If
If chjk3.Value = True Then
strwherein = ""
strwhere = ""
stRsql = lstcustom.RowSource

stRsql = Left(stRsql, Len(stRsql) - 1)

For i = 0 To crit3.ListCount - 1
If crit3.Selected(i) Then



strwherein = strwherein & "'" & crit3.Column(0, i) & "',"

End If
Next i


strwhere = " AND qrycustom1.[Historic action req] IN (" & Left(strwherein, Len(strwherein) - 1) & ""

stRsql = stRsql & strwhere


lstcustom.RowSource = stRsql

End If

If chjk4.Value = True Then
strwherein = ""
strwhere = ""
stRsql = lstcustom.RowSource

stRsql = Left(stRsql, Len(stRsql) - 1)

For i = 0 To crit4.ListCount - 1
If crit4.Selected(i) Then



strwherein = strwherein & "'" & crit4.Column(0, i) & "',"

End If
Next i


strwhere = " AND qrycustom1.[Contacted by] IN (" & Left(strwherein, Len(strwherein) - 1) & ""

stRsql = stRsql & strwhere


lstcustom.RowSource = stRsql

End If



Debug.Print stRsql

lstcustom.Requery

End Sub