access crash when changing form filter

Question

here's my form.

Access always crash if I do these steps:

1. Do a search for the year 2012
2. Then do a search for the proposal number 12-100 (which exists)

No matter what, if I do the year search then the search for the proposal number or vice versa. it will crash. By crash,I mean access stop working and has to restart.

They both work if I close the form before to do the second one.

Here's the code for my find and clear button (That's almost all the code of my form).

 Private Sub btnFind_Click()
    If (Not IsNull(txtResearch) And txtResearch <> "") Then
        Me.Filter = "ProposalNo = '" & txtResearch & "'"
        Me.FilterOn = True
    ElseIf (Not IsNull(txtYear) And txtYear <> "") Then
        Me.Filter = "pyear = " & txtYear
        Me.FilterOn = True
    Else
        Me.Filter = ""
        Me.FilterOn = False
    End If

End Sub



Private Sub btnClear_Click()
    txtResearch = ""
    txtYear = ""
    Me.Filter = ""
    Me.FilterOn = False
End Sub

The problem is it the way I set the filter? I wasn't sure about Me.FilterOn

Also, I did try to compact and repair but no luck

Thank you

Answer 1

Apparently there may be a bit of a problem with filters and SQL Server, but I am not well enough up on the subject. You might also like to look at Allen Browne's notes here: http://www.pcreview.co.uk/forums/access-crashes-remove-filter-sub-form-t2772609.html

You may wish to consider setting the recordsource with a where statement rather than using filters.