Solved Move/Tab to next control

[oxicottin]

Hello, I need to automatically move to the next control in my tab order on my form using VBA. What can I use to accomplish this?

Note: This isn't moving between Tabs in a Tab Control this is just moving to the next control using VBA...

 

[theDBguy]

Can you use either SetFocus or GoTo Control?

 

[oxicottin]

Im trying to figure out how to move to next control after a selection from a popup. I cant use setfocus or go to control because the popup is used for a few different controls and i wouldn't know what controls next

 

[theDBguy]

Im trying to figure out how to move to next control after a selection from a popup. I cant use setfocus or go to control because the popup is used for a few different controls and i wouldn't know what controls next

See if you can determine the next control from the form's tab order settings.

 

[MajP]

This seemed to work

Public Sub GotoNext(ctrl As Access.Control)
  Dim idx As Integer
  idx = ctrl.TabIndex
  For Each ctrl In Me.Controls
    Select Case ctrl.controlType
      Case acTextBox, acListBox, acComboBox, acCheckBox
        If ctrl.TabIndex = idx + 1 Then
           ctrl.SetFocus
           Exit Sub
        End If
    End Select
  Next ctrl
End Sub

pass in the current control you are on.

 

[arnelgp]

you should also add validation if the "next" control allows you to Tab by checking it's TabStop property and that it is Enabled.

 

[MajP]

As @arnelgp points out this needs to get updated. Have not tested it but you can have "no tab stop". Assume you are on control with tab index 10 and 11 and 12 have tab stop set to no. Your next "tab" you want is then 13 if that exists.

 

[cheekybuddha]

It might also be wise to use a different object variable for looping the .Controls collection in case ctrl is used subsequently in the calling code.

Additionally, the +1 will need to be incremented for every control skipped for being TabStop = False or Enabled = False

 

[MajP]

This should handle tab stops set to no. This would be easier to do in a dictionary where you could store the sorted controls with the Index of the Control and the actual Control. Then you would just loop the dictionary and return the first index greater than your current index.

This works though.
1. Create a sorted collection of the available indexes that have tab stops.
2. Loop that list and find the next available

Public Function GetSortedTabControls(frm As Access.Form) As Collection
  Dim ctrl As Access.Control
  Dim AvailableTabs As New Collection
  Dim I As Integer
  For Each ctrl In frm.Controls
    Select Case ctrl.ControlType
      Case acTextBox, acListBox, acComboBox, acCheckBox
        If ctrl.TabStop = True Then
          If AvailableTabs.Count = 0 Then
            AvailableTabs.Add ctrl.TabIndex
          Else
          
           For I = 1 To AvailableTabs.Count
                 If ctrl.TabIndex < AvailableTabs(I) Then
                  AvailableTabs.Add ctrl.TabIndex, I
                  Exit Function
                 End If
                If I = AvailableTabs.Count Then AvailableTabs.Add ctrl.TabIndex
           Next I
        
         End If
      
       End If
     End Select
  Next ctrl
  Set GetSortedTabControls = AvailableTabs
End Function

Public Function GetNextControl(frm As Access.Form, ActiveCtrl As Access.Control, AvailableTabs As Collection) As Control
  Dim I As Integer
  Dim Active_idx As Integer
  Dim ctrl As Access.Control
  Dim nextIDX As Integer
  Active_idx = ActiveCtrl.TabIndex
  nextIDX = Active_idx
  For I = 1 To AvailableTabs.Count
    If AvailableTabs(I) > nextIDX Then
     nextIDX = AvailableTabs(I)
      Exit For
    End If
  Next I
 
  For Each ctrl In frm.Controls
    Select Case ctrl.ControlType
      Case acTextBox, acListBox, acComboBox, acCheckBox
        If ctrl.TabIndex = nextIDX Then
          Set GetNextControl = ctrl
          Exit Function
        End If
     End Select
  Next ctrl
 
End Function

In the form

Private AvailableTabs As Collection
Private Sub Form_Load()
  Set AvailableTabs = mdlNextControl.GetSortedTabControls(Me)
End Sub
Public Function GoToNext()
  Dim ctrl As Access.Control
  Set ctrl = GetNextControl(Me, Me.ActiveControl, AvailableTabs)
  ctrl.SetFocus
End Function

 

[oxicottin]

@MajP I'm getting variable not defined on the On load events "mdlNextControl"

Private Sub Form_Load()
Set AvailableTabs = mdlNextControl.GetSortedTabControls(Me)

 

[MajP]

That was the name of my module. You can delete that or rename the standard module to that. If you put the code in your form then you would have to delete.

 

[twgonder]

Here I am in April of 2024. With a big thanks to MajP for taking the time to answer this question years ago.
This is exactly what I didn't know that I needed for a variety of different tasks in my forms that zig or zag based on the entry controls.
And it's great to get a good example of how to use a collection.

But I have to ask, as you use I as a counter. It's funny, in the first BASIC I learned on a teletype, you could only use one letter variables. And everyone used I% for the for...next iterations. Old BASIC or just an archaic convention that still carries on? (I use lcnt{n} these days).