How to limit the number of rows?

[Jak]

Hi there,

I have alittle problem and I thought it would be quite simple, obviusly I was wrong. I searched as many forums as I could and found many other interesting stuff.

The problem which I have is, I have two tables one called modules and the other called students
The module table has an attribute called "maxstudents". This records the maximum spaces available for that module.i.e 10.

I want a code which shall count the number of ROWS of students who have already enrolled onto a particular module and ONLY allow them to be enrolled if the number of students including them is less than the "maxstudents" value. If however the number is greater, then a msg should appear and the entry should NOT be allowed. Basically I want some way to limit the number of rows of data to be entered into a table. Ps. Can all this be done using the click of a button i.e "Add Student." I think this has something to do with COUNT OR DCOUNT. SOME OF THE SQL STUFF I PUT IN KEEPS ON PUTTING UP SYNTAX ERROR, MAYBE I PUT IT IN THE WRONG PLACES OR SOMETHING

Many thanks in advance.
Jak

 

[Parker]

Hi Jak

You do not need to limit the number of rows in a table (indeed i'm not even sure if this is poss,)

I would do this in code.

Read up on IF and Else statements ------ This is what you need.

Count the number of entries in a table then
If entries in some table is >9 then
Call a msg box to give a mesage
else (make the entry)

If, when your code runs the statement is true it will execute one instruction and if it is false it will execute the other.

Put this in the 'on click' event for a button

 

[Rich]

Do it at subForm level where the number of entries can be limited easily by code

 

[Jak]

Thanks for the replies guy,

What if I do it in this order

IF COUNT (*) => STUDENT.MAXSTUDENT
THEN 'MSGBOX'
ELSE 'MAKE THE ENTRY'

I only suggest this, because the if then else depend on the result of the count. Does the result of the count done be recorded or used for the If then else statement bits in your original suggesstion.

P.S. What do I have to enter for the make the entry bit. And you can't search for IF THEN OR ELSE using the search functions.

Thanks and apologies to all.

 

[Pat Hartman]

Add a footer to the subreport (make sure you enlarge the subreport control on the main form if you want to see it). Then add a control and make its ControlSource: =Count(*) Then change its Name to:txtCountEnrolled

In the Current event of the subform add the following:

If Me.txtCountEnrolled > 9 Then
    Me.AllowAdditions = False
Else
    Me.AllowAdditions = True
End If

 

[paulreed]

That works fine, but the 'txtcountenrolled' needs to be continually refreshed, otherwise it will not recognise that the number of records has increased.

 

[Rich]

Use the forms RecordSetClone.RecordCount method and the FormCurrent event