Excel-like data entry form for access 2003 database

Question

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.

Answer 1

Forms in Access can be configured to be Datasheet view forms, which looks similar to Excel. However, I don't think using a Datasheet Form will solve all the problems you're encountering here.

You might be best off doing something like you suggested in #1. The part where you populate the checkboxes would need to be unbound and you'll have to write code populate the checkboxes and to insert/update/delete the records according to how the boxes are checked. You cannot use unbound fields in Datasheet form or in a Continuous form.

Option #3 would also work. Or alternately you could bind your form to a fabricated ADO recordset. Once again, you'll have to write code to populate the ADO recordset and you'll have to write more code to push all additions/edits/deletions in the ADO recordset back to you data source.

I would stay far away from Option #2. I can't think of very many reasons to use or embed Excel within an MS Access application (Excel's charts maybe being one of the only reasons).

Another thing I want to warn you about is letting your boss dictate how things should look and feel. Of course, the boss is going to have his say, but his input should ideally be left to saying that something is too clumsy, too unintuitive, requires too many clicks, requires too much training to use, etc. I've allowed users to dictate specifics on the looks and behavior of applications and the end result is not good. In other cases where I chose the very best way of implementing a certain feature and designed a user interface for it according to my own knowledge, the outcome has been far better than the cases where I allowed the end users to specify which controls they want, where they go, what data they hold, how they behave, etc. Experienced application developers should have a knowledge about the design environment that trumps that of the end users and allows you to provide them with something that meets or exceeds their expectations. Of course, don't close out feedback from your boss or your end users, but do try to find out why they want something different than they have. It's best not to let the users' old habits and customs dictate what the user interface will look like. You can never make progress that way. Today's applications should have modern, user-friendly user interfaces and you can't get that if you build your application to suit people whose ideas are stuck in the 90's or merely stuck in some other, inferior paradigm.