Linked forms question

[rmason]

I have a form with a subform, on the main form there is a "Termdate" Field. On the subform there is a "EndDate" field. The subform shows when a nurse has worked on a Unit during a specified time. Below is what the form may look like:

Employee Date of hire Termdate
Jane Doe 01/01/01

Unit Start End
PCU 01/01/01 05/03/01
ESD 05/04/01 07/01/02
CCU 07/02/02


Jane doe started in CCU in july 02 and is still there at the present time. What I need to be able to do is, if Jane leaves ,her term date gets filled in, how can I have the LAST BLANK End date get auto populated with the termdate from the main form?? Please explain in laymens terms with example of code needed. I think I need to do something like: If Me.termdate.value > "" then
Me.EndDate.value = Date
I have tried this but cannot get it to work(??)

 

[namliam]

I think an update query would be called for, also much easier...

Regards

 

[rmason]

Thanks Niam, How would I build the query to only change the LAST subform entry?

 

[namliam]

build it in the QBE...

And include the following where clause
Where end is null

Regards