473,326 Members | 2,061 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Validation Rule in Access form

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.

Mar 31 '06 #1
5 5567
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
<mi************@gmail.com> wrote in message
news:11**********************@j33g2000cwa.googlegr oups.com...
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.

Mar 31 '06 #2
No luck... though that may be my not knowing anything about VBA and
accidentally messing something up. Any other ideas?

Mar 31 '06 #3
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
<mi************@gmail.com> wrote in message
news:11*********************@v46g2000cwv.googlegro ups.com...
No luck... though that may be my not knowing anything about VBA and
accidentally messing something up. Any other ideas?

Mar 31 '06 #4
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.

Mar 31 '06 #5
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
<mi************@gmail.com> wrote in message
news:11**********************@u72g2000cwu.googlegr oups.com...
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.

Mar 31 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Dalan | last post by:
I apparently need a bit of assistance regarding the structure of some validation code on the BeforeUpdate or AfterUpdate event on a form for several fields that need to controlled. I did search the...
2
by: Dalan | last post by:
This ought to be simple enough, but not certain which to use. I have a few fields set to Require data to be entered; however, the message displayed by Access 97 is too generic to be of any real...
0
by: Steve V | last post by:
I'm using Access 2000 to build a budgeting/tracking database. Can I make a validation rule (using VBA) that checks the data as if the record has already been added? I've got 5 tables (only the...
2
by: Kostas | last post by:
Just a quick question on this issue. Assume a small set of fixed values for a field. For instance Field Gender, values: Male, Female, Unknown If I create a Combo Box with the above values, I...
6
by: Chuck | last post by:
A97. A database has a table: tblA which has a single text field, B. It is a primary field, indexed and no duplicates. It is used as a lookup for table tblC. A form based on tblA is used to add...
5
by: Don Sealer | last post by:
I've got a validation rule that says something like =xx or xxx or zzzz. Though I may not have described it well it does work fine and that is really not my question. Here's what I'd like to do....
3
by: BrianB830 | last post by:
Hello all, I have a quick question regarding an MS Access database I'm creating. In the entity "ORDER", I have the attributes "Order Date" and "Delivery Date". I need to create a validation...
10
by: gweasel | last post by:
What is the best way to apply a Validation Rule - or rather, where is the best place to put it? Is there an advantage to putting it on the field in the table vs setting the validation rule on the...
3
by: Alex.Sh | last post by:
How can i do something like this: i have a form with A percentage : ______ B percentage: ______ C percentage: ______ I want that the sum of these 3 fields *will never* be more than 100...
7
by: sharsy | last post by:
Hi guys, I would like to setup a validation rule for a database in microsoft access that restricts data entry so that a certain field can only be filled in if another field has a specific answer...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.