?
Solved

MultiSelect Listbox criteria to a Report

Posted on 2000-04-06
10
Medium Priority
?
378 Views
Last Modified: 2012-06-27
This is all in Access 2000.
I have a Form with a multiselect listbox with 3 columns. First column is Providers Last Name, Second is the First Name and Third is the Providers ID. I also have a button that launches a report. This report gets its data from a query, this query gets its selection criteria from the form, there are 2 unbound fields for Start & End Date in the form that the user enters & I need to pass the Provider ID to this query from the MultiSelect ListBox also on that form.
Below is the code I have on the OnClick event for the button that launches the Print Preview of the report.
P.S. I need this also for a button to actually print the report.

Private Sub Command10_Click()
On Error GoTo Err_Command10_Click

    Dim stDocName As String
    Dim varItem As Variant
    Dim Prov_Id As String
   
    stDocName = "productivity_test"
   
    With lstCompanies
        If .MultiSelect = 0 Then
            Prov_Id = .Value
         [Queries].[test_productivity].[er_3_staff] = Prov_Id
        DoCmd.OpenReport stDocName, acPreview
       
        Else
           
            For Each varItem In .ItemsSelected
                Prov_Id = .Column(2, varItem)
              [Queries].[test_productivity].[er_3_staff] = Prov_Id
              DoCmd.OpenReport stDocName, acPreview
               
                Next varItem
   
    End If
End With

Exit_Command10_Click:
    Exit Sub

Err_Command10_Click:
    MsgBox Err.Description
    Resume Exit_Command10_Click
   
End Sub

0
Comment
Question by:ewalsh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
10 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 2690376
What is the question?

I see you are not checking for start- and end-date to be filled when the button is pressed.
0
 
LVL 14

Accepted Solution

by:
mgrattan earned 600 total points
ID: 2690398
The code you are using:

For Each varItem In .ItemsSelected
    Prov_Id = .Column(2, varItem)
    [Queries].[test_productivity].[er_3_staff] = Prov_Id
    DoCmd.OpenReport stDocName, acPreview
Next varItem

needs to update a WHERE clause in the query.  Try changing it to this:

Dim strSQL as String
Dim ctl as Control

Set ctl = lstCompanies

strSQL = "SELECT * FROM test_productivity WHERE er_3_staff ="

For Each varItem in .ItemsSelected
    strSQL = strSQL & ctl.ItemData(varItm) & " OR er_3_staff="
Next varItem

'Now remove the extra OR statement at the end

strSQL = Left$(strSQL, Len(strSQL) - 14)

'Now you can use the strSQL as your report's recordsource.

Dim db as Database
Dim qdf as QueryDef

Set db = CurrentDb

'First delete the query if it already exists
For each qdf in db.QuerDefs
    If qdf.name = "TempQuery" then
        qdf.delete
        Exit For
    End If
Next qdf

Set qdf = db.CreateQuerydef ("TempQuery", strSQL)


Set your reports recordsource to "TempQuery"

Mike.


0
 
LVL 9

Expert Comment

by:BrianWren
ID: 2690405
I believe what I would do is this.  in your loop "For Each varItem in .ItemsSelected", I would build up a global string by concatenation.  Let's say it was called strIDs.

     strIDs = ""
     For Each varItem In .ItemsSelected
                Prov_Id = .Column(2, varItem)
                strIDs = strIDs & ", " & Prov_Id
     Next varItem
   
     DoCmd.OpenReport stDocName, acPreview

Then I  would write

Public Function, RtnStrIDs() As String
     RtnStrIDs = strIDs
End Function
   
Then I would make the query have the following

  .
  .
  .
WHERE ID IN ( RtnStrIDs() )
ORDER BY ...;

Brian
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 3

Expert Comment

by:MikeRenz
ID: 2690435
so if you select many providers....you want to open up one report for each selected...one-at-a-time?

You could just pass all of them:

    For Each varItem In .ItemsSelected
       myString = myString & "," & .Column(2, varItem)
    Next varItem
   
    myString = left(myString, len(MyString)-1)

    DoCmd.OpenReport stDocName, acPreview,,"Prov_ID In (" & myString & ")"

....just a suggestion though

as far as having a print button...use the same code as your preview button code, only change the
   acPreview

to

   acNormal

in the docmd.openreport lines....

However, a better way would be to create a function with all this code in it, then depending on which button is pressed, you would call this function, and then docmd.openreport with either acPreview or acNormal

0
 
LVL 3

Expert Comment

by:MikeRenz
ID: 2690445
brianwren,
   wouldn't your string you built have a leading comma?
0
 

Author Comment

by:ewalsh
ID: 2690563
Yes if you select 3 providers it needs to run the report 3 times.  One for each selection. Its a report for each specific provider & counts total client visits, count of total unique client visits, total money made  ect. for a date range.
I can't do an "OR" or "AND" on the provider_id criteria, would throw the numbers off in my report. Plus this report gets handed to each provider at the end of the month & the powers to be don't want Provider A knowing what Provider B is doing.

This report originaly was a non-multiselect, You picked 1 provider ran the report & then picked the next, ran the report & so on. Users say that it was too time consuming, wanted to select all in 1 shot & have the reports print out for each provider selected.
0
 
LVL 14

Expert Comment

by:mgrattan
ID: 2690584
Ah, then you don't necessarily need my solution.  I was thinking that you were trying to create one report with all selected criteria.  However, you could just use a Group Header in the report to group in the criteria field and have the report break on each group.  That way you don't have seperate print jobs.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 2690680

For a similar situation I created a combobox with all ID's and the choice <*all*>. (UNION query)

Thus offering the possibility to print a single report for adhoc purposes and to print all once a month/week or so.

When combined with the correct grouping on the report as mgrattan proposes, you have a fairly simple piece of code.

0
 
LVL 9

Expert Comment

by:BrianWren
ID: 2690832
    strID = "" 
     For Each varItem In .ItemsSelected
                Prov_Id = .Column(2, varItem)
                strID = Prov_Id
                DoCmd.OpenReport stDocName, acPreview  
     Next varItem
   
     

Then I  would write

Public Function, RtnStrID() As String
     RtnStrID = strID
End Function
     
Then I would make the query have the following

  .
  .
  .
WHERE ID = RtnStrIDs();

Brian


0
 

Author Comment

by:ewalsh
ID: 2893932
Comment accepted as answer
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question