Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    form based query chk boxes (A2000)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: form based query chk boxes (A2000)

    12 check boxes offer 2^12 = 4096 distinct combinations <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    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?

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: form based query chk boxes (A2000)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: form based query chk boxes (A2000)

    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 <code>[Forms]![frmCriteria]![chkAdvocates]</code>.
    Clear the Show check box for this column.
    In the Criteria row, enter <code>[ysnAdvocates] Or Is Null</code>
    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.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: form based query chk boxes (A2000)

    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: form based query chk boxes (A2000)

    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

  7. #7
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: form based query chk boxes (A2000)

    works perfectly

Posting Permissions

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