subform - only select item once

[BigPat]

Hi,

I've had a browse of the help here but I can't find the answer.



I have a subform inside an outer form where you're able to select essential skills - the subform takes its data from the 'skills' table, and the outer form feeds into an 'employees' table. The 'skills' table has a list of 20 essential skills, and the person entering the data can type any or all of them. The data from this subform goes into a third table, 'employeeSkills', which is used for matching roles to candidates. The problem is that it's possible for the person inputting the data to choose the same skill more than once. I'm not sure how to set this that you can only select, say, Customer Service once.

Can someone help please?

Thanks
Pat

[June7]

Your form arrangement doesn't sound right.

Should be main form bound to Employees and subform bound to EmployeeSkills with a combobox to select skill.

Preventing duplication of skill for employee can be done several ways.

1. set the EmployeeID and SkillID fields as compound index in EmployeeSkills table - Access will not allow duplicate pairs - this is the simplest approach

2. construct combobox RowSource to not include skills in list already assigned to employee - a query that requires joining Skills and EmployeeSkills tables with filter parameter dependent on the EmployeeID of form's current record

3. VBA code to validate the user input - least desirable approach

[BigPat]

Thank you. I'll try the compound index idea. I appreciate the help!

[June7]

Option 2 prevents the event whereas 1 and 3 deal with issue after the fact - annoys users with messages and makes them have to do more work. Downside of option 2 is that dependent comboboxes with alias don't work nice in Continuous or Datasheet view form.

But if you have the subform set as datasheet, users should be able to see skills already associated with employee and know not to repeat a selection. However, there could be a lot of skills causing a long list to look through - again slowing productivity.

There is no perfect solution.

[BigPat]

I think that one's a little out of my league though!

[June7]

Edited my previous post with more info.

Dependent combobox not really that complicated. If you want to explore, start with http://datapigtechnologies.com/flash...combobox2.html

[BigPat]

Yes, I've got some dependent combo boxes in my form actually (location/department). I'm not sure I'd know how to code to eliminate a selection already chosen, though. The rest of my subform is as you say - bound to EmployeeSkills with a Skills combo box.

[June7]

Require a dataset of all possible employee and skill pairs. This can be generated with a Cartesian query of the Employess and Skills tables. See the nested subquery in example.

Possible combobox RowSource:

SELECT EmpSkill.SkillID, EmpSkill.SkillDesc
FROM (SELECT Employees.EmpID, Skills.SkillID, Skills.SkillDesc FROM Employees, Skills) AS EmpSkill
LEFT JOIN EmployeeSkills ON (EmpSkill.SkillID = EmployeeSkills.SkillID) AND (EmpSkill.EmpID = EmployeeSkills.EmpID)
WHERE (((EmployeeSkills.SkillID) Is Null AND EmpSkill.EmpID=Forms!formname!EmpID));