Solved

MultiSelect Listbox criteria to a Report

Posted on 2000-04-06
10
371 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Dsum Function for List Box Data 7 45
Trying to open Visio 2013 from a ms access 2016 database 9 31
Dynamically Reorder List Box 4 37
Create report using crosstab query 11 29
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

775 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