[ms office - 2010] Access 2010 - locking a cell in a table/form after text is entered

[fred54]

Hi

Trying to set up an Access 2010 database for administrative use but having a problem with one part. Once someone enters a piece of text into a cell, I want that cell to become locked automatically so it cannot be edited by any other user. The properties option for enabling or locking cells will not do what I want and I am assuming that the database will have to be coded using a macro or VBA to do this. Unfortunately my coding skills are crap and internet searches have yielded nothing. Can anyone point me in the right direction? Thanks

[Hecate]

This is from an Access 2003 perspective but I assume it will work in 2010. If you want this to apply to all cells on the form you can just set 'allow edits' to No under the data tab in form properties. If you want it just to apply to certain cells you could use the 'after update' event in the cell properties and the code allowedits = false. I quite often set allow edit to no for a form by default and provide a control to turn edits on as required so that no-one can accidentally change an entry

[fred54]

Sorry to sound like a newbie but where do I enter 'allowedits = false' - I have experimented with the macros and expression builder using this code but it does not seem to work. Told you my coding was crap!

[Hecate]

In design view right click the required text box and select properties, then choose the event tab and browse down to after update, click the build button to the right and select code. This will present you with a blank procedure which looks like this

 

Private Sub ControlName_AfterUpdate()

 

End Sub

 

Enter allowedits=false between the start and end lines so the finished code looks like this

 

Private Sub ControlName_AfterUpdate()

AllowEdits = False

End Sub

 

Note because this is attached to the after update event it won't work retrospectively only controls updated after you've applied the code will be affected

 

I hope this makes sense - you can pm me if I can help any more

[fred54]

You seem to have set me on the right path but for some reason it is still not working. I have placed the code as instructed but when I enter text into the cell it will accept it and save it OK, however it will allow me to re-edit the same cell again when I return to the form, but as you know I want the cell to be uneditable once text has been entered the first time. I also tried the locked = True and enabled = False to see if it would trigger an action on the form but again nothing happens, no error or anything.

[Hecate]

I see what you mean - I've never noticed that before. Try adding allowedits = false to the on enter event of the control - this works for me. It stops data already in place being overwritten. Welcome to the wonderful world of access programming

[fred54]

Just to let you know I have achieved a resolution using "Me!ControlName.Locked = Not IsNull(Me!ControlName)" although your suggestions seemed to be the easier more obvious solution. It is bugging me why your suggestions did not work but I am not going to let that prevent from moving on with this database!! :-) Thanks again for your help.

[Hecate]

Glad you found a solution - this wouldn't work for me - the locked property for the control isn't supported. I suppose this could be a difference between 2003 and 2007.