Search bar and filter using combo box on a continuous form (1 Viewer)

I have a continuous form with my record source as a query. The query has been set up so that the criteria for the field named "Inventory Type" is being defined by a combo box.
I have this working just fine by using this in the criteria.

[Forms]![Search Inventory]![combotype] OR [Forms]![Search inventory]![combotype] IS NULL

Now I am trying to create a search bar so that AFTER all of the inventory has been filtered the user can search through it to find what they need. However, the code I am using is messing with the combobox filter I have set up. It doesnt work when I have the search bar and code in the form..

This is the search bar code I have set up.
Private Sub searchbox_Change()
Dim strFilter As String
On Error GoTo ErrHandler
If Me.searchbox.Text <> "" Then
strFilter = "[Part description] Like '*" & Me.searchbox.Text & "*'
OR [part number] Like '*" & Me.searchbox.Text & "*'
OR [1st vendor] Like '*" & Me.searchbox.Text & "*'
OR [1st price] Like '*" & Me.searchbox.Text & "*'
OR [2nd part number] Like '*" & Me.searchbox.Text & "*'
OR [2nd vendor] Like '*" & Me.searchbox.Text & "*'
OR [2nd price] Like '*" & Me.searchbox.Text & "*'
OR [3rd part number] Like '*" & Me.searchbox.Text & "*'
OR [3rd vendor] Like '*" & Me.searchbox.Text & "*'
OR [3rd price] Like '*" & Me.searchbox.Text & "*'"
Me.Filter = strFilter
Me.FilterOn = True
Else
Me.Filter = ""
Me.FilterOn = False
End If
With Me.searchbox
.SetFocus
.SelStart = Len(Me.searchbox.Text)
End With
Exit Sub
ErrHandler:
MsgBox Err.Description, vbExclamation

End Sub



Does anyone know how I can BOTH of these things to work? It would make searching and filtering a lot easier. Thanks in advance.

Can I be brutally honest and say that this is a poor approach?

Normally people searching for things like a description, wouldn't be putting a price in, and if searching for a price wouldn't expect to bring up something that happened to have those numbers in the description?

Be specific about what you are searching for, and this becomes much simpler.

You also have a problem that your data set up is not normalised, what happens when vendor 4 arrives?
Your data should be stored in such a way to accomodate a variable number of vendors, without you having to re-invent the wheel.

Have a look at this search form database example for some really good advice and techniques http://allenbrowne.com/ser-62.html

Hey!

So yes I see what you are saying. However all of this is what is being requested by the company that its being developed for. They would never have more than three vendors (which is something that I thought was odd), but were certain that they wanted to leave it at just three.

As far as the approach goes to search, I never thought about it that way. I suppose at most they would search for the part numbers and/or the part description.

So even if I cut it down to just searching between those two, how can I still get both that and the inventory type combo box to work?

Thanks for the suggestion.

Actually JK on everything I just said.. I was going to try and do this a little differently but I am no where near at the level where I can do that. So thanks again for the link. I'm going to do that instead of whatever it was I was trying to do haha

Just because they say they wont' have more than 3 vendors, shouldn't influence the design.

If you design it accomodate a variable number, and even make the form "look" like it only supports three, you'll be doing your job properly, and more importantly look like a superhero when they change their mind and you simply say add a fourth and it will work...

Poor requirements do not and should not dictate poor design. And you should be able to explain why it's poor design, and a bad idea.

Sorry for the late response. I took what you said and applied that here. I'm very new to this so It didnt occur to me that you could do this until you brought it up and I looked into it. Ive separated my "inventory" table into two but have them related so that the user can enter as much information about the part as they want.