Results 1 to 12 of 12
  1. #1
    Lhoj is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2021
    Posts
    57

    Programatically change a sub form's hotizontal scroll

    Here I go with another request



    I have a form with a number of datasheet subforms showing data from different tables. Each subform has its columns' width programmatically resized to fit the text any time the form is requeried. That sometimes causes the form's data to not fit the container and need an horizontal scrollbar to see all the data. I don't want to resize the containers and I have no problem with that since the data is easily checkable with the scroll bar and the user can even manually resize the columns or hide the fields he doesn't want to see. The weird thing is that the forms appear with the horizontal scroll bar in the middle of the form instead of the leftmost part, making the fields to the left (the most important ones) not visible unless the user scrolls the form to the left. Is there any way of doing that in VBA and scrolling the subform all the way to the left after resizing the form?

    Thanks!

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,001
    Perhaps you could set the focus to the field in the first column or perhaps you just need to adjust the tab order of the fields.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    Lhoj is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2021
    Posts
    57
    Yeah, I thought of that. I might try to set the focus on the first field for every subform after resizing just in case, then set it on the field of the main form I need it to be. I was just hoping there would be some sort of magical Form.SetHorizontalScrollLeft or similar :P

    Thx Bob

  4. #4
    Lhoj is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2021
    Posts
    57
    Well, got it working. I have the fields of the subforms disabled individually because I don't want to allow the user even clicking the cells and entering data (even if edits are disallowed on the form) and I still want to let them select records to interact with other main form buttons, so I couldn't disable the whole subforms. So I basically had to manually enable the first control, set the focus, then disable it again for every subform, but it works. A shame there's apparently no easier way around it. I'll leave the code I added in case it helps anyone with a similar problem.

    Code:
        Me.subForm.Controls(0).Enabled = True
        Me.subForm.Controls(0).SetFocus
        Me.subForm.Controls(0).Enabled = False
    Thanks again Bob!

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    9,896
    If we could see the db we'd probably deduce that no 'magic' is required, just eliminating whatever is setting the focus to a field so far to the right that the form opens to a scrolled position. I know that setting focus to a record far enough down will cause a vertical scroll so I don't see how horizontal would be any different. Then again, I learn something every day!

    So if you care to explore further, consider copying, compacting and zipping your file and posting it here for analysis.
    Grief o'er the loss of one loved is not a destination, but a journey.
    Oft content upon arrival are we, only to find there are miles yet to travel.

    Micron

  6. #6
    Lhoj is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2021
    Posts
    57
    There's no other code after the resize code that sets the focus anywhere on those subforms. I guess it's the control.columnWidth instruction itself that actually sets the focus on the field, what I do not get is why it would end up focused on a field in the middle of the form and not the rightmost one if that's the case. I'll have to cycle through the fields names to see what order is it cycling them through and see if that sheds some light, then again it's working alright setting the focus to the leftmost field after the resizing, so that's ok.

  7. #7
    Lhoj is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2021
    Posts
    57
    A little update on me trying to figure out why the focus is set on that middle field of the subform. I've disabled the resizing code and even the custom form placement code that's executed on the main form's load and the focus remains on the same field even now that no code is run when the forms load. Then I tried altering a couple more subforms that are lighter on the fields, making them absurdly bigger so they won't fit the container regardless and all of them seem to keep the focus on the leftmost field. So my question now is, is there a way to set the focus on a certain field through the form's properties or the field's properties? That is the only thing I can see messing the focus on that subform, but I see no property on either that would set the focus there.

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,767
    As has been suggested, post a copy so readers can experience the issue in context.
    Compact/Repair and zip along with instruction details to highlight the issue.

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    9,896
    Check your tab order. I suspect you'll find it is 0 on the control for that datasheet field (as Bob suggested).
    Grief o'er the loss of one loved is not a destination, but a journey.
    Oft content upon arrival are we, only to find there are miles yet to travel.

    Micron

  10. #10
    Lhoj is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2021
    Posts
    57
    It wasn't the order, I had already cycled the fields when resizing and it cycled them in the same order they are on the form. But how dense am I lately? Turns out it was the last field I added, some time after the others and was the only enabled field on the form (I swear I must have checked a dozen times and missed the fact a dozen times) and it seems it automatically sets the focus on the first enabled field of the form if there's any, unless programmatically told otherwise. It's always the smallest silliest thing, isn't it?

    Thank you all for your help

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    9,896
    Good grief. It all seems so obvious now. Yes, the focus moves to the first enabled control in the tab order. Perhaps all you really needed to do was set the form recordset type property to snapshot. You can interact with bound controls but can't change anything.
    Grief o'er the loss of one loved is not a destination, but a journey.
    Oft content upon arrival are we, only to find there are miles yet to travel.

    Micron

  12. #12
    Lhoj is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2021
    Posts
    57
    Thanks Micron, I will try that property next chance I have, it will definitely save a lot of time

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

Similar Threads

  1. Replies: 7
    Last Post: 09-02-2016, 03:47 PM
  2. Replies: 2
    Last Post: 09-10-2015, 03:21 PM
  3. scroll bar in form
    By johnnyc11 in forum Forms
    Replies: 5
    Last Post: 06-07-2013, 07:30 AM
  4. Help fixing a code to change password programatically
    By smartflashes in forum Programming
    Replies: 3
    Last Post: 01-19-2012, 10:20 PM
  5. How To Change Size Of Scroll Bars
    By aamer in forum Access
    Replies: 4
    Last Post: 09-05-2010, 10:55 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