Link to home
Start Free TrialLog in
Avatar of shacho
shacho

asked on

Returning focus to parent from subform with Tab key

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?

Mike
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

"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 ...

mx
Avatar of shacho
shacho

ASKER

>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.
Avatar of shacho

ASKER

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.
Avatar of shacho

ASKER

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.
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 ...

mx
Avatar of shacho

ASKER

Nothing works.
Avatar of shacho

ASKER

I just can't find any way to determine if the conditions are right to move focus when I trap the TAB.
Avatar of shacho

ASKER

Let's start with this.

Private Sub Form_KeyPress(KeyAscii As Integer)
    If [...] Then
        MoveFocus()
    End If
End Sub
Avatar of shacho

ASKER

See if you can fill that box.  I've been trying for four hours.
ok ... summarize those conditions again.

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

mx
Avatar of shacho

ASKER

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.

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


mx
Avatar of shacho

ASKER

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.
Avatar of shacho

ASKER

...because it only looks at Exit.
Avatar of shacho

ASKER

And by the way, if you solve this riddle, you'll also solve this one...
https://www.experts-exchange.com/questions/27279662/Form-Events-Miss-Tab-Key.html
... 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.
ASKER CERTIFIED SOLUTION
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of shacho

ASKER

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)

Mike
"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 ... ?

mx
Avatar of shacho

ASKER

>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! ;>

Mike
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
                Me.Parent.Text2.SetFocus

            End If
        End If
    End If
End Sub
Avatar of shacho

ASKER

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

Mike
Always a pleasure Mike ... but now it's THREE Sapporos !

:-)

mx
Avatar of shacho

ASKER

Gladly!
FWIW, he's the old fashioned way to do that.

Jim.

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
                'rstSubFormClone.MoveNext
660             If frm.DefaultEditing = 3 Or frm.DefaultEditing = 4 Then
670               SubFormNavigate = 0
680               SendKeys "^{tab}"
690             End If
                'rstSubFormClone.Close
700           End If
710         End If
720       End If
730     End If

Exit_SubFormNavigate:
740     rstSubForm.Close
750     Exit Function

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

End Function
Avatar of shacho

ASKER

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.

Cheers,

Mike
Mike,

<<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>.

Jim.
Avatar of shacho

ASKER

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.