Locking a subform & tab control subforms when a date has been entered

[websterh]

Hi All,
I'm new to this site so firstly I'd like to apologise if this has already been answered the way I'm thinking it can be done.

I've built several MS Access databases previously (basic to intermediate incl using VBA and macros) but with updating this database they are looking for some 'fancier' (for me anyway) features.

So here is the run down:
What I am after: when a valid completion date has been entered it will lock the subform it is located on, and the tab controls containing additional subforms.
Structure: 1 client can have many episodes (this individual episode will need to be locked), each episode is linked to many ..... so 1 [client] : M [episodes [lock this]] and also 1 ['lock this'] : M,M,M .
Purpose/Security: so staff do not fudge the numbers, etc after the person has finished, while still having an option via the backend to update/delete the date if required; by an administrator etc.

• I have a form [which will be Split when rolled out] named "Client".

• This has a subform on it named "Episode Post 2010".

• This episode post 2010 subform has a field named "Episode Finish Date". Once a valid date has been entered the subform and the Tab Controls containing subforms are locked too (kind of like 'cutting the head (episode) off and the tail (tab subforms) dies' sort of thing).
• Tab control subform names incl [but not limited to] Prior Treatment, Current Treatment, and Referral; each with their own fields that would need to be locked if the 'Finish Date' is entered.
• I'm looking to see if there is a way of doing this so the subform and tab control subform fields are locked for this particular record/EpisodeID.

I have attempted the 'After Update' function but I don't know how to lock all the subform's fields without limiting the staff to update if it's still open. I have also looked over the option to lock each field and after Lost Focus lock the records field again but with the number of fields I have the VBA coding would be lengthy.



Any assistance would be appreciated.
I may have to clarify this further down the track but i wanted to get this issue out and see if there is any advice/tips.

Thanks.

[apr pillai]

If you want to lock a field the best approach is to write code on the next Field's Got Focus Event. When the field is active we cannot lock it.

You may lock/unlock the sub-form by settings its lock property value to true/false instead of locking individual fields.