Filter not working the first time

[NISMOJim]

I have a minor annoyance I can't seem to figure out. I have a continuous form in Access 2007 that shows all records in an inventory. At the top of the form are search boxes for each field to filter the form (After Update). When this form is first opened and something is typed into one of the search boxes (then press Enter or click another box) the filter is empty showing no records at all. The user can click the "Clear Filter" button then type the same thing into the box and everything works fine. None of the search boxes work the first time after the form is opened, but they all work good from then on until the form is closed. Here is the code (with some of the redundant fields left out);



Public Sub UpdateFilter()
'Establishes what will happen when the filter is applied

Dim Category As String
Dim DateRecd As String
Dim Vendor As String
Dim Qty As String
Dim Descrip As String
Dim PartNo As String
Dim Location As String


Dim fltCategory As String
Dim fltDateRecd As String
Dim fltVendor As String
Dim fltQty As String
Dim fltDescrip As String
Dim fltPartNo As String
Dim fltLocation As String


Forms.frmInventory.FilterOn = False
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If IsNull([Forms]![frmInventory]![cbxSCategory]) = False And _
[Forms]![frmInventory]![cbxSCategory] <> "" Then
Category = [Forms]![frmInventory]![cbxSCategory]
fltCategory = "Cat like '" & Category & "'"
If Forms.frmInventory.Filter = "" Then
Forms.frmInventory.Filter = fltCategory
Else
Forms.frmInventory.Filter = fltCategory
End If
End If
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If IsNull([Forms]![frmInventory]![txtSDateRecd]) = False And _
[Forms]![frmInventory]![txtSDateRecd] <> "" Then
DateRecd = [Forms]![frmInventory]![txtSDateRecd]
fltDateRecd = "DateRecd like '" & DateRecd & "*' "
If Forms.frmInventory.Filter = "" Then
Forms.frmInventory.Filter = fltDateRecd
Else
Forms.frmInventory.Filter = Forms.frmInventory.Filter & "and " & fltDateRecd
End If
End If
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If IsNull([Forms]![frmInventory]![cbxSVendor]) = False And _
[Forms]![frmInventory]![cbxSVendor] <> "" Then
Vendor = [Forms]![frmInventory]![cbxSVendor]
fltVendor = "Vendor like '" & Vendor & "*' "
If Forms.frmInventory.Filter = "" Then
Forms.frmInventory.Filter = fltVendor
Else
Forms.frmInventory.Filter = Forms.frmInventory.Filter & "and " & fltVendor
End If
End If
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If IsNull([Forms]![frmInventory]![txtSQty]) = False And _
[Forms]![frmInventory]![txtSQty] <> "" Then
Qty = [Forms]![frmInventory]![txtSQty]
fltQty = "Qty like '" & Qty & "'"
If Forms.frmInventory.Filter = "" Then
Forms.frmInventory.Filter = fltQty
Else
Forms.frmInventory.Filter = Forms.frmInventory.Filter & "and " & fltQty
End If
End If
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If IsNull([Forms]![frmInventory]![txtSDesc]) = False And _
[Forms]![frmInventory]![txtSDesc] <> "" Then
Descrip = [Forms]![frmInventory]![txtSDesc]
fltDescrip = "Descrip like '*" & Descrip & "*' "
If Forms.frmInventory.Filter = "" Then
Forms.frmInventory.Filter = fltDescrip
Else
Forms.frmInventory.Filter = Forms.frmInventory.Filter & "and " & fltDescrip
End If
End If
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If IsNull([Forms]![frmInventory]![txtSPartNo]) = False And _
[Forms]![frmInventory]![txtSPartNo] <> "" Then
PartNo = [Forms]![frmInventory]![txtSPartNo]
fltPartNo = "PartNo like '" & PartNo & "*' "
If Forms.frmInventory.Filter = "" Then
Forms.frmInventory.Filter = fltPartNo
Else
Forms.frmInventory.Filter = Forms.frmInventory.Filter & "and " & fltPartNo
End If
End If
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If IsNull([Forms]![frmInventory]![txtSLocation]) = False And _
[Forms]![frmInventory]![txtSLocation] <> "" Then
Location = [Forms]![frmInventory]![txtSLocation]
fltLocation = "Location like '" & Location & "*' "
If Forms.frmInventory.Filter = "" Then
Forms.frmInventory.Filter = fltLocation
Else
Forms.frmInventory.Filter = Forms.frmInventory.Filter & "and " & fltLocation
End If
End If
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~




If Forms.frmInventory.Filter = "" Then
ClearFilter
Else
Forms.frmInventory.FilterOn = True
End If


End Sub

Any suggestions would be appreciated, as always. You guys make me look like I know what I'm doing!

[June7]

Have you step debugged? Refer to link at bottom of my post for a tutorial on debugging techniques.

Part of issue might be the "and ". Include a space in front, like: " and ".

If you want to provide project for analysis, follow instructions at bottom of my post.

[NISMOJim]

I tried adding the space before the " and", but it didn't make any difference. I just tried stepping through the code. When I get to the first If statement, I get a run time error 2186 "This property isn't available in design view" message. I guess I don't understand how to check what the filter is doing from design view. I'll try to attach the DB and hope you can find why the filter acts this way. Thank you for your help.

[June7]

The form is saved with a value in the Filter property of "Cat like '2'". So when the form opens and the first search is run, that string is concatenated into the search string. When I did search for a DispDate, the criteria was "Cat like '2' and DispDate like '7/5/2013*'". Then when you Clear Search that string is removed from the Filter property and subsequent searches work.

Remove the string from the Filter property and save form. Can also clear the Filter property at the beginning of the UpdateFilter procedure just to be sure it always starts cleared.

[NISMOJim]

I'da swore I had a "ClearFilter" in the "On Open" property of the form. I added it and it seems to be working right the first time now. Thank you for your guidance.