[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 388
  • Last Modified:

MultiSelect Listbox criteria to a Report

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
ewalsh
Asked:
ewalsh
  • 2
  • 2
  • 2
  • +2
1 Solution
 
nico5038Commented:
What is the question?

I see you are not checking for start- and end-date to be filled when the button is pressed.
0
 
mgrattanCommented:
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
 
BrianWrenCommented:
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
MikeRenzCommented:
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
 
MikeRenzCommented:
brianwren,
   wouldn't your string you built have a leading comma?
0
 
ewalshAuthor Commented:
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
 
mgrattanCommented:
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
 
nico5038Commented:

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
 
BrianWrenCommented:
    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
 
ewalshAuthor Commented:
Comment accepted as answer
0

Featured Post

Industry Leaders: 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!

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now