Access 2016 (1 Viewer)

[slj]

The boss has asked me to create a form based on a query that lets him
1. Select from a list of Groups in a listbox ,
2. in listbox 2 (multi-select) shows all the Companies & Contacts in that Group,
3. in listbox 3 show which contacts he has selected.

He then wants to print a report of only the selections shown in listbox 3.

Can this been done and how? Any ideas? I am at a loss to see how to do this.:banghead:

 

[theDBguy]

Hi. Which part are you having difficulties with? Take the tasks one job at a time and let us know which specific area is not working.

 

[June7]

What you describe is called cascading combobox or listbox. A very common topic. Google or search forum and you will find many discussions and examples.

For example of a search form, review http://allenbrowne.com/ser-62.html

Using a multi-select listbox as criteria to open a filtered report requires VBA code that loops through listbox selected items. The link will lead to example of that as well. The 3rd listbox is not really needed.

 

[slj]

Thanks June7, That is exactly what I needed to do.

 

[Bullschmidt]

And here is an article about using a multi-select listbox as criteria for a report (and a tricky part here is looping to add items to the SQL WHERE clause):

Use a multi-select list box to filter a report

 

[Micron]

3rd maybe not needed, but if boss wants it...
If the 3rd is a requirement, then you basically would put 2 (or 4) buttons between 2nd and 3rd. Right arrow to move selected items to 3rd list (assuming they go from left to right), left to remove them from 3rd list. Other two buttons, if used, would be to move all items either to or from 3rd. If you have the option, I suggest avoiding the 3rd list as it can get complicated for novices. In that case, either type of migration usually involves removing selected items from one list and placing it in another and rebuilding lists, which is not as easy as cascading and simply multi-selecting from list2.

 

[gemma-the-husky]

Actually using the selected items in a list box at run time is one thing. Storing a single selected item in a list box (or combo box) is easy. But storing a list of multiple items selected in a list box for future use is not so easy.

 

[slj]

I am so close that I can taste victory over this monster. :banghead:

All my list boxes behave as expected, but when I run the report it is displaying the entire group not just the companies that were selected.

ListBoxes:
lstGroup
lstLeft
lstRight - is really just a security blanket for the boss, so that he can see which selections he made.

My code on the Preview Report button is:
Private Sub cmdGroupPreview_Click()
On Error GoTo Err_cmdGroupPreview_Click
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant

'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
strWhere = strWhere & ctl.ItemData(varItem) & ","
'Use this line if your value is text
'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 "rtGroupCo", acPreview, , "GroupCode IN(" & strWhere & ")"

Exit_cmdGroupPreview_Click:
Exit Sub

Err_cmdGroupPreview_Click:
MsgBox Err.Description
Resume Exit_cmdGroupPreview_Click

End Sub

What have I done wrong here, because I know it's is totally my mistake.
Thanks so much.

 

[slj]

I'm using pbauldy's code to filter a report by a multi-select listbox.

I am so close that I can almost taste victory, but I am stuck. All my list boxes behave as expected (see Pic). But when I run the report, it displays all the members of the group, not just the members selected.

List Boxes
lstGroup
lstLeft
lstRight - Just a security blanket for the boss so that he can see what he has selected before running the report.

On Click report preview button.

Private Sub cmdGroupPreview_Click()
    On Error GoTo Err_cmdGroupPreview_Click

  Dim strWhereGroup As String
  Dim ctl           As Control
  Dim varItem       As Variant

  '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
    strWhereGroup = strWhereGroup & ctl.ItemData(varItem) & ","
  Next varItem
  'trim trailing comma
  strWhereGroup = Left(strWhereGroup, Len(strWhereGroup) - 1)
  
  'open the report, restricted to the selected items
  DoCmd.OpenReport "rtGroupCo", acPreview, , "GroupCode IN(" & strWhereGroup & ")"

Exit_cmdGroupPreview_Click:
  Exit Sub

Err_cmdGroupPreview_Click:
  MsgBox Err.Description
  Resume Exit_cmdGroupPreview_Click

End Sub

I just can't figure out where I have gone wrong here. I tried to add a second Where stmt, but I don't think I entered it correctly because the report came back empty.

Any suggestions would be greatly appreciated. I have been trying to solve this on my own since May, but I have had no luck. Nothing I have tried has worked. I have read 3 books on VBA and the 2016 Access Bible but still am lost on how to fix this.

 

[theDBguy]

Hi. Just before the DoCmd.OpenReport line, try adding this:

Debug.Print strWhereGroup

and let us know what you get in the Immediate Window. If you don't know what I mean, try using

MsgBox strWhereGroup

and post a screenshot here.

 

[pbaldy]

That code doesn't open the report filtered? What is the data type of the GroupCode field? Use this:

http://www.baldyweb.com/ImmediateWindow.htm

to see what the variable strWhereGroup contains right before the OpenReport line.

 

[pbaldy]

Oh, and post 8 was moderated, I just approved it.

 

[slj]

I got 2,2 in the Msgbox

 

[pbaldy]

Did you select the same item twice? Or is the bound column of the listbox not the appropriate field for this?

 

[theDBguy]

I got 2,2 in the Msgbox

That is interesting. How can you select the same item twice?

 

[slj]

GroupCode is a Number

 

[slj]

Good question. I have the report based off of a query




lstGroup
RowSource:
SELECT DISTINCT [qryGroupCoContacts].[GroupCode], [qryGroupCoContacts].[GroupDesc] FROM qryGroupCoContacts ORDER BY [GroupDesc];

lstLeft:
RowSource:
SELECT DISTINCT qryGroupCoContacts.GroupCode, qryGroupCoContacts.Company, qryGroupCoContacts.Contact, qryGroupCoContacts.CompanyRef, qryGroupCoContacts.ContactRef FROM qryGroupCoContacts WHERE (((qryGroupCoContacts.GroupCode)=[Forms]![frmGroup]![lstGroup]));

 

[pbaldy]

I don't see how 2 could be returned twice if that's the group listbox to the left that the code is looking at. The report does seem to be filtered to the selected group though. Can you attach the db here?

 

[slj]

I have attached a copy of the db

 

[pbaldy]

Doesn't appear to have worked, see:

https://www.access-programmers.co.uk/forums/showthread.php?t=140587