Question Simple Walkthrough of assigning student to course

[alcapps]

in the classes or maybe call it Class2Student table. Create a record for every student that takes or is trying to enroll in a class. Then in that table add dates fields to track the enrollment process example add LetterSentDate field. If you sent a letter then it have a date. If you haven't yet then it would be null. use a boolean field for Not excepting example isNotAccepting = false, and last one AcceptDate is null but check so that you don;t have a date and isNotAccepting is true. I hope this helps.

Al

 

[neilq5]

Thanks alcapps
i've got the majority of it working now.

A question, in my trial enrolment table i have a session and group fields. Each one can only hold 10 students max.
E.G
Session = 1st
Group = B
this is one class of which can only hold 10 students

Is there a way to put a limit on this to prevent me adding more students?
my Session field is a drop down menu and my group is a list

 

[alcapps]

what I normally do is in the table that you have that sets course or class put a number field there that is like a maxstutdent. Then every time you add a student to the table either at before Insert or After update. dsum() to check how many you already have and check it against that number from the table. What I do is do a dlookup() for the number and I put it in a variable on the form. Then I do a dsum() before insert and check that number and if I am over I cancel the insert and give the user a msgbox() saying you have gone over the max number allowed. I hope you understand the concept.

Al

 

[neilq5]

I don't have much experience with VBA and i've never done dsum or dlookup before but i'll look in to them and see if i can do it.

 

[alcapps]

just look online from google.. Dsum() and Dlookup should be easy for you to figure out.

 

[neilq5]

i looked in to it a bit and i think i'll save that for last
now, my new question (and one of my last i promise!)

I'm setting up the class options for the students.

so i have my student table, my options table and my StudentClassOptions table.
They have 4 classes a week that they can choose from a drop down, and there's 3 blocks (terms, semesters) in a year, so they will get the choice of 4 classes 3 times a year but i need a record of what class they took each block, the tutor teaching the class and the room number.

for a layout, i don't know if this is the easiest way to go about it or whatever but i'd have my these dropdowns

Block1period1 Block2period1 Block3Period1
Block1period2 Block2period2 Block3Period2
and so on

is this the best way to go about it?

 

[alcapps]

for me I think you approach this in several ways. I would try to have a datasheet view with the student drop down at the top of the form. Then the datasheet fills with the classes that student has signed up for alreay or already has taken in reverse order of taken. So the last course taken is on top. one of the fields is the block/class dropdown. Again in the before update or before insert you can check to see if they have signed up for that same block and class you can cancel the event and give them a msgbox saying you already signed up for that block and class. Plus you can count and see if they have already signed up for all the current blocks and classes by query. And show a textbox that says something like. "All Blocks and Classes assigned" / "Need 2 more classes for current block" stuff like that. So in all you would have a drop down on the top of the form that had the student names, a datasheet view of the block and classes that they have signed up for. in that datasheet a field for block and class drop down. For Tutors you have Class at the top and in the datasheet view show students signed up for that class. I hope this helps.

 

[neilq5]

I've tried from what i've understood of your advice, the only part i can't get is to prevent a student from double booking a class for the same block/period.

Can I get in simple terms how to go about this?

 

[jdraw]

I've tried from what i've understood of your advice, the only part i can't get is to prevent a student from double booking a class for the same block/period.

Can I get in simple terms how to go about this?

So in your table for this StudentClassBlock you have fields for
StudentID
ClassId
BlockId


make a unique compound index using all 3 fields. This will prevent duplicates. If you try to enter a duplicate Access will warn you. You can create an error handler (you should) and trap that error. You can then put out a m ore meaningful (friendlier) message and carry on.

see http://www.geeksengine.com/article/composite-unique-index-access.html

 

[neilq5]

Thanks Jdraw thats preventing the duplicates.

My problem now though is, I have a query subform that when i try to add a record, it's just overwriting the first class i add for that student.

What am i doing wrong?

 

[jdraw]

Show us the code involved and some data showing the problem.


What fully identifies a record? Do you need Period as well as Block?

 

[neilq5]

Hi jdraw,
I managed to get the classes working,
I'm nearly finished now, i just have to set up some reports and a switchboard.

But another question about the class options.

How would I use the count function to give me a number of people in the same Class in the same Block and Period?

just to keep tabs on class numbers