Returning focus to parent from subform with Tab key

Posted on 2011-09-13
Last Modified: 2013-11-28
I have been hacking away at this for hours and cannot come up with a reliable solution that doesn't involve all sorts of trickery with dummy controls, etc.  There must be a better way....

I have a continuous form embedded in another form.  When I reach the last control in the last record, and I hit the tab key, I want control to return to the main form.  Seems like this should be easy.  But the weird thing is, KeyUp fires AFTER focus has shifted, so I can't find a way to pickup the Tab from the last control.  In other words, let's say there are two controls, tbxBoxA and tbxBoxB.  Box B is the last control and is listening for key strokes.  If hit hit any key OTHER than tab, it picks it up.  But If I'm in tbxBoxA, and I hit Tab, control moves to B (as expected), but then B's KeyUp says "Tab was pressed".  That makes no forking sense in my opinion, but so be it.  So I dunno what to do.  Any ideas?

Question by:shacho
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 17
  • 8
  • 2
LVL 75
ID: 36533957
"the last control in the last record,"
Of course, that's the last control in the first record also :-)

Maybe something like this Mike:

Private Sub Text6_Exit(Cancel As Integer)
    If Me.CurrentRecord = Me.RecordsetClone.RecordCount Then Me.Parent.SomeControl.SetFocus
End Sub

Where Text6 is the last control in the tab order.  I tried this and it works ...


Author Comment

ID: 36533973
>If Me.CurrentRecord = Me.RecordsetClone.RecordCount Then...

Well, here's the rub.  That would work just fine, if I didn't want it to be possible to add records, which I do.  The form's Cycle is set to All Records, so if there are say, 3 records, a TAB from B in R1 will go to A in R2, a TAB from B in R2 will go to A in R3, and a TAB B in R3 will go to A in a new records.  But ... a tab from B in a new record will go back to A in the new record.  I'll try using Exit with test to see if the record is new.

Author Comment

ID: 36533974
Sorry - to be clear - a TAB from B in a new record will go to A in the new record IF the record is incomplete.
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.


Author Comment

ID: 36533979
Or actually .... (sorry) ....
a TAB from B in a new record will go to A in the new record IF the new record is not dirty.
LVL 75
ID: 36533981
A 'new record' should not be Dirty.

Also we have  

If Me.NewRecord = True Then ' .....

And maybe you need to trap the Tab key also ...


Author Comment

ID: 36534022
Nothing works.

Author Comment

ID: 36534025
I just can't find any way to determine if the conditions are right to move focus when I trap the TAB.

Author Comment

ID: 36534030
Let's start with this.

Private Sub Form_KeyPress(KeyAscii As Integer)
    If [...] Then
    End If
End Sub

Author Comment

ID: 36534031
See if you can fill that box.  I've been trying for four hours.
LVL 75
ID: 36534032
ok ... summarize those conditions again.

You are in the last control of the last record ... and ....


Author Comment

ID: 36534038
Last control of a new, non-dirty record.  When I hit TAB I want to change focus.  The natural behavior of the form is to return focus to the first control of the new record.

LVL 75
ID: 36534062
This works for me:

Private Sub Text6_Exit(Cancel As Integer)
    If Me.NewRecord = True And Me.Dirty = False Then
        If Me.CurrentRecord > Me.RecordsetClone.RecordCount Then Me.Parent.SomeControl.SetFocus
    End If
End Sub


Author Comment

ID: 36534070
I tried something similar a while ago.  That almost works, but not if you use the mouse to click in into one of the other controls inside the new record, or Tab backwards.  In that case you don't want to leave the subform, but this code will boot you out.

Author Comment

ID: 36534071
...because it only looks at Exit.

Author Comment

ID: 36534088
And by the way, if you solve this riddle, you'll also solve this one...
... because the stupid form traps the TAB key in every single circumstance EXCEPT when you're on the last control of a new non-dirty record.
LVL 75

Accepted Solution

DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 500 total points
ID: 36534106
Ok ... this seems to work:

Private Sub Text6_KeyDown(KeyCode As Integer, Shift As Integer)
    If KeyCode = vbKeyTab Then
        If Me.NewRecord = True And Me.Dirty = False Then
            If Me.CurrentRecord > Me.RecordsetClone.RecordCount Then Me.btnRunAll.SetFocus
        End If
    End If
End Sub

Also, be SURE to set the Form Key Preview property to Yes.


Author Comment

ID: 36534173
Well well.  You're right, it works.  I simultaneously reached another solution that pares down the trickery to a minimum as follows:

1) Add a Command Button to the form with Height = Width = 0 and Enabled=False
2) Add code:
Private Sub Form_Current()
    Me.btnTabSink.Enabled = Me.NewRecord
End Sub
Private Sub btnTabSink_GotFocus()
    If Me.NewRecord Then Me.Form.Parent.Controls("btnSave").SetFocus
End Sub

This way, control will only land on the (Visible=True, but invisible) tab sink button if the caret is on a new record.

But your approach works with only one event handler so that's better.  Now here's the mystery I'd like to understand.  You used KeyDown.  I tried KeyUp and KeyPress.  Both behaved unexpectedly by trapping the tab on the way IN to the control.  So I never tried KeyDown, assuming the result would be the same.  But it's not.  Key Down fires as expected when the control in question has the focus.  So I guess the real behind the scenes sequence of events is:

1) TAB inside Control A
2) KeyDown trapped by Form and/or Control A
3) Focus moves to Control B
4) KeyUp trapped by Form and/or Control B
5) KeyPress trapped by Form and/or Control B

So that's it.  KeyDown was the magic event I needed.

Thanks - BILLIONS (please settle for 500)

LVL 75
ID: 36534187
"Both behaved unexpectedly by trapping the tab on the way IN to the control. "
Exactly ... no clue why. So, just tried the KeyDown ... bingo!

OK ... 500 + 2 Sapporos

OK ... can I post this same solution to the other Q ... ?


Author Comment

ID: 36534210
>OK ... can I post this same solution to the other Q ...
By all means.  "See here [link]" is fine.

Interestingly, for the same reasons, this approach actually introduces a new problem.  It moves control to the parent form, THEN sends the TAB key.  So the target control immediately loses focus to whatever comes next.  I can work around this, however.  Looks like I'll have to use two events after all.

Sapporos - Anytime! ;>

LVL 75
ID: 36534257
I see ... Well, I wasn't using a main form, just setting to a button on this form.  So, I set up a main/sub ... and yep ... you're right.  So, here is the fix for that:

Private Sub Text6_KeyDown(KeyCode As Integer, Shift As Integer)
    If KeyCode = vbKeyTab Then
        If Me.NewRecord = True And Me.Dirty = False Then
            If Me.CurrentRecord > Me.RecordsetClone.RecordCount Then
               KeyCode = 0

            End If
        End If
    End If
End Sub

Author Comment

ID: 36534276
Hah!  KeyCode is not read only.  I had no idea.  Totally awesome & thanks again.

LVL 75
ID: 36534285
Always a pleasure Mike ... but now it's THREE Sapporos !



Author Comment

ID: 36534289
LVL 57
ID: 36535645
FWIW, he's the old fashioned way to do that.


Function SubFormNavigate(ByVal wKeyCode As Integer, ByVal wShift As Integer, frm As Form, strParam As String) As Integer

        ' Receives : KeyCode representing which key was pressed
        '            Shift indicating whether shift, alt, ctrl were pressed

        ' Returns  : Nothing (subroutine).

        ' Author   : Ian Sparks (Uk).
        ' Date     : 5th July 1994.

        ' Purpose  : This function is called from the start and end of single-value subforms
        '            and catches keystrokes like TAB to move off the current record. It re-maps
        '            these keystrokes to CTRL-TABs to move them to the next subform instead.

        '            The direction the user was trying to move can be checked by the tab order of
        '            the current field. A tab index of 0 is the first field on the subform whilst
        '            any other number is assumed to be the last field on the subform.

        ' Rewrite  : Jim Dettman 10/14/94
        '              Rewrote routine to make it more generic and improve
        '              performance.

        Dim rstSubForm As Recordset
        Dim rstSubFormClone As Recordset
        Dim strBM As String
        Dim fAtNewRecord As Integer

        Dim fShiftDown As Integer                'Was the Shift key pressed down?
        Dim fControlDown As Integer              'Was the Ctrl key pressed down?
        Dim fAltDown As Integer                  'Was the Alt key pressed down?

        Dim fTab As Integer                      'Is the TAB key pressed?
        Dim fUp As Integer                       'Is the UP key pressed?
        Dim fDown As Integer                     'Is the DOWN key pressed?
        Dim fEnter As Integer                    'Is the ENTER key pressed?

        Dim fMovingDown As Integer               'User is trying to move down?
        Dim fMovingUp As Integer                 'User is trying to move up?

        Dim fOnFirstField As Integer             'Are we on first field or not?
        Dim fIsBound As Integer                  'Is this form bound?

        Const RoutineName = "SubFormNavigate"
        Const Version = "2.0.0"

        ' Note version 2.0.o and up is for A97 and up only.

10      On Error GoTo SubFormNavigate_Error

20      SubFormNavigate = wKeyCode

        ' Find out the status of the SHIFT, ALT and control keys

30      fShiftDown = (wShift And SHIFT_MASK) > 0
40      fControlDown = (wShift And CTRL_MASK) > 0
50      fAltDown = (wShift And ALT_MASK) > 0

        ' Find out what actual "movement" key was pressed

60      fTab = (wKeyCode = KEY_TAB)
70      fUp = (wKeyCode = KEY_UP)
80      fDown = (wKeyCode = KEY_DOWN)
90      fEnter = (wKeyCode = KEY_RETURN)

        ' If processing a Control/Tab don't do anything.
100     If fControlDown = True And fTab = True Then Exit Function

        ' Work out which direction the user was trying to move in.

110     fMovingDown = (Not (fShiftDown) And fTab) Or fDown Or fEnter
120     fMovingUp = (fShiftDown And fTab) Or fUp

        ' Decide if this subform is bound.

130     Set rstSubForm = frm.RecordsetClone         ' Refer to forms record set
140     If IsNull(rstSubForm.Name) Then
150       fIsBound = False
160     Else
170       fIsBound = True
180     End If

        ' Decide if we are on first field or not.

190     If strParam = "F" Then
200       fOnFirstField = True
210     Else
220       fOnFirstField = False
230     End If

        ' Which way are we going?

240     If fOnFirstField And fMovingUp Then
250       If Not (fIsBound) Then
260         SubFormNavigate = 0
270         SendKeys "+^{tab}"
280       Else
290         On Error Resume Next
300         strBM = frm.Bookmark
310         fAtNewRecord = (Err = 3021)
320         On Error GoTo SubFormNavigate_Error
330         If (fAtNewRecord) Then
340           If (rstSubForm.RecordCount = 0) Then
350             SubFormNavigate = 0
360             SendKeys "+^{tab}"
370           End If
380         Else
390           rstSubForm.Bookmark = frm.Bookmark
400           Set rstSubFormClone = rstSubForm.Clone()
410           rstSubFormClone.Bookmark = rstSubForm.Bookmark
420           rstSubFormClone.MovePrevious
430           If rstSubFormClone.BOF Then
440             SubFormNavigate = 0
450             SendKeys "+^{tab}"
460           End If
470           rstSubFormClone.Close
480         End If
490       End If
500     Else
510       If Not (fOnFirstField) And fMovingDown Then
520         If Not (fIsBound) Then
530           SubFormNavigate = 0
540           SendKeys "^{tab}"
550         Else
560           On Error Resume Next
570           strBM = frm.Bookmark       ' Get current row bookmark
580           fAtNewRecord = (Err = 3021)
590           On Error GoTo SubFormNavigate_Error
600           If (fAtNewRecord) Then
                ' At new record, but user may have entered data
                ' Jump out only if new record not dirty.
610             If frm.Dirty = False Then
620               SubFormNavigate = 0
630               SendKeys "^{tab}"
640             End If
650           Else
                ' Not at new record
                ' Need to handle case where form is read only
                ' and no new record is available
                'rstSubForm.Bookmark = frm.Bookmark
                'Set rstSubFormClone = rstSubForm.Clone()
                'rstSubFormClone.Bookmark = rstSubForm.Bookmark
660             If frm.DefaultEditing = 3 Or frm.DefaultEditing = 4 Then
670               SubFormNavigate = 0
680               SendKeys "^{tab}"
690             End If
700           End If
710         End If
720       End If
730     End If

740     rstSubForm.Close
750     Exit Function

760     UnexpectedError ModuleName, RoutineName, Version, Err.Number, Err.Description, Err.Source, VBA.Erl
770     Resume Exit_SubFormNavigate

End Function

Author Comment

ID: 36535823
Whoa.  A sort of account for everything approach.  Kudos to the author(s) for the hard work that went into it, but I think the same can be achieved with a lot less code, and SendKeys is unreliable (and unnecessary as you can set focus directly).  Thanks for the post, though; I hadn't considered the case of Shift-Tabbing from the first control.  I'll try to work that in.


LVL 57
ID: 36535850

<<Thanks for the post, though; I hadn't considered the case of Shift-Tabbing from the first control.  I'll try to work that in.>>

  That's why I posted.  Thought it might give you some ideas as the whole thing as not as simple as it first appears.  The whole issue of a SF really being a seperate form raises a raft of issues (ie. records getting saved) all of which is not obvious to the user.

  I have no doubt as well that the code could be improved; this was originally done with Access 2.0.  Things like Keypreview at form level didn't exist back then.  I think though there is a MSKB article as well along the same lines, but I haven't looked it up in quite some time.

  Last, I would typically agree on the send keys, but in this case it's pretty safe.  The user has just entered a key combination, so their hands are on the keyboard.  The chance of them getting to the mouse and moving focus to another window before then code executes are slim.  Won't say it couldn't happen though<g>.


Author Comment

ID: 36535950
mmm, good point.  as it happens i abandoned this approach anyway because i concluded (as i have twice before but keep trying again about once a year) that if you want to use an embedded form for editing records, it just gets too muddy when you try to program sophisticated navigation features.  so in the end i made two forms for the sub table.  one is just a list with no code.  that one gets embedded as a locked subform, for which only two events exist: got and lost focus. if you click on or navigate to the subform with keys, it shows the real editor (the other form) the pushes the focus on to the next control.  it's soooooooo much easier and clear and reliable this way.  and it loads faster.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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 stored procedures 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 Micr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

734 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