[AccessD] Creating Filter Form for Report

On 5/25/06, Bryan Carbonnell <carbonnb at gmail.com> wrote:

> Here is what I am trying to do: I need to generate a report that will
> shom me what courses individuals have had. I need to be able to select
> one or a varyiong number of participants and then have it show me what
> courses they have had.
>
> What I was thinking was have a form that lists the individuals in a
> multi-select listbox, and then use that as a parameter for the query
> the report is based on, but I'm having a serious mind gap as to how to
> get the info from the lsit box to the query.

Here's what I finally did

Created a form with the listbox on it and a command button to run the report.

In the Click even of the report, this is the code I used:

Dim strFilter As String
strFilter = "RESOURCE_ID " & InParticipant
DoCmd.OpenReport "Resource_Class_List_LOCAL", acViewPreview, , strFilter

and the InParticipant function is as follows:

Public Function InParticipant() As String

Dim frm As Form
Dim ctl As Control
Dim varItem As Variant
Dim strIn As String


Set frm = Forms!frmReportSelectParticipant
Set ctl = frm!lstParticipants

For Each varItem In ctl.ItemsSelected
  strIn = strIn & """" & ctl.Column(0, varItem) & """, "
Next varItem

If Right$(strIn, 2) = ", " Then
  strIn = Left$(strIn, Len(strIn) - 2)
  strIn = "In (" & strIn & ")"
Else
  strIn = ""
End If

InParticipant = strIn

Set varItem = Nothing
Set ctl = Nothing
Set frm = Nothing

End Function

Thanks all for your help.

I really need to do more dev work and lett management work :(

Thanks again.

-- 
Bryan Carbonnell - carbonnb at gmail.com
Life's journey is not to arrive at the grave safely in a well
preserved body, but rather to skid in sideways, totally worn out,
shouting "What a great ride!"