Filter causes Products to dissapear in Order form (1 Viewer)

[bjsteyn]

You cant maybe help me with a problem?

I split my DB into a FE and a BE since I have done it my order form is giving me a problem.

When I use my filter on my orders form (to select a product easier) my previous selected products dissapear. I have attached two pics so that you can understand my problem better.

My Current code for my filter is:
Private Sub btnSearch_Click()
Dim strFilterSearch As String
strFilterSearch = Nz(Me!txtSearchFilter, "*")

Me![Orders Subform].Form!ProductID.RowSource = "SELECT ProductName,ProductID,Discontinued FROM Products WHERE [ProductName] Like '*" & strFilterSearch & "*'ORDER BY ProductName"
'Debug.Print "SELECT ProductName,ProductID,Discontinued FROM Products WHERE [ProductName] Like '*" & strFilterSearch & "*'ORDER BY ProductName"

Me!ListCount = Me![Orders Subform].Form!ProductID.ListCount

 

[Guus2005]

It is the query that fills the listbox.
It is a query which consists of two (or more) objects which are left or right joined.
You left join the first part with the second part which is a subquery on which you filter. The first part shows all information but the names of your product. They are filtered out by the second part.

Confused?
Am i making any sense?

HTH

 

[bjsteyn]

If think I understand. (Not really!)
U cant maybe post the query you are talking about.

thanks
bj

 

[bjsteyn]

I have found a solution to my problem.

Now when you use my filter. The product filter I am looking for gets moved to the top.

Using the following code:
Me![Orders Subform].Form!ProductID.RowSource = "SELECT ProductName,ProductID,Discontinued FROM Products ORDER BY InStr(1, [ProductName],'" & strFilterSearch & "',1) DESC,ProductName"

I have attached a pic.