none
Sending variables to a query RRS feed

  • Question

  • I have a multi-select list control that I want to use to select which business lines receive a report. Currently, I string several DoCmds together, one for each report that I need. However, my list can change and vary from 10 to 15 different reports. Ideally, I made this list box which lists the business lines and business line Executives for all lines. The multi-select lets me select how many business lines I want.

    I created two text boxes on the list control form.  One txt bus and the other is txtExec.  Their control sources are list0.column0 and list0.column1.  When I manually click on a name, the text boxes change to represent the correct data.

    Next, I put a references to those two text boxes in my report query thinking that each time the list box changed the text boxes would change and a new report would fire.  When I execute the program, the first report prints perfectly, but the others do not.  The data is not changing or not being passed to the query running the report.  Here is some of the code but I guess the question is can I use a multi-select list box to pass variables to queries?

    Code:

    Private Sub RunRpt_Click()

    Dim StrBiz As String
    Dim StrExec As String
    Dim ctrl As Control
    Dim Varitem As Variant

    Dim strDate As String
    Dim strFile As String
    Dim strFilePath As String

     strDate = [Text25] 'this is the date passed from the list control form

    Set ctrl = Me.List0

    'check for at least one item selected

    If ctrl.ItemsSelected.Count = 0 Then
       
        MsgBox "Please select one or more reports to print", _
        vbOKOnly, "Error"
       
        Exit Sub
    End If

    'cycle through selected items

    For Each Varitem In ctrl.ItemsSelected

    StrExec = Me.[txtExec]

    strFilePath = "C:\MOR\"

    strFile = StrBiz & " Outstanding Regulatory Findings as of " & strDate & "_DRAFT.pdf"
     strFilePath = strFilePath & strFile

    DoCmd.OutputTo acOutputReport, "rptMOR", acFormatPDF, strFilePath, True

    Next Varitem
       
    End Sub

    In the query the business line reference is:

    [Forms]![Select Business Lines/Executives]![txtBiz]

    The reference to the Executive is:

    [Forms]![Select Business Lines/Executives]![txtExec]

    Do you have any suggestions on how to make this work?

    The form containing the list0 and txtExec bound to list0.column1 and txtBiz. to list0.column0 is called Select  Business Lines/Executives.  This form also has text25 for the report date to be passed.


    Dean J. Waring

    Monday, March 28, 2016 3:41 PM

Answers

  • It looks to me like you are simply running the same report x as many items as you have selected in your listbox.  Each subsequent run of the report overwrites the previous pdf file created.  I note in your code that the value of strBiz never changes and the string variable strExec seems to never be used.

    It looks like maybe you expect the value of txtExec to change in your for loop but it doesn't. What you might want to do instead is unbind txtExec and txtBiz and then something like the following:

    For Each Varitem In ctrl.ItemsSelected

    txtExec = ctrl.Column(0,varItem)

    txtBiz = ctrl.Column(1,varItem)

    strFilePath = "C:\MOR\"

    strFile = StrBiz & " Outstanding Regulatory Findings as of " & strDate & "_DRAFT.pdf"
     strFilePath = strFilePath & strFile

    DoCmd.OutputTo acOutputReport, "rptMOR", acFormatPDF, strFilePath, True

    Next Varitem


    • Marked as answer by DeanJW2006 Monday, March 28, 2016 8:17 PM
    Monday, March 28, 2016 5:57 PM

All replies

  • It looks to me like you are simply running the same report x as many items as you have selected in your listbox.  Each subsequent run of the report overwrites the previous pdf file created.  I note in your code that the value of strBiz never changes and the string variable strExec seems to never be used.

    It looks like maybe you expect the value of txtExec to change in your for loop but it doesn't. What you might want to do instead is unbind txtExec and txtBiz and then something like the following:

    For Each Varitem In ctrl.ItemsSelected

    txtExec = ctrl.Column(0,varItem)

    txtBiz = ctrl.Column(1,varItem)

    strFilePath = "C:\MOR\"

    strFile = StrBiz & " Outstanding Regulatory Findings as of " & strDate & "_DRAFT.pdf"
     strFilePath = strFilePath & strFile

    DoCmd.OutputTo acOutputReport, "rptMOR", acFormatPDF, strFilePath, True

    Next Varitem


    • Marked as answer by DeanJW2006 Monday, March 28, 2016 8:17 PM
    Monday, March 28, 2016 5:57 PM
  • Let me try that and get back to you...thanks for the suggestion.

    Dean J. Waring

    Monday, March 28, 2016 6:06 PM
  • Hi, DeanJW2006

    According to your description, please correct me if I have any misunderstandings on your question, I suggest that you can open the report in print preview mode and filter it. Then use DoCmd.OutputTo without specifying the object name. OutputTo will export the open, filtered report.

    DoCmd.OpenReport "yourReport", acViewPreview, , "yourCondition"
    DoCmd.OutputTo acOutputReport, ,acFormatPDF, strFilePath, True

    Tuesday, March 29, 2016 9:05 AM