For Each VarItem in Me.lstOffices
strOffices = strOffices & ",'" & Me.lstOffices.ItemData(varItem) & "'"
Next varItem
where Me.lstOffices is a hidden, blank listbox on the form that gets items added to it based on which offices are checked. Here's the full code below. I know an alternate method of doing this is to use a multiple select listbox instead of checkboxes, but my supervisor seems set on checkboxes.
Expand|Select|Wrap|Line Numbers
- Dim strDocName As String
- Dim varItem As Variant
- Dim strOffices As String
- Dim strStatus As String
- Dim strFilter As String
- 'Clear listbox contents.
- Me.lstOffices.RowSource = ""
- If chkLA Then
- Me.lstOffices.AddItem 1
- End If
- If ChkSF Then
- Me.lstOffices.AddItem 2
- End If
- If chkLV Then
- Me.lstOffices.AddItem 3
- End If
- If chkChi Then
- Me.lstOffices.AddItem 4
- End If
- For Each varItem In Me.lstOffices
- strOffices = strOffices & ",'" & Me.lstOffices.ItemData(varItem) & "'"
- Next varItem
- 'Build filter string
- strFilter = "[SalesOffice] " & strOffice & ""
- ' Apply the filter and switch it on
- With Reports![rptPreJobList]
- .Filter = strFilter
- .FilterOn = True
- End With
- If Len(strOffices) = 0 Then
- strOffices = "Like '*'"
- Else
- strOffices = Right(strOffices, Len(strOffices) - 1)
- strOffices = "IN(" & strOffices & ")"
- End If
- strDocName = "rptPreJobList"
- DoCmd.OpenReport stDocName, acPreview