Passing Multiple Criteria to open a report

[Stacio]

I have two departments (HR & Training) which have seperate databases. HR has an Employee table that contains the fields [Agent ID], [Basic Skill], and [Supv]. Training has a Training table that contains the fields [Agent ID] and

.

What I need to do is provide the total number of agents available in specific or multiple skills listed by supervisor; and the total number of agents that have taken specific training.

I created a query (qryCountAvailable) on the Employee table to count [Agent ID] by [Basic Skill] and grouped by [Supv].  I also created another query (qryCountTraining) to count the [Agent ID] by [Code].  I then created another query (qryMain) combing these two queries to form the source for my report (rptMain).

I also created a form from which to pass one or more skills and the needed training code. I'm having a problem passing multiple criteria to qryMain (which is based on two queries).  I've tried the temp text box approach, subreports, and everything else I can think of.  I know that you can't pass criteria directly to a query.

How can I combine the two queries (qryCountAvailable & qryCountTraining) to accept multiple criteria for my report?

I am able to pass multiple criteria for another report, so that's not the problem.  Here is the code that I use on my form to pass the criteria.  The first option works OK.  It's the second one that is stumping me.

Private Sub cmdContinue_Click()

Dim varItem As Variant
Dim strSQL As String
Dim ctl As Control

Set ctl = Me.List21

Visible = False                      'Hide frmSelect


If Me![optChoose] = 1 Then
    For Each varItem In ctl.ItemsSelected
    strSQL = strSQL & "[Basic Skill]=" & ctl.ItemData(varItem) & " Or "
    Next varItem

strSQL = Left$(strSQL, Len(strSQL) - 4)    'Trim the end of strSQL
DoCmd.OpenReport "rptRosterBuild", acViewPreview, , strSQL
End If

If Me![optChoose] = 2 Then
    For Each varItem In ctl.ItemsSelected
    strSQL = strSQL & ctl.ItemData(varItem) & " Or "     '"[Basic Skill]=" &
    Next varItem

strSQL = Left$(strSQL, Len(strSQL) - 4)    'Trim the end of strSQL

Me![txtTemp] = strSQL

DoCmd.OpenReport "rptMain", acViewPreview
End If

End Sub

Thanks.