Count Validation Expression

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to limit the number of records that can have a checkbox clicked. I
created a query that counts the number of records with that checkbox clicked
and tried to make a form validation expression "(Count total of field) <=
(maximum number of records permitted to have this checkbox clicked)" and it
doesn't work. Help!

I also tried using a Count function expression, but that didn't work, any
ideas?
 
If you're comfortable with VBA, use the form's OnCurrent event to
evaluate the number of "checked" records. You could probably use the
DCount function for this. If the count exceeds your threshold, set the
checkbox control's property to Locked=True. If it's less than the
threshold, to the locked to False.

HTH,
Barry
 
I created a query that returns the count of checked records equal to Yes. I
then added code to the OnCurrent Event as seen below:

Dim TotalCount As Integer

TotalCount = DLookup("[CountOfChecked]", "qryTotalChecked")

If TotalCount >= 8 Then
Me.Checked.Visible = False
End If
If TotalCount < 8 Then
Me.Checked.Visible = True
End If

In order to get to the OnCurrent Event. Open your form in design view. Open
the properties for the entire form by clicking the tiny black box at the
upper left corner of the form. Click on the Event Tab. Set the OnCurrent to
EventProcedure and then click at the end of the statement in the non-white
area. Mine is grey, but the color depends on your Windows settings. This
will activate the ... and send you over to the code editor window. This is
where you type the above code substituting your field names for mine and
your query name for mine. The value 8 is an arbitrary number I picked; so if
your value is 15, then you would replace 8 with 15.

What this code does is evaluate the count. If the count is met or is higher
then the Checked box on the form is completely hidden. Then no one could
even check the box if they wanted. If the count is less than the value set,
the checked box will show. Once the value is met, the checked box will
disappear.

The disadvantage of this is that in order to uncheck a box you would need
someone to change the value in the table.

You might be able to use the Afterupdate Event instead with the above code,
changing the Checked box to become locked/unlocked instead.

I haven't tried this yet.
 
Back
Top