Timetable data-entry form

[gilave]

I have structured a table to record timetable data for classes with the following fields:

GroupID
DayNo
PeriodNo
SubjectID
InstructorID
RoomID

The DayNo ranges from 1 to 5, and each day has 9 periods represented in PeriodNo. There will be 45 records for a given GroupID.

I would like to have the period numbers across the top of the form and the day numbers down the left of the form. What to enter at the intersection of row (day) and column (period) are the other fields of SubjectID, InstructorID, and RoomID.

I am doing this for our timetabling officer who is currently using a DOS based application that presents the timetable on screen as I've described above.

I have created an unbound form with unbound text boxes and populate the controls through code when the form opens. It works but the form is very slow (may because I've used combo boxes).

Anyone out there have a better approach.

 

[SQLSister]

What database backend are you using and what front end? How did you populate the combo boxes - through the database code or through the front end code? Do you have proper indexes on the tables accessed? Are you getting the values from a lookup tables or filtering them from other records like previous schedules?

 

[gilave]

I am using MS Access 2000.

I have 2 tables with exactly the same structure, named GroupTimetable and MasterTimetable with fields as described above. The fields i.e. GroupID, DayNo, PeriodNo, SubjectID, InstructorID, and RoomID are all foreign keys to 6 parent tables named Groups, Days, Periods, Subjects, Instructors, and Rooms respectively.

The GroupTimetable table is a temporary table that has 45 records for a given GroupID. The records represent 9 periods for a given day. Periods are numbered from 1 to 9. There are 5 days (Mon-Fri) represented as day numbers from 1 to 5. So that's why I have 5 days * 9 periods = 45 records.

The MasterTimetable is updated from the GroupTimetable. I use the MasterTimetable to populate the GroupTimetable. The logic of this is; The user is presented with a dialog box to specify a GroupID. After update of this GroupID I check the MasterTimetable if records exist for this group and populate the GroupTimetable. If not then the GroupTimetable is updated with null values. The idea is whether a timetable exists or not I want to display a timetable form and allow the user to edit where necessary.

I believe the structure of my tables are ok and my strategy above seems ok by me. All I want is to display the form like in a crosstab query. The user is already used to a similar screen presentation in a DOS application.

I created 9 labels across the top in the form header with captions showing each of the period numbers 1-9. In the detail section of the form I created 5 labels down the left with captions showing the 5 days (Mon-Fri). Then at each intersection of Day and period I have 3 textboxes for SubjectID, RoomID, and InstructorID for editing. The textboxes are named like SubjectID11, RoomID11, InstructorID11 for Day 1 period 1 and SubjectID12, RoomID12, InstructorID12 for Day 1 Period2 and so on for all days and period combination. I cannot create bound textboxes and display the data like in the way I've designed my form, so I am using unbound textboxes. (The form itself is also unbound). The form is opened through code after the user specifies a GroupID as described above. The GroupTimetable is accessed through recordset and each unbound textbox on the form is set to the appropriate values from the GroupTimetable.

After editing, the MasterTimetable is updated from the GroupTimetable.

I do not have problems with this strategy. I just want comments and suggestions as to whether there is a better way to do it. The bottom line is the user must be presented with a form that would have rows and columns to enter or edit timetable information.

I trust I have explained my problem.