Getting subform to work

T

TLuebke

I'm trying to build a form/subform that allows an instructor to manage grades
for classes. The following query in the subform allows me to change the grade
"Status"
SELECT tblCourses.CourseID, tblAssign.AssignID, tblSubmit.StudentID,
tblSubmit.Status
FROM tblCourses INNER JOIN (tblModule INNER JOIN (tblAssign LEFT JOIN
tblSubmit ON tblAssign.AssignID = tblSubmit.AssignID) ON tblModule.ModuleID =
tblAssign.ModuleID) ON tblCourses.CourseID = tblModule.CourseID;

When I connect to the main form that is based on a table containing the
StudentID and CourseID the subform becomes non updateable.

I"ve tried building the queries various ways but whenever I introduce the
StudentID/CourseID it fails. Ideas?
 
A

Amy Blankenship

TLuebke said:
I'm trying to build a form/subform that allows an instructor to manage
grades
for classes. The following query in the subform allows me to change the
grade
"Status"
SELECT tblCourses.CourseID, tblAssign.AssignID, tblSubmit.StudentID,
tblSubmit.Status
FROM tblCourses INNER JOIN (tblModule INNER JOIN (tblAssign LEFT JOIN
tblSubmit ON tblAssign.AssignID = tblSubmit.AssignID) ON
tblModule.ModuleID =
tblAssign.ModuleID) ON tblCourses.CourseID = tblModule.CourseID;

When I connect to the main form that is based on a table containing the
StudentID and CourseID the subform becomes non updateable.

I"ve tried building the queries various ways but whenever I introduce the
StudentID/CourseID it fails. Ideas?

Could you back up and describe your table structure and what information you
want on the form?

-Amy
 
T

TLuebke

Sure:
tables: tblCourse - two columns = is the title and unique course number
(11-34)
tblModules - two columns = courseID and learning module #
(usually 1-5)
tblAssign two columns = Module # and AssignID # (1-n)

tblLinkCourses- two columns = StudentID and CourseID of classes
enrolled.

tblAssign - three columns =StudentID, AssignID and Submit (lookup
of three status codes D1,D2,C)

Using a seperate form Instructor associates a student to a course in
tblLinkCourses.
tables Course,Module, and Assign are used to break down/track the 21 courses
into the appropriate modules and associated assignments.

I'd like to be able to select a student, see the courses they are in and
update the assignment submit code as needed. Thought I could get the Student
and Course on the main form and the assignment number and submit code on the
subform.
 
A

Amy Blankenship

TLuebke said:
Sure:
tables: tblCourse - two columns = is the title and unique course number
(11-34)
tblModules - two columns = courseID and learning module #
(usually 1-5)
tblAssign two columns = Module # and AssignID # (1-n)

tblLinkCourses- two columns = StudentID and CourseID of classes
enrolled.

tblAssign - three columns =StudentID, AssignID and Submit
(lookup
of three status codes D1,D2,C)

Using a seperate form Instructor associates a student to a course in
tblLinkCourses.
tables Course,Module, and Assign are used to break down/track the 21
courses
into the appropriate modules and associated assignments.

I'd like to be able to select a student, see the courses they are in and
update the assignment submit code as needed. Thought I could get the
Student
and Course on the main form and the assignment number and submit code on
the
subform.

Let me get this straight...
You have courses that are composed of Modules. Then you have an Assign
table that relates AssignID to ModuleID? What purpose does this have? Is
this grouping Modules into assignments somehow? In other words, would
several different Modules have the same AssignID? What is the functionality
of this table relative to the Course table?

Next, you have a LinkCourses table. Are you using this to set up which
courses a student should take? Or courses the student _is_ taking? If it
is of which ones the student _is_ taking, you should probably have some date
information.

Now, you have a _second_ tblAssign? How did you get Access to even let you
do that? What is the function of that table, and what are the status codes
doing? I hope you don't actually mean that that is a lookup field. I
really do hope you mean that it's looking the value up in a separate table.

Anyway, it's difficult to answer your question with things left this vague.
I've gone back several times to try to do so, but couldn't make heads or
tails as to what is going on with your tables.

HTH;

Amy
 
T

TLuebke

Let me get this straight...
You have courses that are composed of Modules. Then you have an Assign
table that relates AssignID to ModuleID? - Yes

What purpose does this have? Is this grouping Modules into assignments
somehow? In other words, would several different Modules have the same
AssignID? - Yes e.g. Course 12 Module 1 has assignments 1-5. Course 12
Module 2 has seven assignments 1-7.

What is the functionality of this table relative to the Course table? -
Tries to systematically break one course into many assignments.

Next, you have a LinkCourses table. Are you using this to set up which
courses a student should take? Or courses the student _is_ taking?
- Which courses they are taking.

Now, you have a _second_ tblAssign? How did you get Access to even let you
do that? - Should have read tblSubmit
What is the function of that table, and what are the status codes doing?
- Wanted to have a table with just the grades for each assignment.

I hope you don't actually mean that that is a lookup field. I really do
hope you mean that it's looking the value up in a separate table. - I will
change this.

Anyway, it's difficult to answer your question with things left this vague.
I've gone back several times to try to do so, but couldn't make heads or
tails as to what is going on with your tables.
- I was confused too, I got this scheme from one of the MVP's on this
forum.

I'm willing to wipe the slate and try anything. I just need to track how a
student is doing across 5-6 courses that consist of 20-30 assignments each.


Thanks
 
A

Amy Blankenship

....

Let me restate what I think you said to make sure I understand:

tblCourses - The purpose of this table is to define courses
CourseID
CourseNumber?
CourseTitle

tblModules - The purpose of this table is to define the modules within each
course
CourseID
ModuleNum (will be 1-5 within each course)

I think this here is at least part of your problem. You don't have a single
unique identifier for each module, yet you're trying to Join on ModuleNum as
if it were a primary key. I'll get to a suggested design later, but just
thought I'd point that out here where it is obvious.

tblAssign - The purpose of this table is to define assignments within each
Module
ModuleNum
AssignID

tblLinkCourses -The purpose of this table is to define which courses a
student is taking right now
StudentID
CourseID

Note that this does not allow for any historical data.

tblSubmit - The purpose of this table is to define what grades the student
made on each assignment (submission?)
StudentID
AssignID
StatusCode

There are two more tables implied by your posts:

tblStudent - Defines Students
StudentID
FirstName
LastName
etc

tblStatusCodes - provides values for the lookin in tblSubmit
CodeID
Code

Now, this is how I might design it:

tblCourses - defines courses
CourseID
CourseNumber
CourseName

tblModules - defines modules
ModuleID
ModuleDesc (text field describing the module)

tblCourseModules - defines what modules are in what courses
ModuleID
CourseID
ModuleNumber (number of the module within the course)

Note that this allows you to reuse Modules so that, for instance, if you had
a Grammar module that you wanted to use in a writing course and a literature
course, you could do that.

tblAssign
ModuleID
AssignID
AssignOrder (makes it easier to know which assignment is which)
AssignDesc (short description of th assignment)

tblSemesters - defines periods of time a course can be offered (name it as
is appropriate for you)
SemesterID
SemesterStart
SemesterEnd

tblCourseOfferings - defines courses that are available for a given semester
CourseOfferingID
CourseID
SemesterID
CourseOfferingDesc - you'd use this if you were offering a course more than
once in a semester to distinguish between offerings

tblStudentCourseOffering - defines which courses a student is taking
StudentID
CourseOfferingID

tblSubmit - records student's grades on assignments
StudentID
AssignID
StatusCode

I've already spent loads of time on this and I know it's a lot to get your
head around, so I'll come back tonight or tomorrow am with the form design.
That way you can look at the two posts along side one another.

HTH;

Amy
 
T

TLuebke

Thanks for your time Amy this is a tough one to crack. I'll study and mockup
the suggestions below an look forward to the form piece

Thanks,
Todd
 
A

Amy Blankenship

TLuebke said:
Thanks for your time Amy this is a tough one to crack. I'll study and
mockup
the suggestions below an look forward to the form piece

OK, obviously I've given you a fairly complicated structure. So it will be
very important to your instructors to be able to correctly put the right
grade for the right assignment for the right student--in the right semester.
So I suggest this form structure:

Semester
Query based on CourseOfferings joined to Course
Module
Query based on a left join of Assignment to tblSubmit to a
query that returns all students enrolled in this CourseOffering

I think that's fairly self-explanatory until you get to the final subform,
so let me go into a bit of detail.

You probably noticed that the Assignment table does not directly reference
CourseOffering. This is kind of a problem, because if you're anything like
me, trying to put in a fully-qualified reference to a control on a nested
form is just a PIA. So what I normally do in such cases is create a global
variable that contains the value of whatever that is. To do this, create a
separate module just for global variables and code relating to them. I
usually call mine "Global" :)

At the top of this module, under the Option Explicit, etc., put

Public CourseOffering_ID As Integer

Then you need a function to return that variable's current value, so on the
next line:

Public Function getCOID() As Integer
getCOID = CourseOffering_ID
End Function

Now you need to be able to set that variable's value, so open the properties
sheet for the subform nested inside Semester based off of the
CourseOfferings/Course query, and go to the "Events" tab. Click on
"Current" and select "Event Procedure" and the ... on the right of that.
This should take you to the VBA code window, and you should be inside a sub
that was already created.

Inside it, type:

If Not Me.NewRecord then
CourseOffering_ID = Me.CourseOfferingID
Else
CourseOffering_ID = 0
End If

The form structure I gave you really isn't appropriate for adding new
CourseOfferings, so you should never need to go down the else path, but it
never hurts to be safe ;-). The else helps prevent Invalid Use of Null
errors.

So now let's look a bit more closely at the innermost subform. I should
explain that I've presumed that the instructor will have each assignment due
at the same time for all students, so that subform is set up to show all
students by assignment, rather than the reverse. If you noticed I said to
use a query joining two tables to a query. The reason for this is what is
called a "frustrated join." If you use a Where clause on a left or right
join, Access treats it as an inner join, and you don't get all the records
you need. You can use a subselect, but I prefer to use a separate stored
query.

To make this query, which I will call frmStudentsInOffering*, you'll need to
open the query grid, and show these tables:

tblStudentCourseOffering
tblStudent

If there is not a join line joining StudentID in tblStudentCourseOffering to
StudentID in tblStudent, then drag one of the StudentID's on top of the
other.

Select the fields in tblStudent that will be most useful in identifying that
student for grading. At a minimum, probably first and last name. You may
want to do an ascending sort on those fields as well. Test the query.
You'll see that it contains information on all students assigned to _all_
Course Offerings, which is not what we need. So, we'll narrow it down with
a Where clause. Drag the field CourseOfferingID from the list of fields
into the grid, uncheck the "show" button, and in the WHERE row, put =
getCOID(). If you test the query again, you will notice that it returns no
records, because the global CourseOffering_ID variable is not set yet.

Next, we want to make the left join query to show a record for each student
enrolled in the course offering to each assignment. You can either do this
as a separate stored query or just do it in the form's data property panel.
In the query grid, show:

tblAssign
tblSubmit
frmStudentsInOffering

Make sure there's a join line between the two AssignID fields and between
the two StudentID fields, and that the joins are set up to "show all records
in tblAssign and only the records in tblSubmit where the joined fields are
equal", "show all records in tblSubmit and only the records in
frmStudentsInOffering where the joined fields are equal".

Now, drag the fields in from the field list onto the new subform. You'll
want to right-click the StatusCode field and change it to a ComboBox. In
the format panel, set the column count to 2, the widths to 0, 1. In the
Data panel, set the data source to tblStatusCodes and the bound column to 1.
Note that this will put the ID of the status code in rather than the status
code itself, but this means that you can globally change a status code if
you need to. It's also slightly more compact than storing several letters.
If that's not what you want, use one column and use a query to set the data
source to the status code (and you probably don't need the ID in the status
codes table at all).

This should work and be updateable, but since this has been 100% from my
head, I can't guarantee it. If you have any problems, post back and we'll
try to work through them.

*A word on naming. You'll notice that I named my query with the prefix
"frm" rather than the more traditional "qry". This is because I often have
hundreds of queries in one of my databases, and if they all start with
"qry," it makes it impossible to quickly find the one I want to use/work on
in the query window. Plus, if I'm in the query window then OBVIOUSLY I am
looking at queries. Therefore, if I prefix my objects at all, I prefix
them according to how they are used, rather than what they are. Since this
is a query that is being used to drive a form, I prefix it with "frm".
Typically, in my own work, I don't prefix tables or forms at all, since
their names tend to be self-explanatory and a prefix just stands in the way
of quick navigation and results in extra typing. I'm not suggesting you
adopt this scheme, just telling you how I do it.

HTH;

Amy
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top