Results 1 to 13 of 13
  1. #1
    slthom is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    May 2014
    Posts
    5

    Looping listbox values to pass to a query


    First post ever here.

    After much searching and reading, I can't seem to find a solution to what I am trying to accomplish. I currently have a combobox on a form where my users select a vendor name and then they click a button to open the report. They then review the report and click a button on a form that opens with the report that saves it as a PDF and opens up a new Outlook message with the PDF file attached (this was Lebans code for the PDF portion). The report uses a query as its source and filters based on the vendor name selected on the combobox. Once a week, the users have to make selections from the combo box and click the button each time to open the reports. The current process works, but becomes cumbersome when they have to do it for quite a few vendors.

    In order to simplify this process, I want to place a listbox on the form and have them make multiple selections, then click a button and code would cycle the listbox, pass each value to the report/query, then my current code runs that opens the report, prints it as a PDF, and then opens up the email message. It would do this for each selection in the listbox.

    Hopefully, all that makes sense.

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    You can start by building your listbox and adjusting its properties, like multiselect. Here is some code that will iterate multiple selections and display results in the Immediate Window. Shortcut to Immediate window is Ctrl+G


    Code:
    Dim varSelection As Variant
    Dim lngID As Long    'Assumes the value in the first column is a number 
    If Me.List0.ItemsSelected.Count = 0 Then
    Exit Sub
    Else
    For Each varSelection In Me.List0.ItemsSelected
    lngID = Me.List0.Column(0, varSelection)
    'Insert code here to do something
    Debug.Print lngID
    Next varSelection
    End If

  3. #3
    burrina's Avatar
    burrina is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    Here is something you could adapt to your needs.
    If Me.lstlist.ItemsSelected.Count = 0 Then
    MsgBox "You must select at least 1 Item"
    Exit Sub
    End If



    Dim strEmail As String
    Dim varItem As Variant
    For Each varItem In lstlist.ItemsSelected

    strEmail = strEmail & lstlist.Column(2, varItem) & ";"

    Next varItem
    If strEmail > "" Then
    strEmail = Left(strEmail, Len(strEmail) - 1)
    End If




    Me.EmailTo.Value = strEmail

    HTH

  4. #4
    slthom is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    May 2014
    Posts
    5
    I have found both of the examples that you guys have posted and they work, but not to the extent that I need them to work. I need each value to be passed to a query from which a report is based. A simple way to look at it would be taking my combobox that I currently have that users use. If somehow, they could choose values in the new listbox and click a button, then the combobox would be populated with their first selection and then run my openreport code, then the combobox would be updated with the next value selected and run the opereport code, etc for all values selected in the list box.

    I have this code that goes through my combobox, opens the report, etc, but the combobox contains many values that my users don't need to run the reports on. If the same thing could be accomplished with a listbox and only selecting the values they need, it would be wonderful.


    Private Sub Command379_Click()

    On Error GoTo Err_Command379_Click

    Dim stDocName As String
    Dim i As Integer
    i = 0

    'Loop through all the data within the combo box and assign value then print the report
    Do While VendorList.ItemData(i) <> ""
    VendorList = VendorList.ItemData(i)
    'stDocName = "rptPurchaseOrderTrackingDetailsByVendor"
    'DoCmd.OpenReport stDocName, acViewPreview
    MsgBox "test"

    i = i + 1
    Loop

    Exit_Command379_Click:
    Exit Sub

    Err_Command379_Click:
    MsgBox Err.Description
    Resume Exit_Command379_Click

    End Sub


    Any ideas or is this even possible?

  5. #5
    burrina's Avatar
    burrina is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    I don't understand. The code I posted would populate a combobox with the selections made. For me, I would need to see what you have. If you could post a DEMO copy, maybe someone could help.

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I think what you are trying to say is that you have a folder with a bunch of reports that need to be emailed. Why not loop through the list box, create the report, cerate an email, attach the report, send the email, move the next ItemsSelected, delete the previous report via Kill and the path, and then repeat.

  7. #7
    slthom is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    May 2014
    Posts
    5
    Yes, that code was great at populating the box with all the values selected, but it's not exactly what I am looking for. Let's say a user selects VendorA, VendorB, and VendorC in the new listbox, then hits a button and the combobox is populated with VendorA and the report opens, then the combobox is populated with VendorB and the report opens, then the combobox is populated with VendorC and the report opens. So, only one value can be in the combobox at a time for the report to open. The report is based on a query and needs this value to run [Forms]![frmPurchaseOrder].[VendorList]. [VendorList] is the name of my combobox and only one value can be in there at a time to run the report.

    Here's what I'm envisioning, clicking a button and this would run:

    combobox = 1st selection of listbox
    open report with combobox value passed to the query

    combobox = next selection of listbox
    open report with combobox value passed to the query

    and so on for each value selected

  8. #8
    burrina's Avatar
    burrina is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    Why don't you just Clear your selection and then re-run the Report or do you want to populate another Combobox or Textbox?

  9. #9
    slthom is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    May 2014
    Posts
    5
    That's exactly what I am trying to do by using the listbox to change the combobox value to create the report and keep going for each value selected, but I have no clue how to get that done. I can find a slew of solutions to loop through the listbox and get one long list of all of the values to the combobox, but I only need one value at a time in the combobox to create the report.

  10. #10
    burrina's Avatar
    burrina is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    Here is a screenshot of what I THINK you might want.Click image for larger version. 

Name:	GroupEmailer.png 
Views:	28 
Size:	155.8 KB 
ID:	16347

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Code can cycle through listbox and send email for each selected item:

    Code:
        Dim varItem As Variant      'Selected items
        With Me.lstVendor
            For Each varItem In .ItemsSelected
                If Not IsNull(varItem) Then
                   'code here to open report filtered to listbox item, send email, close report
                End If
            Next
        End With
    How many vendors in the list?

    Review http://allenbrowne.com/ser-50.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    slthom is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    May 2014
    Posts
    5
    Thanks for all the help guys and maybe gals! After trying many things, here is what I came up with and it works beautifully. Probably not the best database design, but does what it's supposed to do.



    Private Sub ListVendor_DblClick(Cancel As Integer)

    ReportList = "rptPurchaseOrderTrackingDetailsByVendor"

    Dim lngRow As Long
    Dim strMsg As String

    Dim stDocName As String
    Dim stPDFName As String
    Dim blRet As Boolean

    stDocName = Me.ReportList
    stPDFName = "Vendor Shipping Report"

    With Me.ListVendor
    For lngRow = 0 To .ListCount - 1
    If .Selected(lngRow) Then
    'strMsg = strMsg & ", " & .Column(0, lngRow)
    strMsg = .Column(0, lngRow)

    VendorList = strMsg
    DoCmd.openForm "frmEmailPDF", acNormal
    DoCmd.OpenReport stDocName, acViewPreview

    blRet = ConvertReportToPDF(stDocName, vbNullString, _
    stPDFName & ".pdf", False, False, 0, "", "", 0, 0)
    ' To modify the above call to force the File Save Dialog to select the name and path
    ' for the saved PDF file simply change the ShowSaveFileDialog param to TRUE.
    'MsgBox "The file has been saved in your My Documents folder and Outlook will now open up and attach the filename above. Click YES on any warning from Outlook.", vbOKOnly, stPDFName & ".pdf"

    fncEmail

    DoCmd.Close acReport, stDocName
    'MsgBox strMsg

    End If
    Next lngRow

    End With

    ' strip off leading comma and space '
    'If Len(strMsg) > 2 Then
    ' strMsg = Mid(strMsg, 3)
    'End If

    End Sub

  13. #13
    burrina's Avatar
    burrina is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    Glad you got it worked out. Good luck With Your Project.

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

Similar Threads

  1. Replies: 13
    Last Post: 05-14-2013, 06:01 PM
  2. Replies: 4
    Last Post: 02-14-2013, 09:33 PM
  3. Looping Through Controls to Check Values
    By mcktigger in forum Programming
    Replies: 3
    Last Post: 01-25-2013, 12:05 PM
  4. Looping through a multi select listbox - how do I do it?
    By shabbaranks in forum Programming
    Replies: 4
    Last Post: 04-02-2012, 11:56 AM
  5. Replies: 3
    Last Post: 11-26-2010, 12:38 PM

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