How to lock a record in an access subform

Question

I am trying to figure out how to lock a record in a subform once the user moves on to the next record for entering data. But I still want to the user to be able to go back and view other records but not edit the information. Or have a control box that can do this function, once checked off, the record is locked and can only be read only. I don't want the fields to lock upon data entry though, I want the user to be able to select/deselect/add/remove data the entire time they are working on the record, but once they finish and go to the next, or save or click on a control, I want it to all lock and be read only to anyone.

I am using MS Access 2010.

Any help would be appreciated. Thanks!

Answer 1

You could use an integer to store the latest record ID the user has viewed. In the onCurrent event of the subform, check this integer against the ID of the current record. If it is smaller then set the .Enabled property of all the controls on the form to False. Else, set them all to true.

So you'd want to publicly declare an integer and do something like this in the form's onCurrent event.

If Me.ID > recCount Then
    recCount = Me.ID
End If

If recCount < Me.ID Then
    'Disable all controls
    myControl.Enabled = False
Else
    'Enable all controls
    myControl.Enabled = True
End If

Of course, that's not going to prevent people exiting and reloading the DB to edit older records again. If you wanted to secure it better then you could store the ID in a dummy table (with one defined field and one record) instead.

Is it important that the records be rendered read-only sequentially? Because otherwise, it seems as though it'd be far better to just add a boolean field to your table, check that in onCurrent and enable/disable your controls from there.

Answer 2

I would suggest you implement some sort of user management to allow a certain group of users to edit records under a specific set of circumstances. Ex: when a check box is equal -1 and authorized status is positive.

That way, unauthorized users won't be able to edit records, and those with access would have to put a check in the check box before making any changes to the data.

To lock the records on a form or subform you can use

Me.locked = true 'for single form
Me.mySubFormName.locked = true 'for subform 

Use false to allow edit under your choosen scenario~

--

As hoopzbarkley suggested, if you want to lock the record once and for all after it as been edited, that's another thing. As you will have to inspect your data before showing it to the user and use the .lock property if the ID from the blocked records table is to be displayed.

Answer 3

As of Office 2007 Access, set up the properties for the "form" object using the "data" tab.

Allow additions: YES
Allow Deletions: NO
Allow Edits: NO

You will still restrict access to the form using normal means, but it will prevent any and all edits to previously entered fields, and still give access to changes to the record as you add, but deny changes once the record moves to a new record.