Unanswered: Excel-like data entry form for Access 2003
I am trying to convert an excel spreadsheet for staff training records into an Access 2003 database. I have set up the database into 4 tables: Staff, Tasks, Trainers, and TrainingRecords. The training records table has the following fields: RecordID (Autonumber and PK), StaffID (Linked to Staff), TaskID (Linked to tasks), DateCompleted, and TrainerID (Linked to Trainers). I have created all the relevant forms for entering data into the Staff, Tasks, and Trainers Tables. I have also created a form which allows you to enter a new training record, by picking staff name, task name and trainer name from combo boxes.
My problem is that my boss wants the data entry for the training records to look more like the excel spreadsheet, which displays a complete list of all the available tasks, and then the user can fill in the date completed and trainer name for all the relevant items at once, often leaving mulitiple tasks without a created date and trainer (as the staff member hasn't been trained on that particular task yet). I'm not really sure how to start going about this - I had the following ideas but don't know how feasable they are:
1) When the form loads, add checkboxes for all the available tasks and lables from the task name. The user would then click whichever tasks they wanted to add, and specify the same date and trainer name for all the tasks they have ticked.
2) Use the office excel spreadsheet control (10 or 11) to allow data entry and then write some code to interperate the information and create/amend the TrainingRecords table as required. This option should be fairly straight forward for me, as I have done alot of Excel VBA programming. However, I can't figure out how to get access to the Range/Cells object within the spreadsheet to read/write data to it. I'm guessing that this control is not really meant to be used in this way.
3) Create a temporary table (that would be viewed via a subform), this would get populated with a list of all tasks and if the staff member had been trained on them when the user has selected a staff name from a drop down box on the main form. If the user selects a different Staff Member from the drop-down box, the form would refresh and show the data for that staff member.
I am open to any other ideas/suggestions that are more straightforward than the ideas I have had here.