Create proper "reset filter for form and report

[vanlanjl]

I have created a form that contain two combo boxes and two cmd buttons. The
idea of the form is that the user will select a search parameter from each
combo box then will click the first cmd button "cmdCreateReport". Upon doing
so it open a report based of the search criteria.

My problems seems to lay with the seconf cmd button :
cmdRemoveFilter

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![tblContacts].FilterOn = False
End Sub

also here is te code i used for cmdCreateReport

strWhere = "1=1 "
If Not IsNull(Me.cboModel) Then
strWhere = strWhere & " AND [ModelName] =""" & Me.cboModel & """ "
End If
If Not IsNull(Me.cboLocationCode) Then
strWhere = strWhere & " AND [LocationCode] =""" & Me.cboLocationCode & """ "
End If

DoCmd.OpenReport "tblContacts", acPreview, , strWhere
End Sub

So here is the issue:
I open the form and select my parameters and click on cmdCreateReport and it
opens the report correctly. I then click the cmdRemoveFilter button and the
report then will show allsearch parameters possable. I assumed that if I were
to then go back and change the search parameters and click cmdCreateReport a
second time it would then excute properaly but instead the report stays as it
was. So how do I make it where I can keep changing the filter processand make
the report to produce the search each time?

 

[Marshall Barton]

I have created a form that contain two combo boxes and two cmd buttons. The
idea of the form is that the user will select a search parameter from each
combo box then will click the first cmd button "cmdCreateReport". Upon doing
so it open a report based of the search criteria.

My problems seems to lay with the seconf cmd button :
cmdRemoveFilter

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![tblContacts].FilterOn = False
End Sub

also here is te code i used for cmdCreateReport

strWhere = "1=1 "
If Not IsNull(Me.cboModel) Then
strWhere = strWhere & " AND [ModelName] =""" & Me.cboModel & """ "
End If
If Not IsNull(Me.cboLocationCode) Then
strWhere = strWhere & " AND [LocationCode] =""" & Me.cboLocationCode & """ "
End If

DoCmd.OpenReport "tblContacts", acPreview, , strWhere
End Sub

So here is the issue:
I open the form and select my parameters and click on cmdCreateReport and it
opens the report correctly. I then click the cmdRemoveFilter button and the
report then will show allsearch parameters possable. I assumed that if I were
to then go back and change the search parameters and click cmdCreateReport a
second time it would then excute properaly but instead the report stays as it
was. So how do I make it where I can keep changing the filter processand make
the report to produce the search each time?

When a report/form is already open, the open report/form
methods don't do anything beyond moving the focus to the
report/form. This means that your create report button
needs to check if the report is open and use the Filter
property instead of trying to reopen the report. The code
could be like:

strWhere = ""
If Not IsNull(Me.cboModel) Then
strWhere = strWhere & " AND [ModelName] =""" & Me.cboModel
& """ "
End If
If Not IsNull(Me.cboLocationCode) Then
strWhere = strWhere & " AND [LocationCode] =""" &
Me.cboLocationCode & """ "
End If
strWhere = Mid(strWhere, 6)

If CurrentProject.AllReports!tblContacts.IsOpen Then
Reports!tblContacts.Filter = strWhere
Reports!tblContacts.FilterOn = True
Else
DoCmd.OpenReport "tblContacts", acPreview, , strWhere
End If

Note that using 1=1 in strWhere will prevent Access/Jet from
using any performance optimizations while retrieving the
report's data records so I changed the code to not use 1=1.