• form based query chk boxes (A2000)

    Author
    Topic
    #406190

    I’d like to be able to use a form to select query criteria for a series of check boxes. There are 12 boxes which creates, I believe, 105 difference on/off combinations for the buttons. There’s got to be an easy way to do this…I hope?

    E

    Viewing 0 reply threads
    Author
    Replies
    • #840370

      12 check boxes offer 2^12 = 4096 distinct combinations grin

      Can you provide some details of what you want to accomplish? Do you have 12 yes/no fields, and do you want to set selection criteria for each through a check box? If so, what are the names of the fields?

      • #840377

        4096. Yelp! It’s even worse, there are 14 boxes now.

        I have 14 yes/no fields named: ysnAdvocates, ysnBoard, ysnEmployee, ysnISSI, ysnNewsletter, ysnMedia, ysnShp, ysnBlank1 thru ysn Blank7 and I want to set selection criteria via a form but I don’t care how on the form.

        E

        • #840383

          Create a form with 14 – or however many you need – unbound check boxes, i.e. their control source is blank.
          Set their Triple State property to Yes. You can do this for all check boxes at once, if you like.
          Give them meaningful names, for example chkAdvocates, chkBoard, …, chkBlank7.
          Let’s say that the form is named frmCriteria.

          Create a query based on your table, or on an already existing query.
          Add the fields you need, for example to display them in a form or report, to the query grid.
          Next, add a column [Forms]![frmCriteria]![chkAdvocates].
          Clear the Show check box for this column.
          In the Criteria row, enter [ysnAdvocates] Or Is Null
          Repeat for the other 13 or so check boxes and the corresponding yes/no fields.

          Now, open the form, and tick or clear some of the check boxes.
          Then, switch the query to datasheet view to check that the correct records are returned.
          Note: check boxes in the neutral (grayed) state will not impose a restriction.

          • #840546

            Got a further twist. Some of the records have multiple checks which screens them out of the query results. How do I handle it so that if, for example, I check ysnNwltr, every record with a check in ysnNwltr is returned regardless of what else is checked or not checked and so on…

            E

            • #840551

              Create an After Update event procedure for the check box associated with ysnNwltr to set all other check boxes to Null (gray) if it is ticked. If the 14 (or whatever) check boxes are the only ones on the form, you can use a loop instead of enumerating the individual check boxes. In the following, chkNwltr is the name of the check box.

              Private Sub chkNwltr_AfterUpdate()
              Dim ctl As Control

              If Me.chkNwltr = True Then
              For Each ctl In Me.Controls
              If ctl.ControlType = acCheckBox And Not ctl.Name = “chkNwltr” Then
              ctl = Null
              End If
              Next ctl
              End If

              Set ctl = Nothing
              End Sub

            • #840673

              works perfectly

            • #840674

              works perfectly

            • #840552

              Create an After Update event procedure for the check box associated with ysnNwltr to set all other check boxes to Null (gray) if it is ticked. If the 14 (or whatever) check boxes are the only ones on the form, you can use a loop instead of enumerating the individual check boxes. In the following, chkNwltr is the name of the check box.

              Private Sub chkNwltr_AfterUpdate()
              Dim ctl As Control

              If Me.chkNwltr = True Then
              For Each ctl In Me.Controls
              If ctl.ControlType = acCheckBox And Not ctl.Name = “chkNwltr” Then
              ctl = Null
              End If
              Next ctl
              End If

              Set ctl = Nothing
              End Sub

          • #840547

            Got a further twist. Some of the records have multiple checks which screens them out of the query results. How do I handle it so that if, for example, I check ysnNwltr, every record with a check in ysnNwltr is returned regardless of what else is checked or not checked and so on…

            E

        • #840384

          Create a form with 14 – or however many you need – unbound check boxes, i.e. their control source is blank.
          Set their Triple State property to Yes. You can do this for all check boxes at once, if you like.
          Give them meaningful names, for example chkAdvocates, chkBoard, …, chkBlank7.
          Let’s say that the form is named frmCriteria.

          Create a query based on your table, or on an already existing query.
          Add the fields you need, for example to display them in a form or report, to the query grid.
          Next, add a column [Forms]![frmCriteria]![chkAdvocates].
          Clear the Show check box for this column.
          In the Criteria row, enter [ysnAdvocates] Or Is Null
          Repeat for the other 13 or so check boxes and the corresponding yes/no fields.

          Now, open the form, and tick or clear some of the check boxes.
          Then, switch the query to datasheet view to check that the correct records are returned.
          Note: check boxes in the neutral (grayed) state will not impose a restriction.

      • #840378

        4096. Yelp! It’s even worse, there are 14 boxes now.

        I have 14 yes/no fields named: ysnAdvocates, ysnBoard, ysnEmployee, ysnISSI, ysnNewsletter, ysnMedia, ysnShp, ysnBlank1 thru ysn Blank7 and I want to set selection criteria via a form but I don’t care how on the form.

        E

    Viewing 0 reply threads
    Reply To: form based query chk boxes (A2000)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: