Filter a query based on the multiple values of 3 list boxes in a split form

**New To Access** · 10-23-2019, 05:31 AM

Hi all,

I have been looking for a solution that would make this work but i can't seem to get this to work.

I have a split form named Interface_frm, in the split form i have 3 list boxes (list97, list99 and list101) via wich i want to filter a query (All_Documents_Qry).
After my selection in the list boxes the query should get filtered and show up on the bottom half of my split form. When i make no selection in the list boxes i need to get all the records and not a warning messag or pop-up etc.

List97 filters on Service, List99 filters on Doc Type and List101 filters on System.

i made a button (command103) with an event on click with this code behind it:

Private Sub Command103_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("All_Documents_Qry")
For Each varItem In Me!List97.ItemsSelected
strCriteria = strCriteria & ",'" & Me!List97.ItemData(varItem) & "'"
Next varItem
For Each varItem In Me!List99.ItemsSelected
strCriteria = strCriteria & ",'" & Me!List97.ItemData(varItem) & "'"
Next varItem
For Each varItem In Me!List101.ItemsSelected
strCriteria = strCriteria & ",'" & Me!List97.ItemData(varItem) & "'"
Next varItem
If Me!List97.ItemsSelected.Count > 0 Then
For Each varItem In Me!List97.ItemsSelected
strCriteria = strCriteria & ",'" & Me!List97.ItemData(varItem) & "'"
Next varItem
Else
strCriteria = "All_Documents_tbl.Service Like '*'"
End If
If Me!List99.ItemsSelected.Count > 0 Then
For Each varItem In Me!List99.ItemsSelected
strCriteria = strCriteria & ",'" & Me!List99.ItemData(varItem) & "'"
Next varItem
Else
strCriteria = "All_Documents_tbl.Doc Type Like '*'"
End If
If Me!List101.ItemsSelected.Count > 0 Then
For Each varItem In Me!List101.ItemsSelected
strCriteria = strCriteria & ",'" & Me!List101.ItemData(varItem) & "'"
Next varItem
Else

strCriteria = "All_Documents_tbl.System Like '*'"
End If
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
strSQL = "SELECT * FROM All_Documents_Tbl " & _
"WHERE All_Documents_Tbl.Service IN(" & strCriteria & ");"
qdf.SQL = strSQL
DoCmd.OpenQuery "All_Documents_Qry"
Set db = Nothing
Set qdf = Nothing
End Sub

I puzzled this together via things i found online but must have done something wrong. If anybody could help me it would realy mean a lot!

Hope to here from you guys soon. I could not find anything this specific so started this new thread.

**CJ_London** · 10-23-2019, 06:30 AM

Don't want to start responding to your first post with a complaint but really difficult to read your code without indentation. Please repost and surround with the code tags to preserve formatting (highlight your pasted code and click the # button)

**New To Access** · 10-23-2019, 06:44 AM

Hey Ajax,

Sorry about that.

I also was looking some more and changed the code to what i have below (still working on it). This seemed to work better as i did not get any errors, i also can see all the records of my query in the split form but the filter is not doing anything ...

Code:

Private Sub Command103_Click()
Dim varItem As Variant
Dim strSearch As String
Dim Task As String


Me.FilterOn = True
For Each varItem In Me!List97.ItemsSelected
    strSearch = sttrsearch & "," & Me!List97.ItemData(varItem)
Next varItem
If Len(strSearch) = O Then
    Task = "select * from All_Documents_Qry"
Else
    strSearch = Right(strSearch, Len(strSearch) - 1)
    Task = "select * from All_Documents_Qry where ([Service] in (" & strSearch & "))"
End If
    DoCmd.ApplyFilter Task
End Sub

**CJ_London** · 10-23-2019, 07:30 AM

This seemed to work better as i did not get any errors

suggest you put Option Explicit at the top of each module, just below Option Compare Database. Then it will catch errors such as this when you debug>compile the code

strSearch = sttrsearch & "," & Me!List97.ItemData(varItem)

Also get into the habit of using debug.print until you have the code working properly - would make it obvious why this is wrong

Code:

strSearch = Right(strSearch, Len(strSearch) - 1)
debug.print strSearch

and finally

Code:

    Task = "select * from All_Documents_Qry where ([Service] in (" & strSearch & "))"
End If
    DoCmd.ApplyFilter Task

you are applying a whole select query, not just the query part

either use

Task = "[Service] in (" & strSearch & ")"

or apply the whole Task to the form recordsource

Code:

    Task = "select * from All_Documents_Qry where ([Service] in (" & strSearch & "))"
End If
    me.recordsource= Task

With regards building your strSearch - your code assumes these are numbers. if they are text then you do need to surround each value with single quotes

Not clear if these errors are genuine or you have introduced them by free typed your code rather than copy pasting. There is little point in free typing code - it takes longer than copy/paste and you run the risk of introducing non existent errors - or hiding an error because you have subconsciously corrected it

Some more tips:

give your controls meaningful names early on 'Me!List97' could mean anything and trust me, one month down the track you will have forgotten and will spend time working it all out again
Being new to access you are probably not familiar with the terms early and late binding. Using a ! does two things
1. it means the control is late bound, so the code will not error until it is executed (i.e. compile will not pick it if you have typed 'List97' as 'List9') and
2. it does not make use of intellisense.
3. Using a dot (me.list97) uses intellisense (a bit like fill as you go with a combobox) so much harder to make a mistake and is early bound, so if there is something wrong about it, it will be picked up on the compile

**New To Access** · 10-23-2019, 08:52 AM

Thanks Ajax!

I feel like i'm making good progress. The query however is still not filtering, i will paste the full code i am using. In the query criteria i also entered [fORM]![Interface_frm]!
[List_Service] and this for all three list boxes.

Code:

Option Compare DatabaseOption Explicit
Sub Search()
Dim varItem As Variant
Dim strSearch As String
Dim Task As String


Me.FilterOn = True
For Each varItem In Me.List_Service.ItemsSelected
    strSearch = strSearch & "," & Me.List_Service.ItemData(varItem)
Next varItem
For Each varItem In Me.List_Doc_Type.ItemsSelected
    strSearch = strSearch & "," & Me.List_Doc_Type.ItemData(varItem)
Next varItem
For Each varItem In Me.List_System.ItemsSelected
    strSearch = strSearch & "," & Me.List_System.ItemData(varItem)
Next varItem
If Len(strSearch) = 0 Then
    Task = "select * from All_Documents_Qry"
Else
    strSearch = Right(strSearch, Len(strSearch) - 1)
    Debug.Print strSearch
    Task = "select * from All_Documents_Qry where ([Service] in (" & strSearch & "))"
End If
    Me.RecordSource = Task
End Sub


Private Sub Command104_Click()
Dim varItem As Variant
Dim strSearch As String
Dim Task As String


If List_Service = 0 Then
    List_Service = Null
    
Else


For Each varItem In Me.List_Service.ItemsSelected
    List_Service.Selected(varItem) = False
Next varItem


    Task = "select * from All_Documents_Qry"
    DoCmd.ApplyFilter Task
End If


If List_Doc_Type = 0 Then
    List_Doc_Type = Null
    
Else


For Each varItem In Me.List_Doc_Type.ItemsSelected
    List_Doc_Type.Selected(varItem) = False
Next varItem


    Task = "select * from All_Documents_Qry"
    DoCmd.ApplyFilter Task
End If


If List_System = 0 Then
    List_System = Null
    
Else


For Each varItem In Me.List_System.ItemsSelected
    List_System.Selected(varItem) = False
Next varItem


    Task = "select * from All_Documents_Qry"
    DoCmd.ApplyFilter Task
End If


End Sub
Private Sub List_Service_AfterUpdate()
Call Search
End Sub
Private Sub List_Doc_Type_AfterUpdate()
Call Search
End Sub
Private Sub List_System_AfterUpdate()
Call Search
End Sub

**CJ_London** · 10-23-2019, 09:41 AM

Code:

Option Compare Database
Option Explicit

Sub Search()
Dim varItem As Variant
Dim strSearch As String
Dim Task As String


'Me.FilterOn = True
For Each varItem In Me.List_Service.ItemsSelected
    strSearch = strSearch & "," & Me.List_Service.ItemData(varItem)

Next varItem

For Each varItem In Me.List_Doc_Type.ItemsSelected
    strSearch = strSearch & "," & Me.List_Doc_Type.ItemData(varItem)

Next varItem

For Each varItem In Me.List_System.ItemsSelected
    strSearch = strSearch & "," & Me.List_System.ItemData(varItem)

Next varItem


Task = "select * from All_Documents_Qry"

If Len(strSearch) <> 0 Then
    strSearch = Right(strSearch, Len(strSearch) - 1)
    Debug.Print strSearch
    Task =Task & " WHERE [Service] in (" & strSearch & ")"

End If

Me.RecordSource = Task

End Sub

I've modified the code slightly - can you copy paste strSearch from the immediate window after you run it. Also provide some example values for Service which should have been returned - and whether they should have been selected from the listbox selection service, doc_type or system

with regards the calls, Call is a throwback to the early days of Basic, it is only there for backwards compatibility and is not required

I haven't looked at the code for Command104_Click

**CJ_London** · 10-23-2019, 09:45 AM

Just to check - you are not using lookups in your table design? they can cause your query not to work

**ssanfu** · 10-23-2019, 05:31 PM

I think there is a logic error in the code...???

I created a form and added a button and 3 listboxes (I named them using your names).

Click image for larger version.

Name: New2Access1.png
Views: 26
Size: 27.7 KB
ID: 40026

I select all 3 options from all 1 list box

Click image for larger version.

Name: New2Access2.png
Views: 25
Size: 26.4 KB
ID: 40027

So far, so good......

I ran the code from Ajax's post #6 - the result from debug statements was

Code:

select * 
from All_Documents_Qry 
WHERE [Service] in ( Equal Opportunity Training, Safety Training, Sexual Harasment Training)

But the field "Service" does not have Equal Opportunity Training, Safety Training nor Sexual Harasment Training values in that field. However. those 3 doc types ARE in a different field.

If you selected all 3 values from all 3 list boxes, wouldn't you have to have a WHERE clause that looked something like

Code:

select * 
from All_Documents_Qry 
WHERE [Service] in (Cat grooming,Dog walking,Goat milking) OR [List_Doc_Type] in (Equal Opportunity Training,Safety Training,Sexual Harasment Training) OR [List_System] in (Jerry,Jim,Sam)

And if you selected all 3 values from the first 2 list boxes, shouldn't you have

Code:

select * 
from All_Documents_Qry 
WHERE [Service] in (Cat grooming,Dog walking,Goat milking) OR [List_Doc_Type] in (Equal Opportunity Training,Safety Training,Sexual Harasment Training)

(Not sure if the connector should be "AND" or "OR")

Am I missing something???? (wouldn't be the first time

)

**CJ_London** · 10-23-2019, 05:48 PM

I don't disagree with your question on logic - it crossed my mind as well. The OP's code changed from post #1 to post #3 to post#5

**New To Access** · 10-24-2019, 02:28 AM

Originally Posted by Ajax

Code:

Option Compare Database
Option Explicit

Sub Search()
Dim varItem As Variant
Dim strSearch As String
Dim Task As String


'Me.FilterOn = True
For Each varItem In Me.List_Service.ItemsSelected
    strSearch = strSearch & "," & Me.List_Service.ItemData(varItem)

Next varItem

For Each varItem In Me.List_Doc_Type.ItemsSelected
    strSearch = strSearch & "," & Me.List_Doc_Type.ItemData(varItem)

Next varItem

For Each varItem In Me.List_System.ItemsSelected
    strSearch = strSearch & "," & Me.List_System.ItemData(varItem)

Next varItem


Task = "select * from All_Documents_Qry"

If Len(strSearch) <> 0 Then
    strSearch = Right(strSearch, Len(strSearch) - 1)
    Debug.Print strSearch
    Task =Task & " WHERE [Service] in (" & strSearch & ")"

End If

Me.RecordSource = Task

End Sub

I've modified the code slightly - can you copy paste strSearch from the immediate window after you run it. Also provide some example values for Service which should have been returned - and whether they should have been selected from the listbox selection service, doc_type or system

with regards the calls, Call is a throwback to the early days of Basic, it is only there for backwards compatibility and is not required

I haven't looked at the code for Command104_Click

Hi Ajax,

I pasted your code and got this error

Click image for larger version.

Name: dddddd.PNG
Views: 24
Size: 27.5 KB
ID: 40029

Here some examples that should be returned also the values in the different lists.

Click image for larger version.

Name: aaaaaa.PNG
Views: 24
Size: 10.0 KB
ID: 40030

Click image for larger version.

Name: ddddddx.PNG
Views: 25
Size: 12.1 KB
ID: 40031

As for the Command104_Click, this is the clear selection button that clears out the selections from the lists (multiple possible)

Realy appreciate the help guys

**CJ_London** · 10-24-2019, 03:18 AM

first comment - these are all text so you should be including single quotes as you were in your original post

strSearch = strSearch & ",'" & Me.List_Doc_Type.ItemData(varItem) & "'"

Secondly, these are all in separate fields, but your code is putting them all into one field

so you need to build the string separately

Finally - your error is telling you you have repeated Option Compare Database and/or Option Explicit in your module

this is your revised code for multiple fields

Code:

Option Compare Database
Option Explicit

Sub Search()
Dim varItem As Variant
Dim strSearch As String
Dim Task As String

Task = "select * from All_Documents_Qry"


strSearch=""
For Each varItem In Me.List_Service.ItemsSelected
    strSearch = strSearch & ",'" & Me.List_Service.ItemData(varItem) & "'"

Next varItem

If Len(strSearch) <> 0 Then
    Task =Task & " WHERE [Service] in (" & Right(strSearch, Len(strSearch) - 1) & ")"

End if

strSearch=""
For Each varItem In Me.List_Doc_Type.ItemsSelected
    strSearch = strSearch & ",'" & Me.List_Doc_Type.ItemData(varItem) & ","

Next varItem

If Len(strSearch) <> 0 Then
    Task =Task & " " & iif(instr(Task,"WHERE","AND") & " [Doc_Type] in (" & Right(strSearch, Len(strSearch) - 1) & ")"

End if


strSearch=""
For Each varItem In Me.List_System.ItemsSelected
    strSearch = strSearch & ",'" & Me.List_System.ItemData(varItem) & "'"

Next varItem

If Len(strSearch) <> 0 Then
    Task =Task & " " & iif(instr(Task,"WHERE","AND") & " [System] in (" & Right(strSearch, Len(strSearch) - 1) & ")"

End if


Me.RecordSource = Task

End Sub

**New To Access** · 10-24-2019, 03:28 AM

Originally Posted by Ajax

first comment - these are all text so you should be including single quotes as you were in your original post

strSearch = strSearch & ",'" & Me.List_Doc_Type.ItemData(varItem) & "'"

Secondly, these are all in separate fields, but your code is putting them all into one field

so you need to build the string separately

Finally - your error is telling you you have repeated Option Compare Database and/or Option Explicit in your module

this is your revised code for multiple fields

Code:

Option Compare Database
Option Explicit

Sub Search()
Dim varItem As Variant
Dim strSearch As String
Dim Task As String

Task = "select * from All_Documents_Qry"


strSearch=""
For Each varItem In Me.List_Service.ItemsSelected
    strSearch = strSearch & ",'" & Me.List_Service.ItemData(varItem) & "'"

Next varItem

If Len(strSearch) <> 0 Then
    Task =Task & " WHERE [Service] in (" & Right(strSearch, Len(strSearch) - 1) & ")"

End if

strSearch=""
For Each varItem In Me.List_Doc_Type.ItemsSelected
    strSearch = strSearch & ",'" & Me.List_Doc_Type.ItemData(varItem) & ","

Next varItem

If Len(strSearch) <> 0 Then
    Task =Task & " " & iif(instr(Task,"WHERE","AND") & " [Doc_Type] in (" & Right(strSearch, Len(strSearch) - 1) & ")"

End if


strSearch=""
For Each varItem In Me.List_System.ItemsSelected
    strSearch = strSearch & ",'" & Me.List_System.ItemData(varItem) & "'"

Next varItem

If Len(strSearch) <> 0 Then
    Task =Task & " " & iif(instr(Task,"WHERE","AND") & " [System] in (" & Right(strSearch, Len(strSearch) - 1) & ")"

End if


Me.RecordSource = Task

End Sub

Thanks for the feedback Ajax, it helps me with the learning process!

I got an error for the IIF in the code:

Click image for larger version.

Name: aadd.PNG
Views: 25
Size: 51.8 KB
ID: 40033

**CJ_London** · 10-24-2019, 03:51 AM

my bad, should be

iif(instr(Task,"WHERE")>0,"AND","WHERE")

**New To Access** · 10-25-2019, 01:48 AM

Originally Posted by Ajax

my bad, should be

iif(instr(Task,"WHERE")>0,"AND","WHERE")

Thanks Ajax, i don't get an error anymore. But the query is still not getting filtered

Click image for larger version.

Name: dedede.PNG
Views: 20
Size: 16.6 KB
ID: 40040

Any ideas why this might be?

**New To Access** · 10-25-2019, 02:03 AM

Originally Posted by New To Access

Thanks Ajax, i don't get an error anymore. But the query is still not getting filtered

Any ideas why this might be?

I do get an error, i had deleted me.recordsource=Task. I put it back and now i get this error:

Filter a query based on the multiple values of 3 list boxes in a split form

Thread Tools

Filter a query based on the multiple values of 3 list boxes in a split form

Similar Threads

Filling multiple text boxes with different values based on multiple drop down menus.

Multiple combo boxes to filter list results including text and numbers

Access Query to filter out list based on multiple row criteria from another table

Split form filter via combo boxes

filter a subform based on multiple multi select list boxes

Tags for this Thread

Posting Permissions