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

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?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
 set the Cycle property of the subform to Current record
Rey Obrero (Capricorn1)Commented:
better i think is to

 set the Cycle property of the main form to Current record
Rey Obrero (Capricorn1)Commented:
a good example for this is the Orders form from the Northwind database.

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.


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!
Harfang:  As I recall, a subform has only two events - OnEnter and OnExit.

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.

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.