Page 1 of 4 1234 LastLast
Results 1 to 15 of 54
  1. #1
    Remster is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Sep 2010
    Posts
    317

    Record selection with the vertical scrollbar on a continuous subform

    I want to share something I've discovered completely by chance that helps to answer a question I've seen asked on various forums but never seen answered. I realise I may be reinventing the wheel here.

    The question is whether it's possible to use the vertical scrollbar on a continuous form to select records, so the uppermost record is always the current record. By default, the vertical scrollbar on a continuous form scrolls through the records in a sense, but it doesn't change which record is the current record, and it doesn't change which records are visible on the screen until the first or last visible record has been passed.



    The chance discovery is that during screen painting, the form's bookmark becomes out of sync with the recordset's bookmark, because the form's bookmark changes as each row is painted. This means that when the current record leaves the first row through scrolling (.CurrentSectionTop > 0), you can sync the bookmarks (.Recordset.Bookmark = .Bookmark), and then use a well known method to calculate how many records to move through the recordset to make the uppermost record the current record (.Recordset.Move -Round(.CurrentSectionTop / .Section(acDetail).Height).

    Here's the code snippet:

    Code:
    Private Sub Detail_Paint()
    
        If Me.CurrentSectionTop > 0 Then
            Me.Recordset.Bookmark = Me.Bookmark
            Me.Recordset.Move -Round(Me.CurrentSectionTop / Me.Section(acDetail).Height)
        End If
    
    End Sub
    I've attached an example that illustrates how this might be used in a master/parent navigation form.

  2. #2
    Remster is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Sep 2010
    Posts
    317
    Minor changes to code.

  3. #3
    Remster is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Sep 2010
    Posts
    317
    Come to think of it, this bit applies only to scrolling using the arrow keys on the keyboard:

    ... and it doesn't change which records are visible on the screen until the first or last visible record has been passed.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Just discovered this over a year late!
    I was interested in the idea but soon realised that it had some flaws - most notably it is impossible to select the final 'screen' of records

    I also tried using the idea in another app using an associated image and textbox in place of the second subform but had lots of issues with the display updating
    A real pity as the idea in the example app in the thread showed real promise .... even if nobody commented on it at the time
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    Remster is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Sep 2010
    Posts
    317
    Thanks for that. It would be interesting to know what happens for you on the "final 'screen' of records", because it works fine for me. I wonder if it's something to do with screen size or resolution, and hence the number of records displayed. For me, the final screen of records is Sylvain Yulanov to Ken Zuter.

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi
    The scrollbar always scrolls until the last record is visible - in your case Ken Zuter - but cannot be scrolled further for obvious reasons.

    When Access is maximised the last record I can select is Willie Whisen:

    Click image for larger version. 

Name:	Capture.PNG 
Views:	41 
Size:	41.3 KB 
ID:	48228

    If I reduce the size of the Access window, I can reach additional records. In this case as far as Mike Zilchrist:

    Click image for larger version. 

Name:	Restore.PNG 
Views:	42 
Size:	29.3 KB 
ID:	48229

    Whilst I can see all the other names, I can't access their data in the other subform.

    My original intention was to do this as an alternative approach to that used in my web article: Listbox Mouse Move Item Tooltips (isladogs.co.uk).
    Unfortunately, that idea didn't work for me
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    Remster is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Sep 2010
    Posts
    317
    Just to be absolutely clear, are you saying that you can scroll to the bottom of the list as shown in the screenshot below, but the subform on the left still shows the details for Willie Whisen or Mike Zilchrist or whoever?

    Click image for larger version. 

Name:	Capture.JPG 
Views:	39 
Size:	28.7 KB 
ID:	48230

    Last edited by Remster; 07-06-2022 at 04:25 AM. Reason: Screenshot wasn't showing

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Once Ken Zuter is on the screen in the right subform, no further scrolling is possible so no other records can be 'selected'.
    I cannot reach any remaining records in the right subform nor display them in the left subform

    Changing the Access window height allow me to reach a few more records but it is impossible to ever reach e.g. Ken Zuter's details
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    Remster is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Sep 2010
    Posts
    317
    Sorry if this is an obvious question, but have you tried clicking on the down-arrow once you've reached the last screen of records (see the screenshot below)? Once I've reached that point, I can't get any further by dragging what I believe is called the "tab", but the down-arrow takes me all the way.

    Click image for larger version. 

Name:	Capture.JPG 
Views:	39 
Size:	15.2 KB 
ID:	48231

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Doh!
    Yes it is an obvious question yet one that I had somehow overlooked by just using the scrollbar.
    My apologies. It does now work as shown in your screenshot in post #7.

    I'll have another go at applying the idea to my other example.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  11. #11
    Remster is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Sep 2010
    Posts
    317
    Don't worry about it. I'd become so used to the default scrollbar behaviour in playing around with this that it hadn't occurred to me to explain.

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Just returned to this and tried various changes in line with how I hoped to use the idea.

    One very simple change.
    I added a header row to the right hand Players subform. Did you ever try doing that?

    As I expected the left subform is no longer aligned - instead it now shows the data for the last record visible on screen in the right form.
    I expect that could be fixed by tweaking the code
    BUT moving the scrollbar even by small amount caused significant scrolling of both subforms for 20 seconds or so before eventually stopping with the subforms correctly aligned.

    I've attached the only slightly modified version for you to look at.

    Perhaps you can explain what is happening and do you have any ideas for a fix?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  13. #13
    Remster is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Sep 2010
    Posts
    317
    You need to factor in the height of the header:
    Code:
    Private Sub Detail_Paint()
    
        If Me.CurrentSectionTop > Me.Section(acHeader).Height Then
            Me.Recordset.Bookmark = Me.Bookmark
            Me.Recordset.Move -Round(Me.CurrentSectionTop - Me.Section(acHeader).Height / Me.Section(acDetail).Height)
        End If
    
    End sub

  14. #14
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Thanks for providing the code which I also worked out after posting last night
    However, you forgot the brackets shown in RED below

    Code:
    Private Sub Detail_Paint()
    
    
        If Me.CurrentSectionTop > Me.Section(acHeader).Height Then
            Me.Recordset.Bookmark = Me.Bookmark
            Me.Recordset.Move -Round((Me.CurrentSectionTop - Me.Section(acHeader).Height) / Me.Section(acDetail).Height)
        End If
    
    
    End Sub
    However, that wasn't the issue I was referring to when I asked if you had an explanation or a fix.
    With the original code, the behaviour on scrolling with the scrollbar slider or using the down arrow made it unusable.

    Factoring in the header height fixes that scrolling behaviour as well. What I don't understand is why adding a header made scrolling unusable.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  15. #15
    Remster is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Sep 2010
    Posts
    317
    You're quite right about the brackets, of course. I'm not sure how I came to omit them, as I thought I'd copied the amended code from the module straight after testing it. The bit in brackets should be "Me.CurrentSectionTop - Me.Section(acHeader).Height", by the way, but I think you know that.

    As for the real issue, it didn't occur for me until I switched to a different PC just now, so I'll have a think about it.

Page 1 of 4 1234 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 01-16-2018, 02:51 AM
  2. Vertical Scrollbar Appears At Random
    By lccrews in forum Access
    Replies: 3
    Last Post: 10-31-2017, 06:54 PM
  3. Missing Vertical Scrollbar
    By JeffChr in forum Forms
    Replies: 2
    Last Post: 11-21-2014, 07:54 AM
  4. Vertical scrollbar issues with a subform
    By vange2013 in forum Forms
    Replies: 1
    Last Post: 11-27-2013, 09:13 PM
  5. Vertical scrollbar in reports
    By Cedarguy in forum Reports
    Replies: 2
    Last Post: 11-07-2012, 03:15 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