Search box question on form

[matey56]

Hi all,


I have a continuous form with 3 search boxes in the header. Each box searches a field in the backend table. The user types the criteria and clicks the search button and it works perfectly. The only problem is each time a second/third search is executed it re-queries the entire table. I'd like it to only search the results from the first search. Does that make sense? Is that possible?

Here is how I set it up?

Private Sub btnSearchBASCode_Click()
Me.FIND.SetFocus
Me.FilterOn = False
Me.Filter = "[BASCode] Like '*" & FIND & "*'"
Me.FilterOn = True
Me.Requery


End Sub


Private Sub btnSearchLocationToller()
Me.FIND2.SetFocus
Me.FilterOn = False
Me.Filter = "[LocationToller] Like '*" & FIND2 & "*'"
Me.FilterOn = True
Me.Requery


End Sub


Private Sub btnSearchConsistencyStatus_Click()
Me.FIND3.SetFocus
Me.FilterOn = False
Me.Filter = "[ConsistencyStatus] Like '*" & FIND3 & "*'"
Me.FilterOn = True
Me.Requery
End Sub

[Welshgasman]

You need to combine the filters.
Test if each has a value and concaternate accordingly.

Create a sub in the form that does that and call that from your three subs.

[matey56]

Sorry, I'm not sure what you mean by combine the filters. Can you show me?

[davegri]

Have a look at this: comboboxes can be clicked in any order.

Ultimate Dynamic Multiple Cascading Comboboxes, synchronized in any order (accessforums.net)

[Welshgasman]

You use AND to combine your filters

Me.Filter = "[BASCode] Like '*" & FIND & "*'" & " AND " & "[LocationToller] Like '*" & FIND2 & "*'"

Debug.Print Me.Filter or put it into a string variable to see what you actually get, not what you think you have.

However you have to test each to see if you need to add that filter, as the control for it might be empty.