Clearing multiple form fields kills search function

[WLH]

I've got a form with 3 search fields. The fields are not intended to be used in conjunction, but they do all display their results in the same results pane.

When I first start the application, MY search functionality works just as it's supposed to. But when I clear the fields either with the "Clear Filter" button I've got in place or if I delete the information from ANY of the three fields Search functionality stops.

here is the code I have attached to the fields and corresponding buttons


Private Sub Command18_Click()
Me.DocFilter.SetFocus
Me.DocFilter.Text = ""
Me.DocFilter2.SetFocus
Me.DocFilter2.Text = ""
Me.DocFilter3.SetFocus
Me.DocFilter3.Text = ""
Me.FilterOn = False
List0.Requery
End Sub

Private Sub Command4_Click()
Me.FilterOn = False
List0.Requery
End Sub

Private Sub DocFilter_AfterUpdate()
Command4_Click
End Sub

Private Sub DocFilter2_AfterUpdate()
Command4_Click
End Sub

Private Sub DocFilter3_AfterUpdate()
Command4_Click
End Sub


Private Sub List0_Click()
If Len(Me.List0.Value) > 0 Then
Me.Filter = "ID_NO=" & Me.List0.Value
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End Sub

Any thoughts?

[lagbolt]

What's the RowSource of the list? I don't see any connection between the List and TextBoxes. Everything removes the filter except a click on the list.
What does requerying the list do?

[WLH]

I've go a SQL query attached to List0. The search criteria entered into the DocFilter fields is inserted into that query to populate List0.

Is that what you needed to know? I'm far from an expert, or even intermediate for that matter. I'm just trying to learn.

[lagbolt]

What do you mean the "Search functionality stops." Do you get errors? List won't populate?
Also, post the query text. That seems an integral part of the system you want to troubleshoot.

[WLH]

After I clear the DocFilter fields the list will not longer populate regardless of the information entered.

here is the query in use


SELECT Drawing.DWG_NO, Drawing.DRAWING_desc, Drawing.ID_NO
FROM Drawing
WHERE (((Drawing.DWG_NO)
Like Replace("*" & Replace(Replace(Replace(Replace([Form].[DocFilter],"/","*"),"-","*"),"\","*"),"_","*") & "*","**","*")))
OR (((Drawing.DRAWING_desc) Like "*" & [Form].[DocFilter2] & "*"))
OR (((Drawing.EQUIP_NO) Like Replace("*" & Replace(Replace(Replace(Replace([Form].[DocFilter3],"/","*"),"-","*"),"\","*"),"_","*") & "*","**","*")));

[lagbolt]

Even if you enter something in all three of your search boxes?

[WLH]

Ah

If I enter something that would return results in all three boxes, then I get results. Even after Clearing the filter.

If I enter something in box 1 and 3(numeric), I get results

If I enter something in box 1 and 2, or box 2 and 3, I get nothing.

If there something wrong with my query, Or in the VB code causing the error? Or, knowing my luck, both?

[WLH]

bit more clarification, sorry.

When I initially open the Form I can enter information into any combination of the 3 fields I'm using and get results.

If I modify the information in any of the fields I get updated results to match the input.

The moment I hit the "Clear Filter" button (command18) I have to use either fields 1,3 or 1,2,3 to get results.

I get no results from single field criteria, or fields 1,2 or 2,3

The same thing happens if i highlight and delete information from any 1 of the fields (this includes deleting, or backspacing the field data away)

so....is it something simple, or am I in a tight spot?

[lagbolt]

I believe the problem is with the WHERE clause of your query. If you delete data from a text box the value is Null. All math or comparisons done with a Null results in a Null. I believe this is killing your query.

The only criteria that should appear in the query'e WHERE clause are those for which a textbox on the form has usable data. This complicates your life since you'll need to construct the where clause dynamically.

What I would do is remove the complicated nested Replace() statements from the query. Troubleshooting or debugging something that looks like this

Replace("*" & Replace(Replace(Replace(Replace([Form].[DocFilter3],"/","*"),"-","*"),"\","*"),"_","*") & "*","**","*")))

is...well...tedious. Are you replacing all those special characters with '*'?

Create a function that does this. Run your user input through that function. Save the function's output to a hidden textbox or variable. If your function's return value has data suitable for a search, ie 'Not IsNull()', then write the where clause and assign the resulting sql to the rowsource of the list.

Does that make sense? Does that sound really hard? Heres a little chunk of code that replaces various characters in a string with some other character.

Function ReplaceChars(ByVal someString As String) As String
   Const REPLACE_WITH As String = "*"
   Dim varChars As Variant
   Dim var As Variant
   
   'construct array of characters to be replaced
   varChars = Split("\ / _ -")
   'traverse the array
   For Each var In varChars
      'replace the current var with the REPLACE_WITH
      someString = Replace(someString, CStr(var), REPLACE_WITH)
   Next var
   'assign to function
   ReplaceChars = someString
End Function

This exposes a where clause that recalculates everytime you reference it...

Property Get WhereClause() As String
   Dim str As String
   If Not IsNull(Me.DocFilter) Then
      str = "OR Drawing.DWG_NO Like '*" & Me.ReplaceChars(Me.DocFilter) & "*' "
   End If
   If Not IsNull(Me.DocFilter2) Then
      str = str & "OR Drawing.DRAWING_desc Like '*" & Me.DocFilter2 & "*' "
   End If
   If Not IsNull(Me.DocFilter3) Then
    str = str & "OR Drawing.EQUIP_NO Like '*" & Me.ReplaceChars(Me.DocFilter3) & "*' "
   End If
   If Len(str) > 0 Then str = "WHERE False " & str
   WhereClause = str
End Property

So you're almost there. Now, on every AfterUpdate you want to set the rowsource of the list to ...

me.lst.rowsource = _
  "SELECT Drawing.DWG_NO, Drawing.DRAWING_desc, Drawing.ID_NO " & _
  "FROM Drawing " & _
  WhereClause

Maybe you need to requery the list when you change the rowsource. Not sure.

See if that gets you somewhere.