Limiting Class Size (1 Viewer)

jane4587

New member
Local time
Today, 10:56
Joined
Sep 8, 2019
Messages
5
Hello Everyone,
I am having some probelms with my database. I have the following tables with appropriate information
Family Information
Child information
Class list
class roster - where we add the kids to the appropriate drop down box icon from class list.

Here is what I need to do. I need to LIMIT how many kids can be in a certain class. I also want an error message to show up when people attempt to put a certain kid in a class that is full. However I want to be able to change my class size say from 8 to 12.

How do I do this?
 

June7

AWF VIP
Local time
Today, 09:56
Joined
Mar 9, 2014
Messages
5,423
Use VBA code in control (combobox ?) BeforeUpdate event to count records for the class. Use a DCount() domain aggregate function.
 

Micron

AWF VIP
Local time
Today, 13:56
Joined
Oct 20, 2018
Messages
3,476
in addition, you will have to compare the results of the count to the current limit for the class and allow or not. However, I think your form ought to display the limit and the current enrollment count upon form opening. Then all you'd have to do is add the attempted number to the form field holding the current count and determine if the result exceeds the limit. The field to show the current count is for the user to see how many they can add, which ought to be better than letting them try some number only to find that they can't add that many. If you did it that way, then what? They keep reducing and trying again until it works?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:56
Joined
Feb 19, 2002
Messages
42,971
Displaying the maximum allowed and the current count will be useful to the user but I would NOT use them as part of the validation. When a user goes to add someone, you want to get the latest information available. For all you know, the form has been open and sitting idle for two hours and others have been adding students to the class. So, always use dCount() in the Form's BeforeUpdate event at the end of the procedure after everything else has been calculated and cancel the update if the class is already full.
 

jane4587

New member
Local time
Today, 10:56
Joined
Sep 8, 2019
Messages
5
Excuse my ignorance but exactly where and how would I put this in? Or rather which places if there are more than one.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:56
Joined
May 7, 2009
Messages
19,169
create a form for your Class Roster table (eg, datasheet form).
add code to Class textbox's BeforeUpdate Event:
Code:
Private Sub Class_BeforeUpdate(Cancel As Integer)
    Cancel = Nz(DCount("1", "[Class Roster]", "[Class]=" & [Class]), 0) >= 12
    If Cancel Then
        MsgBox "This class is already full"
    End If
End Sub
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:56
Joined
Feb 19, 2002
Messages
42,971
Arnel is on the right track but you should NOT hardcode the student count. You should also use the correct event which is the Form's BeforeInsert event. The student count is an attribute of the class and should come from the class table so it can be different for each class.
Code:
Private Sub Form_BeforeInsert(Cancel As Integer)
If DCount("*", "tblClassRoster", "ClassID = " & Me.ClassID) >= Nz(DLookup("StudentLimit", "tblClass", "ClassID = " & Me.ClassID),0) Then
    Cancel = True
    MsgBox "This class is already full.  No more students are allowed."
    Me.Undo
    Exit sub
End If
End Sub
The BeforeInsert event runs as soon as the user types a single character into any bound field.
 
Last edited:

June7

AWF VIP
Local time
Today, 09:56
Joined
Mar 9, 2014
Messages
5,423
@PatHartman, you say to use BeforeInsert but did you mean BeforeUpdate as shown in code?
I just tested and BeforeInsert executes when typing a single character into any bound field of new record, not existing. I have never used BeforeInsert.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:56
Joined
Feb 19, 2002
Messages
42,971
No. I meant BeforeInsert. Thanks. I fixed the code.

You only want the code to run when you are inserting a new record. You don't want it to run if you are updating existing records. The code could be placed in the BeforeUpdate event of the form, but it would have to be embedded in another If:
Code:
If Me.NewRecord Then
    ///code from above
End If
 

June7

AWF VIP
Local time
Today, 09:56
Joined
Mar 9, 2014
Messages
5,423
Okay, but I would still prefer executing code only once as with BeforeUpdate.

What if user wanted to modify an existing record?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:56
Joined
Feb 19, 2002
Messages
42,971
We're talking here about limiting the number of records "added" to the roster. The question has nothing to do with updating existing records. Updating an existing record would not require checking whether or not the count was exceeded. This particular code is only needed when someone tries to add a new record. The point of doing it in the BeforeInsert event is that event runs immediately as soon as the user types the first character into a NEW record. If the max number of students have been added already, there is no reason to allow the user to continue typing and waste his time. If you do the validation of this in the BeforeUpdate event, you have to let the user enter the entire record for no reason. It is a waste of his time and he will resent it. No need to make the users angry with the application. You know immediately whether or not it is valid to add a new student. Don't prolong the data entry unnecessarily.
 

June7

AWF VIP
Local time
Today, 09:56
Joined
Mar 9, 2014
Messages
5,423
I should have clarified I would use BeforeUpdate of control, not form.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:56
Joined
Feb 19, 2002
Messages
42,971
If you use the BeforeUpdate event of a control, you still need to check first for Me.NewRecord since the code we are discussing should NOT apply to an existing record.

The BeforeUpdate event of a control is not terrible but it still requires the user to enter all the text of at least one field rather than just a single character. I'm not sure why you think this is better but it isn't nearly as bad as letting the user enter the entire record before rejecting the input.

Personally, I rarely use the BeforeUpdate event of a control since that event is not capable of ensuring that data is actually entered so rather than split up the validation code for each field, I do it all in the BeforeUpdate event of the FORM where I can ensure that required fields are present, related fields are proportional (i.e. Start Date <= End Date), and any other requirements are met.

Given all that, I would use the BeforeUpdate event of a control if I needed to prevent duplicates. For example, entering a SSN. If the application does not allow duplicate SSN's (and it shouldn't), it is less confusing for the user to check this immediately rather than allowing the entire record to be entered before looking to see if the SSN already exists.

The various control and form events were designed for specific types of processing. You can get away sometimes with doing things in an event intended for a different purpose but there is always a price to pay.

I found a horrendous example of this a few years ago. An app I took over was saving incomplete or even invalid data. I examined the code and found THOUSANDS of lines of duplicate code. The programmer had noticed the problem and tried (unsuccessfully) to fix it. He went as far as validating EVERY field in several control events of EVERY control. So he validated up to 50 fields * 6 control events (Click, Dirty, Change, Lost Focus, Mouse Up, Exit) * 50 fields. fld1 validated fld1. fld2 validated fld1 and fld2. fld3 validated fld1, fld2, and fld3, etc. And this was repeated in multiple forms. So we got up to about 10,000 lines of useless code. The code was useless because NONE of it was in either the control's BeforeUpdate event or the Form's BeforeUpdate event where it belonged. So the code raised lots of errors but never stopped the bad data from being saved. It also caused a certain amount of confusion because he was sloppy about managing the tab order so on a couple of forms, fld6 was earlier in the tab order than fld4 so flds5 & 6 were being checked for null before they could reasonably be expected to be present.
 

jane4587

New member
Local time
Today, 10:56
Joined
Sep 8, 2019
Messages
5
Ok i think I am understanding now. Let me further explain this database to all of you and maybe it will help. I have the following forms
Family information
child information
payment information
classes - we offer over 100 classes it is in 1 form the list of classes offered.
and a class roster.
All of these are linked in what I call our customer dashboard. They are linked in such a way that If i add someone in it auto populates in the appropriate field for each of the forms listed.
I need it to do 2 things that i cannot figure out.
I need when I add a kid from a combo box created from 1 form the classes form, i need it to pop up a warning saying the class is full if it goes above 6 kids. I need to be able to change this though on the fly with a customer standing at my window. It needs to be user friendly where I or the manager can do this.
Next I need the database to create class attendance rosters for the classes we offer where the kids from each class are automatically listed from being added to the class. also if a kid drops out of that class i need them to be removed as well as if they are doing a makeup class in a different class.

I appreciate everyones help so far. I think with your help I will have created everything we need for the database to function.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:56
Joined
Feb 19, 2002
Messages
42,971
I gave you the answer to your first question if you look back to post #7. Now you have additional requirements.

Are you saying that you want to add a seventh child to the class even when the maximum count is six or are you saying that you want to increase the maximum count to seven so you can add a seventh child.

And the rest of the last sentence seems to ask us to build the whole app for you. Please try to do this yourself. We are happy to step you through it. But you'll proceed more smoothly if you try to get your requirements down up front.

For starters, is the StudentLimit a field in the class table?

You also need a student (or person table) and the roster is a junction table that connects students to classes. The rows in the roster table are what you are counting when you are trying to determine how many students are already enrolled in a class. When you delete a student from the roster table, that automatically reduces the count of enrolled students since the count is NEVER stored anywhere. You always count the rows in the roster table for a specific class and that tells you how many students are enrolled.
 

jane4587

New member
Local time
Today, 10:56
Joined
Sep 8, 2019
Messages
5
Pat Hartman,
I have every intention of building this thing myself I just need help in learning how to do it. Even if its a youtube video but I have tried for 5 years building it and learning this database myself and now I have reached the point i need help.

In response to your question about class size: I want to add a 7th child even if the limit is 6. No the student limit is not a field in the class table however I can add it if need be. Basically I have every class we offer and the times in 1 table. They are not separate tables. I have attached a screen shot of my roster table. The childs name auto populates to this table. Will this work for what you mentioned as my "roster" table in your previous message? I want to keep a record of every class lets say child john smith attends even if they drop out of that class i want a record of it. I want to set a limit for kids who status in the class is current. this way i only see kids who status is current and not dropped or something else.
 

Attachments

  • Annotation 2019-09-11 180444.png
    Annotation 2019-09-11 180444.png
    88.5 KB · Views: 43
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:56
Joined
Feb 19, 2002
Messages
42,971
If ALL class sizes are ALWAYS the same, you could use a literal value for class size. I don't recommend it but you could. If class size varies by class, then you really need to add a field to the class table so you can hold the limit for that particular class.

If classes are limited in size, then how can you just add more students? If you can add more students regardless of whether or not the class is full, what is the point of maintaining a limit in the first place? It might make more sense to let a human decide rather than the computer. In that case, your roster form can display the number of students currently enrolled and a real, physical maximum beyond which you cannot go. Then the human can decide whether to add the student or start a new class.

A picture of your schema would be more useful. I'm pretty sure that your tables are not correctly defined to do what you are asking to do. Open the Relationships window and arrange the diagram so that all tables are visible and all the fields are showing in every table. It is important that we see everything, if you can't make that happen, then uploading just empty tables will give us a starting place. If you want help moving stuff around and actually normalizing the schema, you WILL need to upload a database because no one will want to retype the whole thing.
 

Users who are viewing this thread

Top Bottom