How would you do it?

[Zach_G]

Looking for some guidance here on what I would think is a relatively simple task, but for the past few days has eluded me.

I have two tables, "tblProjects" and "tblEmployees" setup with a many-many relationship using a linking table "lnktblProjects_Employees".

I have a main form in datasheet view "frmProjects" with the record source being "Projects"



There is a "Date_of_Employment" field in "tblEmployees". What I want is to have the oldest Employee's "Date_of_Employment" to show up on the main form "frmProjects". Any suggestions?

Zach

[Zach_G]

Ok, for anyone reading here's what I've come up with so far:

Created a subform sbfProjects_Employees to show all employees on a project and in the footer of the subform created a control "OldestEmployment" with source =min([Date_of_Employment]). In the main form i created another control box that references the subform =[sbfProjects_Employees]![OldestEmployment].

This works great... in form view. But in datasheet view all the rows show the the first row value. It's not until after you click on each row does it update with the right value.

[Zach_G]

Seems I'm hearing myself talk alot in here...apparently it cannot be done. It's as if the subform doesn't exist in the parent in datasheet view until the subform is expanded. The reference to the subform returns #Error until the subform has been expanded and viewed. Maybe a limitation of Access? Any suggested work-arounds?