Access 2016

[slj]

Had to zip it.

 

[pbaldy]

Maybe I'm unclear as to the goal, but lstLeft is the one in the center, not the one on the left. That one is lstGroup. Which is supposed to filter the report?

 

[slj]

lstGroup shows all of the groups that can be selected
lstLeft filters the companies/contacts within that group that the boss wants to contact
lstRight is just a display of which companies the boss selected.

 

[pbaldy]

So is it supposed to filter by company and contact rather than group? In your picture above, should those 2 selected records be the only ones on the report?

 

[June7]

Procedure is pulling GroupCode from lstLeft, not company identifier. Use CompanyRef as bound column. Move it so it is first column in listbox query. GroupCode just needs to be in WHERE clause, including it as a column in lstLeft is optional.

DoCmd.OpenReport "rtGroupCo", acPreview, , "CompanyRef IN(" & strWhereGroup & ")"

 

[slj]

yes, they should be the only ones on the report (from the Pictures)

 

[pbaldy]

I should get out of the way, but since I've already done this see if it's what you want:

  Dim strWhere As String
  Dim ctl           As Control
  Dim varItem       As Variant
  Dim strWhereCo As String
  Dim strWhereContact As String

  'make sure a selection has been made
  If Me.lstLeft.ItemsSelected.Count = 0 Then
    MsgBox "Must select at least 1 Company"
    Exit Sub
  End If

  'add selected values to string
  Set ctl = Me.lstLeft
  For Each varItem In ctl.ItemsSelected
    strWhereCo = strWhereCo & ctl.Column(3, varItem) & ","
    strWhereContact = strWhereContact & ctl.Column(4, varItem) & ","
  Next varItem
  'trim trailing comma
  strWhereCo = Left(strWhereCo, Len(strWhereCo) - 1)
  strWhereContact = Left(strWhereContact, Len(strWhereContact) - 1)
  
  'open the report, restricted to the selected items
  strWhere = "CompanyRef IN(" & strWhereCo & ") AND ContactRef IN(" & strWhereContact & ") AND GroupCode = " & Me.lstGroup
  Debug.Print strWhere
  DoCmd.OpenReport "rtGroupCo", acPreview, , strWhere

 

[slj]

Ok, I have tried both suggestions.

The suggestion from June7 gets me every group the company/contacts are a member of, instead of just selected group from lstGroup in the report
ex:
AHS Lighting (group selected),
Koch Furniture,(Company)
Diane (Contact)
Managhan's Furniture
Mark

Then I tried pbaldy's code and I am getting error:
Syntax error (missing operator) in query expression 'CompanyRef in (2949,3260) AND ContactRef IN (,) AND GroupCode = 2'

 

[pbaldy]

Forgot I had to change the column count property of the listbox to 5.

 

[slj]

Where it is set to 4?

 

[pbaldy]

I said 5, and it's a property of the listbox (lstLeft).

 

[slj]

It works perfectly. THANK YOU, THANK YOU, THANK YOU

 

[pbaldy]

Happy to help!