Select all records from a subform

[jmersing]

I am using some comboboxes on my main form (frmMain) to filter the records of my unbound subform frm(viewlist). I would like to query all of the records that are filtered at the moment and use them in a a chart. However I can't figure out the SQL. Here is what I have:

SELECT qryNewestReport.PasID, qryNewestReport.Location, qryNewestReport.Week,
FROM qryNewestReport
where qryNewestReport.PasID = forms![employees list]!Viewlist.Form!pasid


Obviously this query only returns the active record where the cursor is placed on the subform. Is there a way to Select All in this scenario?

Thanks
Jim

 

[Rich]

look up the SelTop method or use a multiselect list box

 

[jmersing]

I looked it up but it gives no reference to syntax or phrasing. Does it go in the SQL statement and if so how?
SelTop(PasID) as ID would not work.

I'm somewhat committed to the combo boxes at this point.
Thanks
Jim

 

[Rich]

How is the subform filtered at the minute? If it's based on a query then you can use the same query for the report

 

[jmersing]

Like This:
Dim strsql As String

strsql = "SELECT * " _
& "FROM qryNewestReport " _
& "WHERE 1 = 1 " _
& "AND week >= forms.[employees list].startdate " _
& "AND week <= forms.[employees list].enddate "

If Forms![employees list]!department > "" Then
strsql = strsql & "AND Code = '" & Forms![employees list]!department & "' "
End If

If Forms![employees list]!Type > "" Then
strsql = strsql & "AND Type = '" & Forms![employees list]!Type & "' "
End If
If Forms![employees list]!Plan > "" And Forms![employees list]!Plan2 > "" Then
strsql = strsql & "and Plan in ('" & Forms![employees list]!Plan & "','" & Forms![employees list]!Plan2 & "')"
Else
If Forms![employees list]!Plan > "" Then
strsql = strsql & "AND Plan = '" & Forms![employees list]!Plan & "' "
End If
End If

Forms![employees list]!ViewList.Form.RecordSource = strsql