Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2009
    Posts
    9

    Unanswered: Locking Specific Records- ACCESS 2007

    Hi,

    We use our ACCESS database for data entry purposes, and since our database is split, meaning many users are constantly updating and adding new data, changing things, etc. I would like to be able to lock individual records once that record is 100% accurate and finalized so no one messes with it or deletes it by accident!

    Is there a way to have a check box in the form or in the table that once checked "locks" the record so no one is able to delete or update it?

    I am assuming it would be some code like:

    Formname.Checkbox = true [locked] ?? I tried something this and of course it doesn't work! I am a beginner so bare with!

    Thanks for your help in advance,

    Jenny

  2. #2
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by JennyD View Post
    Hi,

    We use our ACCESS database for data entry purposes, and since our database is split, meaning many users are constantly updating and adding new data, changing things, etc. I would like to be able to lock individual records once that record is 100% accurate and finalized so no one messes with it or deletes it by accident!

    Is there a way to have a check box in the form or in the table that once checked "locks" the record so no one is able to delete or update it?

    I am assuming it would be some code like:

    Formname.Checkbox = true [locked] ?? I tried something this and of course it doesn't work! I am a beginner so bare with!

    Thanks for your help in advance,

    Jenny
    Jenny,

    Yes, it is possible.

    I actually lock all records. The user has to use a Edit button to unlock the record to make changes.

    This has to be handled at the form level.

    In your case, I would use a data/time field that holds the date using Date(), or Date and time using Now(), if needed, to mark a record was finalized.

    I like to use the date over a check box since it provided a lot more detail.

    If you want a check box to show, then you can calculate it from the date field and not even show the date.

    Me.CheckBoxCompleted = IsDate(Me.DateCompleted)


    I usually use a command button to place Date() ot if needing time Now() into the field.

    The trick is to use the Form's On Current Event to lock the record.

    Example:

    Code:
    Private Sub Form_Current()
    
        If Me.NewRecord or IsNull(Me.DateCompleted) Then
        
        
            Me.AllowEdits = True
    
           '' if there is a sub form(s) use the following 
            Me.SubFormName.Locked = False
        
        Else
        
               
            Me.AllowEdits = False
           '' if there is a sub form(s) use the following 
            Me.SubFormName.Locked = True
              
    
          End If
    
    End Sub


    Hope this helps ...
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    Aug 2009
    Posts
    9
    That worked great! Thanks so much.

    It won't allow for edits in the forms, however is is possible to take the "locking" one step further and prevent people from deleting the record and also prevent people from deleting or updating the record in the table view?

    Thanks again!

  4. #4
    Join Date
    May 2009
    Posts
    5
    Quote Originally Posted by JennyD View Post
    That worked great! Thanks so much.

    It won't allow for edits in the forms, however is is possible to take the "locking" one step further and prevent people from deleting the record and also prevent people from deleting or updating the record in the table view?

    Thanks again!
    Exactly our issue as well. How do you prevent records from being deleted, while still allowing edits?

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Me.AllowEdits = True
    Me.AllowDeletions = False


    As for doing things directly in a Table, if this is a split-app, what are the end-users doing even having direct access to the Back End?

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    May 2009
    Posts
    5
    Quote Originally Posted by Missinglinq View Post
    Me.AllowEdits = True
    Me.AllowDeletions = False


    As for doing things directly in a Table, if this is a split-app, what are the end-users doing even having direct access to the Back End?

    Linq ;0)>
    Yes, it is a split database. The users do not have to access the "backend" directly to delete records, but can from the "frontend" which links to the tables in the backend.

Posting Permissions

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