This is my first post: so be gentle with me. I am new to VB and have the beginner’s book to VB programming. Access 2003 VBA Programmer's Reference

My question is this. I want to create a form that allows me to decrease a value. For example, if I have 10 slots in a class, and a student register, one slot is (subtracted) decreased. I want the form to automatically subtract the value from the sum total.

Point me in the right direction.

Please
Need help

The first thing you need to do is to create a field in the class section table with a maximum enrollment number. This needs to be set for each class.

Then on the form, you will need to run a routine that counts all the records in the Roster table (junction table between sections and students) and gets a total of records with the section PK.

Then, on the main form, subtract the number of students registered (Roster Table) from the MaxEnrollment number in the section table.

Do you need code?

HTH


mmcdonal

Alternatively I was just thinking you could create a totals query that always had the total enrollment of each section, and then just go grab this number once instead of doing a record search. That is very easy.



mmcdonal

Yes, can you send me the code. Thanks for your help.:D

I am assuming:
1. That you have a table for courses.
2. a table for sections of courses.
3. a table for students.
4. a table for rosters or enrollments that is a junction table for students and sections.

If so, in the sections table, you need to add a field for total enrollment allowed. Alternatively, if you go by Room capacity, and you have a room table, you can set capacity there (section would look up to Room). But for now, assume section has the maximum enrollment (intMax)

Then create a query with the roster and section table. Include the section PK, intMax, and Student either SSN or however you reference the student table. Then go to View > Totals, and for the student field select Count.

This should give you query results like this:

Section Max Student
0001 20 14
0002 35 15
...

Call the query qryMaxCount. Now I am not sure where or how you want to display this data. You can create a form and then drop it on your Section form and link it to the section number. Then requery the form after an insert event in the Roster subform. How do you want this to work from here?

HTH







mmcdonal