Controlling scrollbar position

[SyntaxSocialist]

So I've got a form:
- A set of unbound controls filters (using a dynamically-built WHERE statement) a subform in datatable view.
- A set of bound controls displays the currently selected subform record; the two reflect one another.
- A set of unbound checkboxes at the bottom allows the user to change which fields are displayed in the subform.

Turns out, all of this makes for a pretty big form. Some of the users have screens that will not fit the full form due to resolution settings, physical size, aspect ratio, etc. Whatever the reason, the result is one or both scrollbars appearing on the form.

Fine.

The issue is this: Whenever the filter controls are changed, the form's Current event runs (because the After Update events for the filter controls all change the main form and subform's Recordsource). But the Current event causes the form to requery (or refresh?), meaning the focus returns to the control with Tab Index 1 (a "Home" button near the top of the form).

Thus, the form tends to "jump around" a lot when users are determining the records they want displayed.

Is there a way to avoid this?
- Can I prevent the "Home" button from getting the focus during those times?
- Alternatively, can I set the position of the scroll bars so the user doesn't see that jumping around every time a control is updated? I found some interesting ideas that worked for continuous forms, but mine is a single form, so bookmarking methods won't solve my issue.

I found this but it's a little over my head and a little dated. Not sure if it's maybe close to what I'm looking for...

Any ideas?

 

[SyntaxSocialist]

Ok, so I've made some progress here: I can just set the tabstop property to false for all the controls on the form (or, alternatively, disable all the controls), and then set them all back to their original state, I'll be all set

Problem is, I'm not familiar with how to do that kind of batch property change...

EDIT: I have the impression this should work, but it's telling me that the Access.Control object "ctrl" doesn't support the TabStop property...

    Dim ctrl As Access.Control

    For Each ctrl In Me.Controls
        ctrl.TabStop = False
    Next

 

[CJ_London]

Not all controls have a tabstop property - such as labels

Here is a link that will assist

http://msdn.microsoft.com/en-us/library/office/aa224135(v=office.11).aspx

your code will need to be something like:

For Each ctrl In Me.Controls
        if ctrl.controltype<> acLabel then ctrl.TabStop = False
Next

Another control without a tabstop property would be a line

 

[SyntaxSocialist]

Not all controls have a tabstop property - such as labels

Here is a link that will assist

http://msdn.microsoft.com/en-us/library/office/aa224135(v=office.11).aspx

Thanks! I was literally JUST looking at that So I've made some more progress. But it's got me stumped again:

When I put Me.btnHome.TabStop = False at the top of the AfterUpdate event, and Me.btnHome.TabStop = True at the bottom, it seems to do the trick. So that's the concept proven. YAY!

But when I put this at the top:

    For Each ctrl In Me.Controls
        If ctrl.ControlType = acCommandButton Or ctrl.ControlType = acComboBox _
            Or ctrl.ControlType = acTextBox Or ctrl.ControlType = acCheckBox _
            Or ctrl.ControlType = acOptionGroup Then
                ctrl.TabStop = False
        End If
    Next

and this at the bottom:

    For Each ctrl In Me.Controls
        If ctrl.ControlType = acCommandButton Or ctrl.ControlType = acComboBox _
            Or ctrl.ControlType = acTextBox Or ctrl.ControlType = acCheckBox _
            Or ctrl.ControlType = acOptionGroup Then
                ctrl.TabStop = True
        End If
    Next

it somehow manages to "tab" back to by Home button (btnHome)... which is definitely an acCommandButton...

HUH?!

EDIT: To add the the conundrum, Debug.Print Me.btnHome.TabStop prints "False"

EDIT 2: When I remove the 2nd section of code (that reinstates the TabStop property), it again manages to somehow "tab" back to btnHome (which has a TabStop value of "False"). Then, pressing the TAB button on my keyboard makes my bound controls (that display the current record) go to the next record.

EDIT 3: Using the Enabled property instead of TabStop works as expected. But the controls turn grey for a moment while the procedure is executing. This is ugly, and probably a bit scary for anyone who doesn't understand why it's doing that (like, say, all the users ). I can use this solution, but I'd prefer to use the TabStop method.

 

[CJ_London]

pressing the TAB button on my keyboard makes my bound controls (that display the current record) go to the next record.

Change the Cycle property for the form to current record or current page, depending on how your form is set up (you'll find it under the Other tab)

 

[SyntaxSocialist]

Change the Cycle property for the form to current Record or current page, depending on how your form is set up (you'll find it under the Other tab)

No luck. Current record did a good job of stopping the tab key from cycling through any records when the 2nd section of code was commented out, but didn't stop btnHome from getting the focus.

Current Page did nothing.

But good news! I discovered that the problem lay in the fact that the control triggering the form requery (refresh?) was also having its TabStop property changed. I guess that can make things wonky. No matter, since the control that is clicked to trigger the requery (refresh?) will always be in full view. The form will "tab" to it, since it's the only tab-able control available, but if anything, that's better than the way it was before!

Solution:

At the start of the AfterUpdate event:

    Dim ctrl As Access.Control
    [B]Dim strActiveCtrl As String
    strActiveCtrl = Screen.ActiveControl.Name[/B]

    For Each ctrl In Me.Controls
        If [B][COLOR="red"]ctrl.Name <> strActiveCtrl And ([/COLOR][/B]ctrl.ControlType = acCommandButton _
            Or ctrl.ControlType = acComboBox Or ctrl.ControlType = acTextBox _
            Or ctrl.ControlType = acCheckBox Or ctrl.ControlType = acOptionGroup[B][COLOR="Red"])[/COLOR][/B] Then
                ctrl.TabStop = False
        End If
    Next

And at the end:

    For Each ctrl In Me.Controls
        If [COLOR="red"][B]ctrl.Name <> strActiveCtrl And ([/B][/COLOR]ctrl.ControlType = acCommandButton _
            Or ctrl.ControlType = acComboBox Or ctrl.ControlType = acTextBox _
            Or ctrl.ControlType = acCheckBox Or ctrl.ControlType = acOptionGroup[COLOR="red"][B])[/B][/COLOR] Then
                ctrl.TabStop = True
        End If
    Next

 

[Mihail]

Maybe THIS will help you in order to restore the focus to the last control you use.

 

[SyntaxSocialist]

Maybe THIS will help you in order to restore the focus to the last control you use.

Thanks

But I actually don't even need that. Because the only control that doesn't get its TabStop property changed ends up being the control that the form "tabs" to on refresh, it gets that focus back, anyway!

HUZZAH!