Validation Rule in Access form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

michael.meenan@gmail.com - 31 Mar 2006 18:22 GMT

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?

Basically, I'm setting up a registration DB for a conference, and need
to limit the number of people who can register for particular workshops.

Reply to this Message

tina - 31 Mar 2006 18:42 GMT

try the following code in the checkbox control's BeforeUpdate event, as

If Me!CheckboxControlName Then
If DCount(1, "QueryName") = (max number allowed) Then
Cancel = True
Msgbox "This workshop is full."
End If
End If

replace CheckboxControlName with the actual name of the control. replace
(max number allowed) with the number or the reference to wherever you're
storing the number, and leave out the parentheses. replace QueryName with
the name of your query that counts the checkmarked records.

hth

> 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
[quoted text clipped - 7 lines]
> Basically, I'm setting up a registration DB for a conference, and need
> to limit the number of people who can register for particular workshops.

Reply to this Message

michael.meenan@gmail.com - 31 Mar 2006 19:25 GMT

No luck... though that may be my not knowing anything about VBA and
accidentally messing something up. Any other ideas?

Reply to this Message

tina - 31 Mar 2006 19:30 GMT

no other suggestions, no. if you want to work with the code until it runs,
post the code you tried (with the replacements that i instructed you to
make), and i'll try to help you troubleshoot it.

hth

> No luck... though that may be my not knowing anything about VBA and
> accidentally messing something up. Any other ideas?

Reply to this Message

michael.meenan@gmail.com - 31 Mar 2006 19:34 GMT

Private Sub Men_BeforeUpdate(Cancel As Integer)
If Me!Men Then
If DCount(1, "zMenTotal") = 15 Then
Cancel = True
MsgBox "This workshop is full."
End If
End If
End Sub

The only field in the query zMenTotal is MenTotal, which is done by
COUNT(Men) AS MenTotal FROM Workshops WHERE Men=True. However, it pulls
from the checkbox Men which is located in table Workshops. The name of
the control in the form is also Men.

Reply to this Message

tina - 31 Mar 2006 19:48 GMT

ok, since the query is a Totals query, change the DCount() function to a
DLookup() function, as

Private Sub Men_BeforeUpdate(Cancel As Integer)
If Me!Men Then
If DLookup("MenTotal", "zMenTotal") = 15 Then
Cancel = True
MsgBox "This workshop is full."
End If
End If
End Sub

hth

> Private Sub Men_BeforeUpdate(Cancel As Integer)
> If Me!Men Then
[quoted text clipped - 9 lines]
> from the checkbox Men which is located in table Workshops. The name of
> the control in the form is also Men.

Reply to this Message