form limiting number of records in subform

[yaro.kobzar]

Hi everyone,
I have a form called Student Records, which contains a subform that is linked to another query (Students and Classes Query) which can be used to edit student information and class registration.
I need to create a limit on a number of records that can be registered in the query to 30. If it goes over 30 i need an error message to pop up.

Can anyone help me out on how I could get that to work?
Thanks

 

[spikepl]

count the records using the function Count. Disable possibility to ad records, once the count reaches 30. Perhaps display a message saying so, or show current count at all times. Enable the possibility to add records if count <30.

 

[yaro.kobzar]

"count the records using the function Count. Disable possibility to ad records, once the count reaches 30. Perhaps display a message saying so, or show current count at all times. Enable the possibility to add records if count <30. "

Hmmm that's a good idea. I understand where you're going with this.
Where would i add that count function?
I'm sorry for these dumb questions. I'm new to this and I'm trying to learn these coding processes.
Could you help me out?

 

[spikepl]

Put
=Count(*)
in a textbox eg txtEnrolled

 

[spikepl]

And don't freak if you get en "Err" displayed, when there are no records (yet) - that's normal

 

[yaro.kobzar]

thanks spikepl. I understand now how it works...
here's another thing:
we have for example 4 different classes (each separated by a 4 digit number like 4000, 4001, 4002, and 4003). And each class has a different number of maximum allowed of records (registrations). Class 4000 and 4002 can have a maximum of 30 registrations while 4001 and 4003 can have maximum of 26.

Is it possible to build a Count function in a
Private Sub Class_Item___BeforeUpdate(Cancel As Integer)
that will count number of registrations for the class you want to register and then warm if it's full? does that make any sense or am i confused and confusing everyone? =)

btw, Private Sub Class_Item___BeforeUpdate(Cancel As Integer) is the box in the sub form where you'd enter the 4 digit class item to register

 

[spikepl]

BeforeUpdate is too late - what is the point of letting anyone doing clicking and typing and then discard it?

I would still recommend you proceed as suggested.

Write a Sub that sets

Me.AllowAdditions=True (or not) as the requirement may be), depending on what the 4-digit number is on your form, and depending on what the count is.

Insert this function in the handlers for the forms events:
AfterInsert - so it gets updated after you insert a record
AfterDelConfirm -so it gets updates after you delete a record
OnLoad -so it gets updated when you open the form

 

[yaro.kobzar]

all right...
what do you think about this idea...
I have a query that already counts the totals for each class.

Would it be easier to link that query with the item number when registering so that it will lookup what the total is and if it is <30 allow the registration?

 

[spikepl]

Not sure how your whole thing hangs together, so cannot really contribute with anything wise here If you get it wrong, it WILL bite you, but you'll be enrichened by the experience

On a side note - why TYPE the 4-digit number ? It's an obvious candidate for a combo box, to select from, rather than type

 

[yaro.kobzar]

Ok I think i got an idea of what i want it to do...
I took your advice and made it a combobox and made it much easier...

Here's what i have:
The Query Class Roster Master Group has two columns: Class Numbers and SumOfCountOfStudent ID. The class numbers are 1-30 for each class and the other sums all of the students.

Now I need a function in this form that will look at Class Roster Master Group query and see if class is full or not. I took class 7 because it is full.
When i register a new student it doesn't show me a message box. it keeps on registering.

Is my DLookup function wrong?

Private Sub ITEM___BeforeUpdate(Cancel As Integer)
If DLookup("SumOfCountOfStudent ID", "Class Roster Master Group", "Class Numbers=7") > 30 Then
Me.AllowAdditions = False
MsgBox "the limit for records in this table has been reached", vbOKOnly
Me.Undo
End If

 

[spikepl]

Both your lookup and the event handler.

It is a pain in the derriere to use table and column names with spaces. Don't. To save what you do have, you need to put such names with spaces inside square brackets [] when you refer to them.

When you code, and the code does not work, the first question to check is what are the values of the variables. YOu shoud lstep through the code using the debugger, and inspect eg the output of your Dlookup, or output it using debug.print . Had you done that, you would probably have solved this by yourself. The principle is simple: first check the variables.

Your Sub: You ask it to undo the changes in user input, and it does, but you do not cancel the update, so garbage goes into the db.

Add
Cancel=True
inside the IF