List box

  • Thread starter Thread starter PJ
  • Start date Start date
P

PJ

I would like to have a list box in a form based off of a query that you can
select multiple criteria and a report would produce off of what was selected.
How would I do that?


Thanks in advance
 
You will probably want your ListBox [Multi Select] property to be set to
"Extended".

Then, the approach I would use would be to build the Where clause for my
report's record source in VBA, stringing together the selected values.

Here's the code to do that.
=================
Option Compare Database
Option Explicit

Private Sub cmdOpenReport_Click()
Const PROC_NAME As String = "cmdOpenReport_Click"

Dim vItem As Variant
Dim sWhereClause As String

On Error GoTo ErrorHandler

If Me.lstSelections.ItemsSelected.Count = 0 Then
Exit Sub
End If

' Change FieldName below to the name of your field.
sWhereClause = "[FieldName] In ("

' Loop the selected items.
For Each vItem In Me.lstSelections.ItemsSelected
' Add the value of the current selection to the Where clause.
sWhereClause = sWhereClause & """" &
Me.lstSelections.ItemData(vItem) & """, "
Next

' Trim off the final ", "
sWhereClause = Left(sWhereClause, Len(sWhereClause) - 2)

' Close the parens.
sWhereClause = sWhereClause & ")"

' Change MyReportName below to the name of your report.
DoCmd.OpenReport "MyReportName", acViewPreview, , sWhereClause

Cleanup:
Exit Sub

ErrorHandler:
MsgBox "Error: " & Err.Number & ", " & Err.Description, , Me.Name & "."
& PROC_NAME

On Error Resume Next

GoTo Cleanup

End Sub
==================
End of code
 
Back
Top