Autofill form and allow to edit

[bluenana]

Hi professionals of the forum, I'm a newbie of Access. Please help me how to do the following task:
I have 2 tables StudySchedule and ExamSchedule.StudySchedule contains the detail information of opening classes of many different periods. I need to create a form that can:
1. let the user choose a period
2. form will generates automatically exam schedules of the opening classes of the chosen using the relevant information in StudySchedule. Those entries will be saved in ExamSchedule.
3. Note that there are 8 fields of the exam schedules generated/counted from some fields of StudySchedule. The 3 other fields of the exam schedules need to be entered by the users. User must also have the ability to edit the generated fields.
Please help me.

 

[Cronk]

What is the structure of the two tables and how do they relate? Maybe you could provide a picture of the Access relationships screen.

 

[Mihail]

Well come to the forum !

While you don't show us what you have (the actual DB structure) we can't try to answer to your questions.

 

[bluenana]

Hi all, in the previous post, I forget to mention one table. That means I have all 3 related table as follows:
StudySchedule (Period, SubjectID, ClassID, StartWeek, EndWeek, Day, StartSection, EndSection, MajorID)
Registration (Period, SubjectID, ClassID, StudentID, Group, RegisterDate)
ExamSchedule (Period, SubjectID, ClassID, Group, ExamDate, StartSection, CountOfStudents, Room, SubmitResultDate, Examiner)
E.g.:

• StudySchedule (#19/08/2013#, '054001', 'ISS01', 1, 16, 'Monday', 3, 6, 'CS') : the class ISS01 of the subject 054001 in the period 19/08/2013 is taught in every Monday from section 3 to 6. The class is opened from the week 1 to week 16.
• Registration (#19/08/2013#, '054001', 'ISS01', '5050001', 1, #01/08/2013#): The student with ID 5050001 registered the subject 054001 opened in the period of #19/08/2013# on 01/08/2013 and then he was arranged to the class ISS01, group 1. (Note that, one class is divided into some groups).

I need to create a form that will generate rows of ExamSchedule from the 2 other tables. Because the number of students of a class may be larger than the capacity of a room, so we have to arrange the schedule for each group. So the procedure as follows:

1. In the form, user chooses a period from a combo box including the period list having opening classes.
2. For each group of opening classes of the selected period, a row is generated with the format of ExamSchedule. Some fields of the rows are counted from the information of StudySchedule and Registration. I write a SQL for generating for you to imagine easily:

SELECT R.Period, R.SubjectID, R.ClassID, R.Group,
DateAdd("d",7*17+[S.Day]-2,[R.Period]) AS ExamDate,
"Section " & [S.StartSection] AS StartSection,
Count(R.StudentID) AS CountOfStudents,
DateAdd("d",7*2+[S.EndWeek],[ExamDate]) AS SubmitResultDate
FROM StudySchedule S, Registration R
WHERE S.Period = R.Period AND S.SubjectID = R.SubjectID AND S.ClassID = R.ClassID
AND S.Period = selectedPeriod
GROUP BY R.Period, R.SubjectID, R.ClassID, R.Group, S.EndWeek, S.Day, S.StartSection

3. On the generated, user can edit the attributes which have been generated automatically if he wants. User also can fill value for blank attributes (ones have not been counted from the 2 other tables).

I tried to describe details. Please help me do the above task. Thank you so much
​

 

[Cronk]

Waiting.....