G
Guest
Hi friends,
I have been searching the discussions and the web and found one of Allen
Browne's posting that was close to what I was trying to get at but I still
get nothing filtered onto the report.
Scenario: I have a report bound to a query and have multiple filter criteria
(ie including combos, lists, and textboxes all sitting on different tabs on
an unbound form (all unbound too), with a Button (onclick) that builds WHERE
string
and then apply it to the form's Filter or the report's
WhereCondition. I do that with the code below but I am not gettin anything
filtered on the report even I know there are some records that meet the
criteria...
What am I doing wrong? Can anyone help me?
Private Sub btnPreview_Click()
'On Error GoTo Err_btnPreview_Click
Dim cbo As ComboBox
Dim lst As ListBox
Dim strWhere As String
Dim iLen As Integer
'CropName Combo
Set txtCbo = Me.cboCrop
If Not IsNull(cbo) Then
strWhere = strWhere & "(CropName = """ & cbo & """) AND "
End If
'GrowerName List
Set txtLst = Me.lstGrower
If Not IsNull(lst) Then
strWhere = strWhere & "(GrowerName = """ & lst & """) AND "
End If
'Similar for other list, combo or text boxes here.
'Did we get anything?
iLen = Len(strWhere) - 5 'Without trailing " AND ".
If iLen < 1 Then
MsgBox "No criteria"
Else
strWhere = Left$(strWhere, iLen)
'Use it for this form.
Me.Filter = strWhere
Me.FilterOn = True
'Open the report and apply filter.
DoCmd.OpenReport "rptSowing", acViewPreview, , strWhere
End If
Set cbo = Nothing
Set lst = Nothing
End Sub
I have been searching the discussions and the web and found one of Allen
Browne's posting that was close to what I was trying to get at but I still
get nothing filtered onto the report.
Scenario: I have a report bound to a query and have multiple filter criteria
(ie including combos, lists, and textboxes all sitting on different tabs on
an unbound form (all unbound too), with a Button (onclick) that builds WHERE
string
and then apply it to the form's Filter or the report's
WhereCondition. I do that with the code below but I am not gettin anything
filtered on the report even I know there are some records that meet the
criteria...
What am I doing wrong? Can anyone help me?
Private Sub btnPreview_Click()
'On Error GoTo Err_btnPreview_Click
Dim cbo As ComboBox
Dim lst As ListBox
Dim strWhere As String
Dim iLen As Integer
'CropName Combo
Set txtCbo = Me.cboCrop
If Not IsNull(cbo) Then
strWhere = strWhere & "(CropName = """ & cbo & """) AND "
End If
'GrowerName List
Set txtLst = Me.lstGrower
If Not IsNull(lst) Then
strWhere = strWhere & "(GrowerName = """ & lst & """) AND "
End If
'Similar for other list, combo or text boxes here.
'Did we get anything?
iLen = Len(strWhere) - 5 'Without trailing " AND ".
If iLen < 1 Then
MsgBox "No criteria"
Else
strWhere = Left$(strWhere, iLen)
'Use it for this form.
Me.Filter = strWhere
Me.FilterOn = True
'Open the report and apply filter.
DoCmd.OpenReport "rptSowing", acViewPreview, , strWhere
End If
Set cbo = Nothing
Set lst = Nothing
End Sub