This is a real PITA you have there, with other DB you could have triggers to test the data, and in V2010 you now have data level macros that can act like a trigger and check the record too... however, this is more FYI than useful for your situation.
Seth has some fairly useful options for you. I like the on current event check in the forms.
So, what about it the user gets to the table directly (yep it happens)
So my thought for possible work-around:
Hide the data table either thru the properties (or via
naming convention, prefix
usys_
, that will set the table to act like a system table). Set the hidden property for any queries you base on this table too. The normal user who opens the DB is not usually going to have the "show hidden..." or "show system..." options set for the navigation. Then in your forms you will have to either base the forms on queries/recordsets that exclude the "locked" record, or check for this record as Seth has mentioned.
Second thought, establish a second table that duplicates the data table; however, it will only contain your one record. Then as above, set the property of this table to Hidden as above. Now you can then create a union query between the two tables for use in reports and to display the data in forms (i.e. combobox, listbox, non-updatable text fields) as union queries in MSAcess are entirely non-updatable (
(Why is my query read-only?) your "primary" record is safe. For any of the remaining data manipulation, you use the visible data table. Drawback with this method... you will not be able to include this hidden table record as part of a relationship between tables for 1:M etc... you can however include the union query on the key with the option for right/left-joins. This could get messy and not something I've done.
My Third thought is a variation upon a theme, call it 2A, Create the hidden table as in 2; however, keep that record within your main datatable. Now you can use that hidden record to reset the main table record using a variety of means in VBA, on open/close events, etc...