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

Sprinks -

Everything seems to have worked great with one exception. I got the
ClassName, SessionDay, SessionTime to display within my combobox, I've done
everything you've listed, but when I run my form and try to select a class, I
get a message in the status bar that says "Control cannot be edited. It is
bound to the expression 'Enrollments!SessionID'". So I can't select a class
for a student...

I appreciate all of your help, thank you.

ivalum21

"Sprinks" wrote:

> 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. ... Moreover, since the SessionID determines the ClassID as well, you do not ...
    (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
    ... 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)
  • Using a subform to display and add info into table
    ... I have a subform where I want to display the classes the student is signed ... ClassID), and Sessions. ... I also want in my subform to have the ClassName be a combo box that displays ...
    (microsoft.public.access.forms)
Loading