Multiple selections for a report

[Mojo1]

I currently use a combo box to preview a record in a report. I want the option of selecting more than one record for a report. I have tried using a list box with not much luck. Can any one suggest a way to make it work?

Thanks in Advance!

 

[SydneyGeek]

I had to do something like this for a client earlier this year. Basically, they wanted to be able to select multiple records from a list box and view them in a report -- but the report could be one of several layouts depending on the equipment being tested.
In essence I did the following:
1. Create a list box with multi-select enabled (check in the Properties for the ListBox)
2. On clicking the Preview button, the record IDs were copied into a SQL string which was used to replace a query -- (a) Create SQL (b) Delete existing QueryDef (c) Create new QueryDef using the same name, with the SQL string as the code for the QueryDef
3. Open the required report, which had the QueryDef as the RecordSource.

This code was written for Access 97 so it's DAO, but it might help:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> cmdPreview_Click()
  <SPAN style="color:#00007F">Dim</SPAN> strName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
  <SPAN style="color:#00007F">Dim</SPAN> strList <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
  <SPAN style="color:#00007F">Dim</SPAN> strFilterText <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
  <SPAN style="color:#00007F">Dim</SPAN> varItem <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
  <SPAN style="color:#00007F">Dim</SPAN> strQuote
  <SPAN style="color:#00007F">Dim</SPAN> strSQL
  <SPAN style="color:#00007F">Dim</SPAN> qdf
  <SPAN style="color:#00007F">Dim</SPAN> dbs <SPAN style="color:#00007F">As</SPAN> Database
  
  <SPAN style="color:#00007F">Set</SPAN> dbs = CurrentDb()
  strQuote = Chr$(34)
  strList = ""
  
  <SPAN style="color:#00007F">With</SPAN> Me!lstEquip
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> varItem <SPAN style="color:#00007F">In</SPAN> .ItemsSelected
      strName = strQuote & .Column(0, varItem) & strQuote & ","
      strList = strList & strName
    <SPAN style="color:#00007F">Next</SPAN>
    
    strFilterText = Left(strList, Len(strList) - 1)
  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
[txtList] = strFilterText

strSQL = "SELECT Equipment.* FROM Equipment WHERE (((Equipment.EquipmentNo) In (" & strFilterText & ")));"

dbs.QueryDefs.Delete "qryEquipNoFilter"
<SPAN style="color:#00007F">Set</SPAN> qdf = dbs.CreateQueryDef("qryEquipNoFilter", strSQL)


<SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> [cSheet]
  <SPAN style="color:#00007F">Case</SPAN> 8
    DoCmd.OpenReport "Inspection Sheet 8 Multi", acViewPreview
  <SPAN style="color:#00007F">Case</SPAN> 9
    DoCmd.OpenReport "Inspection Sheet 9 Multi", acViewPreview
  <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN>
    DoCmd.OpenReport "Inspection Sheet Multi", acViewPreview
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>