Filter report based on form selection and duplicates in table (1 Viewer)

lj2830 · Jul 26, 2017

Hi All,

I've been searching this site and Google for an answer to what seems simple in my head but I cant figure out how to do it in Access. Please can you offer some help?

To explain how my DB works:

Table: I enter data into the table, the data is application name, user ID and entitlement, these are all text columns

Form & Report: I have a listbox where I can select multiple entitlements from the table and a button to open and filter a report based on the entitlement selection.

My issue:

What I would like to do in addition to the above is only show the user IDs that are the same based on the entitlements selected in the listbox rather than all of the users with the entitlements. I think its just an and to the filter I already have on the entitlements but cant seem to do it.

As an example
in the table there is user1 with entitlement 1 and user1 with entitlement 2 and user2 with entitlement 2

If you select entitlement 1 & 2 on the form listbox I would only want User1 returned like the below example

user1 with entitlement 1 user1 with entitlement 2

For some reason, I cant get the user ID part to work, as it shows all users with the selected entitlement.

Thanks in advance for any help.

Cheers Lee

Minty · Jul 26, 2017

Can you show us the code you have that isn't working ?

lj2830 · Jul 26, 2017

Thanks Minty for replying, please see code below.

Cheers Lee

Code:

Dim strWhere As String
 Dim ctl As Control
 Dim varItem As Variant
'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 = 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 "ApplicationACL1", acPreview, , "ID IN(" & strWhere & ") and In (SELECT [UserID] FROM [ApplicationACL1] As Tmp GROUP BY [UserID] HAVING Count(*)>1 )"

Minty · Jul 26, 2017

You need to qualify the second part of the criteria - try

Code:

DoCmd.OpenReport "ApplicationACL1", acPreview, , "ID IN(" & strWhere & ") and  [COLOR="Red"][UserID] [/COLOR] In (SELECT [UserID] FROM [ApplicationACL1] As Tmp GROUP BY [UserID] HAVING Count(*)>1 )"

lj2830 · Jul 26, 2017

Thanks Minty. It now works

Filter report based on form selection and duplicates in table (1 Viewer)

lj2830

New member

Minty

AWF VIP

lj2830

New member

Minty

AWF VIP

lj2830

New member

Similar threads

Users who are viewing this thread