If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back  dBforums > PC based Database Applications > Microsoft Access > check box and -1

LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Registered User
Join Date: Sep 2010
Location: UK
Posts: 153
check box and -1

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.

Reply With Quote
  #2 (permalink)  
Registered User
Join Date: Sep 2010
Location: UK
Posts: 153
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
Me.txtbox00A = 0
End If

End Sub

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

So in the query i have



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
Reply With Quote
  #3 (permalink)  
Registered User
Join Date: Sep 2010
Location: UK
Posts: 153
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

Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On