Subforms

[Lisae3]

I am trying to create a form that enables me to record attendance at
activities. I have successfully created a subform that is linked to the main
form through a field Student ID, although this only works through a subform
based on a table whose field are the activities. However I would like the
fields in the subform to come from a a field (Activity Name) in another table
(Activities Table). I have tried setting up a query and simply building the
subform from the table however the list of activities in the Activities Table
do not show as fields in the subform. Further to this I would like to be
able from to choose a different list of activities based on the period in
which it was held - Which would be chosen from a drop down list in the form.
I have absolutely no idea how to do this so any help would be greatly
appreciated.

Thanks

Lisa

 

[Jeanette Cunningham]

Hi Lisa,
the critical part of getting subforms to work is the way you have set up the
tables in your database.

If you can post the relevant table names, along with the Primary key and the
foreign key and which table it is related to which, and whether it is
one-to-many or many-to-many with a junction or xref table, we may be able to
help.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Lisae3]

Hi Jeanette

The tables are as follows (Fields Below):

Activity Details:
Activity ID (Autonumber)
Activity Name (Text)
Provider ID (Number)
Year(Text - Lookup)
Period (Text - Lookup)
Monday (Yes/No)
Tuesday (Yes/No)
Wednesday (Yes/No)
Thursday (Yes/No)
Friday (Yes/No)
Saturday (Yes/No)
Holiday Provision (Yes/No)
Lunch Time (Yes/No)
3:15 - 5:15 (Yes/No)
5:30 - 7:30 (Yes/No)
Other Time (Text)
Duration (Text)
Room (Text)
Capacity (Text)
Cost Per Session (Currency)
Cost Per Term (Currency)

Dates:

Week Commencing (Date/Time)

Emergency Contact Details:

Student ID (Autonumber)
First Name (Text)
Surname (Text)
Emergency Contact Name 1 (Text)
Emergency Contact Name 2 (Text)
Emergency Telephone 1 (Text)
Emergency Telephone 2 (Text)
Emergency Telephone 3 (Text)
Medical (Memo)
Medication (Memo)
Consent Form (Yes/No)

Provider Details:

Provider ID (Autonumber)
First Name (Text)
Surname (Text)
Company (Text)
Address 1 (Text)
Address 2 (Text)
Address 3 (Text)
Postcode (Text)
Telephone (Text)
Mobile (Text)
Email (Text)
Additional Staff Details (Memo)
CRB Check (Yes/No)
Insured (Yes/No)

Pupil Details:

Student ID (Autonumber)
First Name (Text)
Surname (Text)
Address 1 (Text)
Address 2 (Text)
Address 3 (Text)
Postcode (Text)
Year (Text)
Group (Text)
Date of Birth (Text)

Register:

Student ID (Number)
Week Commencing (Text Lookup from Table (Date))
Activity Name (Text)

I have two queries that combine information from two tables - The first is
provider and activity details (Combining info from the same named tables) and
the second is Student and emergency Contact details (Combining info from the
same named tables).

I want the register form to have Student ID, First Name, Surname, Date of
Birth, Year, Group - From Pupil Details. Then a subform (Linked through the
student ID) to list the different activities as columns and week commencing
(Selection box) as a row where the cells are yes/no options, however the
subform must be able to change based on the period in which the activity is
in. Therefore I would like a box in which I can select which records (Period
and Year) I want to change.

Hope this makes sense.

Many Thanks

Lisa

 

[Jeanette Cunningham]

Lisa,
I would like to make a suggestion about the tables to use for this database.
Tables as I describe below would make it much easier and even possible to
track the activities offered and who has enrolled for them.

One activity may be offered multiple times (over different semesters, or
possibly simultaneous streams.)

A provided may be the provider for only part of a year (e.g. due to
illness), or in some cases you may end up with more than one activity
instance in a year or semester (e.g. Activity run twice, or dual streams).

As well as a table for Students, you need 2 separate tables for the Activity
(the activity that is offered periodically) and the ActivityInstance (the
particular one offered in period 3 or Semester 2 of 2009 in room 4 by
teacher 99.)

You will then need another table for the students enrolled in that
ActivityInstance.

This ActivityInstance table should have different fields
for:
TheYear
TheSemester
ThePeriod
ActivityID which activity this is
and so on.


Once you have that, you can create a query based on tables Student,
Activity, ActivityInstance, and Teacher, and get at the fields for the year,
semester, period, room, and teacher for each student.

You could have a table for providers.

A sample database to play with coud be helpful here and there is one at

http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=232

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia