Results 1 to 4 of 4
  1. #1
    user9 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2013
    Location
    US
    Posts
    43

    Changes to record in table while form is open - record locking

    I have a design for a multi-user database:
    1. Form A is used to update Table A and Table B (simultaneously On Click)


    2. Form B is used to review Table B, approve records, then delete them from Table B

    The problem is if Form B is opened on a record (with Primary Key CASENUMBER) and the record for that CASENUMBER is edited in Form A.

    How do I put a lock on a specific record so that if it is being viewed in a form it cannot be viewed/edited in another?

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    You can create a recordset clone of your form's recordset using DAO. When you open the recordset object you can add

    + dbDenyWrite + dbDenyRead

  3. #3
    user9 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2013
    Location
    US
    Posts
    43

    Re: Changes to record in table while form is open - record locking

    Quote Originally Posted by ItsMe View Post
    You can create a recordset clone of your form's recordset using DAO. When you open the recordset object you can add

    + dbDenyWrite + dbDenyRead
    Thank you for the reply!

    So in the VBA for Form A, at some point in the process I would create a Private Sub Clone() and assign my copy queries to that instead of the On Click for Save Record? Then after that logic add the Deny Write/Deny Read syntax.

    I'm an Access novice so all advice is welcome.

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    In all honesty, the suggestion I made is based soley on your request to deny edits and viewing of a record. I am not sure how using dbDenyRead will effect the User's experience. Since your user is currently "looking" at the records in a subform, it may be a conflict.

    perhaps something like

    Dim rst As DAO.Recordset
    Set rst = Me.RecordsetClone(dbOpenDynaset, dbPessimistic)

    will be more appropriate

    as soon as you move your recordset to

    rst.edit

    it will lock that row until the rst.MoveNext command or equivalant.


    Locking out Users from viewing and editing records simply because another user has a form open, is not a conventional approach for me.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 03-13-2013, 06:13 PM
  2. Replies: 3
    Last Post: 03-09-2013, 10:39 AM
  3. Replies: 3
    Last Post: 02-06-2013, 07:23 PM
  4. Replies: 3
    Last Post: 08-26-2012, 10:04 PM
  5. If record does not exist, open add new record form
    By RobertIngles in forum Programming
    Replies: 2
    Last Post: 02-01-2011, 08:47 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums