Distinct listbox to collect all records from table

[lj2830]

Hi All,

I've been searching this site and Google for an answer to a list box question, I'm a little stuck and wondered if you can offer some advice regarding my issue?

To explain my DB:
I have a table called ApplicationACL1 which contains users and entitlements, some users will hold the same entitlement.

I have set up a form and distinct list box with the entitlements from the table for a user to select.

The user selects the entitlements from the list box and clicks a button on the form to output the results to a report where the same user has the entitlements selected from the list box.

My issue:
It works as expected if I remove the distinct from the row source of the list box and select each entitlement more than once, but when I add the distinct back the results are blank, Im guessing this is due to the .items selected code in my button only looking at the items selected in the list box and not checking back with the table for all of the other entitlements which are the same as the selected. but I don't know how to do this.

Thanks in advance for any help, I've listed the code below for reference.

Cheers Lee

Dim frm As Form
Dim ctl As Control
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 frm = ApplicationEntitlementSelection
 Set ctl = Me.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 "Report", acPreview, , "ID IN(" & strWhere & ") and [UserID] In (SELECT [UserID] FROM [ApplicationACL1] As Tmp GROUP BY [UserID] HAVING Count(*)>1 )"

 

[Ranman256]

DONT use multi-select lists to run queries.
It is much easier to just select items from the listbox, and add them to a 'picked' table.
no vb code needed. Just 1 query.
user dbl-clicks the item in list, it runs an append query to the tPicked table.

then simply open a query that has the join the tPicked table to the tData table and it pulls only those items.

 

[lj2830]

Thank you, I've been able to get it working using that method.