Restricting Record Selections (Access 2000)

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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

Reply | Quote