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 __
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:
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?