Solved

MultiSelect Listbox criteria to a Report

Posted on 2000-04-06
10
372 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
  • 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 150 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

832 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