Results 1 to 7 of 7
  1. #1
    RasterImage is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jun 2011
    Location
    UK
    Posts
    34

    Goto record in subform - sometimes

    Hello,


    I have a subform on one page of a tabbed main form.
    I want the subform to open on the last record, and I want it go to the last record when I change to a new record on the main form. However, if I change to a different page on the main form, then go back to the subform page, I do not want the subform to go to the last record - I want it to stay where it is.
    Can't be that hard but I've spent fruitless hours on this! Suggestions very much appreciated.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    You could probably have code in the main form Open event that will set focus to last record of the subform or to filter the subform to only the one record. And then code in other events to remove filter. How many pages on this tab? Is it only one of the pages that has a subform? The subform is set for what view - SingleForm, Continuous, Datasheet?
    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
    RasterImage is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jun 2011
    Location
    UK
    Posts
    34
    At the moment, when the subform loads I run
    DoCmd.GoToRecord , "", acLast
    so the subform opens with the last record, just as I want. But if I change to a different record on the main form, the subform goes back to the first record.
    I don't know how to do it with code in the main form.

    To answer your questions:
    The subform is on one of 5 pages on the this tab.
    (There are other subforms on other pages.)
    The subform (and the main form) are both SingleForm view.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    This worked for me in the main form OnCurrent event:
    With Me.ctrSection.Form.RecordsetClone
    If Not .EOF Then
    .MoveLast
    Me.ctrSections.Form.Bookmark = .Bookmark
    End If
    End With

    To leave the subform on record user moved to (unless it is the first record they moved to - therein is a conundrum), you could change to:
    If .BOF And Not .EOF Then

    Either might error in situation where there are no records in the subform. Did not test that.

    Replace ctrSections with the name of your subform container control.
    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.

  5. #5
    RasterImage is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jun 2011
    Location
    UK
    Posts
    34
    Not bad!
    With the first paragraph of code you gave me, everything works exactly how I want it to - except I do get an error when I go to a record on the main form where there are no records in the subform. It says error '3021': no current record. The debugger highlights the line
    Me.Lessons.Form.Bookmark = .Bookmark
    Any idea how to get around that?

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    First check the record count of the recordset.

    If .RecordCount > 0 Then
    If Not .EOF Then
    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.

  7. #7
    RasterImage is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jun 2011
    Location
    UK
    Posts
    34
    Hey, that did it.
    Thanks June7, I'd never have been able to do that, I've never come across that Bookmark thing before.
    Yeah, that's nice now.

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

Similar Threads

  1. list box goto record issue
    By Madmax in forum Access
    Replies: 2
    Last Post: 07-07-2011, 06:17 AM
  2. Replies: 1
    Last Post: 06-01-2011, 06:44 PM
  3. Goto a new record in tabbed subform
    By snoopy2003 in forum Programming
    Replies: 3
    Last Post: 03-05-2011, 04:24 PM
  4. Replies: 1
    Last Post: 12-22-2010, 01:28 AM
  5. Replies: 7
    Last Post: 12-13-2010, 03:12 PM

Tags for this Thread

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