Limiting records

N

Ned

I have a situation where I need to limit the number of records.
We have an ongoing program, where attendees are coming and going throughout
the year. Each attendee is assigned a mentor, but I need to limit the mentor
to only 30 active participants.
So, in my attendee table, I have the fields "name", "status"
(active/inactive), and mentor-id (linked to my mentor table).
If a 31st attendee is added to a mentor, I need to issue a warning and
prevent the record being added.
What's the best way to accomplish this?
Thanks in advance,
-Ned.
 
J

John Vinson

I have a situation where I need to limit the number of records.
We have an ongoing program, where attendees are coming and going throughout
the year. Each attendee is assigned a mentor, but I need to limit the mentor
to only 30 active participants.
So, in my attendee table, I have the fields "name", "status"
(active/inactive), and mentor-id (linked to my mentor table).
If a 31st attendee is added to a mentor, I need to issue a warning and
prevent the record being added.
What's the best way to accomplish this?
Thanks in advance,
-Ned.

There's no easy way to do it in a Table (Access tables don't have
"triggers" which would be the solution in SQL/Server or Oracle).

WHat you can do is ensure that all updating is done using a Form, and
use the Form's Beforeinsert event to trap attempts to add a new
record. The simplest way would be to have a Form based on the Mentor
table, with a subform showing that mentor's mentees; in the subform's
BeforeInsert event you'ld use DCount() to count the number already
assigned and cancel the insert if there are already 30.

John W. Vinson[MVP]
 
K

Klatuu

Dim intMentCount As Integer

intMentCount = DCount("*", "attendee", "[mentor_id] = " & Me.Mentor & _
" And [Status] = 'active'")
If intMentCount >= 30 Then
MsgBox "Mentor " & Me.Mentor & " Already Has " & intMentCount & _
" Attendees Assigned", vbExclamation + vbOkOnly
End If
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top