T
TishyMouse
Hi all
this is a follow-up question from my question under 'Queries' yesterday,
hope someone will be able to help as I'm nearly there with my little
application! Definitely a steep learning curve for me!
Basically, I have a database to store employee skills.
The database comprises, amongst other things, 7 tables for storage of
Areas - High level groupings of skills
Skills - Skills that an employee may have/acquire
Roles - Job types
Targets - Definition of different target levels for each skill; each Skill
will have up to 7 Targets defined (TargetLevels 1-7) with associated
TargetText.
Actions - Actions to be taken by the employee to reach a particular target
level - may be role-specific
Employees - Details of employees including role
Assessments - current and target levels for each employee for each skill
These are defined as follows:
tblAreas
========
AreaID (AutoNum, PK)
AreaText (Text, unique index)
tblSkills
==========
SkillID (AutoNum, PK)
AreaID (Integer)
SkillText (Text)
AreaID + Skill = Unique index
tblTargets
==========
TargetID (AutoNum, PK)
SkillID (Integer)
TargetLevel (Integer)
TargetText (Text)
SkillID + TargetLevel = Unique index
tblRoles
========
RoleID (AutoNum, PK)
RoleText (Text, unique index)
tblActions
==========
ActionID (AutoNum, PK)
RoleID (Integer)
TargetID (Integer)
ActionText (Text)
TargetID+RoleID = Unique index
tblEmployees
============
EmployeeID (AutoNum, PK)
LastName (Text)
FirstName (Text)
RoleID (Integer)
LastName + FirstName = Unique index
tblAssessments
==============
EmployeeID (Integer)
SkillID (Integer)
TargetLevel (Integer)
ActualLevel (Integer)
EmployeeID + SkillID = Primary key
I have successfully created a form for managing Actions:
frmManageActions has a main form (source tblAreas) and then cascading
subforms detailing Skills (source tblSkills), Targets (source tblTargets) and
Actions (source tblActions).
This works fine.
Now I want to create a form where I select an Employee and then can select
an Area, a Skill, and enter associated TargetLevel and ActualLevel for that
employee.
So far, I have created a main form frmAssessment (source tblAreas) which
also has a combo box cboEmployeeID which returns the EmployeeID from a list
of LastName, FirstName.
Then there are subforms for Skills (source tblSkills) and Assessments.
The problem I think lies in this last subform sfrmTblAssessments
This currently has a source defined as:
SELECT * FROM tblAssessments WHERE EmployeeId=Parent!cboEmployeeID;
It also has two combo boxes and two text boxes as follows:
cboTargetLevel and cboActualLevel
both with row sources
SELECT tblTargets.TargetText, tblTargets.TargetLevel FROM tblTargets WHERE
(((tblTargets.SkillID)=IIf(IsNull(Forms!frmAssessment!sfrmTblSkills.Form!SkillID),[SkillID],Forms!frmAssessment!sfrmTblSkills.Form!SkillID)))
ORDER BY tblTargets.TargetLevel, tblTargets.TargetText;
which return the appropriate TargetLevel to populate txtTargetLevel (source
TargetLevel) and txtActualLevel (source ActualLevel) respectively.
I seem to be having a couple of problems with this approach:
1. The combo boxes in sfrmTblAssessments aren't getting updated when I
select a new skill. I have tried using various events to trigger a refresh
but without much success.
2. The data entered in the form is not being stored to the correct Employee
record in tblAssessments - i keep getting duplicate errors. Also even when
there is a record in tblAssessments for the correct combination of EmployeeID
and SkillID, the correct details don't appear in the form.
As there is only one combination of TargetLevel/ActualLevel per employee per
skill maybe this doeesn't belong in a separate sub form (?)
BTW I have tried explicitly setting up One-to-Many relationships
(referential integrity enforced) between tblSkills and tblAssessments on
SkillID and between tblEmployees and tblAssessments on EmployeeID. However
this doesn't appear to make a difference.
Anyway, thanks for reading this far if you managed it. Would appreciate any
guidance on this.
TishyMouse
this is a follow-up question from my question under 'Queries' yesterday,
hope someone will be able to help as I'm nearly there with my little
application! Definitely a steep learning curve for me!
Basically, I have a database to store employee skills.
The database comprises, amongst other things, 7 tables for storage of
Areas - High level groupings of skills
Skills - Skills that an employee may have/acquire
Roles - Job types
Targets - Definition of different target levels for each skill; each Skill
will have up to 7 Targets defined (TargetLevels 1-7) with associated
TargetText.
Actions - Actions to be taken by the employee to reach a particular target
level - may be role-specific
Employees - Details of employees including role
Assessments - current and target levels for each employee for each skill
These are defined as follows:
tblAreas
========
AreaID (AutoNum, PK)
AreaText (Text, unique index)
tblSkills
==========
SkillID (AutoNum, PK)
AreaID (Integer)
SkillText (Text)
AreaID + Skill = Unique index
tblTargets
==========
TargetID (AutoNum, PK)
SkillID (Integer)
TargetLevel (Integer)
TargetText (Text)
SkillID + TargetLevel = Unique index
tblRoles
========
RoleID (AutoNum, PK)
RoleText (Text, unique index)
tblActions
==========
ActionID (AutoNum, PK)
RoleID (Integer)
TargetID (Integer)
ActionText (Text)
TargetID+RoleID = Unique index
tblEmployees
============
EmployeeID (AutoNum, PK)
LastName (Text)
FirstName (Text)
RoleID (Integer)
LastName + FirstName = Unique index
tblAssessments
==============
EmployeeID (Integer)
SkillID (Integer)
TargetLevel (Integer)
ActualLevel (Integer)
EmployeeID + SkillID = Primary key
I have successfully created a form for managing Actions:
frmManageActions has a main form (source tblAreas) and then cascading
subforms detailing Skills (source tblSkills), Targets (source tblTargets) and
Actions (source tblActions).
This works fine.
Now I want to create a form where I select an Employee and then can select
an Area, a Skill, and enter associated TargetLevel and ActualLevel for that
employee.
So far, I have created a main form frmAssessment (source tblAreas) which
also has a combo box cboEmployeeID which returns the EmployeeID from a list
of LastName, FirstName.
Then there are subforms for Skills (source tblSkills) and Assessments.
The problem I think lies in this last subform sfrmTblAssessments
This currently has a source defined as:
SELECT * FROM tblAssessments WHERE EmployeeId=Parent!cboEmployeeID;
It also has two combo boxes and two text boxes as follows:
cboTargetLevel and cboActualLevel
both with row sources
SELECT tblTargets.TargetText, tblTargets.TargetLevel FROM tblTargets WHERE
(((tblTargets.SkillID)=IIf(IsNull(Forms!frmAssessment!sfrmTblSkills.Form!SkillID),[SkillID],Forms!frmAssessment!sfrmTblSkills.Form!SkillID)))
ORDER BY tblTargets.TargetLevel, tblTargets.TargetText;
which return the appropriate TargetLevel to populate txtTargetLevel (source
TargetLevel) and txtActualLevel (source ActualLevel) respectively.
I seem to be having a couple of problems with this approach:
1. The combo boxes in sfrmTblAssessments aren't getting updated when I
select a new skill. I have tried using various events to trigger a refresh
but without much success.
2. The data entered in the form is not being stored to the correct Employee
record in tblAssessments - i keep getting duplicate errors. Also even when
there is a record in tblAssessments for the correct combination of EmployeeID
and SkillID, the correct details don't appear in the form.
As there is only one combination of TargetLevel/ActualLevel per employee per
skill maybe this doeesn't belong in a separate sub form (?)
BTW I have tried explicitly setting up One-to-Many relationships
(referential integrity enforced) between tblSkills and tblAssessments on
SkillID and between tblEmployees and tblAssessments on EmployeeID. However
this doesn't appear to make a difference.
Anyway, thanks for reading this far if you managed it. Would appreciate any
guidance on this.
TishyMouse