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
