VBA - How do I say next visible control in tab order

[Zak14]

How do I get VBA to say:
Go to the first control in the tab index after the current active control (that is visible atm),
except such and such controls (because they will lose visibility in a moment).

 

[GinaWhipp]

I'm not sure what you are asking, if the Control is not visible it won't stop there. The line to make it invisible should be in the After_Update event so that once the value is changed tabbing should make it skip. Are you experiencing something else?

 

[Zak14]

I've got an expand/collapse section of my form set up.
When you click the expand/collapse button, if the focus is on anything that is about to collapse, currently the focus changes to the next field in the tab order ('txtAddress' which I had to specify).

If I decide to change the layout of the form or even make txtAddress collapsible, the focus won't go to the correct place.

I want to tell the code to go to the next control in the tab order (without having to specify which control that is).
It must be a visible control that the focus goes to, because I will add more collapsible sections in the form (and some controls may be invisible from time to time).
It shouldn't take the focus to anywhere in the current 'about to collapse' tab group: "ExpandCollapseSection1".

Here is the post which is an example of what I'm doing.
Here is a download of the example form.

 

[GinaWhipp]

This...

I want to tell the code to go to the next control in the tab order (without having to specify which control that is)

...is the problem. One way or the other you have tell the code which Control to Tab to. You might want to include something using the Tag property of the Control. Nothing else comes to mind.

Side note: Nice concept!

 

[Zak14]

Thanks.

How can I adapt what I said in post #3 using tag properties then?
Dw, if you see my code in the db I linked to, you'll see I can have multiple tags because I use the If InStr function rather than If ctl.Tag = "tag"

You can download my db form example from post #3 if it helps.

Thank you.

 

[GinaWhipp]

I have looked at your database but not read the code. I was making a suggestion as to how you *might* be able to adapt your code. Sorry for the misunderstanding, but I was not making the offer to adapt your code.

 

[Zak14]

I wasn't asking you to adapt my code.
I was asking how is it that I can "adapt what I said in post #3 using tag properties".

My underlying question is
How do I say "next visible control in tab order"?
or even just "next control in tab order".
If not that, how can I say "last control in tab order".
Or "if ctl is after activecontrol".
Any one of these will help me.

 

[GinaWhipp]

Oops, sorry, my misunderstanding... Try looking at the TabIndex Property. The code below (untested) should determine the next Tab Stop, with some adapting it might give you what you need...

Dim ctl As Control
For Each ctl In Me.Controls
If ctl.TabIndex = Me.ActiveControl.TabIndex + 1 Then
ctl.SetFocus
Exit For
End If

 

[Zak14]

Oops, sorry, my misunderstanding... Try looking at the TabIndex Property. The code below (untested) should determine the next Tab Stop, with some adapting it might give you what you need...

Dim ctl As Control
For Each ctl In Me.Controls
If ctl.TabIndex = Me.ActiveControl.TabIndex + 1 Then
ctl.SetFocus
Exit For
End If

I thought of that approach, but what if it's not +1; what if it's +2 or 3 or vice versa.
I understand that your code says "next tab stop", but is there a way to say "first visible tab index after the activecontrol"

 

[Zak14]

I just wrote the following:

    If InStr(1, Me.ActiveControl.Tag, "ExpandCollapseSection1") <> 0 Then
        For Each ctl In Me.Controls
            If Not InStr(1, ctl.Tag, "ExpandCollapseSection1") <> 0 Then
                If ctl.Visible = True Then
                    ctl.SetFocus
                    Exit For
                End If
            End If
        Next
    End If

This says:

    If focus is in one of the 'to be collapsed' controls, then
        for every control in the form
            If the control is not one of the the 'to be collapsed' controls, then
                If it is visible, then
                    Set Focus to this control
                    Stop the loop here after you set focus to a control

This sets focus to the first visible control in the form which is the form logo. I want to set focus to the next control from the 'to be collapsed' section.

 

[GinaWhipp]

Yeah, I don't know, that is what I am trying to esplain, I have not given thought as to the *how* part.

 

[Zak14]

Yeah, I don't know, that is what I am trying to esplain, I have not given thought as to the *how* part.

Is there any way to say any of these? I'm sure there must be.

"last control in tab index"
"if ctl is after activecontrol"
"first visible tab index after the activecontrol"

Because then I can alter it to suit my specs.

 

[Zak14]

Ok I tried this. Why doesn't it work? I'm confident that there's a problem in the tabindex line (red). I don't think access recognizes this code, at least in my case.

    If InStr(1, Me.ActiveControl.Tag, "ExpandCollapseSection1") <> 0 Then
        For Each ctl In Me.Controls
            [COLOR="Red"]If ctl.TabIndex = Me.ActiveControl.TabIndex + 1 Then[/COLOR]
                ctl.SetFocus
                If Not InStr(1, ctl.Tag, "ExpandCollapseSection1") <> 0 Then
                    If ctl.Visible = True Then
                        Exit For
                    End If
                End If
            End If
        Next
    End If

It says:

    If the active control is in the 'to be collapsed' section, then
        For each control in this form
            [COLOR="red"]If this control's tab index is 1 greater than the active control's tab index, then[/COLOR]
                Set focus to this control
                If this control is NOT in the 'to be collapsed' section, then
                    If this control is visible, then
                        Exit the for command (loop) here
                    End If
                End If
            End If
        Next
    End If

 

[GinaWhipp]

Hmm, thinking out loud...

If the Control is invisible it might be better to actually *set* the Tab Index. Telling it to go to the next one doesn't help if the the next one is invisible. So, give it the Tab Index of the Control you want to land on. Make sense?

And again, I am not testing this I am just throwing out ideas.

 

[CJ_London]

If your controls are stacked vertically and the next control, is the next one that is visible below the current control, then you could use the control top property to determine which contol is next - it will be the one with the lowest top value which is greater than the top+height of the current control and visible.

Something like

private sub findnexttab()
dim nearest as single
dim ctrl as control
dim ctrlname as string
 
    lowest=me.insideheight 'just set a max value to measure against
    ctrlname=""
    for each ctrl in me.controls
        if ctrl.controltype<>aclabel then 'labels can't have focus
            if ctrl.top>(me.activecontrol.top+me.activecontrol.height) and ctrl.top-me.activecontrol.top<nearest and ctrl.visible=true then 'this is the the nearest visible so far
                ctrlname=ctrl.name 'set the name of the control to have the focus
                nearest=ctrl.top-me.activecontrol.top 'reduce nearest to lower figure
            end if
        end if
    next
    if ctrlname<>"" then me(ctrlname).setfocus 'otherwise do nothing
 
end sub

 

[CJ_London]

If InStr(1, Me.ActiveControl.Tag, "ExpandCollapseSection1") <> 0 Then
For Each ctl In Me.Controls
If ctl.TabIndex = Me.ActiveControl.TabIndex + 1 Then

The reason why your code is failing is that not all controls have a tabindex - for example labels.

 

[Zak14]

The reason why your code is failing is that not all controls have a tabindex - for example labels.

Thanks, I realised that and had already changed it and added these lines.

            If ctl.ControlType = acTextBox Then
                If ctl.TabStop = True Then

But is there a way to determine if the control has a tabindex property? That would be much more efficient then specifying what type of control I want it to move to.

 

[CJ_London]

only by trying to refer to it and generating an error when it doesn't exist, then handling the error

on error go to errctrl
for each ctrl in me.controls
    if ctrl.tabindex=1 then
        ...
        ...
 
 
 
errctrl:
msgbox err.number & " " & err.description

 

[MSAccessRookie]

Have you considered the TAB Stop Property? I believe you should be able to use that to skip a Field despite the Tab Index Value of the Field. I assume from the conversation that you are using the Visible Property to hide the Fields, so whenever you set the Visible Property = NO, also set the Tab Stop Property = NO (and vice/versa). See if that helps you out. I think it might be a step in the right direction.

-- Rookie

 

[The_Doc_Man]

Maybe I'm more than a little dense, but when I run into this problem the thing that comes to mind is this:

Let's say you have controls X1, X2, and X3, and for some reason X2 is going away for a while and you want to skip it.

If you are in control X1 and tab out of it, the event that fires first is the X1_LostFocus event, and at that point, if you do nothing, X2 is next - but you have not yet selected it. If you take action in X1's _LostFocus event to make X2 have .TabStop=No, then you can just let the _LostFocus code hop out and "do its thing." If X2 is no longer eligible as a tab stop when you execute the End Sub of the _LostFocus but X3 IS eligible, it just hops to X3 as though that was the right place. The trick is that if you wait until the X2_GotFocus routine, you are screwed because it is too late.

I can't say I've discarded whole sections before, but I have a bunch of forms that enable and disable selected controls from the _LostFocus routines and they work correctly without me ever looking at the TabIndex properties. I just have to manipulate the .TabStop property according to what is staying or vanishing.

Now, there IS a trick to this - in the sense that if you are really talking about "sections" on a form, you have to look at a control's .Parent property to determine what section it is in when you start manipulating other properties.

Hope this helps.