RE: Using a subform to display and add info into table

OK. So the Session table already has its own primary key. This is good.
However, you currently do not have a way of capturing which session a student
is enrolled in, so you need a SessionID field in Enrollments.

Moreover, since the SessionID determines the ClassID as well, you do not
need the ClassID in the Enrollments table. Also, Day is a reserved word.
Using them as field names can cause unpredictable behavior. I suggest the
following changes:

Enrollments: EnrollmentID (AutoNumber PK), StudentID (FK), SessionID (FK)
Sessions: SessionID (PK), ClassID (FK), SessionDay, SessionTime

Your subform can be based on Enrollments only, linked to the main form by
the StudentID. Although there may be many Sessions records, if we make the
Session combo box sorted by class name, then by session day and time, it
should be easy to navigate to the correct class and session.

SessionID Combo Box:
ControlSource: SessionID in Enrollments table
RowSource:
SELECT Sessions.SessionID, Classes.ClassName, Sessions.SessionDay,
Sessions.SessionTime FROM Classes INNER JOIN Sessions ON Classes.ClassID =
Sessions.ClassID ORDER BY Classes.ClassName, Sessions.SessionDay,
Sessions.SessionTime;
Column Count: 4
BoundColumn: 1
ColumnWidths: 0";1";1";1"

The combo box will display the courses, days & times. You may need to
adjust the column widths. When you select a row, the class will be
displayed, but the SessionID will be stored in the SessionID field. To
display the day and time in other textboxes, use the Column property of the
combo box:

SessionDay: Me!YourComboBox.Column(2)
SessionTime: Me!YourComboBox.Column(3)

Hope that helps.
Sprinks

"ivalum21" wrote:

> Here is the info on all my tables:
>
> Students: StudentID (PK), FirstName, LastName
> Classes: ClassID (PK), ClassName
> Sessions: SessionID (PK), ClassID (FK), Day, Time
> Enrollments: ClassID (FK), StudentID (FK)
>
> The form I'm talking about is named Assign, and it assigns a student to
> classes. The form is based off of the Students table, then the subform I
> have based on the Classes, Enrollments and Sessions tables. I'm not sure if
> that is right.
>
> Thanks for your help.
>
> ivalum21
>
> "Sprinks" wrote:
>
> > Hi, ivalum.
> >
> > You must have a non-updateable query. If you open the query, you will
> > likely not be able to change data in it, either.
> >
> > Does ClassID define a session? What are the primary keys of Classes and
> > Sessions? If there are multiple sessions of a given ClassID you will need a
> > different primary key for Sessions. A multiple field one of ClassID, Day,
> > and Time would work, but it's easier to just use an AutoNumber one. You'll
> > never see it anyway.
> >
> > Post back and I'll be able to help you.
> >
> > Sprinks
> >
> > "ivalum21" wrote:
> >
> > > I have a form based on a table called Students, it just displays their first
> > > name.
> > >
> > > I have a subform where I want to display the classes the student is signed
> > > up for. I have the following tables: Classes, Enrollments (StudentID,
> > > ClassID), and Sessions (ClassID, Day, Time). I'm using all three of these
> > > tables for my subform's record source and when I run my form it won't let me
> > > input any data for a student.
> > >
> > > I also want in my subform to have the ClassName be a combo box that displays
> > > all classes from the Classes table.
> > >
> > > Please help!
.

Relevant Pages

  • RE: Using a subform to display and add info into table
    ... you currently do not have a way of capturing which session a student ... so you need a SessionID field in Enrollments. ... >> ControlSource: SessionID in Enrollments table ...
    (microsoft.public.access.forms)
  • insert data from unbound form & combo boxes to table
    ... access as the programmer we hired for our non-profit group went AWOL with an ... has the LMHSCID, sessionID, Student ... If a student chooses four classes they would have four entries. ...
    (microsoft.public.access.formscoding)
  • RE: Using a subform to display and add info into table
    ... ClassName, SessionDay, SessionTime to display within my combobox, I've done ... you currently do not have a way of capturing which session a student ... so you need a SessionID field in Enrollments. ... > need the ClassID in the Enrollments table. ...
    (microsoft.public.access.forms)
Loading