Alert message when number exceeds a fixed number

[agnelofer1]

I have set-up an education database where end users can allocate candidates to a particular course date. But when the number of allocated candidates for that particular course for that course date goes beyond 24 then a messages prompts the end user.

For simiplicity, i can tell you that I have created a query 'course_counter_qry' which has 3 columns as shown below:
Column 1 [course]
1.ECG Basic Rhythm
2.ECG Basic Rhythm

Column 2 [course_date]
1.25/10/2011
2.01/11/2011

Column 3 [count_of_each_course_day]
1.24
2.10

each record in column 3 shows the current number of candidates that are allocated for that particular course day. Eg: 24 candidates are allocated for ECG Basic Rhythm on 25/10/2011

Now when the end user is going to allocate another candidate for ECG Basic Rhythm on 25/10/2011, which is done on a seperate form [candidate_allocation_form] on a control (final_course_date) based on a table [candidates_tbl], i need a message to prompt to the user "Candidates exceed total number of 24".

I was thinking like, OnUpdate of the 'final_course_date' control, we should open 'course_counter_qry' as recordset and then may be check if the particular course date exceeds 24 and if so then message prompts ...but i am not able to get my head around.

Also if any other method is suitable, then let me know.. your help is much appreciated.

 

[Mr. B]

First, I would never store the number of candidates assigned to any class on any date. This is a calculated value and therefore should not be stored, but should be calculated each time it is needed.

Now when the end user is going to allocate another candidate for ECG Basic Rhythm on 25/10/2011, which is done on a seperate form [candidate_allocation_form] on a control (final_course_date) based on a table [candidates_tbl],

Second, I would suggest that you perhaps take a more proactive approach. You mentioned that you have another form where the user is actually allocating the candidate to the class on a specific date.

I would suggesnt that you have the control (I am assuming that the candidates may be listed in a list box or you may have a text box where the name can be typed in.) where the candidate is identified to be disabled when your form opens. Then require the user to first select the course and the date and when they have selected both of these, you would be able to use DCount to determine immediately how may candidates are currently allocated to that course on that date. Once you have the count from DDount, you can display the count with a message by using a label on your form that you populate and display to the user. If the count returned by DCount is less than 24 then you simply enable the control that allows the user to select the candidate, otherwise the control would remain disabled and no additional candidate can be assigned.

Just my two cents.