Open a report with values from many combo box values

[Gavin59]

New to this forum, so many thanks inadvance.
I have a frmOpenReport which has 7 combo boxes linked to 7 Query's.
I have found code to add selected values from one combo box from a button 'Open Report' to a report but struggling to find how to select selected values from all comboboxs and add to a report.

My code so far.

Option Compare Database
Option Explicit
Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant
'make sure a selection has been made
If Me.HLO.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 employee"
Exit Sub
End If
'add selected values to string
Set ctl = Me.HLO
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 "rptEmployees", acPreview, , "EmpID IN(" & strWhere & ")"



Exit_cmdOpenReport_Click:
Exit Sub
Err_cmdOpenReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenReport_Click
End Sub


Is this possible?
If not what would be a neater way to go?
Any help greatly appreciated.

Many thanks in advance

 

[Gavin59]

Uncle Gizmo
Thankyou for having a look. You are correct. For some reason I thought the boxes on the form were comboboxes, where actually they are List boxes.

I'll resume my search for code that will allow me to add selected values from all List boxes to a report, unless you know of a way to amend the code I have to allow me to do this.

Many thanks again for correcting me in the first place

 

[Gavin59]

Many thanks for the quick reply. I'm not sure what a Union Qery is or what it does, but Il'll have a look at a few books and on line to see what I need to do.
I'm new to this, so it's a big learning curve. Many thanks for your help.

 

[Gavin59]

Sorry for the long wait. Thankyou for your time. I've been trying to figure out a way forward, but Access is still alien to me, but slowly learning bits & pieces. May be I'm trying to run before I can walk!

I'll do as you suggest and post a new thread stating List boxes this time and a more detailed requirement of what I need.
Thanks again.