Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 7th, 2006, 08:59 PM
Registered User
 
Join Date: Feb 2006
Location: , , USA.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Automatically Subtract the Value

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
 
Old March 13th, 2006, 10:04 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old March 13th, 2006, 10:06 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old March 13th, 2006, 05:58 PM
Registered User
 
Join Date: Feb 2006
Location: , , USA.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old March 14th, 2006, 08:33 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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




Similar Threads
Thread Thread Starter Forum Replies Last Post
How to subtract hours from sysdate in oracle mcmouli Oracle 4 January 7th, 2011 06:52 AM
Subtract date stealthdevil Access VBA 2 December 7th, 2007 03:58 PM
Automatically change value abhisheksud Classic ASP Databases 0 December 13th, 2005 02:33 AM
subtract times in XSLT nabrown78 XSLT 1 April 19th, 2005 03:43 AM
Can't subtract days from a date Please Help Arsi C# 1 January 6th, 2005 03:34 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.