Sending list box results to a temporary table for reporting

Situation:  You’ve got a list box of surgeons.  You want to run a report and as a criteria to the report, you want to filter by the surgeons selected in the list box.  How do you do this?

Here’s the list box of surgeons with a couple selected:

lstSurgeon

lstSurgeon

Note:  multi select property = ‘simple’ to allow multiple selections

So, in code, before running the report we send the surgeon names to a temporary table:

‘send surgeons to temp table
Dim I as integer
i = 0
CurrentDb.Execute “delete * from trpttmp_Surgeons”
For i = 0 To Me.lstSurgeon.ListCount – 1
If Me.lstSurgeon.Selected(i) Then
Debug.Print Me.lstSurgeon.Column(0, i)
CurrentDb.Execute “INSERT INTO trpttmp_Surgeons ( Surgeon ) SELECT ” & Me.lstSurgeon.Column(0, i) & ” AS Surgeon”
End If
Next i

DoCmd.OpenReport “rpt10Surgery Scores”, acViewPreview, , “txtSurgeons IN (SELECT [surgeon] FROM trpttmp_Surgeons)”

This results in the following table being cleared and filled:

trpttmp_Surgeons

Note the use of the IN Clause in the openreport command.  If you don’t understand the use of the IN clause, go here:

http://office.microsoft.com/en-us/access-help/in-operator-HP001032243.aspx?CTT=5&origin=HP001032287

It’s not the greatest explanation, though…

 

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *