Access subform to replicate spreadsheet style data entry without using combo boxes


I’m having an issue with data entry of fixed groups that include null values. I’d like to know if the correct handling is to use a VBA recordset object (DAO or ADO?) to populate a sub form that displays existing records and and also appends any changes to a source table.

I'm tracking employee attributes in Access for budget and planning purposes for 100+ administrative staff. I have an Employee form and an Attributes sub-form where managers need to assign percentages to each employee from a fixed list of 7 employee skills. Currently this information is being done on a spreadsheet horizontally with a totals column.

The easy way out of this is to have an EmpSkill table with the categories and provide a drop down list so users can select a SkillType and the percentages. Please note that some percentages could be Null for each employee such as for Legal below.
% Admin 30%
% Events 20%
% Financial 30%
% Legal __
% Presentations/Reporting 20%
% Research/Writing __
% Other

But with 100+ administrative employees and 7 skills to assign, we're looking at 700+ drop-down selections to populate the data. I would rather allow data to be entered "spreadsheet style" without combo boxes, so I’d need to allow the users to:
1.      See a data entry form showing the full list of available categories so they can enter the percentages, and
2.      See the existing percentages on the sub form when scrolling though the records in the master Employee Form

The problem I’m having is figuring out what should be the Control Source of the EmplSkills subform? If I query the tblEmpSkill and the tblEmpSkillPct tables and use:
A. an equi-join on ESID, I get no records unless there are percentages entered
B. an outer join on ESID, I see all the categories from the EmpSkill table whether or not percentages are added, but when that query is added as the Control Source for the EmpSkills subform, the Empl_ID link to the main form filters the null values in the subform so only Skills with percentages (for that Empl_ID) are displayed.
C. don’t include EmpI_ID at all, I can see all the categories again, but the sub form no longer displays the percentages for each employee

Currently the table structure is:

PctgDateTime (timestamp)

So can I use unbound subform, an when a record changes in the master form, populate the subform with a recordset that outer-joins tblEmpSkill and tblEmpSkillPct on ESID, and then adds EmpID to every row in the recordset, I’d then have as a control source a recordset that I can display to the users and they can enter the data. If there are existing percentaes they’d be displayed:
ESID, Empl_ID, Skill Type, Pctg
1      21700      Admin      ____      
2      21700      Events      ____
3      21700      Financial      ____
4      21700      Legal      ____
5      21700      Present      ____
6      21700      Research      ____
7      21700      Other      ____

And when the sub form loses focus, or with a commend button, I could INSERT INTO the data into the tblEmpSkillsPct table.

Does this sound right? Does it matter if use the ADO or DAO object models?

DavisroBudget AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I've attached an example showing what I described in your other thread.  You have a many-to-many relationship and that requires three tables.
Employees, Skills, and EmployeeSkills.

The sample includes a query to populate EmployeeSkills with a record for every employee for every skill.  You only need to run it once.  As each new employee is added using the form, the AfterInsert event runs an append query that appends the skill records for the new employee.  The master/child links keep the main form/subform sync'd.  Only a couple of lines of code are needed in the AfterInsert event to run the query to populate EmployeeSkills.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DavisroBudget AnalystAuthor Commented:
You just made my holiday card list!!!

My most heartfelt thanks!!! Thank you for the example!!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.