I am working on almost an identical problem. Some of the reports are not
yet
done, so you will see some gaps in the code, but this is the essence of
it.
Example 1 shows how I build the Where Condition from multiple list boxes
with multiple selections.
Example 2 shows how I put together the where condition from the various
list
boxes
**********Example 1************************
Private Sub PrintReport(lngView As Long)
Dim strWhere As String 'String that will hold filtering as selected
on
form
Dim strWhereNext As String 'Used to concantenate multiple field
selections
Dim strDocName As String 'The Report version to open
Dim strFieldName As String 'The field name to include in the Where
Condition
'Billable Product Offering
strWhere = BuildWhereCondition("lstBillProdOffering")
strFieldName = "tblBudgetVSActualLbrPO.BillableProductOffering "
If Len(strWhere) > 0 Then
strWhere = strFieldName & strWhere
End If
'Master Activity
strWhereNext = BuildWhereCondition("lstMActivity")
strFieldName = "tblMasterActivity.MActivity "
If Len(strWhereNext) > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND " & strFieldName & strWhereNext
Else
strWhere = strFieldName & strWhereNext
End If
End If
'Activity
strWhereNext = BuildWhereCondition("lstActivity")
strFieldName = "tblBudgetVSActualLbrPO.Activity "
If Len(strWhereNext) > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND " & strFieldName & strWhereNext
Else
strWhere = strFieldName & strWhereNext
End If
End If
'BillNetwork
strWhereNext = BuildWhereCondition("lstBillNetwork")
strFieldName = "tblActivity.actvContractActivity "
If Len(strWhereNext) > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND " & strFieldName & strWhereNext
Else
strWhere = strFieldName & strWhereNext
End If
End If
'Pool
strWhereNext = BuildWhereCondition("lstPool")
strFieldName = "tblBudgetVSActualLbrPO.Pool "
If Len(strWhereNext) > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND " & strFieldName & strWhereNext
Else
strWhere = strFieldName & strWhereNext
End If
End If
'Home Room
strWhereNext = BuildWhereCondition("lstHomeRoom")
strFieldName = "tblBudgetVSActualLbrPO.acctgunit "
If Len(strWhereNext) > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND " & strFieldName & strWhereNext
Else
strWhere = strFieldName & strWhereNext
End If
End If
Select Case Me.opgReportFormat
Case 1 'By Product Offering
Case 2 'By Master Activity
Case 3 'By Activity
strDocName = "rptPVAByActivityPO"
Case 4 'By By BillNetwork
Case 5 'By By Pool
Case 6 'By Home Room
strDocName = "rptPVAByAcctgUnitPO"
End Select
DoCmd.OpenReport strDocName, lngView, , strWhere
End Sub
*************Example 2
Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control
Set ctl = Me.Controls(strControl)
Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("
With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select
BuildWhereCondition = strWhere
End Function
***********End Code******************
Berny said:
Hello,
I'm having a problem using stLinkCriteria; I'm trying to also include a
multi-list box selection into my criteria.
I'm at the end of my rope trying to make this work.
Can anyone give me a push in the right dirrection?
I'm using the following code to make the multi-list selection
If Me!TradeSelection.ItemsSelected.Count > 0 Then
For Each varTradeItem In Me!TradeSelection.ItemsSelected
strTradeCriteria = strTradeCriteria & "Trade = " & Chr(34) &
Me!TradeSelection.ItemData(varTradeItem) & Chr(34) & "OR "
Next varTradeItem
strTradeCriteria = Left(strTradeCriteria, Len(strTradeCriteria) -
3)
Else
MsgBox "You have not made a selection" , vbExclamation, "Nothing
to
find!"
End If
Then I'm trying to add another field on the form called SBKG to the
LinkCriteria
stLinkCriteria = "[SBKG]= '" & Me![cboSBKG] & "' And [Trade]= '" &
strTradeCriteria & "'"
The goal is to open the following report but I can't figure-out what I
doing
wrong
DoCmd.OpenReport strDocName, acViewPreview, , stLinkCriteria
I would greatly appreciate any help anyone can provide
Thank you
