Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jan 2003
    Location
    Eau Claire, Wisconsin, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Restricting Record Selections (Access 2000)

    Howdy!
    I have a continuous form based on a query. One of the controls is a yes/no box.

    Is it possible to determine when 10% of the total yes/no boxes have been checked and then disable the rest? If a previously checked box gets unchecked, then the rest would become re-enabled.

    Thanks for your thoughts.

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

    Re: Restricting Record Selections (Access 2000)

    In a continuous form, there is actually only one set of controls. If you disable a check box, it will be disabled in ALL records, so the user wouldn't be able to uncheck a box any more. Conditional formatting can enable/disable a control, but it is not available for check boxes. So you will have to use code to prevent the user from checking another check box if the limit has been reached. Here is an example, using the Discontinued field in the Products table from the NorthWind sample database:

    Private Sub Discontinued_BeforeUpdate(Cancel As Integer)
    If Me.Discontinued = True Then
    If DCount("*", "Products", "Discontinued=True") > DCount("*", "Products") / 10 Then
    MsgBox "Enough already!", vbExclamation
    Cancel = True
    End If
    End If
    End Sub

  3. #3
    Star Lounger
    Join Date
    Jan 2003
    Location
    Eau Claire, Wisconsin, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Restricting Record Selections (Access 2000)

    Thanks for the reply, Hans. That works great. However, I have to base the form on a query so I slightly modified your example by adding a "+1" into it.
    ...
    If DCount("*", "qryPromotionEligible", "Promote=True") + 1 > DCount("*", "tblPromotionEligible") / 10 Then
    ...
    This appears to work just like I envisioned and so far I'm happy - so there must be some flaw in my logic. What do you think?

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

    Re: Restricting Record Selections (Access 2000)

    My example displays an error message if more than 10% of the records is checked; by adding the + 1, you display an error message if at least 10% of the records is checked. That corresponds to your original question, so it is correct.

Posting Permissions

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