Complete a form based on choosing one field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BarbaraM - 30 Jan 2007 17:06 GMT

I have 2 tables, one called Plan, the other is StudentPlan

In Plan, there are 8 records, Each identifying a student "Track" and which
courses correspond to that track.

I would like to be able to populate the fields in StudentPlan automatically,
when a Track is chosen.

For example, on the Initial student Plan Form, the user would choose
"Honors" in the field named Track , then all of the courses would copy over
from the "Plan" to that Student's plan. (All fields are the same). This would
only happen when the Track is changed - I don't want it to continually
update.
Since students may have variations of the Honors track, the user can then go
in and change any fields not appropriate to that particular student.

Reply to this Message

John Vinson - 30 Jan 2007 20:11 GMT

>I have 2 tables, one called Plan, the other is StudentPlan
>
[quoted text clipped - 11 lines]
>Since students may have variations of the Honors track, the user can then go
>in and change any fields not appropriate to that particular student.

You'll need to have the AfterUpdate event of the Track control run an
Append query, appending the eight (or perhaps the number may vary!)
into the StudentPlan table.

It's not clear from your message how your tables are structured. If
you have one *FIELD* for each course, you're on the wrong track...!

John W. Vinson[MVP]

Reply to this Message

BarbaraM - 30 Jan 2007 20:39 GMT

I have one field for each core Academic area and the semester in which the
student is slated to take the course.
For Example: FrLAS1, FrLAS2, FrMAS1, FrMaS2 etc. (8 for each year, 32 in all)
ie:{FrLaS1=Fr(Freshman)LA(Language Arts)S1 (Semester1)}

What is entered into each of these fields is the course for which the
student is slated based on his academic track. Therefore, for Language Arts,
an Honors student takes a different course than a Technical Student.

I've been playing around with this and an Update Query accomplishes this -
as the student's individual plan need to be initially populated based on his
academic trackand an update query does this based on the chosen track.
I now need to figure out how to limit this to just one student at a time.
(I've been away from Access for a few months and it is amazing what I have
forgotten)
Once I run this query, the users will work with the student on another form,
where they can customize his plan assigning Electives etc.

Is this the wrong way to develop this??

> >I have 2 tables, one called Plan, the other is StudentPlan
> >
[quoted text clipped - 20 lines]
>
> John W. Vinson[MVP]

Reply to this Message

John Vinson - 30 Jan 2007 21:36 GMT

>I have one field for each core Academic area and the semester in which the
>student is slated to take the course.
>For Example: FrLAS1, FrLAS2, FrMAS1, FrMaS2 etc. (8 for each year, 32 in all)
>ie:{FrLaS1=Fr(Freshman)LA(Language Arts)S1 (Semester1)}

This is called "Committing Spreadsheet" and it's a very common error
in table design.

"Fields are expensive, records are cheap." You should consider
properly normalizing your design using THREE tables in place of this
one:

Students
StudentID
LastName
FirstName
<other biographical data>

Courses
CourseNumber
Class <e.g. "Fr", "So", "Jr">
Area <e.g. "LA", "MA", ...>
Level <honors track, technical track, etc.>
CourseName
<other info about the course itself>

Enrollment
StudentID <who's assigned to the class>
CouurseNumber <which class is she assigned to>
<other info about THIS student in THIS course as appropriate>

>What is entered into each of these fields is the course for which the
>student is slated based on his academic track. Therefore, for Language Arts,
[quoted text clipped - 6 lines]
>(I've been away from Access for a few months and it is amazing what I have
>forgotten)

Let's get the table design correct first. You can use a Query which
references a Form for its criteria, using

=[Forms]![NameOfForm]![NameOfControl]

as a criterion to select just one studentID for example.

>Once I run this query, the users will work with the student on another form,
>where they can customize his plan assigning Electives etc.
>
>Is this the wrong way to develop this??

Yes, I'm afraid so. :-{(

John W. Vinson[MVP]

Reply to this Message

ItsBarbara - 05 Feb 2007 01:54 GMT

I evidently don't make clear what I am doing.
I do have the tables you recommended.
Students
Courses
Enrollment(Current Classes)

In addition, I have
Course History
Course Requests
Plan
4YearStudentPlan (This is where we record what courses the student will need
to complete the required number of credits for graduation. For Example,
College Prep requires 8 LA classes, 8 Math Classes, 6 Soc St., etc.)

Many tracks can have the same courses (from the Courses table) as part of
the 4 year plan. Theoretically, every student essentially could have a
different plan. I have the "General Plans" in the Plan Table, from which we
populate a StudentPlan initially. Once the StudnentPlan is initially
populated, the counselor goes into the student's plan and customizes it based
on Elective classes, or Failed classes, etc.
Thus, after the initial meting, the counselor only looks at the StudentPlan
for each student to determine progress toward graduation.

The Enrollment may, or may not, echo the 4 year plan. For example, if a
student fails a class, then the courses he takes may not happen in the order
of the plan, however, the plan stands as those are the courses needed for
graduation.

I have the courses in the student's plan grey out once the student passes
the course. That way, anyone looking at a student can see at a glance, what
courses are needed toward graduation, and which have been satisfied. There
are Tabs for the Current Classes, Course History, and Course Requests

I have built it and have it working now the way I need now. I ended up using
Macros to perform some of the functions (because I do not know much about
Visual Basic).

I'm certain that there are other ways to build this, but I do need the
Tables that I have listed for everything to work.

I really appreciate your feedback, and I have found many answers through
this news group. If I could find a comprehensive class in Access and Visual
Basic, I would jump at the chance to take it. The ones I have taken have been
basically a waste of time and taught by people with limited knowledge.

The most knowledgable folks I've found have been in this newsgroup. Again,
thank you and I really appreciate your help.

> >I have one field for each core Academic area and the semester in which the
> >student is slated to take the course.
[quoted text clipped - 53 lines]
>
> John W. Vinson[MVP]

Reply to this Message