Solved

Programmatically Tab to Next Control

Posted on 2008-10-13
20
3,085 Views
Last Modified: 2013-11-28
How do you tab to the next control on your form using VBA code in an event subroutine?

I am using Access 2003 on an XP machine.
0
Comment
Question by:dastrw
  • 7
  • 5
  • 4
  • +1
20 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22704803

me.controlname.setfocus

give more info on what you are trying to do
0
 
LVL 2

Author Comment

by:dastrw
ID: 22704843
I have a bunch of forms each of which has some controls/fields that are enabled/disabled, visible/hidden, etc.

I just want to go to the next valid field without having to know what it is.

If I use the SetFocus method then I have to know which field is visible and enabled and not locked which is too complicated at this time.

Thanks.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22704883
you may need to refer to the tab index of the controls.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 2

Author Comment

by:dastrw
ID: 22705042
I want a single generic command that will work with any form.  Something like:

Me.TabNext

or

Me.Controls.MoveNext

I haven't found anything like that yet.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22705065
because those animals are not born yet...
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 22705106
Indeed the control's TabIndex is key (a SendKeys alternative may seem simple by comparison - but it's not worth it).
 

Function fMoveNext()
On Error Resume Next
 
    Dim ctl As Control
    Dim intInd As Integer
    Dim intTest As Integer
    Dim blnEnabled As Boolean
    Dim blnDone As Boolean
    
    intInd = Nz(Me.ActiveControl.TabIndex) + 1
    
    Do Until blnDone
        intInd = intInd Mod Me.Controls.Count
        For Each ctl In Me.Controls
            intTest = ctl.TabIndex
            If intTest = intInd Then
                blnEnabled = ctl.Enabled And ctl.TabStop = True
                If blnEnabled Then
                    ctl.SetFocus
                    blnDone = True
                    Exit For
                End If
            End If
        Next
        intInd = intInd + 1
    Loop
    
    Set ctl = Nothing
    
End Function

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22705526
dastrw,
just curious as to how/why/where you are planning to apply the function?

when you said, you just want to go to the next field, why do you need to do it in codes?
0
 
LVL 2

Author Comment

by:dastrw
ID: 22705680
So here's the long sad story of why I want to do this:
1) Users are easily confused
2) Access doesn't let you attach menu bars directly to forms.  If you create a menu bar it gets stuck up with all the other general Access menu bars
3) I would like to simulate a menu bar that is attached directly to my forms rather than hiding it up with all the other Access menu bars so the easily confused users can find them.
4) I simulated a menu bar by using a combo box.  The combo box has the text "Select Action..." in it by default.  When the users click on the combo box's drop down arrow all of the possible actions are revealed.  They can then pick one and the form will do what they want.
5) After they pick an action, I replace their selection with "Select Action..." so it looks just like before.

Everything is OK, except after this whole process, the text "Select Action" is 'selected', i.e. it is in reverse-text where the text is white and the background is black.  This looks confusing to the user.

The only solution I have found is to move focus off of the control so that the text is unselected.

So instead of a mechanism to programatically move off the text, I would need something that could either:

1) Attach menu bars directly to the form
2) Unselect the text so it isn't in reverse mode
3) Somehow set the forms so that when users tabbed from field to field the cursor was set to be at the beginning or end of the text rather than selecting the entire text.  I know you can do this for Access as a whole when navigating through tables, but I don't know how you do this for form fields.

I thought about using a button or a graphic that users could click on with an invisible combo box or list box lurking behind it that would pop-up when the button or graphic was clicked on, but that was getting to be a little too convoluted.  What I have works now except for the selected text in the combo box window.
0
 
LVL 8

Expert Comment

by:Joe Overman
ID: 22705854
I might be missing something:
but why not just create a hidden text box and set the focus on it.  After the user selects what he/she wants from your combo box and you reset it the combo box value move the focus to a hidden text box and your text is no longer selected the user is none the wiser.
0
 
LVL 2

Author Comment

by:dastrw
ID: 22705923
I didn't think you could set focus to something that wasn't visible and enabled.  I may be wrong.
0
 
LVL 8

Expert Comment

by:Joe Overman
ID: 22706197
You are right dastrw, guess I lost my head.  But the idea is the same, set the focus one some other object other than combo box.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22706235
that was the very first suggestion...
0
 
LVL 8

Expert Comment

by:Joe Overman
ID: 22706359
Capricorn is right, after rereading we have the same idea and path.
0
 
LVL 2

Author Comment

by:dastrw
ID: 22706492
I could create a "virtually" hidden field.  This would be a very very small transparent field up in one corner of the form.  The only downside would be that if someone tabbed through all the fields they would end up there and wonder what it was.

It turns out I have Help buttons on all of the forms, and my temporary solution is just to set focus to the help buttons which will always be enabled.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 22706539
Was there something wrong with the function I suggested?
I believe it's tested... :-s
I personally dislike small / hidden controls - I find them to be hackish.
Your Help button is an option - but I don't see why when the next control in sequence is a available...
0
 
LVL 8

Expert Comment

by:Joe Overman
ID: 22706545
You can also turn the tab stop off on that virtual field.
0
 
LVL 2

Author Comment

by:dastrw
ID: 22706564
LPurvis,

Don't worry.  You're gonna get all the points as you answered my question.

I'm just keeping this open in case someone has some more good ideas.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 22706743
lol if it was the points I was worried about I dare say I'd strive for a bit more participation :-)
But until now it looked like you'd entirely overlooked the possibility!
And I don't really see what the alternative is given the requirement to "go to the next valid field without having to know what it is."
All your forms might have a Help button - and that's all well and good.  But assuming as much then *requires* that your forms do.  The only danger with the function as it stands is if you had a form with *no* enabled or Tabstopped controls (but that can easily be trapped for too).
The user's experience (and an understandable progression of events for them) is always important to keep in mind.  That's why we set tab orders in the first place.
But by all means - consider alternatives.  Indeed I'd always encourage you to do so.
(I still don't like hidden controls for focus setting though ;-)
0
 
LVL 2

Author Comment

by:dastrw
ID: 22706897
I agree with you on the focus setting and hidden objects.  I wish I could write more straightforward code.

It seems that on a number of occasions Microsoft lets you drag the horse almost to where the water trough is, but not quite close enough for him to get a drink.

Then you have to somehow teach the horse how to use a straw.
0
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 500 total points
ID: 22707003
There's some truth to that.
But to consider this instance by way of example - the function could (and really should) be made into a generic one - such that you insert it once into a standard module in your application and then use it from any form you choose as if it were a built in piece of functionality.
You then just call
    fTabNextControl Me
from any form you choose to navigate to the next control.  
It's the ability to do things like this which extends an environment like Access beyond some end user tool for creating basic utilities.  
The requirements that are achievable with a little bit of code implemented as a standard function I'd rather see stay that way and MS concentrate on implementing new and more exciting options (or otherwise unachievable functionality).
Though if it's not much effort for us - it could be argued that it probably wouldn't be for them either ;-)

Function fTabNextControl(Optional pfrm As Access.Form)
On Error Resume Next
 
    Dim ctl As Control
    Dim intInd As Integer
    Dim intTest As Integer
    Dim blnEnabled As Boolean
    Dim blnDone As Boolean
    
    If pfrm Is Nothing Then
        Set pfrm = Screen.ActiveForm
    End If
    
    intInd = Nz(pfrm.ActiveControl.TabIndex) + 1
    
    Do Until blnDone
        intInd = intInd Mod pfrm.Controls.Count
        For Each ctl In pfrm.Controls
            intTest = ctl.TabIndex
            If intTest = intInd Then
                blnEnabled = ctl.Enabled And ctl.TabStop = True
                If blnEnabled Then
                    ctl.SetFocus
                    blnDone = True
                    Exit For
                End If
            End If
        Next
        intInd = intInd + 1
    Loop
    
    Set ctl = Nothing
    
End Function

Open in new window

0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question