Results 1 to 10 of 10
  1. #1
    Chris1112 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    31

    Record Lock Issue When Subform is Opened and Record Edited

    I have a field on a form that opens another form on double-click with the following code:


    Code:
    Private Sub Lender_DblClick(Cancel As Integer)    
    DoCmd.OpenForm "frmLenderNotes", , , "[Job]='" & Me!Job & "'"
    End Sub
    When I edit the field in the form that pops up, frmLenderNotes, and exit, the record in the original form is missing the primary key and is no longer editable. If I do not edit the field and exit the form, this does not happen.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Do you want to provide db for analysis? Follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    TG_W is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2010
    Location
    Manvel, TX
    Posts
    299
    Try adding Me.Refresh to your main form in On Got Focus in Events.

  4. #4
    Chris1112 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    31
    Quote Originally Posted by TG_W View Post
    Try adding Me.Refresh to your main form in On Got Focus in Events.
    I added Me.Refresh to the On Got Focus event of the form and the field but it does not resolve the issue. If I hit F5, the data reappears. I've tested with a msgbox command and the event is triggering. So what is the difference between Me.Refresh and hitting F5?

    Thanks

  5. #5
    TG_W is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2010
    Location
    Manvel, TX
    Posts
    299
    F5 should actually be comporable to Me.Requery, not Me.Refresh.

  6. #6
    Chris1112 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    31
    So I put Me.Requery on the main form's On Got Focus event and it doesn't trigger. I am opening a second form, changing data, then closing it to get back to the main form. Shouldn't this trigger the event?

  7. #7
    TG_W is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2010
    Location
    Manvel, TX
    Posts
    299
    It should have triggered it, yes. I use 2 different methods to do what you are trying to do - (1) close the main form and reopen it upon closing of the subform or (2) add a refresh button to press after updating in the subform. I know completely automated would be better, but these have been my workarounds. I'm going to play with some coding and post back if I find something better.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You have a form open and open another form to edit data and need to refresh the data on the first form because of the edit? Are you using acDialog parameter when opening the second form? acDialog will suspend code execution in the calling procedure until the second form closes.

    DoCmd.OpenForm "formname", , , , , acDialog
    Me.Requery
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Chris1112 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    31
    Quote Originally Posted by June7 View Post
    You have a form open and open another form to edit data and need to refresh the data on the first form because of the edit? Are you using acDialog parameter when opening the second form? acDialog will suspend code execution in the calling procedure until the second form closes.

    DoCmd.OpenForm "formname", , , , , acDialog
    Me.Requery
    Thanks for the suggestion. I added the acDialog option but the problem persisted. I finally added the requery command to the form close of the second form. It appears to have fixed the issue.

  10. #10
    TG_W is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2010
    Location
    Manvel, TX
    Posts
    299
    With a little more research, I found the code below. Add it before closing the subform that pops up while leaving the main form open. It worked like a charm in my form.

    Forms!YourFormNameHere.Form.Refresh

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

Similar Threads

  1. Replies: 1
    Last Post: 04-04-2012, 05:11 PM
  2. Help with a basic record pause or lock?
    By valkster in forum Programming
    Replies: 0
    Last Post: 03-12-2012, 06:14 AM
  3. Lock Record for Editing on a Linked table
    By khalid in forum Programming
    Replies: 3
    Last Post: 06-14-2011, 08:37 AM
  4. lock a record
    By Nokia N93 in forum Programming
    Replies: 3
    Last Post: 02-21-2011, 02:51 PM
  5. Replies: 5
    Last Post: 06-29-2010, 01:24 PM

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