[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2006-04-14
Medium Priority
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 16453124
 set the Cycle property of the subform to Current record
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 16453146
better i think is to

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

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 16453184
a good example for this is the Orders form from the Northwind database.

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

LVL 58

Expert Comment

ID: 16453523

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

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

Expert Comment

ID: 16456813

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 31

Expert Comment

ID: 16457830
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

harfang earned 2000 total points
ID: 16458140
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 {http://support.microsoft.com/kb/233324/EN-US/}.


Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

873 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