+ Post New Thread
Results 1 to 8 of 8
Office Software Thread, Access 2010 - locking a cell in a table/form after text is entered in Technical; Hi Trying to set up an Access 2010 database for administrative use but having a problem with one part. Once ...
  1. #1

    Join Date
    Dec 2007
    Posts
    27
    Thank Post
    3
    Thanked 1 Time in 1 Post
    Rep Power
    0

    Access 2010 - locking a cell in a table/form after text is entered

    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

  2. #2

    Join Date
    Feb 2006
    Location
    South Cumbria
    Posts
    199
    Thank Post
    26
    Thanked 29 Times in 24 Posts
    Rep Power
    22
    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

  3. #3

    Join Date
    Dec 2007
    Posts
    27
    Thank Post
    3
    Thanked 1 Time in 1 Post
    Rep Power
    0
    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!

  4. #4

    Join Date
    Feb 2006
    Location
    South Cumbria
    Posts
    199
    Thank Post
    26
    Thanked 29 Times in 24 Posts
    Rep Power
    22
    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

  5. #5

    Join Date
    Dec 2007
    Posts
    27
    Thank Post
    3
    Thanked 1 Time in 1 Post
    Rep Power
    0
    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.

  6. #6

    Join Date
    Feb 2006
    Location
    South Cumbria
    Posts
    199
    Thank Post
    26
    Thanked 29 Times in 24 Posts
    Rep Power
    22
    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

  7. #7

    Join Date
    Dec 2007
    Posts
    27
    Thank Post
    3
    Thanked 1 Time in 1 Post
    Rep Power
    0
    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.

  8. #8

    Join Date
    Feb 2006
    Location
    South Cumbria
    Posts
    199
    Thank Post
    26
    Thanked 29 Times in 24 Posts
    Rep Power
    22
    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.

SHARE:
+ Post New Thread

Similar Threads

  1. Replies: 2
    Last Post: 12th October 2010, 08:38 AM
  2. MS Access - Create table linked to a Record in another table
    By RabbieBurns in forum Office Software
    Replies: 2
    Last Post: 5th May 2010, 08:52 AM
  3. Replies: 2
    Last Post: 15th December 2009, 03:36 PM
  4. Replies: 0
    Last Post: 25th June 2009, 03:45 PM
  5. Replies: 0
    Last Post: 26th January 2009, 02:36 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •