I have two list boxes on this form. The list boxes are used to filter
a report (rptBoM). Here's a synopsis of each routine:
1. btnCancel_Click() -- Pretty straightforward, just exits the form.
2. btnRunReport_Click() -- Checks to see that the user has selected at
least one item in the lbProjects table has been selected. Then it
calls function "ListBoxToIN" to populate a "where" statement for the
upcoming report generation. There are three options for report
generation shown...preview, email, and excel export. The report itself
gets its "filter" property by going straight into this form and
plucking out the "tbWhere" invisible text box value. That's not
necessary unless exporting to pdf or emailing since those options
don't have a filtering option on their action.
3. ListBoxToIn -- A function subroutine that takes the first column of
every selected row in a list box (which is passed in the calling
parameters) and builds a list of those NUMBERS (they have to be
numbers because it doesn't surround each entry by a quote...) enclosed
in parentheses and separated by commas. If the caller passes a field
name ("fn"), ListBoxToIn completes the "where" clause by prefixing the
field name and an "in" operator.
Take a look and let me kinow if it was helpful.
Option Compare Database
Option Explicit
Private Sub btnCancel_Click()
DoCmd.Close acForm, Me.Form.Name
End Sub
Private Sub btnRunReport_Click()
tbWhere = ListBoxToIn(Me.lbProjects, "ProjectID") 'Here's where
the "IN" statement is populated from the lbProjects list box.
If Me.lbDisciplines.ItemsSelected.Count > 0 Then
tbWhere = tbWhere & " AND " & ListBoxToIn(Me.lbDisciplines,
"DisciplineID") 'Here's where any lbDiscipline entries are
populated
End If
Select Case Me.optOutputStyle
Case 1
DoCmd.OpenReport "rptBoM", acViewPreview 'Previews
the report. In this instance I could have used a calling parameter
Case 2
Dim fn As String
fn = GetExcelFileName(CurrentProject.Path) 'A
function to prompt the user for a file name for an Excel workbook.
DoCmd.OutputTo acOutputReport, "rptBOM", acFormatXLS,
fn 'Outputs the report to an excel workbook.
Case 3
DoCmd.SendObject acSendReport, "rptBoM",
acFormatPDF, , , , "Bill of Materials Report" 'Outputs the report to
an email (A2007)
End Select
DoCmd.Close acForm, Me.Form.Name
btnRunReport_ClickExit:
Exit Sub
End Sub
Function ListBoxToIn(LB As Control, Optional fn As String = "",
Optional Max As Integer = 10000) As String
If LB.ItemsSelected.Count = 0 Or LB.ItemsSelected.Count > Max Then
MsgBox "Select at least one and no more than the maximum items
or press Cancel", vbOKOnly, "Selection Error"
GoTo ListBoxToIn_Exit
End If
Dim Where As String
Dim comma As String
Dim varItem As Variant
comma = " "
If fn <> "" Then
Where = fn & " in ("
Else
Where = "("
End If
With LB
For Each varItem In .ItemsSelected
Where = Where & comma
Where = Where & .ItemData(varItem)
comma = ", "
Next varItem
End With
Where = Where & ")"
ListBoxToIn = Where
ListBoxToIn_Exit:
Exit Function
End Function
Private Sub lbProjects_AfterUpdate()
Me.btnRunReport.Enabled = Me.lbProjects.ItemsSelected.Count
End Sub
