Results 1 to 13 of 13
  1. #1
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171

    Trouble passing multiple listbox selections made on a form to the report title when it runs

    I have a form which allows the user to make multiple selections for the criteria (Labor Cat) when the report is run. The report is running fine and all the selection criteria is passing to the report and subreports.

    I know this is basic but I can't get the users selections to display in the report title. I want the user to have a record of what is contained in the report. Currently it is only picking up one selection but not all of them.



    My title looks like this LABEL [Labor Category Report for] txtLaborCat [selections made from the form]

    txtLaborCat - Control Source is LaborCat. I have tried pulling the selections using the !Forms! argument but that didn't work.

    I know this is pretty basic but I'm stumped.

    Code:
    Private Sub cmdPerfIssuesLaborCatRpt_Click()
    On Error GoTo Err_cmdPerfIssuesLaborCatRpt_Click
      Dim strWhere      As String
      Dim ctl           As Control
      Dim varItem       As Variant
      'make sure a selection has been made
      If Me.lstLaborCatSelection.ItemsSelected.Count = 0 Then
        MsgBox "Must select at least 1 employee"
        Exit Sub
      End If
      'add selected values to string
      Set ctl = Me.lstLaborCatSelection
      For Each varItem In ctl.ItemsSelected
            strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
      Next varItem
      Debug.Print strWhere
      'trim trailing comma
      strWhere = Left(strWhere, Len(strWhere) - 1)
      'open the report, restricted to the selected items
      DoCmd.OpenReport "rptPerfIssuesbyLaborCat", acPreview, , "LaborCat IN (" & strWhere & ")"
      
    Exit_cmdPerfIssuesLaborCatRpt_Click:
      Exit Sub
    Err_cmdPerfIssuesLaborCatRpt_Click:
      MsgBox Err.Description
      Resume Exit_cmdPerfIssuesLaborCatRpt_Click
        
    End Sub
    Attached Thumbnails Attached Thumbnails Capture.PNG  

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You could put the value of strWhere into a textbox, which would let you refer to it from the report.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171
    I had a line below the DoCmd command which didn't work' Is this setup wrong?
    txtLaborCat = strWhere

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What exactly does "didn't work" mean? Where in the code above did you have it, and how did you try to use it in the report?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I think you need to put that line above the Docm.OpenReport, not below (too late to pass the value there).

    Cheers,
    Vlad

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Oh, sorry, bad eyesight. Below the OpenReport line is too late, the report is already open. Put it right before that line.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171
    OK.... took your advise and added this to the end of the Do.Cmd line in two different ways.

    txtLaborCat has the Control Source set to LaborCat.

    DoCmd.OpenReport "rptPerfIssuesbyLaborCat", acPreview, , "LaborCat IN (" & strWhere & ")", txtLaborCat = strWhere
    DoCmd.OpenReport "rptPerfIssuesbyLaborCat", acPreview, , "LaborCat IN (" & strWhere & ")", strWhere = txtLaborCat

    When I run the report I am only getting the first selection in the list, not all the selections that were made for the report.
    If I leave Control Source blank I get nothing in the textbox on the report.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Two lines:

    Me.txtLaborCat = strWhere
    DoCmd.OpenReport "rptPerfIssuesbyLaborCat", acPreview, , "LaborCat IN (" & strWhere & ")"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171
    My bad..... I didn't see the word ABOVE the DoCmd!

  10. #10
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171
    Tried your code...

    I did not recognize txtLaborCat because it not on the form where this code is running, it is on the report.
    The code I provided above, runs on a cmd on the form which triggers the report to run.

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Trying to populate it after it's open probably won't work. I'd either use a textbox on the form and have the report get it there, or pass it in OpenArgs and have code in the format event of the section containing the textbox populate it.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171
    Thanks so much, it works great!!! Always appreciate the help!

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 21
    Last Post: 06-11-2015, 03:03 PM
  2. Replies: 3
    Last Post: 04-29-2015, 04:02 PM
  3. Replies: 2
    Last Post: 09-08-2012, 08:25 PM
  4. Replies: 1
    Last Post: 01-22-2012, 02:41 PM
  5. Replies: 1
    Last Post: 03-02-2009, 11:54 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums