Search code examples
ms-accessfilterms-access-2013query-builderiif

List box and Option group filtering 3 different rows in MAIN list box in MS Access 2013

I've been struggling with the following for a while and would be more than happy for some brainpower ;)

I have an Advanced filter form, which filters through orders via many different filters, currently the one that I can't make function is the following:

I have 4 controls on a form (all unbound):

  1. ogProductType - option group that allows you to pick from 3 types of products

  2. lbAllProducts - list box displaying all types of products. It has 2 rows, and gets filtered by tbSearchProducts which searches by the name of the product, but the lb is bound by the first row which is the product code. The filter is done by inserting criteria into the name row: 

    "Like "*" & [tbSearchProducts] & "*"".

  3. tbSearchProducts - text box for searching the product by name. Has requery for lbAllProducts OnChange.

  4. Main orders list box - lbOrders - which has many rows. The ones we care about in this case are ID; eProductCode; fProductCode; kProductCode. It is supposed to display all IDs of all orders where the filters are true (all orders where type 1 product is bought). In the query builder I have the following code as criteria for each them:

In lbOrders:

for eProductCode row
IIf([ogProductType]=1 And Len(Nz([lbAllProducts]))>0;[lbAllProducts];"")

for fProductCode row
IIf([ogProductType]=2 And Len(Nz([lbAllProducts]))>0;[lbAllProducts];"")

for kProductCode row
IIf([ogProductType]=3 And Len(Nz([lbAllProducts]))>0;[lbAllProducts];"")

I want the Main list box to be filtered depending on what type of product was chosen (eProductCode is chosen with the option group set on First option = 1; fProductCode = 2; kProductCode=3), but ignore the other 2 rows totally. Sadly, with that false statement saying the criteria for the field is "", in case it was not chosen via the option group, it doesn't work.

How can I stack 3 criteria, all taking values from the same list box, but only if option group is the right on, without them interfering with each other?

I tried with "*" in false and it doesn't work...

Any ideas?

Solution

  • I've solved it :)

    You simply never use the criteria to filter the 3 rows... You just use the field itself to load the proper row that you need and put in criteria...

    Here is the code for the field:

    Expr1: IIf([obProducts]=1 And Len(Nz([lbSearchProduct]))>0;[tblOrders]![еProductCode];IIf([obProducts]=2 And Len(Nz([lbSearchProduct]))>0;[tblOrders]![fProductCode];IIf([obProducts]=3 And Len(Nz([lbSearchProduct]))>0;[tblOrders]![kProductCode])))

    And in criteria for that row I set the list box that has the bound column for product code lbAllProducts