Filter once but never again

[RobotMush]

I am working on a filter that will take one or the other or both of my two combo boxes that will have the criteria to filter.
The first time I run it, the filter runs great wheather it is one or the other or both combo boxes that have the criteria in it. However, after clicking the remove filter button. (On Click program with DoCmd.ShowRecords and the combo boxes made = "" )
and running the filter again, wheater with a different criteria or the same criteria, it runs through the entire program then shows the message, "Records not found"

I know that I am needing to get the entire filter system cleaned out like I have just opened the Form but I can not seem to get it done.

Any help would be greatly appreciated.

Below is the entire program should you want to review and ask more questions.

Thank you for any help or suggestions

RobotMush (Technical User)

 

[dip757]

did you try DoCmd.Refresh .....

 

[Ascentient]

No Code there but when the condition is met to remove the filter are you issueing a me.filter = "" and then requerying the datasource of that control?

for instance.

cboComboBox.filter =[CustZip] = '55555'

if condition = met then
cboComboBox.Filter = ""
cboComboBox.requery
end if

 

[RobotMush]

Ascentient
Tried that as you can see by the program below. Didn't work giving me the same problems.

Do I have it in the right place for the program?

Private Sub RmvFltrcbo_Click()
DoCmd.ShowAllRecords
YrFltrcbo = ""
DptFltrcbo = ""
Me.Filter = ""
Me.Requery
End Sub

Also noticed that I didn't include the program from the last post. Here it is.

Private Sub ApplyFiltercmd_Click()

'To filter Year and Department in DataFind-qry
Dim strYR As String
Dim strDept As String

'Check DateSelect and DeptFilter for data

If IsNull(YrFltrcbo) Then
If IsNull(DptFltrcbo) Then
Me.Filter = False
MsgBox "Please select Date And/Or Department to Filter"
Exit Sub
Else
strDept = Me.Dept
Me.Filter = "Dept='" & DptFltrcbo & "'"
Me.FilterOn = True
With Me.RecordsetClone
.FindFirst Me.Filter
If Not .NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Else
MsgBox "No Records Meeting '" & DptFltrcbo & "' Filtering were Found."
MsgBox "Please Press Remove Filter"
End If
End With
End If
Exit Sub
Else
If IsNull(DptFltrcbo) Then
strYR = Me.LTD
Me.Filter = "LTD='" & YrFltrcbo & "'"
Me.FilterOn = True
With Me.RecordsetClone
.FindFirst Me.Filter
If Not .NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Else
MsgBox "No Records Meeting '" & YrFltrcbo & "' Filtering were Found."
MsgBox "Please Press Remove Filter"
End If
End With
Exit Sub
Else
strYR = Me.LTD
strDept = Me.Dept
Me.Filter = "LTD= '" & YrFltrcbo & "' and Dept='" & DptFltrcbo & "'"
Me.FilterOn = True
With Me.RecordsetClone
.FindFirst Me.Filter
If Not .NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Else
MsgBox "No Records Meeting '" & YrFltrcbo & "' and '" & DptFltrcbo & "' were Found."
MsgBox "Please Press Remove Filter"
End If
End With
End If
End If
End Sub

 

[Ascentient]

If IsNull(YrFltrcbo) Then
If IsNull(DptFltrcbo) Then
Me.Filter = False ' Should this be me.filteron = false ???? or Me.Filter = ""
MsgBox "Please select Date And/Or Department to Filter"
Exit Sub

Short of running it myself I couldn't tell you why it is doing it. By all accounts of what the SHOWALLRECORDS mean, you don't need to do anything with the Me.Filter and Me.FilterOn properties.