Tab out of a subform and set focus back on main form moving to the next record.

Posted on 2006-04-14
Last Modified: 2008-02-01
I have a form and a subform that are based on tables with a one-to-one relationship.  The subform is NOT in datasheet vew, but rather in form view because there can be no more than one record in the subform for any record in the main form.  Therefore, it just looks better having the subform in form view.  Some records entered in the main form will not involve using the subform.  

When a user adds a record to the main form they can tab through it and then if they continue tabbing they will enter the subform and can add a record to it.  However, if they continue tabbing through the subform, when they reach the last field, they will go to another record in the subform if they hit tab.  This might confuse the user.  I would like the focus to go back to the main form and, if possible, go to the next record.  

Any ideas?
Question by:tdeinzer2005
    LVL 119

    Expert Comment

    by:Rey Obrero
     set the Cycle property of the subform to Current record
    LVL 119

    Expert Comment

    by:Rey Obrero
    better i think is to

     set the Cycle property of the main form to Current record
    LVL 119

    Expert Comment

    by:Rey Obrero
    a good example for this is the Orders form from the Northwind database.

    LVL 58

    Expert Comment


    Although the Cycle property is useful, it does not address all problems:
    * tab through (solved)
    * page down (you can trap that)
    * mouse scroll (harder)
    * next or new records from toolbar or menu
    * record navigation buttons (you disabled them)

    Basically, the subform can only have one record. This can be managed in a single event, the subform's current event:

    Private Sub Form_Current()
        If Me.CurrentRecord > 1 Then
            Me.<name of first control>.SetFocus
        End If
    End Sub

    It now is a "one record only" form, taking care of all the problems at once.

    Another neat solution is to edit both records from the main form. Try this type of query:

        SELECT tblMain.*, tblBis.strField [, tblBis.strMoreFields]
        FROM tblMain LEFT JOIN tblBis
        ON tblMain.KeyField = tblBis.KeyField;

    Try to edit some records in that query. You will see that you can actually just type text in fields of the "right" side, and that the related records are added automatically.
    Use that as source for your form.
    It might make sense to set aside the fields from the related table, grouped in a rectangle for instance.

    Hope this helps!
    LVL 44

    Expert Comment

    Harfang:  As I recall, a subform has only two events - OnEnter and OnExit.
    LVL 58

    Expert Comment


    True, a subform _control_ has only those two. But the _form_ used as subform has all normal form events. The above was meant for the Current event of that form.

    LVL 30

    Expert Comment

    Example: Names used in other EE Questions!

    Form: frmMain
    SubForm: fsubSrvCall

    Main Form module:
    Option Compare Database
    Dim varWhichForm As Byte '1 in main form, 2 in subform

    Private Sub fsubSrvCall_Enter()
    varWhichForm = 2 ' set to know it was in subform
    End Sub

    Private Sub fsubSrvCall_Exit(Cancel As Integer)
        If varWhichForm = 1 Then
            ' in main form, do normal
            'varwhichform=2 set to 1 and advance one record
            DoCmd.GoToRecord acDataForm, Me.Name, acNext  ' advance main form 1 record.
            varWhichForm = 1
        End If
    End Sub

    As sugggested,  cycle within same record.  Open subform in new window. Set Cycle to Current Record.
    Ctrl+Tab leaves subform to main form.
    LVL 58

    Accepted Solution

    hnasr, sorry to say so, but your example is one of poor programming. What it basically says is "whenever the user exits the subform, the main form should advance to the next record".
    For example, the user:
    * while in the subform, clicks on a field in the main form
    * uses Ctrl+Shift+Tab to exit from the subform backwards.

    This is not good. There are only three relatively clean ways to reimplement all records cycling with the presence of a subform.
    1) Do not use a subform -- as explained above.
    2) Create a zero-width borderless control as the last in the tab order of the subform. Create an _Enter event for it and examine Screen.PreviousControl. It it's the previous control, it's a forward Tab, so jump to the first control of the main form and advance to the next record, i.e "cycle to next". If it's the first control, it's a Shift+Tab, so jump to the contol just above the subform.
    3) Trap the keys at the subform level and examine each keystroke in conjunction with Screen.ActiveControl...

    Strangely enough, the MS official sample for this problem uses an _Exit event, with the same unwanted effects {}.


    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    Suggested Solutions

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
    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…

    755 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

    18 Experts available now in Live!

    Get 1:1 Help Now