Filter repeat in report

[anil]

Hi all
I am using the list box in the form to filter the query and then view
the report.

Problem is when I get report for one time say for locationName = "ABC"
and closes the report.When I select next time LocationName = "XYZ" and
click button to open report.then i get records inreport for both "ABC"
and "XYZ".

Actually the filter in the report does not clear it self and keep
adding the list for next time until I close the form and reopen it.

I have also put Me.Filter ="" and Me.filteron =False on the Close event
of the report but it does not help.
Can Any one please tell where i am making the mistake or Do I need to
add some thing more.

Thanks
anil

 

[Duane Hookom]

You are not providing details on how the list box is used to filter the
report. This is fairly critical to finding a solution.

 

[anil]

Hi Duane
I am using the following code:
Dim i As Integer
Dim strLocation As String, strParameter As String
Dim strIN As String
Dim flgSelectAll As Boolean, flgSelectNone As Boolean
Dim varItem As Variant
Sub modlistbox()
{FOR LOCATIONNAME}
For i = 0 To LocationName.ListCount - 1
If LocationName.Selected(i) Then
If LocationName.Column(0, i) = "All" Then
flgSelectAll = True
End If
strIN = strIN & "'" & LocationName.Column(0, i) & "',"
End If
Next i
strLocation = " [LocationName] in (" & left(strIN, Len(strIN) - 1) &
") "
{ FOR PARAMETERNAME}
For i = 0 To ParameterName.ListCount - 1
If ParameterName.Selected(i) Then
If ParameterName.Column(0, i) = "All" Then
flgSelectAll = True
End If
strIN = strIN & "'" & ParameterName.Column(0, i) & "',"
End If
Next i
strParameter = " [ParameterName] in (" & left(strIN, Len(strIN) - 1)
& ") "
End Sub
Private Sub ResultsLD_Click()
Call modlistbox
If Not flgSelectAll Then
DoCmd.OpenReport "rptresults", acViewPreview, , strLocation &
" and " & " _ SampleTakenForID = " & SampleTakenForID
Else
DoCmd.OpenReport "rptresults", acViewPreview
End If
For Each varItem In Me.LocationName.ItemsSelected
Me.LocationName.Selected(varItem) = False
Next varItem
End Sub
Private Sub ResultsPD_Click()
Call modlistbox
If Not flgSelectAll Then
DoCmd.OpenReport "rptresults", acViewPreview, , strParameter &
" and " & " _ SampleTakenForID = " & SampleTakenForID
Else
DoCmd.OpenReport "rptresults", acViewPreview
End If
For Each varItem In Me.ParameterName.ItemsSelected
Me.ParameterName.Selected(varItem) = False
Next varItem
End Sub
Actually I want to filter not only on the basis of LocationName but
also on the ParameterName so I have 2 list boxes.I have made public sub
and then by clicking command button I use the list box and call the
module.
I know that I can use all in one button that user can select parameter
and location at same time but requirement is that one button for
getting records for LocationName ,2nd for ParameterName and 3rd for
both parameterName and LocationeName.
I have gone through code today and found the problem but not the
solution.Actually in sub modListbox() the LocationName and
parameterName do not clear until the form is closed.Is there any way to
clear that.

Also in line " For Each varItem In
Me.ParameterName.ItemsSelected".......
how can I select LocationName and parameter at the same time.
thanks
anil

 

[Duane Hookom]

I would not use public variables but this is just a matter of taste. I use a
solution for building a Where Condition like outlined at
http://www.tek-tips.com/faqs.cfm?fid=3936.

 

[anil]

Thanks Duane
It was the problem of public variables ,but it was good help from code
thanks
anil

I would not use public variables but this is just a matter of taste. I use a
solution for building a Where Condition like outlined at
http://www.tek-tips.com/faqs.cfm?fid=3936.


--
Duane Hookom
MS Access MVP

Hi Duane
I am using the following code:
Dim i As Integer
Dim strLocation As String, strParameter As String
Dim strIN As String
Dim flgSelectAll As Boolean, flgSelectNone As Boolean
Dim varItem As Variant
Sub modlistbox()
{FOR LOCATIONNAME}
For i = 0 To LocationName.ListCount - 1
If LocationName.Selected(i) Then
If LocationName.Column(0, i) = "All" Then
flgSelectAll = True
End If
strIN = strIN & "'" & LocationName.Column(0, i) & "',"
End If
Next i
strLocation = " [LocationName] in (" & left(strIN, Len(strIN) - 1) &
") "
{ FOR PARAMETERNAME}
For i = 0 To ParameterName.ListCount - 1
If ParameterName.Selected(i) Then
If ParameterName.Column(0, i) = "All" Then
flgSelectAll = True
End If
strIN = strIN & "'" & ParameterName.Column(0, i) & "',"
End If
Next i
strParameter = " [ParameterName] in (" & left(strIN, Len(strIN) - 1)
& ") "
End Sub
Private Sub ResultsLD_Click()
Call modlistbox
If Not flgSelectAll Then
DoCmd.OpenReport "rptresults", acViewPreview, , strLocation &
" and " & " _ SampleTakenForID = " & SampleTakenForID
Else
DoCmd.OpenReport "rptresults", acViewPreview
End If
For Each varItem In Me.LocationName.ItemsSelected
Me.LocationName.Selected(varItem) = False
Next varItem
End Sub
Private Sub ResultsPD_Click()
Call modlistbox
If Not flgSelectAll Then
DoCmd.OpenReport "rptresults", acViewPreview, , strParameter &
" and " & " _ SampleTakenForID = " & SampleTakenForID
Else
DoCmd.OpenReport "rptresults", acViewPreview
End If
For Each varItem In Me.ParameterName.ItemsSelected
Me.ParameterName.Selected(varItem) = False
Next varItem
End Sub
Actually I want to filter not only on the basis of LocationName but
also on the ParameterName so I have 2 list boxes.I have made public sub
and then by clicking command button I use the list box and call the
module.
I know that I can use all in one button that user can select parameter
and location at same time but requirement is that one button for
getting records for LocationName ,2nd for ParameterName and 3rd for
both parameterName and LocationeName.
I have gone through code today and found the problem but not the
solution.Actually in sub modListbox() the LocationName and
parameterName do not clear until the form is closed.Is there any way to
clear that.

Also in line " For Each varItem In
Me.ParameterName.ItemsSelected".......
how can I select LocationName and parameter at the same time.
thanks
anil