Happy new year to all,


I have a query which is driving a form showing a list of parts we supply.

The table which the query is filtering is called tbl_Products.

In tbl_Products i have a few check boxs for each part. These check boxes are used to help filter the results in the form. The same parts can be used on different machines, so i tick the check boxes for the machines which the same part

So part number XYZ may be used on machine ABC & F. so i tick the boxes in the table for ABC & F for part XYZ

My result are not filtering how i would like them to. In the form i want to click on all parts relating to machine A but it only shows the parts where A is the only check box selected. I would like it to show all the parts where checkbox A is selected. If checkbox A & B have been selected i would like it to show that parts to. So basically any combination as long as the part has A selected.

in my query, under Criteria, if i put -1 under check box A and this works. It shows parts with just A selected and other combinations containing A and "whatever"

however, i can't used -1 as the form is being driven by some other check boxes in the main form. So i need to see what the value of the check box is in the main form to filter the results.

In the design view of the query, i tried the following:-

Like IIf(IsNull([forms]![frm_Parts_Selection]![MachineA]),"0",[forms]![frm_Parts_Selection]![MachineA])

I have the same code for each machine in the criteria.

This didn't work as it would only display the part if i selected the right check boxes.

I think i need to mimic the -1 value somehow? So i look at the form, look at the filter checkboxes. i noticed say checkbox A (machineA) is selected. I convert this value to -1 and reference it in the query somehow, whick inturn drives the form to show only the parts where A have been selected any any combination involving A.

that's what i think i need to do, but i would be grateful of any help.

thanks
marcus

ok well after many hours of searching the internet, trialing of generally failing and feel like poo i think i have a solution.

When i click on the checkbox in the form to view say parts for machineA i write either a 0 or -1 to an unbound text box.

see below

Private Sub CTXMachineA_Click()

If Me.MachineA.Value = -1 Then
Me.txtbox00A = -1
Else
Me.txtbox00A = 0
End If

Me.Requery
End Sub


I then reference this textbox in the query which controls the form.

So in the query i have

MachineA

[Forms]![frm_Parts_Selection]![txtbox00A]

So bascially if the two checkboxes match (one on the form and one in the parts field) just that part is displayed. The part may be used on other machines but if MachineA has been ticked the part is displayed..

I'm sure there is a more graceful way of doing this but for now it works
thanks
Marcus

spoke too soon. it doesn't work

When i put the code in the criteria for the other machines i'm back to square one or should i say -1

dam