Code to Capture Multiple Groups of Control Values and Insert Mutliple Records

[cooleddie]

Hello,
I have been tasked to create an Employee Skills Evaluation DB and need a little help from you folks that know what you are doing! In my career I have always gotten the job of maintaining / editing applications that have already been created by someone else with a much higher skillset than I have so doing this from scratch is a little tougher to me. I will give the background on the project.

My department has created a skills assessment questionnaire in a spreadsheet that contains over 120 questions spread across four categories. They would like for me to create an Access front end for data entry and of course a DB to store the assessments. Each employee (approximately 40) will be evaluated twice a year. I have determined that it is best practice to try to normalize the DB and not go the easy route with a flat table and over 120 Question fields. I have created the following tables: Employee, Skill Detail, EmpSkills (Junction Table), and Assessments.

My big issue is trying to figure out how to create an appropriate front end that will allow the Evaluator to enter make the Skill Rating selection and then submit. Each question can have only one of four possible answers: Proficient, Familiar, Needs Help, and Not applicable. Each entry will also have a Comments section.

Due to the structure of the database, I will have a record for each answer that will contain the Skill, Rating, Comments, along with the Employee ID. I've started out trying to use a Tab Form as the basis for the design so each of the Four Categories will have its own Tab. Each Tab will have its own Submit button to Insert the records into the Assessments table. My question is how do create the code that will grab say the Skill (Label), Rating (Either a Radio Button or Drop Down), Comments (Text Box) for the first Skill, Insert it as a Record, and then move to the next group of controls to do the same for the next Skill. This will iterate down through all of the skills on that Tab. My confusion is on how to either Group the Controls somehow to form the Record and then go to the Next group of controls for the next Record. I read where I might be able to use the "Tag" attribute on the controls to group together, but not sure if it will work for this.

I have pasted a copy of a rough layout of the form to help give a general idea. I am looking at using either Radio Buttons for the Rating selections or a Drop Down selection so I have both on the front of this Tab just to see how they look. I think I may use the Drop Down just because it might be easier. I welcome any suggestions on that, also. Thank you for your time and assistance!

[Dal Jeanis]

Generally, my preference is to use the natural capabilities of Access, rather than to have the user clicking a "submit" button for each record.

The first question I'd ask is one about usage. Will all 120 questions be answered every time, or is there a common use case where an Evaluator is entering only a few ratings? Might there be different evaluators for different skill sets for the same employee?

The reason I ask is this - if the standard will be to have all 120 entered at one time, then I'd suggest having the initial entry into a temp table, with a single "submit" or "commit" at the end to copy them to the production table.

So, after selecting the employee and setting the date (and, of course, logging in so that the person doing the Evaluation and/or data entry is identified in the database), the form would create 120 records in the temp table for that employee, date, and evaluator. Each record would be flagged as "Unverified" or "Not Entered" - which might be a different value stored in the same field as "not applicable".

The Evaluator or Data Entry person could then use a standard continuous form to update the skill records, and no "submit" button would be required. Moving from field to field or record to record would automatically save the records.
When entry was completed, the user could press a button labeled "verify" and the form would detect any records that had not yet been entered. (For example, a Dcount of the "unverified" records, and if greater than zero, apply a filter to show only the unverified records.

This strategy can also be useful to allow double-entry, if your users are not known for accuracy. The same 120 could be entered a second time into the temp table, and the two batches compared to determine whether there are any miskeys.

[cooleddie]

Thank you for your reply, Dal Jeanis. I will try to answer your questions as best I can.

Generally, my preference is to use the natural capabilities of Access, rather than to have the user clicking a "submit" button for each record.
I would like to do that, but I guess I just don't know how to do it given the multiple record situation. The user would not click Submit for each record, but for the entire collection of records on that Tab. So for instance, in the "Safety" Tab, there might be 25 Skills under "General", each one with a rating and comment. Each one of those collections would be an individual record in the Assessments table of the database. That table would look something like: AutoID, EmployeeID, Date, SkillID, Rating, Comments. I don't necessarily have to have a Submit button on each Tab, but I thought it might help the Evaluator to break it down and give them that capability on each one so that they could complete a section and Submit.

The first question I'd ask is one about usage. Will all 120 questions be answered every time, or is there a common use case where an Evaluator is entering only a few ratings? Might there be different evaluators for different skill sets for the same employee? There will be multiple Evaluators, but they will only perform the assessment for their employees so a Supervisor in the Eastern District would complete a full 120 question assessment for his/her employees and so on. The desire is to have them answer all 120 questions every time.

The reason I ask is this - if the standard will be to have all 120 entered at one time, then I'd suggest having the initial entry into a temp table, with a single "submit" or "commit" at the end to copy them to the production table.

So, after selecting the employee and setting the date (and, of course, logging in so that the person doing the Evaluation and/or data entry is identified in the database), the form would create 120 records in the temp table for that employee, date, and evaluator. Each record would be flagged as "Unverified" or "Not Entered" - which might be a different value stored in the same field as "not applicable".

The Evaluator or Data Entry person could then use a standard continuous form to update the skill records, and no "submit" button would be required. Moving from field to field or record to record would automatically save the records.
When entry was completed, the user could press a button labeled "verify" and the form would detect any records that had not yet been entered. (For example, a Dcount of the "unverified" records, and if greater than zero, apply a filter to show only the unverified records. Now that I have read it more and more, I think I am starting to catch on to your suggestion and get a vision in my head! So the main form will contain the drop down to select the Employee that is being evaluated and capture the Evaluator and Date. Once they login or access this form, my code will generate 120 rows in the temp table populating the Evaluator ID, EmployeeID, Date, and the Skill along with empty Rating and Comment Fields. The SubForm will display this information and the end user will then move from record to record and enter the Rating and Comments. Once complete, they click a Submit button that will copy to the production table along with executing the validation checks that you mentioned. Am I on the right track??

This strategy can also be useful to allow double-entry, if your users are not known for accuracy. The same 120 could be entered a second time into the temp table, and the two batches compared to determine whether there are any miskeys.

[Dal Jeanis]

Yep. That's exactly what I meant.

Some experts would say that the temp table is unnecessary - or excessive - but I think that particular design feature allows you to let your user be interrupted, and come back later, without affecting the entry of the data or leaving partially entered junk on the production table.

Any particular design detail has advantages and disadvantages. The critical rule is this - no matter what people tell you is "best" or "standard",you should always use a solution that you yourself understand. You are the guy that has to support the thing.