I have a form that serves as the front end to one of my tables. Think of this table as the top level table for which almost all of the other tables are related. Once this form is filled, and especially once the other tables are also filled, I really don't want someone to come back and edit some of the fields in that top level form. Doing so would really change the logic of the entire system. It would be similar to if I had a database of historic maintenance records for different transportation devices that I own, and after 20 years to change one of the vehicles from a car to an airplane. The historical records wouldn't make sense any more.
So I would like it if once the record is saved, I don't want certain fields from being edited. Deleting the whole record and starting over would be OK, and changing other fields will be OK. Just not changing certain fields. Furthermore, if the user is creating the record for the first time, it should be OK to change their mind. But once the record is saved, that should be the end of it.
I am primarily interested in preventing this at the form level, with VBA. I don't think it is possible to protect it at the database level.
I'm thinking like something in the OnCurrent, and check to see if the Auto-Incremented ID is null, then edits are OK, but if the ID has a value, then no edits?
The tag property can often be useful:
For Each ctl In Me.Controls
If ctl.Tag = "SysLock" Then
ctl.Locked = (Not Me.NewRecord)
<...>
After that, I suggest you ensure that your tables have a create time stamp and an update time stamp. Once these are written, you can prevent editing. It may be that any record created within the last half hour is fair play, for example.