Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211

    GET/SET Subform Horizontal Scrollbar VBA (Access 2016)

    I have a form used for importing member records into my database from an Excel workbook.
    The workbook contains the new members to be imported into my database.

    The form contains 2 subforms, one to show the records to be imported, and one to show existing members already in the database.
    The subforms contain too many columns to show them all in my form without scrolling left or right. Therefore I enable the horizontal scrollbars so we can view all of the fields.

    I already have code that automatically scrolls the second subform to the right record based on choosing a record in the first subform. This part works fine.

    What I really want to do is find a Windows API solution to programatically scroll the second subform horizontally when I manually scroll the first subform horizontally.
    The intent is that when I expose hidden columns in the first subform, the second one will also scroll to the same position horizontally (and if possible, vice-versa).

    I would need a subroutine to both get and set the scrollbar position. I used to be able to do this pretty easily in a .Net WinForm, but not sure how to do it in Access.


    I would also need an event which fires when the first subform is scrolled horizontally. Since the subform's scrollbar is not really a control (I think), I'm not sure how to do this, or if it can be done.

    I've seen reference to Steve Lebans' code to do this, but that was written prior to Access 2007 and doesn't seem to work with current Access versions.

    Does anyone have any ideas how to trap an event when I manually scroll one subform horizontally, GET the new scroll position, then run a subroutine which SETS the horizontal scroll position on the other subform using the Windows API?

    Thanks...

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Never used either CurrentSectionLeft or top properties but I believe this is what you need; note - at first it seems to be about position of form window, but be sure to read the Remarks paragraph.
    EDIT - I'm not sure what the trigger would be though. Perhaps a mouse event? Perhaps the same as whatever was used for the code you already have.
    EDIT2 - A link is usually a good idea in a post (re: Leban's code) so that no one proposes what you already tried. Perhaps it uses API calls and hasn't been updated (re: ptrSafe) which shouldn't be too hard to do. I presume it is this one, http://www.lebans.com/setgetsb.htm

    which supposedly supports horizontal scrolling. Assuming you don't find updated code, you could try modifying the API code - research ptrSafe. It has been covered here many times. IMO Colin has the right approach, so if searching here, use Isladogs member name and ptrSafe.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Quote Originally Posted by Micron View Post
    Never used either CurrentSectionLeft or top properties but I believe this is what you need; note - at first it seems to be about position of form window, but be sure to read the Remarks paragraph.
    EDIT - I'm not sure what the trigger would be though. Perhaps a mouse event? Perhaps the same as whatever was used for the code you already have.
    EDIT2 - A link is usually a good idea in a post (re: Leban's code) so that no one proposes what you already tried. Perhaps it uses API calls and hasn't been updated (re: ptrSafe) which shouldn't be too hard to do. I presume it is this one, http://www.lebans.com/setgetsb.htm

    which supposedly supports horizontal scrolling. Assuming you don't find updated code, you could try modifying the API code - research ptrSafe. It has been covered here many times. IMO Colin has the right approach, so if searching here, use Isladogs member name and ptrSafe.
    Thanks Micron. That was the article. I didn't actually look at it, just read the forum threads about it, and they all seemed to feel it didn't work with Access 2007 and above. Don't think the ptrSafe thing is the culprit, as that in my experience is more related to 64-bit Office / PCs. I've had to change all my declarations to use ptrSafe since I upgraded Office to 64-bit. I found this document, https://www.cadsharp.com/docs/Win32API_PtrSafe.txt, which has been invaluable in that effort. SO easy to use Find to get the proper API declaration and simply replace existing declarations with that (along with any needed variables of course).

    Most of the forum posts I read were more related to scrolling list boxes, not forms. But, I will try loading the Lebans code project and see what I can do with it. The triggering event is indeed the concern. I think access scrollbars do not have an hWnd value if I remember correctly, and don't trigger an event when you move them.

    Update:
    Downloaded the Lebans database, and can't even open it since it's an MDB. Help says try to compact it first, which I did. The compact command won't work on an older version of Access database either, even though I specified to save the new one using the dbVersion120 argument. So stuck on that...

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    in my experience is more related to 64-bit Office / PCs
    Ja, but if you posted what Office version you were using I missed that so I took a wild guess that could be the issue. If the Lebans db is v97, Isladogs or somebody else might upgrade it in stages - I can tell you that I cannot open it with 365 either. I do have v97 on another laptop but sorry, I don't have the time to look into it. Tomorrow I leave for Miami so I'll be limited as to what I can do (last minute things, you know). Or maybe Google knows where a newer version is.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I just found this thread after getting an email from the OP

    I haven't converted Stephen Lebans' code to a new version or updated his APIs to work in 64-bit.
    Both are possible but converting Stephen's code can be hard work as he uses so many (often obscure) APIs

    Luckily there is a much simpler approach to scrolling 2 subforms together which requires no code!
    See attached example app and screenshots below

    Click image for larger version. 

Name:	Horiz1.png 
Views:	47 
Size:	47.1 KB 
ID:	48101

    Click image for larger version. 

Name:	Horiz2.png 
Views:	47 
Size:	31.9 KB 
ID:	48102

    A similar method works for synchronising vertical scrolling
    Attached Files Attached Files
    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

  6. #6
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Thanks Micron & Isladogs for all of your help.

    SO much easier to use subform-within-subform to accomplish this without any code.

    I opted to embed the second subform inside of the first subform's footer instead of using a top-level subform and putting both other subforms inside that. Various reasons, not the least of which is that the 2-subforms-inside-subform method keeps both subform vertical scrollbars way off screen, and only visible if I scroll the containing subform to the right all the way. I was not able to find a method of forcing the vertical scrollbar to the left side of the form. It can be done with controls within a form, but not for the form itself, which has no ScrollbarAlign property.

    The subform-2-within-subform-1-footer method at least shows the first subform's vertical scrollbar all the time. Still have to scroll the first subform horizontally to see the second subform's vertical scrollbar, but that's less important to me. My code already selects the appropriate record in the second subform when I click on a record in the first subform, so scrolling is not as important.

    All things being equal, the Lebans solution, even if lousy with API calls would work better visually, but I'm quite willing to accept the limitations of the no-code version.

    Again, thanks so much for all the suggestions!

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I have also tried putting the second subform in the footer of the first but found getting the layout right was problematic

    However its very easy to move the vertical scrollbars to the left side by changing the orientation property to right to left
    on both subforms:

    Click image for larger version. 

Name:	LeftScrollbars.PNG 
Views:	41 
Size:	36.9 KB 
ID:	48107

    Does that achieve what you want?
    Note that the vertical scrolling isn't synchronised though that can be achieved in a different way if you need to do that

    Also in case its of any use to you, attached is Stephen Lebans utility updated to A2000 MDB so it can be opened in current versions of Access. I haven't modified any code or tested it so it may not be fully functional
    Attached Files Attached Files
    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

  8. #8
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Quote Originally Posted by isladogs View Post
    I have also tried putting the second subform in the footer of the first but found getting the layout right was problematic

    However its very easy to move the vertical scrollbars to the left side by changing the orientation property to right to left
    on both subforms:

    Click image for larger version. 

Name:	LeftScrollbars.PNG 
Views:	41 
Size:	36.9 KB 
ID:	48107

    Does that achieve what you want?
    Note that the vertical scrolling isn't synchronised though that can be achieved in a different way if you need to do that

    Also in case its of any use to you, attached is Stephen Lebans utility updated to A2000 MDB so it can be opened in current versions of Access. I haven't modified any code or tested it so it may not be fully functional
    Thanks Colin. Never thought of using right-to-left. I was aware of the setting but had no idea what it did. Meantime, I've gone with the second subform in the first subform's footer, and adjusted everything so the columns line up.

    As far as vertical scrolling being synchronized, I do that differently, since the 2 subforms don't contain the same data. No point in trying to sync them. What I DO do is move the second subform to the proper location by triggering an event to locate the record matching that one in the first subform. If it's not there, then I find the next record in the sort order instead. This works fine in my project.

    Thanks so much for including the A2K version of the Lebans utility. I was going to post a request for that very thing. I will look into it and see if I can get it to work using the API techniques. Will post if successful.

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You're welcome.
    The approach you described to 'synchronising' the selected record in each subform is much the same as I use - except I use a hidden textbox to maintain the record selected info.

    Anyway, I wrote up my solutions at Synchronise SubForm Scrolling (isladogs.co.uk)
    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

  10. #10
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Quote Originally Posted by isladogs View Post
    You're welcome.
    The approach you described to 'synchronising' the selected record in each subform is much the same as I use - except I use a hidden textbox to maintain the record selected info.

    Anyway, I wrote up my solutions at Synchronise SubForm Scrolling (isladogs.co.uk)
    I don't use a textbox, because I call a subroutine on the second subform and send the identifying information from the selected record on the first subform as an argument to that subroutine, no need to store the value in a hidden control. Not filtering the second subform in any way, just selecting a record on it and scrolling the continuous form to that record.

  11. #11
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    I really appreciate you sending that Lebans project. I was able to get it to work after fixing all the PtrSafe declarations. His code still wasn't working properly, but a little tinkering got it to work, at least the part that returns a value representing how far the scrollbars were scrolled.

    Next step, getting the SET part to work properly, so the second subform can be auto-scrolled horizontally to match the first subform.
    One big challenge is how to recognize that the first scrollbar was indeed scrolled. No event is triggered that I can find. One option: The Lebans code uses a form timer set to 250 and executes the code each timer event to get the scrollbar location. Not sure how efficient that is, seems like it uses resources. Also, a given project may already have a form timer, which could conflict. Of course I would put the timer on the first subform rather than the hosting form.

    Is there a more resource friendly way you can think of to trigger the code?

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I haven't studied Stephen's code but from your description that sounds like the simplest approach....and possibly the only approach.
    If you do get this working, I'd be interested to see the solution. Even if you fail, a converted version of Stephen's code would be interesting.

    Good luck with your project
    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
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Here is my port of the Stephen Lebans project. His project didn't work without some tweaks. I also simplified it considerably.

    I chose to put my functionality in a Class rather than a Module.
    The main reason for this was to add an API timer to the class so it would be able to continuously test the positions of the ScrollBars and compare them to the previous positions, and if different, allow automatic synchronizing of the other SubForm's Scrollbar positions. This is because an Access Form or SubForm does not fire an event when you change the Scrollbar. I also wanted to have the timer at the class level so as not to conflict with any timers that the existing forms were using for other purposes.

    This last functionality is NOT implemented yet. The sample project simply allows for getting / setting the Scrollbar position programmatically or force sync the 2 SubForm Scrollbars by clicking a button on the main form.

    Please feel free to check out the attached project and supply suggestions or identify issues.

    Thanks again for all of the help and suggestions.

    GetSetScrollbars.zip

  14. #14
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi Ron
    Thanks for posting your version.
    I've tried it with mixed results
    a) 32-bit Access 365 - I get error 453 at startup
    Click image for larger version. 

Name:	error453.PNG 
Views:	28 
Size:	5.3 KB 
ID:	48124

    Debug highlights the line for the horizontal scrollbar position
    Click image for larger version. 

Name:	debug.PNG 
Views:	28 
Size:	7.8 KB 
ID:	48125

    When the form opens the textboxes are blank and the form is non-functional

    b) 64-bit Access 365 - it worked perfectly! Well done

    So I looked at the code and made 2 changes

    In modSyncScrollbars I added conditional compilation using Win64 compiler (which normally I never use

    Code:
    #If Win64 Then
        Declare PtrSafe Function GetWindowLongPtr Lib "user32" Alias "GetWindowLongPtrA" (ByVal hwnd As LongPtr, ByVal nIndex As Long) As LongPtr
    #Else 'added by isladogs
        Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long) As Long
    #End If
    Then a linked change in clsSyncScrollbars
    Code:
       ' scrollbar found, get style and verify it's the one we want                ' (horiz = 137573172, vert = 137573173)
                    #If Win64 Then
                        sbStyle = GetWindowLongPtr(hWndSB, GWL_STYLE)
                    #Else  'added by isladogs
                        sbStyle = GetWindowLong(hWndSB, GWL_STYLE)
                    #End If
    It now works in 32-bit Access as well - see attached
    I expect there is a better way of doing this than the above but I haven't time to look at it further at the moment
    Attached Files Attached Files
    Last edited by isladogs; 06-27-2022 at 10:20 AM.
    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
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Wow, thanks Colin.

    I've seen many examples with conditional compilation declarations like that, but they usually are not necessary, as PtrSafe declarations don't normally cause problems in the 32-bit installations. I guess this particular API is an exception.

    It seems like you should also put conditional compilation around the declaration of hWndSb variable, as the 2 different APIs have different types for that argument. I'm surprised it worked as-is. I usually get errors when converting to PtrSafe APIs if any of the arguments are of the wrong type. I don't have access to a 32-bit envioronment, so can't test that. Would it hurt, in your opinion, to do that anyway, just in case?

    Thanks again for all your help.

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

Similar Threads

  1. Replies: 53
    Last Post: 07-16-2022, 12:02 PM
  2. Replies: 4
    Last Post: 01-16-2018, 02:51 AM
  3. Replies: 0
    Last Post: 12-29-2016, 11:19 PM
  4. Replies: 2
    Last Post: 03-14-2014, 05:42 PM
  5. Vertical scrollbar issues with a subform
    By vange2013 in forum Forms
    Replies: 1
    Last Post: 11-27-2013, 09:13 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