Activity entry form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a DB "School.mdb" with three tables:
Students: Student_key, Last, First....
StudentsActitities: Student_key, Activity
Activity: Activity_Name

I have ben working on this for quite a while and can't resolve my problems.
I would like to have a form where the user can select an Activity (from the
"Activity" table) and when the activity is selected the student names for
that activity will be displayed. I then want to be able to add and delete
students ("Students" table) from the selected activity. The link is the
"StudentsActivities" table.

I have tried subreports, subforms, listboxes, etc.. Everything I've tried
leads to one problem or another.

Some ideas would be greatly appreciated.


Phil
 
Zip the .MDB file and email it to me and I will show you how.
FIREBALL-XL5 AT BIGFOOT DOT COM
 
Hi,

I have a DB "School.mdb" with three tables:
Students: Student_key, Last, First....
StudentsActitities: Student_key, Activity
Activity: Activity_Name

I have ben working on this for quite a while and can't resolve my problems.
I would like to have a form where the user can select an Activity (from the
"Activity" table) and when the activity is selected the student names for
that activity will be displayed. I then want to be able to add and delete
students ("Students" table) from the selected activity. The link is the
"StudentsActivities" table.

I have tried subreports, subforms, listboxes, etc.. Everything I've tried
leads to one problem or another.
Click to expand...

A Subform should do the trick, with a tweak or two.

Create a Form based on Activity. On this Form, create a Subform based
on StudentsActivities; use Activity_Name as the Master Link Field, and
Activity as the Child Link Field.

On the Subform, put a Combo Box control. The RowSource of this combo
might be

SELECT Student_Key, [Last] & ", " & [First], <other identifying
fields>
FROM Students
ORDER BY [Last], [First];

(Since names are not necessarily unique you may want to add some other
fields to distinguish Jane Brown the 3rd grade blonde from Jane Brown
the 5th grade brunet <g>).

Use Student_Key as the Control Source to store the selected student's
key value into StudentsActivities. If you set the Combo's FieldWidths
property to

0;1.25;<suitable widths>

you'll see the student name (like "Brown, Jane") in the combo, but the
computer will store the ID.

If you've tried this and are having problems, please post back
describing just what you've done and what problems you're having.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Thanks John,

I Did as you stated, however I created two subforms. One subform based on
StudentsActivities and with the combo box for adding names. The second
subform to display the students with a record source of:

SELECT StudentsActivities.Student_Key, StudentsActivities.Activity,
Students.Student_Key, Students.Last, Students.First, Students.Grade
FROM Students INNER JOIN StudentsActivities ON Students.Student_Key =
StudentsActivities.Student_Key;

After trying many thing I'm close to working except for one problem. When I
click anywhere in any of the forms I get the message:
"You cannot add or change a record because a related record is required in
table 'Student'"

If I close the message and click on the combo box and select a name the name
gets added but when I exit Access the DB is locked up and I have to do a
cntrl-alt-del and End-Task to free it up.

Any Ideal?
Thanks,

John Vinson said:
Hi,

I have a DB "School.mdb" with three tables:
Students: Student_key, Last, First....
StudentsActitities: Student_key, Activity
Activity: Activity_Name

I have ben working on this for quite a while and can't resolve my problems.
I would like to have a form where the user can select an Activity (from the
"Activity" table) and when the activity is selected the student names for
that activity will be displayed. I then want to be able to add and delete
students ("Students" table) from the selected activity. The link is the
"StudentsActivities" table.

I have tried subreports, subforms, listboxes, etc.. Everything I've tried
leads to one problem or another.
Click to expand...

A Subform should do the trick, with a tweak or two.

Create a Form based on Activity. On this Form, create a Subform based
on StudentsActivities; use Activity_Name as the Master Link Field, and
Activity as the Child Link Field.

On the Subform, put a Combo Box control. The RowSource of this combo
might be

SELECT Student_Key, [Last] & ", " & [First], <other identifying
fields>
FROM Students
ORDER BY [Last], [First];

(Since names are not necessarily unique you may want to add some other
fields to distinguish Jane Brown the 3rd grade blonde from Jane Brown
the 5th grade brunet <g>).

Use Student_Key as the Control Source to store the selected student's
key value into StudentsActivities. If you set the Combo's FieldWidths
property to

0;1.25;<suitable widths>

you'll see the student name (like "Brown, Jane") in the combo, but the
computer will store the ID.

If you've tried this and are having problems, please post back
describing just what you've done and what problems you're having.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
Back
Top