Solved

MultiSelect Listbox criteria to a Report

Posted on 2000-04-06
10
369 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 3

Expert Comment

by:MikeRenz
Comment Utility
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
Comment Utility
brianwren,
   wouldn't your string you built have a leading comma?
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:ewalsh
Comment Utility
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
Comment Utility
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
Comment Utility

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
Comment Utility
    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
Comment Utility
Comment accepted as answer
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now