Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160

    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.

    thanks
    marcus

  2. #2
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    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

  3. #3
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •