Access 2016 (1 Viewer)

slj · Jun 19, 2019

Had to zip it.

pbaldy · Jun 19, 2019

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 · Jun 19, 2019

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 · Jun 19, 2019

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 · Jun 19, 2019

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 · Jun 19, 2019

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

pbaldy · Jun 19, 2019

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

Code:

  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 · Jun 20, 2019

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 · Jun 20, 2019

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

slj · Jun 20, 2019

Where it is set to 4?

pbaldy · Jun 20, 2019

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

slj · Jun 20, 2019

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

pbaldy · Jun 20, 2019

Happy to help!

Access 2016 (1 Viewer)

slj

Registered User.

pbaldy

Wino Moderator

slj

Registered User.

pbaldy

Wino Moderator

June7

AWF VIP

slj

Registered User.

pbaldy

Wino Moderator

slj

Registered User.

pbaldy

Wino Moderator

slj

Registered User.

pbaldy

Wino Moderator

slj

Registered User.

pbaldy

Wino Moderator

Similar threads

Users who are viewing this thread