CODEWITHSUNDEEP
ms-access
MrGibbage
MrGibbage

Reputation: 2646

How can I get my Access form to prevent editing of a field once saved?

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?

Upvotes: 1

Views: 5985

Answers (3)

Skip
Skip

Reputation: 1

"Lock" the fields (CustomerId and ProjectName) This prevents accidental changes to these fields. Create a "New Record" button that: GoToRecord New, CustomerID.locked = 0 and ProjectName.Locked = 0, then lock these two fields after updating each.

Upvotes: 0

Fionnuala
Fionnuala

Reputation: 91376

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.

Upvotes: 2

Comrad_Durandal
Comrad_Durandal

Reputation: 641

You could specify some sort of VBA even OnCurrent to look for the presence of data in the record being viewed - then tell it to change the entry box(es) to Enabled = No and Locked = Yes - that way, there is no way for data to be entered into these fields AND they cannot even be selected in the Tab Order (if I remember right).

Upvotes: 1

Related Questions