Results 1 to 6 of 6
  1. #1
    snipe's Avatar
    snipe is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    287

    "Delete" a record, requery then go to the next record


    Hey guys,

    I have a continuous form that looks like this:
    Click image for larger version. 

Name:	Screen Shot 2015-01-24 at 4.07.02 PM.png 
Views:	15 
Size:	93.6 KB 
ID:	19446

    We don't like to delete our names here, so instead of actually deleting a record, my delete button just marks the linked field [Deleted] with an "x". The vba code for the delete button marks the field as "x" then requeries. Which will remove it from view. Each time the form is requeried, though, the first record is selected. So if a user is deleted the 105th and 106th records, he has to scroll way back to where he was each time.

    How can I add a blurb of code that gets the record number of the 1 directly before the deleted one, mark the desired one as "x", requery, then goto that previous record?

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    My guess is to try recordsetclone and Bookmark. I'm looking for an example, but not having great luck.

    http://bytes.com/topic/access/answer...okmark-problem

    https://msdn.microsoft.com/en-us/lib...ice.15%29.aspx

    Guessing...

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Try using CurrentRecord. In the delete button event, first save the current record number to a variable, tempvar, or hidden textbox. Requery the form and the 'next' record now has the position of the removed record.

    Dim intRecNum
    intRecNum = Me.CurrentRecord
    ...
    DoCmd.GoToRecord acDataForm, "formname", acGoTo, intRecNum

    or if you want 'previous' record: Me.CurrentRecord - 1
    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.

  4. #4
    snipe's Avatar
    snipe is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    287
    June7,

    Definitely on the right path. My form that has all of the records is actually a subform, so I got the "F_DR_Select_Sub is not open" error?

    How do I get around that?

    Parent form name is "F_DR_Select"

    EDIT: I used the parent form name, and now I am getting a "Can't go to the specified record error"

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    DoCmd.GoToRecord , , acGoTo, intRecNum
    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.

  6. #6
    snipe's Avatar
    snipe is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    287
    Quote Originally Posted by June7 View Post
    DoCmd.GoToRecord , , acGoTo, intRecNum

    Perfect. Thanks, buddy.

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

Similar Threads

  1. Replies: 2
    Last Post: 11-07-2014, 02:53 PM
  2. Replies: 7
    Last Post: 11-30-2013, 12:33 PM
  3. Replies: 10
    Last Post: 03-15-2013, 05:46 AM
  4. turn "about to delete record" warning back on
    By markjkubicki in forum Programming
    Replies: 1
    Last Post: 11-01-2012, 12:21 PM
  5. Replies: 4
    Last Post: 06-14-2010, 02:31 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