multiple selection from a list box 1

[tina68]

I am trying to run a report in Access, using a form, the user currently can select a customer and run a financial report, how can I set up the form so that a user can select multiple customers and run one report? The customer numbers are coming from a link table from Informix. Please help me!!

 

[randysmid]

Hi,
This is much simpler than it seems. Add a listbox to your form that contains the customers (we'll call it lstCustomers). In code, we will be able to pass a filter to the report, which will then only print records corresponding to the customers desired. Cool?

In code, you can send all the selected rows to the report as a filter. Here is the code that will do that:
'place this code in the clicked event of your command
'button which starts the report, e.g., "Run Report"
' - presume the listbox is called lstCustomers
' the recordsource for the report is a query
' containing all the field elements you need
Dim strFilter as string
Dim varItem as variant
For Each varItem In Me!lstCustomers.ItemsSelected
strFilter = strFilter & "[CustomerNumb] = '" & _
Me![lstCustomers].ItemData(varItem) & "' OR "
Next
' the next bit of code will subtract out the last "OR"
If strFilter <> &quot;&quot; Then
strFilter = Left(strFilter, Len(strFilter) - 4)
End If
' now, run the report using strFilter to pass a string
' containing the needed customers
DoCmd.OpenReport &quot;CustomerReport&quot;, acPreview,,strFilter
HTH,
Randy Smith
California Teachers Association

 

[randysmid]

One more note:
This code example presumes that the CustomerNumb field is a string datatype. If it is numeric, then you will need to remove the single quote marks, as such:
'this example assumes the customer number is numeric
' - the single quote marks have been removed
For Each varItem In Me!lstCustomers.ItemsSelected
strFilter = strFilter & &quot;[CustomerNumb] = &quot; & _
Me![lstCustomers].ItemData(varItem) & &quot; OR &quot;
Next
HTH,
Randy Smith
California Teachers Association