Form updating table instead of just pulling information from it...

[Guest]

I have a database with Classes, Students, Enrollments, etc. tables. I have
form where I want to "assign" each student to a class. The form is based on
the Students table, and I have a subform based on my Enrollements and
Sessions tables.

I have two combo boxes in my subform, one for the class, and one for the
session. When I select a class, I want it to populate the sessions combobox
to the appropriate sessions for the selected class. The cboClass is based on
the Classes table, and the cboDay I have coded in VBA Editor as the following:

cboDay.RowSource = "SELECT Sessions.SessionID, Sessions.Day,
Sessions.ClassID FROM Sessions WHERE Sessions.ClassID = " & cboClass & ";"

When I initially run the program it seems to work, the cboDay is populated
correctly, but when I go into the Sessions table, my form has inserted a
random number in the Day field.

It's not just taking what I have in the Session table for the Day value,
it's inserting a new record. Why is this???

- ivalum21

 

[Guest]

You have mentioned only the forms, but the important thing is the
relationship between the tables that are the record source for the forms.
Without knowing a little about the structure of your database it is difficult
to do more than speculate about the best approach. It sound like you could
be getting an autonumber in the Session table. In general, since each
student can be in many classes, and each class can be taken be many students,
there is a many-to-many relationship between Students and Classes. This is
where an Enrollment table typically comes in. It is a junction table that
resolves the many-to-many relationship.

tblStudent
StudentID (primary key, or PK)
Name and other personal information fields

tblClass
ClassID (PK)
Subject, Teacher, etc. (information fields specific to the class)

tblEnrollment
EnrollmentID (PK)
StudentID (foreign key, or FK)
ClassID (FK)
Date, semester, and other information specific to that particular class
session

Establish a relationship between the PK fields in tblStudents and tblClasses
and the correspondingly named fields in tblEnrollment. Note that the FK
fields need to be the same data type (from table design view) as the PK
fields to which they are related, or if the PK is Autonumber they need to be
Number.
One approach is to build a form based on tblClasses and a subform (just
another form, really) based on tblEnrollment. The subform could have a combo
box based on tblStudents for selecting student names. In form design view
drag the icon for the subform onto the main form. Now when you enter or
select class information you can populate the Enrollment records for that
class with student names. You could approach it in similar fashion but from
the other direction by basing your main form on tblStudents, and selecting
classes from a combo box based on the tblClasses in the Enrollment subform.
Adding Sessions could be just matter as establishing a relationship between
the PK in tblClasses and a FK in tblSessions.