Help with Classroom Management Database Template

[alpinegroove]

Hello,


I need help with the Classroom Management Database template available on Microsoft's Web site:

http://office.microsoft.com/en-us/te...001018407.aspx

This database allows adding students to class rosters from the Classes form. Each class has a record, and a sub-form allows to associate students with the class, using a drop-down menu.

However, I need to be able to enroll students in classes from the Students form. That form has a sub-form that lists the classes the student is enrolled in.

I am unable to make changes to students' schedules via this sub-form. I would like to edit a student's schedule from the Students form, rather than from the Classes form.

Is anyone here familiar with this template? Can someone please take a look and see if it would be possible to enroll students through the Students form?

Thanks.

[ssanfu]

I'm not sure if this is what you want, but take a look.

[alpinegroove]

Thank you very much for sending this, Steve.
If I understand it correctly, you created the form Studentz and subform z_subform.

I see that I can add a class to a student by double-clicking the available classes listed under lstClasses. When I do that, it appears in the subform and is added to the Students and Classes table.

1. How do I remove a class from a student's schedule using this new form? I don't seem to be able to delete records from the subform.
2. Is it possible to sort the subform by Class Name?
3. If you have a moment, could you please briefly explain how you achieved this so that I know for future reference?
Thanks again! I really appreciate your help.

[alpinegroove]

I also have a more general question about form design:
What is the advantage of putting the SQL statement in the form's Record Source rather than basing the form on a query?
Similarly, what is the use of Raw Source?

Thanks!

[ssanfu]

Thank you very much for sending this, Steve.
If I understand it correctly, you created the form Studentz and subform z_subform. YES

I see that I can add a class to a student by double-clicking the available classes listed under lstClasses. When I do that, it appears in the subform and is added to the Students and Classes table.

Sorry, I should have added a label to tell you that. (done)

1. How do I remove a class from a student's schedule using this new form? I don't seem to be able to delete records from the subform.

You can select the class in the subform (make the triangle show up in the record selector), then hit the Delete button on the keyboard. I also added a button in the detail section to delete a class. You still have to select which class to delete first.

2. Is it possible to sort the subform by Class Name?

Yes. Change the subform record source. (I think I did that) Selected which fields to sort by.

3. If you have a moment, could you please briefly explain how you achieved this so that I know for future reference?

I looked at the table "Students And Classes" (I really hate spaces in object names) and saw that it was a junction table. I copied a form and modified it to assign a class to a student. I used a list box displays the classes (could also have used a combo box). The PK of the student is in the main section of the form and the list box selects the PK of the class. You could just as easy select a class and add students.

I modified (see new attachment) the "Students And Classes" to make the StudentID and ClassID fields a compound PK. Is there ever a case where you would want a class assigned to a student more that once? Using both fields as the primary key will cause an error if the same class is assigned twice to a student. The Student/Class verification could be done with a lot of code, but it is easier if the table has a compound PK.

Other than that, I didn't do too much...

PS ..... this template is a good start, but it still needs a lot of work.

[ssanfu]

I also have a more general question about form design:
What is the advantage of putting the SQL statement in the form's Record Source rather than basing the form on a query?

One advantage of using a saved query it that it can be used multiple times. For example, it can be the record source for a form and for 3 reports. Each report can display different data. If the form and the 3 reports had a SQL statement as the record source, you would have to modify the SQL in 4 locations to make the changes or risk not having the correct data displayed/printed.
I use both, but lately have started to using saved queries more. It is easier to modify a saved query - you don't have to open the form to make the change.
Just my opinions......

Similarly, what is the use of Raw Source?

I don't know what you mean by "Raw Source".

[alpinegroove]

Thank you very much and apologies for my delayed response. I did not have access to a computer for a few days.

I now see how to delete classes. I kept right-clicking and looking for "Delete Record", but that doesn't come up. Using the button you created or the Delete key work fine.

The compound PK seems like a good idea since students should not be able to take the same class twice. How does the compound PK achieve that?

How easy is it to create an error message that is displayed if there is a time conflict between a class I am trying to add and a class that a student is already enrolled in?

Also, you write that the template still needs a lot of work. Could you give me a sense of what else needs to be done so I know what to work on?

Thanks again!