Programatically change a sub form's hotizontal scroll

[Lhoj]

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!

[Bob Fitz]

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.

[Lhoj]

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

[Lhoj]

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.

    Me.subForm.Controls(0).Enabled = True
    Me.subForm.Controls(0).SetFocus
    Me.subForm.Controls(0).Enabled = False

Thanks again Bob!

[Micron]

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.

[Lhoj]

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.

[Lhoj]

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.

[orange]

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.

[Micron]

Check your tab order. I suspect you'll find it is 0 on the control for that datasheet field (as Bob suggested).

[Lhoj]

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

[Micron]

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.

[Lhoj]

Thanks Micron, I will try that property next chance I have, it will definitely save a lot of time