Comparing data in continuous subforms 1

[Vyurr]

Good morning all,

I think I'm close to solving this puzzle but there's just one piece missing. I have a main form with a two continuous subforms on it. The subforms refer to required levels that an employee must have in different "competencies" in order to fill a certain job. So jobs have compentency levels and employees have competency levels. What I would like to do is conditionally format the employees competencies to change to red if the employee has not yet met the required level.

Tables:

tblJobLevelReq

CompID
JobID
Level


tblEELevel

CompID
EmployeeID
Level


I have created a simple query that extracts the CompID and Level for a particular job and then created a subsubform to place in the footer of the EmployeeLevel subform. This I hoped would allow me to compare the Job Level Required with the Employee Level Attained.

I've set up the Child/Master to link the CompID between the subform and the subsubform...but I'm still missing something....I can tell, because its not working (haha).

Any ideas would be appreciated.

V

 

[dhookom]

I would include the tblJobLevelReq table in the record source of the subform where you want to use the conditional formatting.

Duane
Hook'D on Access
MS Access MVP

 

[Vyurr]

Hi Duane,

Thanks for the response. I must be missing something. The Record Source of the subform that I want formatted is currently set to tblEELevel (so that the values can be editted on the form). How would I add tblJobLevelReq to the record source while still maintaining the ability to edit the records?

V

 

[dhookom]

If you want to "conditionally format the employees competencies" it thought this would be in the tblEELevel which could be joined to tblJobLevelReq so Level could be compared across the two tables.

I'm not sure how data is stored in your tables or what the primary/foreign keys are. This is significant in determing if a record can be edited.

Duane
Hook'D on Access
MS Access MVP

 

[Vyurr]

Thanks again for the response, I'll try to clarify.

The main form is the employee form. It is bound to tblEmployees which contains the following data:

tblEmployees

EmployeeID (PK)
FirstName
LastName

This main form contains a number of subforms, one for their current unit, manager and section, one for their current job title and job number, one for their required competency levels of the job they are in, and one for the competency levels that they have achieved. The forms are bound to the following tables:

tblEmployeeLevel (levels the employee has attained)

EmployeeID (Dual PK)
CompID (PK with EmployeeID and FK to tblCompetencies)
Level
JobID (FK to tblJobs)
UnitID (FK to tblUnits)

tblJobLevelReq (levels required by a position)

CompID (Dual PK)
JobID (PK with CompID and FK to tblJobs)
LevelReq


CHILD/MASTER Links

subfrmUnitInfo linked to mainform by EmployeeID txtbox
subfrmJobInfo linked to mainform by EmployeeID txtbox
subfrmJobLevelReq linked to mainform by EmployeeID txtbox
subfrmEmployeeLevels linked to mainform byEmployeeIDtxtbox

So, with this, as I change records (employees) on the mainform, all of the subforms update to show the current employee's information.

subfrmEmployeeLevels and subfrmLevelReq are (and must be) continuous subforms (Actually, they are in datasheet view)

What I am attempting to achieve is to have the employee levels to be updateable on subfrmEmployeeLevels but to ALSO have them conditionally formatted to RED and BOLD if the level the employee currently has is less than the level required for their current job.

I'm not even sure this is possible because I am trying to compare values (by CompID) between two continuous (datasheet) subforms.

Thanks again,

V

 

[Vyurr]

So much for clarification, I apologize...I made and error in the table structure. The tables look like this:

tblEmployees

EmployeeID(PK)
FirstName
LastName
JobID (FK to tblJobs)
UnitID (FK to tblUnits)


tblEmployeeLevel

EmployeeID (Dual PK)
CompID (PK with EmployeeID and FK to tblCompetencies)
Level


tblJobLevelReq (levels required by a position)

CompID (Dual PK)
JobID (PK with CompID and FK to tblJobs)
LevelReq


Sorry for the confusion.

V

 

[dhookom]

How could this be true

subfrmJobLevelReq linked to mainform by EmployeeID txtbox

if there is not EmployeeID in the tblJobLevelReq table?

Duane
Hook'D on Access
MS Access MVP

 

[Vyurr]

You are correct....again (haha)

subfrmJobLevelReq is linked by JobID

V

 

[Vyurr]

I can't seem to solve this issue...so I'll let it be. Thanks for the time.

V