Using a subform to display and add info into table

[ivalum21]

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!

 

[Sprinks]

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]

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]

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]

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]

Hi, ivalum.

Please post the following properties:

Combo Box
---------------------
ControlSource
BoundColumn
RowSource
ColumnCount

Subform (Control)

 

[ivalum21]

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]

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]

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]

Hi, ivalum.

This took a little thought. It always helps me to think about the “thingsâ€
(tables) and the relationships.

In this case, there is really a new “thingâ€â€”i.e., an “instance†of one of
the sessions. Each SessionID will have 16 or so “instances†in a semester.
Each instance will have many students, and, in fact, the same students that
are associated with that session.

This relationship suggests the following tables:

SessionDays:
SessionDayID AutoNumber (PK)
SessionID Integer (FK to Sessions)
SessionDate Date/Time

Attendance:
AttendanceID AutoNumber (PK)
SessionDayID Integer (FK to SessionDays)
StudentID Integer (FK to Students)
Present Yes/No

To enter the instance, create a main form based on SessionDays that includes
a combo box for the SessionID (with the same properties as the one on your
enrollment form), the same textboxes displaying the day & time via the Column
property, and a textbox for the SessionDate.

The students are a little trickier. Since StudentName is not a field in
Attendance, you could use a combo box to display the student name based on
the StudentID in the Attendance table, but a combo box implies user choice,
and you really don’t want the user to change the name, but rather just check
off if he/she was there. So I suggest a continuous subform based on a query,
linked by the SessionDayID:

SELECT Attendance.AttendanceID, Attendance.SessionDayID,
Attendance.StudentID, Students.StudentName, Attendance.Present
FROM Attendance INNER JOIN Students ON Attendance.StudentID =
Students.StudentID;

Include a textbox for the StudentName and a checkbox for Present. Set the
StudentName’s Locked property to Yes (Data tab) and Tab Stop property (Other
tab) to No to prevent changes to and tabbing into the textbox.

To “preload†Attendance with records for all of the students for this
particular SessionDayID, create a command button to insert the records after
you’ve entered the main form record. The button will execute an Append query
and requery the subform to display the added records, ready for you to check
off the attendance.

Dim stDocName As String

stDocName = "AppendAttendanceRecords"
DoCmd.OpenQuery stDocName, acNormal
' Requery subform
Me!AttendanceQuery.Requery

' AppendAttendanceRecords Query SQL:

INSERT INTO Attendance ( SessionDayID, StudentID )
SELECT SessionDays.SessionDayID, Enrollments.StudentID
FROM SessionDays INNER JOIN Enrollments ON SessionDays.SessionID =
Enrollments.SessionID
WHERE (((SessionDays.SessionID)=[Forms]![SessionDays]![SessionID]));

To prevent adding duplicates by pressing the command button more than once,
add a multiple field index to the Attendance table.

Open Attendance in Design View, and choose View, Indexes. Leave the indexes
that Access has already created for you. On a new line at the bottom, give
the index a name, such as SingleDayStudent, and choose the SessionDayID from
the pick box. On the next line, enter nothing in the Name column, but choose
StudentID in the FieldName pick box. Go back up to the previous line, and
three fields will show at the bottom of the window. Set Unique to Yes, save
& exit. Access will now prevent you from adding duplicates, and will display
a message to the user.

Please note that the code above is tested but in many cases refers to other
objects. If you use different names for your queries, fields, tables, and
forms, you will need to adjust the code accordingly.

Hope that helps.
Sprinks

 

[ivalum21]

Sprinks -

Alright, I have finished everything you have listed here, and now the only
problem I'm having is with the VB code. To prevent any confusion I've made
my tables/queries/properties the same as your names, I can change them later
if need be. But I have taken your VB code and query and copied it into my
command button's click event exactly the way it appears in your message. The
query is coming up red thus meaning that it must be wrong somehow...any ideas?

I have tested the Attedance form and it works great when I put the
appropriate information manually into the tables. But without the VB code, I
can't have the data updated automatically.

Thank you Sprinks,
ivalum21

Hi, ivalum.

This took a little thought. It always helps me to think about the “thingsâ€
(tables) and the relationships.

In this case, there is really a new “thingâ€â€”i.e., an “instance†of one of
the sessions. Each SessionID will have 16 or so “instances†in a semester.
Each instance will have many students, and, in fact, the same students that
are associated with that session.

This relationship suggests the following tables:

SessionDays:
SessionDayID AutoNumber (PK)
SessionID Integer (FK to Sessions)
SessionDate Date/Time

Attendance:
AttendanceID AutoNumber (PK)
SessionDayID Integer (FK to SessionDays)
StudentID Integer (FK to Students)
Present Yes/No

To enter the instance, create a main form based on SessionDays that includes
a combo box for the SessionID (with the same properties as the one on your
enrollment form), the same textboxes displaying the day & time via the Column
property, and a textbox for the SessionDate.

The students are a little trickier. Since StudentName is not a field in
Attendance, you could use a combo box to display the student name based on
the StudentID in the Attendance table, but a combo box implies user choice,
and you really don’t want the user to change the name, but rather just check
off if he/she was there. So I suggest a continuous subform based on a query,
linked by the SessionDayID:

SELECT Attendance.AttendanceID, Attendance.SessionDayID,
Attendance.StudentID, Students.StudentName, Attendance.Present
FROM Attendance INNER JOIN Students ON Attendance.StudentID =
Students.StudentID;

Include a textbox for the StudentName and a checkbox for Present. Set the
StudentName’s Locked property to Yes (Data tab) and Tab Stop property (Other
tab) to No to prevent changes to and tabbing into the textbox.

To “preload†Attendance with records for all of the students for this
particular SessionDayID, create a command button to insert the records after
you’ve entered the main form record. The button will execute an Append query
and requery the subform to display the added records, ready for you to check
off the attendance.

Dim stDocName As String

stDocName = "AppendAttendanceRecords"
DoCmd.OpenQuery stDocName, acNormal
' Requery subform
Me!AttendanceQuery.Requery

' AppendAttendanceRecords Query SQL:

INSERT INTO Attendance ( SessionDayID, StudentID )
SELECT SessionDays.SessionDayID, Enrollments.StudentID
FROM SessionDays INNER JOIN Enrollments ON SessionDays.SessionID =
Enrollments.SessionID
WHERE (((SessionDays.SessionID)=[Forms]![SessionDays]![SessionID]));

To prevent adding duplicates by pressing the command button more than once,
add a multiple field index to the Attendance table.

Open Attendance in Design View, and choose View, Indexes. Leave the indexes
that Access has already created for you. On a new line at the bottom, give
the index a name, such as SingleDayStudent, and choose the SessionDayID from
the pick box. On the next line, enter nothing in the Name column, but choose
StudentID in the FieldName pick box. Go back up to the previous line, and
three fields will show at the bottom of the window. Set Unique to Yes, save
& exit. Access will now prevent you from adding duplicates, and will display
a message to the user.

Please note that the code above is tested but in many cases refers to other
objects. If you use different names for your queries, fields, tables, and
forms, you will need to adjust the code accordingly.

Hope that helps.
Sprinks

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]

Ivalum,

Sorry you're having trouble, but I think you're almost there.

- Did you create a query by cutting & pasting the INSERT SQL statement into
the SQL View window, and save it by the name “AppendAttendanceRecords�
Check the spelling carefully, because I noticed you were spelling
“attendance†“attendence†in earlier posts.
- The command button code requeries the subform, and assumes that the name
of the subform control is “AttendanceQueryâ€. Change it to the name of your
control if not.
- If the form and subform are working manually, then I presume the query of
the subform is correct.
- Is your enrollment table called “Enrollments†and the attendance table
“Attendance� Check the spelling carefully.
- With the Attendance form open, with a SessionID and SessionDate entered,
try executing the AppendAttendanceRecords query from the query window. After
executing it, close the Attendance form, reopen it, and go the same record.
Did it insert the student records?

If none of these tests resolve your code, post the non-null data properties
and name of your subform, and the names of the subform and insert queries.

Sprinks

Sprinks -

Alright, I have finished everything you have listed here, and now the only
problem I'm having is with the VB code. To prevent any confusion I've made
my tables/queries/properties the same as your names, I can change them later
if need be. But I have taken your VB code and query and copied it into my
command button's click event exactly the way it appears in your message. The
query is coming up red thus meaning that it must be wrong somehow...any ideas?

I have tested the Attedance form and it works great when I put the
appropriate information manually into the tables. But without the VB code, I
can't have the data updated automatically.

Thank you Sprinks,
ivalum21

Hi, ivalum.

This took a little thought. It always helps me to think about the “thingsâ€
(tables) and the relationships.

In this case, there is really a new “thingâ€â€”i.e., an “instance†of one of
the sessions. Each SessionID will have 16 or so “instances†in a semester.
Each instance will have many students, and, in fact, the same students that
are associated with that session.

This relationship suggests the following tables:

SessionDays:
SessionDayID AutoNumber (PK)
SessionID Integer (FK to Sessions)
SessionDate Date/Time

Attendance:
AttendanceID AutoNumber (PK)
SessionDayID Integer (FK to SessionDays)
StudentID Integer (FK to Students)
Present Yes/No

To enter the instance, create a main form based on SessionDays that includes
a combo box for the SessionID (with the same properties as the one on your
enrollment form), the same textboxes displaying the day & time via the Column
property, and a textbox for the SessionDate.

The students are a little trickier. Since StudentName is not a field in
Attendance, you could use a combo box to display the student name based on
the StudentID in the Attendance table, but a combo box implies user choice,
and you really don’t want the user to change the name, but rather just check
off if he/she was there. So I suggest a continuous subform based on a query,
linked by the SessionDayID:

SELECT Attendance.AttendanceID, Attendance.SessionDayID,
Attendance.StudentID, Students.StudentName, Attendance.Present
FROM Attendance INNER JOIN Students ON Attendance.StudentID =
Students.StudentID;

Include a textbox for the StudentName and a checkbox for Present. Set the
StudentName’s Locked property to Yes (Data tab) and Tab Stop property (Other
tab) to No to prevent changes to and tabbing into the textbox.

To “preload†Attendance with records for all of the students for this
particular SessionDayID, create a command button to insert the records after
you’ve entered the main form record. The button will execute an Append query
and requery the subform to display the added records, ready for you to check
off the attendance.

Dim stDocName As String

stDocName = "AppendAttendanceRecords"
DoCmd.OpenQuery stDocName, acNormal
' Requery subform
Me!AttendanceQuery.Requery

' AppendAttendanceRecords Query SQL:

INSERT INTO Attendance ( SessionDayID, StudentID )
SELECT SessionDays.SessionDayID, Enrollments.StudentID
FROM SessionDays INNER JOIN Enrollments ON SessionDays.SessionID =
Enrollments.SessionID
WHERE (((SessionDays.SessionID)=[Forms]![SessionDays]![SessionID]));

To prevent adding duplicates by pressing the command button more than once,
add a multiple field index to the Attendance table.

Open Attendance in Design View, and choose View, Indexes. Leave the indexes
that Access has already created for you. On a new line at the bottom, give
the index a name, such as SingleDayStudent, and choose the SessionDayID from
the pick box. On the next line, enter nothing in the Name column, but choose
StudentID in the FieldName pick box. Go back up to the previous line, and
three fields will show at the bottom of the window. Set Unique to Yes, save
& exit. Access will now prevent you from adding duplicates, and will display
a message to the user.

Please note that the code above is tested but in many cases refers to other
objects. If you use different names for your queries, fields, tables, and
forms, you will need to adjust the code accordingly.

Hope that helps.
Sprinks

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

:

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

:

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




:

Hi, ivalum.

Please post the following properties:

Combo Box
---------------------
ControlSource
BoundColumn
RowSource
ColumnCount

Subform (Control)
----------------------
LinkMasterFields
LinkChildFields

Subform (Form)
-------------------
RecordSource



:

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

:

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

:

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

:

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

:

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!

 

[ivalum21]

Sprinks -

I created a query called AppendAttendanceRecords and used the INSERT SQL
statement you wrote below (and I did notice the attendance spelling thing a
couple posts ago...got that fixed...). However, when I have the Attendance
form open, I then run the INSERT query and I get an Enter Parameter Value
window that pops saying "Forms!SessionDay!SessionID" and wants me to type in
a value. I thought the only thing that was wrong is that it should be "Form"
instead of "Forms", but that didn't work either. Any ideas??

ivalum21

Here is the code in my command buttons click event:

Private Sub Command5_Click()

Dim stDocName As String

stDocName = "AppendAttendanceRecords"
DoCmd.OpenQuery stDocName, acNormal
' Requery subform
Me!Attendance_subform.Requery

End Sub

Everything else is okay.

Ivalum,

Sorry you're having trouble, but I think you're almost there.

- Did you create a query by cutting & pasting the INSERT SQL statement into
the SQL View window, and save it by the name “AppendAttendanceRecords�
Check the spelling carefully, because I noticed you were spelling
“attendance†“attendence†in earlier posts.
- The command button code requeries the subform, and assumes that the name
of the subform control is “AttendanceQueryâ€. Change it to the name of your
control if not.
- If the form and subform are working manually, then I presume the query of
the subform is correct.
- Is your enrollment table called “Enrollments†and the attendance table
“Attendance� Check the spelling carefully.
- With the Attendance form open, with a SessionID and SessionDate entered,
try executing the AppendAttendanceRecords query from the query window. After
executing it, close the Attendance form, reopen it, and go the same record.
Did it insert the student records?

If none of these tests resolve your code, post the non-null data properties
and name of your subform, and the names of the subform and insert queries.

Sprinks

Sprinks -

Alright, I have finished everything you have listed here, and now the only
problem I'm having is with the VB code. To prevent any confusion I've made
my tables/queries/properties the same as your names, I can change them later
if need be. But I have taken your VB code and query and copied it into my
command button's click event exactly the way it appears in your message. The
query is coming up red thus meaning that it must be wrong somehow...any ideas?

I have tested the Attedance form and it works great when I put the
appropriate information manually into the tables. But without the VB code, I
can't have the data updated automatically.

Thank you Sprinks,
ivalum21

Hi, ivalum.

This took a little thought. It always helps me to think about the “thingsâ€
(tables) and the relationships.

In this case, there is really a new “thingâ€â€”i.e., an “instance†of one of
the sessions. Each SessionID will have 16 or so “instances†in a semester.
Each instance will have many students, and, in fact, the same students that
are associated with that session.

This relationship suggests the following tables:

SessionDays:
SessionDayID AutoNumber (PK)
SessionID Integer (FK to Sessions)
SessionDate Date/Time

Attendance:
AttendanceID AutoNumber (PK)
SessionDayID Integer (FK to SessionDays)
StudentID Integer (FK to Students)
Present Yes/No

To enter the instance, create a main form based on SessionDays that includes
a combo box for the SessionID (with the same properties as the one on your
enrollment form), the same textboxes displaying the day & time via the Column
property, and a textbox for the SessionDate.

The students are a little trickier. Since StudentName is not a field in
Attendance, you could use a combo box to display the student name based on
the StudentID in the Attendance table, but a combo box implies user choice,
and you really don’t want the user to change the name, but rather just check
off if he/she was there. So I suggest a continuous subform based on a query,
linked by the SessionDayID:

SELECT Attendance.AttendanceID, Attendance.SessionDayID,
Attendance.StudentID, Students.StudentName, Attendance.Present
FROM Attendance INNER JOIN Students ON Attendance.StudentID =
Students.StudentID;

Include a textbox for the StudentName and a checkbox for Present. Set the
StudentName’s Locked property to Yes (Data tab) and Tab Stop property (Other
tab) to No to prevent changes to and tabbing into the textbox.

To “preload†Attendance with records for all of the students for this
particular SessionDayID, create a command button to insert the records after
you’ve entered the main form record. The button will execute an Append query
and requery the subform to display the added records, ready for you to check
off the attendance.

Dim stDocName As String

stDocName = "AppendAttendanceRecords"
DoCmd.OpenQuery stDocName, acNormal
' Requery subform
Me!AttendanceQuery.Requery

' AppendAttendanceRecords Query SQL:

INSERT INTO Attendance ( SessionDayID, StudentID )
SELECT SessionDays.SessionDayID, Enrollments.StudentID
FROM SessionDays INNER JOIN Enrollments ON SessionDays.SessionID =
Enrollments.SessionID
WHERE (((SessionDays.SessionID)=[Forms]![SessionDays]![SessionID]));

To prevent adding duplicates by pressing the command button more than once,
add a multiple field index to the Attendance table.

Open Attendance in Design View, and choose View, Indexes. Leave the indexes
that Access has already created for you. On a new line at the bottom, give
the index a name, such as SingleDayStudent, and choose the SessionDayID from
the pick box. On the next line, enter nothing in the Name column, but choose
StudentID in the FieldName pick box. Go back up to the previous line, and
three fields will show at the bottom of the window. Set Unique to Yes, save
& exit. Access will now prevent you from adding duplicates, and will display
a message to the user.

Please note that the code above is tested but in many cases refers to other
objects. If you use different names for your queries, fields, tables, and
forms, you will need to adjust the code accordingly.

Hope that helps.
Sprinks


:

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

:

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

:

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




:

Hi, ivalum.

Please post the following properties:

Combo Box
---------------------
ControlSource
BoundColumn
RowSource
ColumnCount

Subform (Control)
----------------------
LinkMasterFields
LinkChildFields

Subform (Form)
-------------------
RecordSource



:

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

:

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

:

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

:

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.

 

[Sprinks]

Forms!FormName!FormControlName is the proper syntax for referring to a
control on an open form. The term Forms! refers to the collection of all
open forms.

The error message means that Access cannot find the control you're
specifying, and so is asking you to provide a value. Since you know the form
is open, this could only be because either:

- Your form is not named SessionDay
- Your control is not named SessionID

Hope that resolves it.
Sprinks

Sprinks -

I created a query called AppendAttendanceRecords and used the INSERT SQL
statement you wrote below (and I did notice the attendance spelling thing a
couple posts ago...got that fixed...). However, when I have the Attendance
form open, I then run the INSERT query and I get an Enter Parameter Value
window that pops saying "Forms!SessionDay!SessionID" and wants me to type in
a value. I thought the only thing that was wrong is that it should be "Form"
instead of "Forms", but that didn't work either. Any ideas??

ivalum21

Here is the code in my command buttons click event:

Private Sub Command5_Click()

Dim stDocName As String

stDocName = "AppendAttendanceRecords"
DoCmd.OpenQuery stDocName, acNormal
' Requery subform
Me!Attendance_subform.Requery

End Sub

Everything else is okay.

Ivalum,

Sorry you're having trouble, but I think you're almost there.

- Did you create a query by cutting & pasting the INSERT SQL statement into
the SQL View window, and save it by the name “AppendAttendanceRecords�
Check the spelling carefully, because I noticed you were spelling
“attendance†“attendence†in earlier posts.
- The command button code requeries the subform, and assumes that the name
of the subform control is “AttendanceQueryâ€. Change it to the name of your
control if not.
- If the form and subform are working manually, then I presume the query of
the subform is correct.
- Is your enrollment table called “Enrollments†and the attendance table
“Attendance� Check the spelling carefully.
- With the Attendance form open, with a SessionID and SessionDate entered,
try executing the AppendAttendanceRecords query from the query window. After
executing it, close the Attendance form, reopen it, and go the same record.
Did it insert the student records?

If none of these tests resolve your code, post the non-null data properties
and name of your subform, and the names of the subform and insert queries.

Sprinks

Sprinks -

Alright, I have finished everything you have listed here, and now the only
problem I'm having is with the VB code. To prevent any confusion I've made
my tables/queries/properties the same as your names, I can change them later
if need be. But I have taken your VB code and query and copied it into my
command button's click event exactly the way it appears in your message. The
query is coming up red thus meaning that it must be wrong somehow...any ideas?

I have tested the Attedance form and it works great when I put the
appropriate information manually into the tables. But without the VB code, I
can't have the data updated automatically.

Thank you Sprinks,
ivalum21

:

Hi, ivalum.

This took a little thought. It always helps me to think about the “thingsâ€
(tables) and the relationships.

In this case, there is really a new “thingâ€â€”i.e., an “instance†of one of
the sessions. Each SessionID will have 16 or so “instances†in a semester.
Each instance will have many students, and, in fact, the same students that
are associated with that session.

This relationship suggests the following tables:

SessionDays:
SessionDayID AutoNumber (PK)
SessionID Integer (FK to Sessions)
SessionDate Date/Time

Attendance:
AttendanceID AutoNumber (PK)
SessionDayID Integer (FK to SessionDays)
StudentID Integer (FK to Students)
Present Yes/No

To enter the instance, create a main form based on SessionDays that includes
a combo box for the SessionID (with the same properties as the one on your
enrollment form), the same textboxes displaying the day & time via the Column
property, and a textbox for the SessionDate.

The students are a little trickier. Since StudentName is not a field in
Attendance, you could use a combo box to display the student name based on
the StudentID in the Attendance table, but a combo box implies user choice,
and you really don’t want the user to change the name, but rather just check
off if he/she was there. So I suggest a continuous subform based on a query,
linked by the SessionDayID:

SELECT Attendance.AttendanceID, Attendance.SessionDayID,
Attendance.StudentID, Students.StudentName, Attendance.Present
FROM Attendance INNER JOIN Students ON Attendance.StudentID =
Students.StudentID;

Include a textbox for the StudentName and a checkbox for Present. Set the
StudentName’s Locked property to Yes (Data tab) and Tab Stop property (Other
tab) to No to prevent changes to and tabbing into the textbox.

To “preload†Attendance with records for all of the students for this
particular SessionDayID, create a command button to insert the records after
you’ve entered the main form record. The button will execute an Append query
and requery the subform to display the added records, ready for you to check
off the attendance.

Dim stDocName As String

stDocName = "AppendAttendanceRecords"
DoCmd.OpenQuery stDocName, acNormal
' Requery subform
Me!AttendanceQuery.Requery

' AppendAttendanceRecords Query SQL:

INSERT INTO Attendance ( SessionDayID, StudentID )
SELECT SessionDays.SessionDayID, Enrollments.StudentID
FROM SessionDays INNER JOIN Enrollments ON SessionDays.SessionID =
Enrollments.SessionID
WHERE (((SessionDays.SessionID)=[Forms]![SessionDays]![SessionID]));

To prevent adding duplicates by pressing the command button more than once,
add a multiple field index to the Attendance table.

Open Attendance in Design View, and choose View, Indexes. Leave the indexes
that Access has already created for you. On a new line at the bottom, give
the index a name, such as SingleDayStudent, and choose the SessionDayID from
the pick box. On the next line, enter nothing in the Name column, but choose
StudentID in the FieldName pick box. Go back up to the previous line, and
three fields will show at the bottom of the window. Set Unique to Yes, save
& exit. Access will now prevent you from adding duplicates, and will display
a message to the user.

Please note that the code above is tested but in many cases refers to other
objects. If you use different names for your queries, fields, tables, and
forms, you will need to adjust the code accordingly.

Hope that helps.
Sprinks


:

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

:

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

:

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




:

Hi, ivalum.

Please post the following properties:

Combo Box
---------------------
ControlSource
BoundColumn
RowSource
ColumnCount

Subform (Control)
----------------------
LinkMasterFields
LinkChildFields

Subform (Form)
-------------------
RecordSource



:

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

:

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)

 

[ivalum21]

Sprinks -

I'm having trouble getting the command button to work properly. I know
exactly what the problem is, I just don't know how to fix it.

When I put the VB code into my database, I used what I believe is the name
for my subform (when I have my subform selected, my properties window says
"Attendance subform", but when I try to put that into the VB code you have it
won't let me use a space. So then I tried to do "Attendance_subform", but
that is where my problem comes in.

When I click on my control button, it comes up and says its going to run my
append query, I click Yes, it tells me how many rows I'm going to append, I
click Yes, and then it tells me it can't find the Attendance_subform referred
to in my expression.

Do I have the wrong subform name? I don't think I do because when my
subform is selected, the name property under the Other tab says Attendance
subform.

Help?

ivalum21

Forms!FormName!FormControlName is the proper syntax for referring to a
control on an open form. The term Forms! refers to the collection of all
open forms.

The error message means that Access cannot find the control you're
specifying, and so is asking you to provide a value. Since you know the form
is open, this could only be because either:

- Your form is not named SessionDay
- Your control is not named SessionID

Hope that resolves it.
Sprinks

Sprinks -

I created a query called AppendAttendanceRecords and used the INSERT SQL
statement you wrote below (and I did notice the attendance spelling thing a
couple posts ago...got that fixed...). However, when I have the Attendance
form open, I then run the INSERT query and I get an Enter Parameter Value
window that pops saying "Forms!SessionDay!SessionID" and wants me to type in
a value. I thought the only thing that was wrong is that it should be "Form"
instead of "Forms", but that didn't work either. Any ideas??

ivalum21

Here is the code in my command buttons click event:

Private Sub Command5_Click()

Dim stDocName As String

stDocName = "AppendAttendanceRecords"
DoCmd.OpenQuery stDocName, acNormal
' Requery subform
Me!Attendance_subform.Requery

End Sub

Everything else is okay.

Ivalum,

Sorry you're having trouble, but I think you're almost there.

- Did you create a query by cutting & pasting the INSERT SQL statement into
the SQL View window, and save it by the name “AppendAttendanceRecords�
Check the spelling carefully, because I noticed you were spelling
“attendance†“attendence†in earlier posts.
- The command button code requeries the subform, and assumes that the name
of the subform control is “AttendanceQueryâ€. Change it to the name of your
control if not.
- If the form and subform are working manually, then I presume the query of
the subform is correct.
- Is your enrollment table called “Enrollments†and the attendance table
“Attendance� Check the spelling carefully.
- With the Attendance form open, with a SessionID and SessionDate entered,
try executing the AppendAttendanceRecords query from the query window. After
executing it, close the Attendance form, reopen it, and go the same record.
Did it insert the student records?

If none of these tests resolve your code, post the non-null data properties
and name of your subform, and the names of the subform and insert queries.

Sprinks




:

Sprinks -

Alright, I have finished everything you have listed here, and now the only
problem I'm having is with the VB code. To prevent any confusion I've made
my tables/queries/properties the same as your names, I can change them later
if need be. But I have taken your VB code and query and copied it into my
command button's click event exactly the way it appears in your message. The
query is coming up red thus meaning that it must be wrong somehow...any ideas?

I have tested the Attedance form and it works great when I put the
appropriate information manually into the tables. But without the VB code, I
can't have the data updated automatically.

Thank you Sprinks,
ivalum21

:

Hi, ivalum.

This took a little thought. It always helps me to think about the “thingsâ€
(tables) and the relationships.

In this case, there is really a new “thingâ€â€”i.e., an “instance†of one of
the sessions. Each SessionID will have 16 or so “instances†in a semester.
Each instance will have many students, and, in fact, the same students that
are associated with that session.

This relationship suggests the following tables:

SessionDays:
SessionDayID AutoNumber (PK)
SessionID Integer (FK to Sessions)
SessionDate Date/Time

Attendance:
AttendanceID AutoNumber (PK)
SessionDayID Integer (FK to SessionDays)
StudentID Integer (FK to Students)
Present Yes/No

To enter the instance, create a main form based on SessionDays that includes
a combo box for the SessionID (with the same properties as the one on your
enrollment form), the same textboxes displaying the day & time via the Column
property, and a textbox for the SessionDate.

The students are a little trickier. Since StudentName is not a field in
Attendance, you could use a combo box to display the student name based on
the StudentID in the Attendance table, but a combo box implies user choice,
and you really don’t want the user to change the name, but rather just check
off if he/she was there. So I suggest a continuous subform based on a query,
linked by the SessionDayID:

SELECT Attendance.AttendanceID, Attendance.SessionDayID,
Attendance.StudentID, Students.StudentName, Attendance.Present
FROM Attendance INNER JOIN Students ON Attendance.StudentID =
Students.StudentID;

Include a textbox for the StudentName and a checkbox for Present. Set the
StudentName’s Locked property to Yes (Data tab) and Tab Stop property (Other
tab) to No to prevent changes to and tabbing into the textbox.

To “preload†Attendance with records for all of the students for this
particular SessionDayID, create a command button to insert the records after
you’ve entered the main form record. The button will execute an Append query
and requery the subform to display the added records, ready for you to check
off the attendance.

Dim stDocName As String

stDocName = "AppendAttendanceRecords"
DoCmd.OpenQuery stDocName, acNormal
' Requery subform
Me!AttendanceQuery.Requery

' AppendAttendanceRecords Query SQL:

INSERT INTO Attendance ( SessionDayID, StudentID )
SELECT SessionDays.SessionDayID, Enrollments.StudentID
FROM SessionDays INNER JOIN Enrollments ON SessionDays.SessionID =
Enrollments.SessionID
WHERE (((SessionDays.SessionID)=[Forms]![SessionDays]![SessionID]));

To prevent adding duplicates by pressing the command button more than once,
add a multiple field index to the Attendance table.

Open Attendance in Design View, and choose View, Indexes. Leave the indexes
that Access has already created for you. On a new line at the bottom, give
the index a name, such as SingleDayStudent, and choose the SessionDayID from
the pick box. On the next line, enter nothing in the Name column, but choose
StudentID in the FieldName pick box. Go back up to the previous line, and
three fields will show at the bottom of the window. Set Unique to Yes, save
& exit. Access will now prevent you from adding duplicates, and will display
a message to the user.

Please note that the code above is tested but in many cases refers to other
objects. If you use different names for your queries, fields, tables, and
forms, you will need to adjust the code accordingly.

Hope that helps.
Sprinks


:

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

:

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

:

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




:

Hi, ivalum.

Please post the following properties:

Combo Box
---------------------
ControlSource
BoundColumn
RowSource
ColumnCount

Subform (Control)
----------------------
LinkMasterFields
LinkChildFields

Subform (Form)
-------------------
RecordSource



:

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

:

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

 

[Sprinks]

Hi, ivalum.

You have the right name. This problem is one reason most developers use
names that don't contain spaces--they make the delimiting brackets around
object names optional.

Me!MySubform.Requery & Me![MySubform].Requery are equivalent but
Me!My Subform.Requery won't work.

Change the line to:

Me![Attendance subform].Requery

Also, it sounds like the insert query is running, but because the requery
doesn't happen, it wouldn't show the new records. If you exit the form, and
come back to the same record, are the student records there?

Sprinks

Sprinks -

I'm having trouble getting the command button to work properly. I know
exactly what the problem is, I just don't know how to fix it.

When I put the VB code into my database, I used what I believe is the name
for my subform (when I have my subform selected, my properties window says
"Attendance subform", but when I try to put that into the VB code you have it
won't let me use a space. So then I tried to do "Attendance_subform", but
that is where my problem comes in.

When I click on my control button, it comes up and says its going to run my
append query, I click Yes, it tells me how many rows I'm going to append, I
click Yes, and then it tells me it can't find the Attendance_subform referred
to in my expression.

Do I have the wrong subform name? I don't think I do because when my
subform is selected, the name property under the Other tab says Attendance
subform.

Help?

ivalum21

Forms!FormName!FormControlName is the proper syntax for referring to a
control on an open form. The term Forms! refers to the collection of all
open forms.

The error message means that Access cannot find the control you're
specifying, and so is asking you to provide a value. Since you know the form
is open, this could only be because either:

- Your form is not named SessionDay
- Your control is not named SessionID

Hope that resolves it.
Sprinks

Sprinks -

I created a query called AppendAttendanceRecords and used the INSERT SQL
statement you wrote below (and I did notice the attendance spelling thing a
couple posts ago...got that fixed...). However, when I have the Attendance
form open, I then run the INSERT query and I get an Enter Parameter Value
window that pops saying "Forms!SessionDay!SessionID" and wants me to type in
a value. I thought the only thing that was wrong is that it should be "Form"
instead of "Forms", but that didn't work either. Any ideas??

ivalum21

Here is the code in my command buttons click event:

Private Sub Command5_Click()

Dim stDocName As String

stDocName = "AppendAttendanceRecords"
DoCmd.OpenQuery stDocName, acNormal
' Requery subform
Me!Attendance_subform.Requery

End Sub

Everything else is okay.

:

Ivalum,

Sorry you're having trouble, but I think you're almost there.

- Did you create a query by cutting & pasting the INSERT SQL statement into
the SQL View window, and save it by the name “AppendAttendanceRecords�
Check the spelling carefully, because I noticed you were spelling
“attendance†“attendence†in earlier posts.
- The command button code requeries the subform, and assumes that the name
of the subform control is “AttendanceQueryâ€. Change it to the name of your
control if not.
- If the form and subform are working manually, then I presume the query of
the subform is correct.
- Is your enrollment table called “Enrollments†and the attendance table
“Attendance� Check the spelling carefully.
- With the Attendance form open, with a SessionID and SessionDate entered,
try executing the AppendAttendanceRecords query from the query window. After
executing it, close the Attendance form, reopen it, and go the same record.
Did it insert the student records?

If none of these tests resolve your code, post the non-null data properties
and name of your subform, and the names of the subform and insert queries.

Sprinks




:

Sprinks -

Alright, I have finished everything you have listed here, and now the only
problem I'm having is with the VB code. To prevent any confusion I've made
my tables/queries/properties the same as your names, I can change them later
if need be. But I have taken your VB code and query and copied it into my
command button's click event exactly the way it appears in your message. The
query is coming up red thus meaning that it must be wrong somehow...any ideas?

I have tested the Attedance form and it works great when I put the
appropriate information manually into the tables. But without the VB code, I
can't have the data updated automatically.

Thank you Sprinks,
ivalum21

:

Hi, ivalum.

This took a little thought. It always helps me to think about the “thingsâ€
(tables) and the relationships.

In this case, there is really a new “thingâ€â€”i.e., an “instance†of one of
the sessions. Each SessionID will have 16 or so “instances†in a semester.
Each instance will have many students, and, in fact, the same students that
are associated with that session.

This relationship suggests the following tables:

SessionDays:
SessionDayID AutoNumber (PK)
SessionID Integer (FK to Sessions)
SessionDate Date/Time

Attendance:
AttendanceID AutoNumber (PK)
SessionDayID Integer (FK to SessionDays)
StudentID Integer (FK to Students)
Present Yes/No

To enter the instance, create a main form based on SessionDays that includes
a combo box for the SessionID (with the same properties as the one on your
enrollment form), the same textboxes displaying the day & time via the Column
property, and a textbox for the SessionDate.

The students are a little trickier. Since StudentName is not a field in
Attendance, you could use a combo box to display the student name based on
the StudentID in the Attendance table, but a combo box implies user choice,
and you really don’t want the user to change the name, but rather just check
off if he/she was there. So I suggest a continuous subform based on a query,
linked by the SessionDayID:

SELECT Attendance.AttendanceID, Attendance.SessionDayID,
Attendance.StudentID, Students.StudentName, Attendance.Present
FROM Attendance INNER JOIN Students ON Attendance.StudentID =
Students.StudentID;

Include a textbox for the StudentName and a checkbox for Present. Set the
StudentName’s Locked property to Yes (Data tab) and Tab Stop property (Other
tab) to No to prevent changes to and tabbing into the textbox.

To “preload†Attendance with records for all of the students for this
particular SessionDayID, create a command button to insert the records after
you’ve entered the main form record. The button will execute an Append query
and requery the subform to display the added records, ready for you to check
off the attendance.

Dim stDocName As String

stDocName = "AppendAttendanceRecords"
DoCmd.OpenQuery stDocName, acNormal
' Requery subform
Me!AttendanceQuery.Requery

' AppendAttendanceRecords Query SQL:

INSERT INTO Attendance ( SessionDayID, StudentID )
SELECT SessionDays.SessionDayID, Enrollments.StudentID
FROM SessionDays INNER JOIN Enrollments ON SessionDays.SessionID =
Enrollments.SessionID
WHERE (((SessionDays.SessionID)=[Forms]![SessionDays]![SessionID]));

To prevent adding duplicates by pressing the command button more than once,
add a multiple field index to the Attendance table.

Open Attendance in Design View, and choose View, Indexes. Leave the indexes
that Access has already created for you. On a new line at the bottom, give
the index a name, such as SingleDayStudent, and choose the SessionDayID from
the pick box. On the next line, enter nothing in the Name column, but choose
StudentID in the FieldName pick box. Go back up to the previous line, and
three fields will show at the bottom of the window. Set Unique to Yes, save
& exit. Access will now prevent you from adding duplicates, and will display
a message to the user.

Please note that the code above is tested but in many cases refers to other
objects. If you use different names for your queries, fields, tables, and
forms, you will need to adjust the code accordingly.

Hope that helps.
Sprinks


:

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

:

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

:

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




:

Hi, ivalum.

Please post the following properties:

Combo Box
---------------------
ControlSource
BoundColumn
RowSource
ColumnCount

Subform (Control)
----------------------
LinkMasterFields
LinkChildFields

Subform (Form)
-------------------
RecordSource



:

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...

 

[ivalum21]

Sprinks -

When I exited the form and came back in, the old records stay in there, so
previous dates appear first, but when I went into add new students into other
classes, those wouldn't appear until the VB code ran correctly. I made the
change to add bracekts around my subform name.

I believe everything is running correctly, I'm going to delete my fake data
and start using actual data we have available and see how it runs.

The next step is creating some reports displaying student attendance, class
lists and stuff like that.

Thank you very much for all of your help, I really appreciate it.

ivalum21

Hi, ivalum.

You have the right name. This problem is one reason most developers use
names that don't contain spaces--they make the delimiting brackets around
object names optional.

Me!MySubform.Requery & Me![MySubform].Requery are equivalent but
Me!My Subform.Requery won't work.

Change the line to:

Me![Attendance subform].Requery

Also, it sounds like the insert query is running, but because the requery
doesn't happen, it wouldn't show the new records. If you exit the form, and
come back to the same record, are the student records there?

Sprinks

Sprinks -

I'm having trouble getting the command button to work properly. I know
exactly what the problem is, I just don't know how to fix it.

When I put the VB code into my database, I used what I believe is the name
for my subform (when I have my subform selected, my properties window says
"Attendance subform", but when I try to put that into the VB code you have it
won't let me use a space. So then I tried to do "Attendance_subform", but
that is where my problem comes in.

When I click on my control button, it comes up and says its going to run my
append query, I click Yes, it tells me how many rows I'm going to append, I
click Yes, and then it tells me it can't find the Attendance_subform referred
to in my expression.

Do I have the wrong subform name? I don't think I do because when my
subform is selected, the name property under the Other tab says Attendance
subform.

Help?

ivalum21

Forms!FormName!FormControlName is the proper syntax for referring to a
control on an open form. The term Forms! refers to the collection of all
open forms.

The error message means that Access cannot find the control you're
specifying, and so is asking you to provide a value. Since you know the form
is open, this could only be because either:

- Your form is not named SessionDay
- Your control is not named SessionID

Hope that resolves it.
Sprinks

:

Sprinks -

I created a query called AppendAttendanceRecords and used the INSERT SQL
statement you wrote below (and I did notice the attendance spelling thing a
couple posts ago...got that fixed...). However, when I have the Attendance
form open, I then run the INSERT query and I get an Enter Parameter Value
window that pops saying "Forms!SessionDay!SessionID" and wants me to type in
a value. I thought the only thing that was wrong is that it should be "Form"
instead of "Forms", but that didn't work either. Any ideas??

ivalum21

Here is the code in my command buttons click event:

Private Sub Command5_Click()

Dim stDocName As String

stDocName = "AppendAttendanceRecords"
DoCmd.OpenQuery stDocName, acNormal
' Requery subform
Me!Attendance_subform.Requery

End Sub

Everything else is okay.

:

Ivalum,

Sorry you're having trouble, but I think you're almost there.

- Did you create a query by cutting & pasting the INSERT SQL statement into
the SQL View window, and save it by the name “AppendAttendanceRecords�
Check the spelling carefully, because I noticed you were spelling
“attendance†“attendence†in earlier posts.
- The command button code requeries the subform, and assumes that the name
of the subform control is “AttendanceQueryâ€. Change it to the name of your
control if not.
- If the form and subform are working manually, then I presume the query of
the subform is correct.
- Is your enrollment table called “Enrollments†and the attendance table
“Attendance� Check the spelling carefully.
- With the Attendance form open, with a SessionID and SessionDate entered,
try executing the AppendAttendanceRecords query from the query window. After
executing it, close the Attendance form, reopen it, and go the same record.
Did it insert the student records?

If none of these tests resolve your code, post the non-null data properties
and name of your subform, and the names of the subform and insert queries.

Sprinks




:

Sprinks -

Alright, I have finished everything you have listed here, and now the only
problem I'm having is with the VB code. To prevent any confusion I've made
my tables/queries/properties the same as your names, I can change them later
if need be. But I have taken your VB code and query and copied it into my
command button's click event exactly the way it appears in your message. The
query is coming up red thus meaning that it must be wrong somehow...any ideas?

I have tested the Attedance form and it works great when I put the
appropriate information manually into the tables. But without the VB code, I
can't have the data updated automatically.

Thank you Sprinks,
ivalum21

:

Hi, ivalum.

This took a little thought. It always helps me to think about the “thingsâ€
(tables) and the relationships.

In this case, there is really a new “thingâ€â€”i.e., an “instance†of one of
the sessions. Each SessionID will have 16 or so “instances†in a semester.
Each instance will have many students, and, in fact, the same students that
are associated with that session.

This relationship suggests the following tables:

SessionDays:
SessionDayID AutoNumber (PK)
SessionID Integer (FK to Sessions)
SessionDate Date/Time

Attendance:
AttendanceID AutoNumber (PK)
SessionDayID Integer (FK to SessionDays)
StudentID Integer (FK to Students)
Present Yes/No

To enter the instance, create a main form based on SessionDays that includes
a combo box for the SessionID (with the same properties as the one on your
enrollment form), the same textboxes displaying the day & time via the Column
property, and a textbox for the SessionDate.

The students are a little trickier. Since StudentName is not a field in
Attendance, you could use a combo box to display the student name based on
the StudentID in the Attendance table, but a combo box implies user choice,
and you really don’t want the user to change the name, but rather just check
off if he/she was there. So I suggest a continuous subform based on a query,
linked by the SessionDayID:

SELECT Attendance.AttendanceID, Attendance.SessionDayID,
Attendance.StudentID, Students.StudentName, Attendance.Present
FROM Attendance INNER JOIN Students ON Attendance.StudentID =
Students.StudentID;

Include a textbox for the StudentName and a checkbox for Present. Set the
StudentName’s Locked property to Yes (Data tab) and Tab Stop property (Other
tab) to No to prevent changes to and tabbing into the textbox.

To “preload†Attendance with records for all of the students for this
particular SessionDayID, create a command button to insert the records after
you’ve entered the main form record. The button will execute an Append query
and requery the subform to display the added records, ready for you to check
off the attendance.

Dim stDocName As String

stDocName = "AppendAttendanceRecords"
DoCmd.OpenQuery stDocName, acNormal
' Requery subform
Me!AttendanceQuery.Requery

' AppendAttendanceRecords Query SQL:

INSERT INTO Attendance ( SessionDayID, StudentID )
SELECT SessionDays.SessionDayID, Enrollments.StudentID
FROM SessionDays INNER JOIN Enrollments ON SessionDays.SessionID =
Enrollments.SessionID
WHERE (((SessionDays.SessionID)=[Forms]![SessionDays]![SessionID]));

To prevent adding duplicates by pressing the command button more than once,
add a multiple field index to the Attendance table.

Open Attendance in Design View, and choose View, Indexes. Leave the indexes
that Access has already created for you. On a new line at the bottom, give
the index a name, such as SingleDayStudent, and choose the SessionDayID from
the pick box. On the next line, enter nothing in the Name column, but choose
StudentID in the FieldName pick box. Go back up to the previous line, and
three fields will show at the bottom of the window. Set Unique to Yes, save
& exit. Access will now prevent you from adding duplicates, and will display
a message to the user.

Please note that the code above is tested but in many cases refers to other
objects. If you use different names for your queries, fields, tables, and
forms, you will need to adjust the code accordingly.

Hope that helps.
Sprinks


:

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

:

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

:

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




:

Hi, ivalum.

Please post the following properties:

Combo Box
---------------------
ControlSource
BoundColumn
RowSource
ColumnCount

 

[Sprinks]

Congratulations, and my pleasure.

Sprinks

Sprinks -

When I exited the form and came back in, the old records stay in there, so
previous dates appear first, but when I went into add new students into other
classes, those wouldn't appear until the VB code ran correctly. I made the
change to add bracekts around my subform name.

I believe everything is running correctly, I'm going to delete my fake data
and start using actual data we have available and see how it runs.

The next step is creating some reports displaying student attendance, class
lists and stuff like that.

Thank you very much for all of your help, I really appreciate it.

ivalum21

Hi, ivalum.

You have the right name. This problem is one reason most developers use
names that don't contain spaces--they make the delimiting brackets around
object names optional.

Me!MySubform.Requery & Me![MySubform].Requery are equivalent but
Me!My Subform.Requery won't work.

Change the line to:

Me![Attendance subform].Requery

Also, it sounds like the insert query is running, but because the requery
doesn't happen, it wouldn't show the new records. If you exit the form, and
come back to the same record, are the student records there?

Sprinks

Sprinks -

I'm having trouble getting the command button to work properly. I know
exactly what the problem is, I just don't know how to fix it.

When I put the VB code into my database, I used what I believe is the name
for my subform (when I have my subform selected, my properties window says
"Attendance subform", but when I try to put that into the VB code you have it
won't let me use a space. So then I tried to do "Attendance_subform", but
that is where my problem comes in.

When I click on my control button, it comes up and says its going to run my
append query, I click Yes, it tells me how many rows I'm going to append, I
click Yes, and then it tells me it can't find the Attendance_subform referred
to in my expression.

Do I have the wrong subform name? I don't think I do because when my
subform is selected, the name property under the Other tab says Attendance
subform.

Help?

ivalum21

:

Forms!FormName!FormControlName is the proper syntax for referring to a
control on an open form. The term Forms! refers to the collection of all
open forms.

The error message means that Access cannot find the control you're
specifying, and so is asking you to provide a value. Since you know the form
is open, this could only be because either:

- Your form is not named SessionDay
- Your control is not named SessionID

Hope that resolves it.
Sprinks

:

Sprinks -

I created a query called AppendAttendanceRecords and used the INSERT SQL
statement you wrote below (and I did notice the attendance spelling thing a
couple posts ago...got that fixed...). However, when I have the Attendance
form open, I then run the INSERT query and I get an Enter Parameter Value
window that pops saying "Forms!SessionDay!SessionID" and wants me to type in
a value. I thought the only thing that was wrong is that it should be "Form"
instead of "Forms", but that didn't work either. Any ideas??

ivalum21

Here is the code in my command buttons click event:

Private Sub Command5_Click()

Dim stDocName As String

stDocName = "AppendAttendanceRecords"
DoCmd.OpenQuery stDocName, acNormal
' Requery subform
Me!Attendance_subform.Requery

End Sub

Everything else is okay.

:

Ivalum,

Sorry you're having trouble, but I think you're almost there.

- Did you create a query by cutting & pasting the INSERT SQL statement into
the SQL View window, and save it by the name “AppendAttendanceRecords�
Check the spelling carefully, because I noticed you were spelling
“attendance†“attendence†in earlier posts.
- The command button code requeries the subform, and assumes that the name
of the subform control is “AttendanceQueryâ€. Change it to the name of your
control if not.
- If the form and subform are working manually, then I presume the query of
the subform is correct.
- Is your enrollment table called “Enrollments†and the attendance table
“Attendance� Check the spelling carefully.
- With the Attendance form open, with a SessionID and SessionDate entered,
try executing the AppendAttendanceRecords query from the query window. After
executing it, close the Attendance form, reopen it, and go the same record.
Did it insert the student records?

If none of these tests resolve your code, post the non-null data properties
and name of your subform, and the names of the subform and insert queries.

Sprinks




:

Sprinks -

Alright, I have finished everything you have listed here, and now the only
problem I'm having is with the VB code. To prevent any confusion I've made
my tables/queries/properties the same as your names, I can change them later
if need be. But I have taken your VB code and query and copied it into my
command button's click event exactly the way it appears in your message. The
query is coming up red thus meaning that it must be wrong somehow...any ideas?

I have tested the Attedance form and it works great when I put the
appropriate information manually into the tables. But without the VB code, I
can't have the data updated automatically.

Thank you Sprinks,
ivalum21

:

Hi, ivalum.

This took a little thought. It always helps me to think about the “thingsâ€
(tables) and the relationships.

In this case, there is really a new “thingâ€â€”i.e., an “instance†of one of
the sessions. Each SessionID will have 16 or so “instances†in a semester.
Each instance will have many students, and, in fact, the same students that
are associated with that session.

This relationship suggests the following tables:

SessionDays:
SessionDayID AutoNumber (PK)
SessionID Integer (FK to Sessions)
SessionDate Date/Time

Attendance:
AttendanceID AutoNumber (PK)
SessionDayID Integer (FK to SessionDays)
StudentID Integer (FK to Students)
Present Yes/No

To enter the instance, create a main form based on SessionDays that includes
a combo box for the SessionID (with the same properties as the one on your
enrollment form), the same textboxes displaying the day & time via the Column
property, and a textbox for the SessionDate.

The students are a little trickier. Since StudentName is not a field in
Attendance, you could use a combo box to display the student name based on
the StudentID in the Attendance table, but a combo box implies user choice,
and you really don’t want the user to change the name, but rather just check
off if he/she was there. So I suggest a continuous subform based on a query,
linked by the SessionDayID:

SELECT Attendance.AttendanceID, Attendance.SessionDayID,
Attendance.StudentID, Students.StudentName, Attendance.Present
FROM Attendance INNER JOIN Students ON Attendance.StudentID =
Students.StudentID;

Include a textbox for the StudentName and a checkbox for Present. Set the
StudentName’s Locked property to Yes (Data tab) and Tab Stop property (Other
tab) to No to prevent changes to and tabbing into the textbox.

To “preload†Attendance with records for all of the students for this
particular SessionDayID, create a command button to insert the records after
you’ve entered the main form record. The button will execute an Append query
and requery the subform to display the added records, ready for you to check
off the attendance.

Dim stDocName As String

stDocName = "AppendAttendanceRecords"
DoCmd.OpenQuery stDocName, acNormal
' Requery subform
Me!AttendanceQuery.Requery

' AppendAttendanceRecords Query SQL:

INSERT INTO Attendance ( SessionDayID, StudentID )
SELECT SessionDays.SessionDayID, Enrollments.StudentID
FROM SessionDays INNER JOIN Enrollments ON SessionDays.SessionID =
Enrollments.SessionID
WHERE (((SessionDays.SessionID)=[Forms]![SessionDays]![SessionID]));

To prevent adding duplicates by pressing the command button more than once,
add a multiple field index to the Attendance table.

Open Attendance in Design View, and choose View, Indexes. Leave the indexes
that Access has already created for you. On a new line at the bottom, give
the index a name, such as SingleDayStudent, and choose the SessionDayID from
the pick box. On the next line, enter nothing in the Name column, but choose
StudentID in the FieldName pick box. Go back up to the previous line, and
three fields will show at the bottom of the window. Set Unique to Yes, save
& exit. Access will now prevent you from adding duplicates, and will display
a message to the user.

Please note that the code above is tested but in many cases refers to other
objects. If you use different names for your queries, fields, tables, and
forms, you will need to adjust the code accordingly.

Hope that helps.
Sprinks


:

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

:

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

:

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)
-------------------

 

[Hend]

hi
I was looking for that and fainaly i found it but

I tride to do as you side but nothing happend can you tell me once more step
by step


thank you

 

[Spier2vb]

hi

I was looking for that and fainaly i found it but

I tride to do as you side but appear this message to me >

Microsoft office access can’t append the records in the append query.
Microsoft office access set 0 field(s) to Null due to a type conversion
failure, and it didn’t add 48 record(s) to the table due to key violation, 0
record(s) due to lock violation, and 0 record(s) due to validation rule
violations.
Do you want to run the action query anyway?
To ignore the error(s) and run the query, click Yes.
For an explanation of the causes of the violations, click help.