Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-08-2019, 11:50 AM   #1
jane4587
Newly Registered User
 
Join Date: Sep 2019
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
jane4587 is on a distinguished road
Exclamation Limiting Class Size

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?

jane4587 is offline   Reply With Quote
Old 09-08-2019, 12:39 PM   #2
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,425
Thanks: 0
Thanked 568 Times in 564 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Limiting Class Size

Use VBA code in control (combobox ?) BeforeUpdate event to count records for the class. Use a DCount() domain aggregate function.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 09-08-2019, 04:11 PM   #3
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,096
Thanks: 10
Thanked 220 Times in 208 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Limiting Class Size

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?

__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Old 09-08-2019, 06:38 PM   #4
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,271
Thanks: 15
Thanked 1,594 Times in 1,514 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Limiting Class Size

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 09-09-2019, 10:37 AM   #5
jane4587
Newly Registered User
 
Join Date: Sep 2019
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
jane4587 is on a distinguished road
Re: Limiting Class Size

Excuse my ignorance but exactly where and how would I put this in? Or rather which places if there are more than one.
jane4587 is offline   Reply With Quote
Old 09-09-2019, 11:02 AM   #6
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Limiting Class Size

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
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-09-2019, 01:54 PM   #7
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,271
Thanks: 15
Thanked 1,594 Times in 1,514 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Limiting Class Size

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.

__________________
Bridge Players Still Know All the Tricks

Last edited by Pat Hartman; 09-09-2019 at 02:42 PM.
Pat Hartman is offline   Reply With Quote
Old 09-09-2019, 02:36 PM   #8
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,425
Thanks: 0
Thanked 568 Times in 564 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Limiting Class Size

@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.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 09-09-2019 at 02:42 PM.
June7 is offline   Reply With Quote
Old 09-09-2019, 02:44 PM   #9
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,271
Thanks: 15
Thanked 1,594 Times in 1,514 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Limiting Class Size

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
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 09-09-2019, 03:16 PM   #10
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,425
Thanks: 0
Thanked 568 Times in 564 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Limiting Class Size

Okay, but I would still prefer executing code only once as with BeforeUpdate.

What if user wanted to modify an existing record?
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 09-09-2019, 03:34 PM   #11
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,271
Thanks: 15
Thanked 1,594 Times in 1,514 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Limiting Class Size

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 09-09-2019, 05:54 PM   #12
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,425
Thanks: 0
Thanked 568 Times in 564 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Limiting Class Size

I should have clarified I would use BeforeUpdate of control, not form.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 09-10-2019, 07:42 AM   #13
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,271
Thanks: 15
Thanked 1,594 Times in 1,514 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Limiting Class Size

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 09-11-2019, 01:44 PM   #14
jane4587
Newly Registered User
 
Join Date: Sep 2019
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
jane4587 is on a distinguished road
Re: Limiting Class Size

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.
jane4587 is offline   Reply With Quote
Old 09-11-2019, 02:39 PM   #15
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,271
Thanks: 15
Thanked 1,594 Times in 1,514 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Limiting Class Size

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.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
System error "&H80040111 (-2147221231) Class factory cannot supply requested class davidb88 Excel 2 06-30-2014 05:03 AM
Need help implementing property of base class in derived class. papadilbert Modules & VBA 6 12-01-2011 06:11 AM
Correct syntax to make Class Function Private yet callable by instances of Class mdlueck Modules & VBA 4 11-07-2011 07:32 AM
Limiting a text box size yhgtbfk General 5 11-17-2004 06:15 AM
[SOLVED] Limiting field size of a form variable tbasha Forms 2 09-20-2000 12:56 PM




All times are GMT -8. The time now is 11:28 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World