Storing Calculated Controls

[hntsignif]

I know that this isn't great practice but if someone can give me an
alternative, that would work as well.

I have a +1 field to generate a unique number for each entry in a field
called ProjNo and a ProjYear field that contains the fiscal year (-10). Each
fiscal year I have the ProjNo reset to 0 and change the fiscal year up by one.

In the form I have the following calculated control:
="PR" & Format([ProjNo],"000") & [ProjYear]

This produces the unique number (PR001-10) that will follow the project
through it's lifecycle. Team member assignments, files and all other
associated documents will have the number in the calculated format for
reference.

I will have many team members assigned to the project and I would like to do
that via subform based on the unique number but in order to do that, I need
the unique number to populate back to the field FullProjNo in the original
table. There are no other unique identifiers through-out the project.

I have four current tables: ProjNoAssignment (contains all project specific
items) Employee (Employee List), Position (Project Positions List) and
Projects (Subform table that combines Employee, Position, FullProjNo)

 

[Allen Browne]

So your main table (ProjNoAssignment) has numeric fields ProjNo and
ProjYear, but the related table (Projects) has a FullProjNo field?

To me, the main table is correct (properly normalized with atomic fields),
but the Projects table is wrong. Would you consider dropping
Projects.FullProjNo, replacing it with ProjNo and ProjYear number fields?
You can now link your subform on both fields (in its LinkMasterFields and
LinkChildFields properties), and you're there.