Solved

Returning focus to parent from subform with Tab key

Posted on 2011-09-13
27
554 Views
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?

Mike
0
Comment
Question by:shacho
  • 17
  • 8
  • 2
27 Comments
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
"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
0
 

Author Comment

by:shacho
Comment Utility
>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.
0
 

Author Comment

by:shacho
Comment Utility
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.
0
 

Author Comment

by:shacho
Comment Utility
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.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
0
 

Author Comment

by:shacho
Comment Utility
Nothing works.
0
 

Author Comment

by:shacho
Comment Utility
I just can't find any way to determine if the conditions are right to move focus when I trap the TAB.
0
 

Author Comment

by:shacho
Comment Utility
Let's start with this.

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

Author Comment

by:shacho
Comment Utility
See if you can fill that box.  I've been trying for four hours.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
ok ... summarize those conditions again.

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

mx
0
 

Author Comment

by:shacho
Comment Utility
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.

0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
0
 

Author Comment

by:shacho
Comment Utility
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.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:shacho
Comment Utility
...because it only looks at Exit.
0
 

Author Comment

by:shacho
Comment Utility
And by the way, if you solve this riddle, you'll also solve this one...
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Forms/Q_27279662.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.
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 500 total points
Comment Utility
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.

mx
0
 

Author Comment

by:shacho
Comment Utility
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
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
"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
0
 

Author Comment

by:shacho
Comment Utility
>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
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
0
 

Author Comment

by:shacho
Comment Utility
Hah!  KeyCode is not read only.  I had no idea.  Totally awesome & thanks again.

Mike
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Always a pleasure Mike ... but now it's THREE Sapporos !

:-)

mx
0
 

Author Comment

by:shacho
Comment Utility
Gladly!
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
0
 

Author Comment

by:shacho
Comment Utility
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
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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.
0
 

Author Comment

by:shacho
Comment Utility
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.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now