Subform - is this possible?

[DebbieG]

I don't know how to accomplish what I want. I want a Main form that shows a
particular class. Then in a subform I want to list all possible students
(there won't be many) and then choose whether they attended, were absent, or
were excused. I put the subform on the main form (unbound). Then I put a
combo box on the subform for each record. I thought I could choose one of
the options and then grab the ID of the class and append the record to the
appropriate table. But when I choose an option it changes for all the
students.



My Main form is based on a query that has the following fields from these
tables;



Events table

------------

EventID

EventDate

EventTypeCD

EventCD



EventTypeCD table

--------------------

EventType



EventCD table

---------------

EventNM





The Join properties are to show all records from the Events table



My subform is based on a query that has the following fields from these
tables:



Students table

--------------

SSN

Name



EventsStudents table

---------------------

EventStatusCD (P = Present, A = Absent, E = Excused)

EventID



Joined on SSN field. The Join properties are to show all records from the
Students table.



I have the Main form and the subform linked on the EventID.



Right now, it displays only the students who are in the EventsStudents table
(which makes sense). And I can't figure out an easy way to add more
students. What I want: when I select a class, I want it to display ALL the
students -- some will already have an EventStatusCD for that particular
class and some won't (these students won't be the EventsStudents table yet).
I would want to use a combo box to select the EventStatusCD.



Is this making any sense? Or am I going about this the wrong way?



Debbie

 

[Steve Schapel]

Debbie,

Why is the subform unbound? It seems to me it should be bound to the
EventsStudents table (which I presume has a field to identify the
Student), and then the subfrom should be linked to the main form via the
EventID field from both.

I suppose the overall concept is that there is a EventsStudents record
for every possible combination of Event and Student. From this point of
view, you could set things up so that every time a new Event is added,
an Append Query is run to add a record for this event for each Student
to the EventsStudents table. And similarly, every time a new Student is
added, run an Append query to add a record for this student for all
existing events to the EventsStudents table. That way, the records will
already be there on the subform whenever you access the record for any
Event, and all you then need to do is enter the EventStatus as required.

 

[DebbieG]

Steve,

Thanks for your response. I was trying unbound because if it was bound I
would only see the students that already had an EventStatusCD for that
EventID.

These classes are on certain dates and I wouldn't need to add students for
past classes. And wouldn't that table could get huge over time by adding
records that I would never use.

Would it make sense to have the Main form show a student and the subform be
the list of classes? But then I'd have the same problem. It would should
the classes that a student has an EventStatusCD for.

Have I set up the design of the tables wrong?

It's hard for me to believe there isn't an easy way to indicate that a
student has attended a certain class. I would think schools would need a
way to enroll a student into classes.

Again, thanks for your help. I am getting very frustrated.

Debbie


Debbie,

Why is the subform unbound? It seems to me it should be bound to the
EventsStudents table (which I presume has a field to identify the
Student), and then the subfrom should be linked to the main form via the
EventID field from both.

I suppose the overall concept is that there is a EventsStudents record
for every possible combination of Event and Student. From this point of
view, you could set things up so that every time a new Event is added,
an Append Query is run to add a record for this event for each Student
to the EventsStudents table. And similarly, every time a new Student is
added, run an Append query to add a record for this student for all
existing events to the EventsStudents table. That way, the records will
already be there on the subform whenever you access the record for any
Event, and all you then need to do is enter the EventStatus as required.

 

[Steve Schapel]

Debbie,

If you have a form based on the Events table, and a subform based on the
EventsStudents table, yes it will only show students already enrolled
in this event (or otherwise marked as absent, excused, or whatever), but
then there is no problem adding new students. You just, well, add new
records to the subform. I am not really sure what the problem is. I am
assuming the subform will be continuous view. As mentioned earlier, I
am also assuming you made a mistake in your original post, and that the
EventsStudents table also includes a field to identify the Student
(presumably the SSN... although using a SSN as an identifier is
generally regarded as not a good idea, but that's another story!) The
easiest way to do it, then, would be to have a combobox on the subform
bound to the SSN field, with the combobox's Row Source set to the
Students table, set up so you can see the students' names in the
combobox drop-down list, and use this combobx to enter new students in
the Event.

 

[DebbieG]

Steve,

YEA! Putting a combobox on the subform for the SSN worked. It added the
record!!!

Since you were so helpful ... Yes, the subform is a continuous view. And,
yes, the EventsStudents table includes the SSN. Is there a way for the
combobox to appear just in the new record, not for the existing records? I
don't want the user to change the student on the existing record. If they
enter the wrong student, I would rather they delete the record and then add
the correct record. Does that make sense? Am I asking for the impossible?

Thanks so much!
Debbie


Debbie,

If you have a form based on the Events table, and a subform based on the
EventsStudents table, yes it will only show students already enrolled
in this event (or otherwise marked as absent, excused, or whatever), but
then there is no problem adding new students. You just, well, add new
records to the subform. I am not really sure what the problem is. I am
assuming the subform will be continuous view. As mentioned earlier, I
am also assuming you made a mistake in your original post, and that the
EventsStudents table also includes a field to identify the Student
(presumably the SSN... although using a SSN as an identifier is
generally regarded as not a good idea, but that's another story!) The
easiest way to do it, then, would be to have a combobox on the subform
bound to the SSN field, with the combobox's Row Source set to the
Students table, set up so you can see the students' names in the
combobox drop-down list, and use this combobx to enter new students in
the Event.

 

[Steve Schapel]

Debbie,

Would it achieve the desired result if you set the Allow Edits property
of the subform to No?