Avatar of Davisro
Flag for United States of America asked on

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?

Microsoft AccessVBA

Avatar of undefined
Last Comment

8/22/2022 - Mon

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.

You just made my holiday card list!!!

My most heartfelt thanks!!! Thank you for the example!!!
Your help has saved me hundreds of hours of internet surfing.