Solved Move/Tab to next control (1 Viewer)

oxicottin

Learning by pecking away....
Local time
Today, 16:28
Joined
Jun 26, 2007
Messages
856
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

I’m here to help
Staff member
Local time
Today, 13:28
Joined
Oct 29, 2018
Messages
21,553
Can you use either SetFocus or GoTo Control?
 

oxicottin

Learning by pecking away....
Local time
Today, 16:28
Joined
Jun 26, 2007
Messages
856
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

I’m here to help
Staff member
Local time
Today, 13:28
Joined
Oct 29, 2018
Messages
21,553
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.
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 16:28
Joined
May 21, 2018
Messages
8,609
This seemed to work
Code:
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:28
Joined
May 7, 2009
Messages
19,246
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

You've got your good things, and you've got mine.
Local time
Today, 16:28
Joined
May 21, 2018
Messages
8,609
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

AWF VIP
Local time
Today, 21:28
Joined
Jul 21, 2014
Messages
2,329
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

You've got your good things, and you've got mine.
Local time
Today, 16:28
Joined
May 21, 2018
Messages
8,609
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

Code:
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
Code:
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

Learning by pecking away....
Local time
Today, 16:28
Joined
Jun 26, 2007
Messages
856
@MajP I'm getting variable not defined on the On load events "mdlNextControl"

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

MajP

You've got your good things, and you've got mine.
Local time
Today, 16:28
Joined
May 21, 2018
Messages
8,609
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

Member
Local time
Today, 15:28
Joined
Jul 27, 2022
Messages
178
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).
 

Users who are viewing this thread

Top Bottom