filter report based on multi listbox selection (1 Viewer)

lj2830

New member
Local time
Today, 09:51
Joined
Jul 26, 2017
Messages
8
Hi All,

I'm after some advice as a newbie as I cant figure out how to achieve the below.

I have a DB which using a listbox on a form to lookup entitlements from a table and filter a report based on the listbox selection. The report returns the same user ID which have the entitlements selected on the listbox from the table on a report.

Everything works as expected until I change the list box to multi select which I believe is creating null values and the report is blank.

Can someone help with the code required to select all records when multi select is turned on as there are lots of entitlements so I cant leave it off to select one by one.

Code below

Code:
Dim varItem As Variant
Dim strWhere As String
'make sure a selection has been made
 If Me.Entitlements.ItemsSelected.Count = 0 Then
   MsgBox "You must choose at least 1 entitlement"
   Exit Sub
 End If
'add selected values to string
 Set ctl = Entitlements
 For Each varItem In ctl.ItemsSelected
  strWhere = strWhere & ctl.ItemData(varItem) & ","
  
 Next varItem
'trim trailing comma
 strWhere = Left(strWhere, Len(strWhere) - 1)
'open the report, restricted to the selected items
DoCmd.OpenReport "ToxicAnalysisReport", acPreview, , "ID IN(" & strWhere & ") and [UserID] In (SELECT [UserID] FROM [ApplicationACL1] As Tmp GROUP BY [UserID] HAVING Count(*)>1 )"


Thanks,
Lee
 

isladogs

MVP / VIP
Local time
Today, 09:51
Joined
Jan 14, 2017
Messages
18,186
See Allen Browne's website for a detailed explanation on how to work with a multi-select listbox

http://allenbrowne.com/ser-50.html

NOTE; He's just changed servers so you may get an error but persevere - he's the guru
 

lj2830

New member
Local time
Today, 09:51
Joined
Jul 26, 2017
Messages
8
Thanks Ridders.

I have looked at the code on the link you provided, however I cant see that it will do what I require, sorry still learning Access.

Can you explain how the code checks the items selected and knows to select more than one record from the table?

Could you point out the code from the link that does this please?

Thanks,
Lee
 

isladogs

MVP / VIP
Local time
Today, 09:51
Joined
Jan 14, 2017
Messages
18,186
Hi

See the section 'The Code' ! :)

Mutliselect listboxes aren't easy for 'newbies' to work with
However Allen's explanation is better than any of us will be able to manage

If you copy his code and then carefully modify field & control names to suit your situation, it should work just as you require
 

ashleedawg

"Here for a good time"
Local time
Today, 02:51
Joined
Jun 22, 2017
Messages
154
To 'select all' the rows, set Multi Select to Simple or Extended, and add a button called btnSelectAll with code:

Code:
Private Sub btnSelectAll_Click()
'select all rows in  Me.Entitlements
Dim counter As Integer
For counter = 0 To Me.Entitlements.ListCount-1
    Me.Entitlements.Selected(counter) = True
Next counter
End Sub

Your code then (correctly) sets strWhere to a string like (1,2,3,4,5). I don't see Multi Select making a difference at this point.

I think the issue is not related to Multi Select but to the report criteria:

"ID IN(" & strWhere & ") and [UserID] In (SELECT [UserID] FROM [ApplicationACL1] As Tmp GROUP BY [UserID] HAVING Count(*)>1 )"

Is USERID part of the report's control source? Do you only want to see data for USERID's that exist more than once in ApplicationACL1? Could you post example data from the listbox and from ApplicationACL1, as well as the report's control source?
 
Last edited:

lj2830

New member
Local time
Today, 09:51
Joined
Jul 26, 2017
Messages
8
Thanks Ridders.

I have pasted the code from the link as you suggested, however, sorry I must be doing something wrong. the results are still the same as the value of the listbox is always null with select distinct multi select.

for example
in the table there is user1 with entitlement 1 and user1 with entitlement2 and user3 with entitlement1

on the listbox in the form the selection of entitlements is entitlement 1, entitlement 2 and entitlement 3 only displayed once due to the distinct as there are lots of the same name entitlement.

What should happen is when you choose entitlement 1 and entitlement 2, the result returned should be user1 with entitlement 1 and user1 with entitlement 2.

This works if I remove the distinct from the listbox as the list box then shows entitlement 1 twice, so providing I choose the correct entitlement for user1 from the listbox, the results are returned as I understand it can loop through the items selected.

I think the issue is because the distinct is included in the row source for the listbox so in the items selected it doesn't know which user to select, i.e. user1 or user3.

Thanks and sorry if im not explaining myself.

Cheers,
Lee
 
Last edited:

Users who are viewing this thread

Top Bottom