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

Sprinks -

You're awesome. Works great. But now that I have the ability to sign
students up for a class, I would like to be able to keep track of their
attendence.

I have created an Attendence table using SessionID, StudentID, and Date.
And I'm going to want my form based on the Classes table, then have a subform
based on the Attendence table....?? This is where I get lost, because I
want it to display the ClassName, and then list all the students that are
signed up for that class, then have a Date field to distinguish between
classes, then next to the students' name have a checkbox to verify if they
were there on that particular class.

Can you help me out with this last little feature of my database?? Thank
you very much for all your help!

ivalum21

"Sprinks" wrote:

> I believe the ControlSource of the combo box is the problem. Access is
> interpreting it as an expression, rather than your SessionID field. A
> control can be bound either to a field or an expression, but not both.
>
> Open your form in design view and double-click on the subform to edit the
> subform itself. Show the properties for the combo box, click the pick box to
> the right of the ControlSource window, and select SessionID.
>
> Hope that helps.
> Sprinks
>
> "ivalum21" wrote:
>
> > Here is the information you requested...
> >
> > Combo Box
> > ---------------------
> > ControlSource: =Enrollments!SessionID
> > BoundColumn: 1
> > 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;
> > ColumnCount: 4
> >
> > Subform (Control)
> > ----------------------
> > LinkMasterFields: StudentID
> > LinkChildFields: StudentID
> >
> > Subform (Form)
> > -------------------
> > RecordSource: SELECT Enrollments.* FROM Enrollments;
> >
> > ivalum21
> >
> >
> >
> >
> > "Sprinks" wrote:
> >
> > > Hi, ivalum.
> > >
> > > Please post the following properties:
> > >
> > > Combo Box
> > > ---------------------
> > > ControlSource
> > > BoundColumn
> > > RowSource
> > > ColumnCount
> > >
> > > Subform (Control)
> > > ----------------------
> > > LinkMasterFields
> > > LinkChildFields
> > >
> > > Subform (Form)
> > > -------------------
> > > RecordSource
> > >
> > >
> > >
> > > "ivalum21" wrote:
> > >
> > > > 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!
.