Shifting Focus from one sub-form to the other ~ LEARN MS-ACCESS TIPS AND TRICKS

Shifting Focus from one sub-form to the other.

Sample Form with two Sub-Forms:

Right-click to open the Large Image in a New Tab/Window.

The focus jump path.

Right-click to open the Large Image in a New Tab/Window.

Last week we saw how to set focus to a particular field in a sub-form from the main form field. I didn't say how these three forms relate to each other and are bound together in the earlier example. We know how the Main Form and Subform Link work. When the main form record moves to another record, its corresponding records in the 1st Subform appear, because both are linked by a common field value. There is a 2nd subform on the Main Form. The 2nd subform is not related to the Main Form; it is related to the 1st subform, but it cannot be directly linked to the 1st subform.

Two related subforms and two issues to solve.

Two subforms to be linked together via the Main Form, using a common field value in both subforms. The 1st subform is directly linked to the main Form.
When the Tab key transfers control from the 1st subform's last TextBox (the School-Year), the Amount TextBox in the 2nd subform should become active.

Assume that the current record on the 1st subform (frm_Session) Primary Key field Session ID value is 1.

The Relationship pattern.

1. Main Form Student ID -> 1st SubForm Student ID

2. 1st SubForm Session ID -> 2nd SubForm Session ID via Main Form

We want to transfer focus from the first subform Session record to its corresponding Payment record Amount field in the second subform.

Normally, when you press the Tab key on the last field of the current record, the insertion point moves to the first field of the next record on the same Form (with Session ID value 2). Automatically, the Payment record linked to the Session record also changes to synchronize with Session ID value 2, because it is directly linked to frm_session.

So the control from the first record is lost with Session ID 1, and we cannot enter the Payment Value in the Amount field of the frm_Payment for the same Session ID 1. We want the frm_Payment's Amount field to be current and enter the payment value for Session ID 1. If you are lost in the story details, then check the second image diagram.

So, the question is how to jump focus from the first record in both subforms when the focus is lost from the last field of the first subform, and transfer the Focus to its corresponding record's Amount field on the second subform?

Linking Both SubForms Together.

Let us explore how to link the subforms to synchronize related records on both subforms.

The 1st subform (frm_Session) is directly linked to the Main Form (frm_Students) using the Students Table Field Student ID (Primary Key) and the Session Table common Field Student ID (Foreign Key).

The current student record on the main Form can have several session records on the frm_Session Form. Each Session record on the Session subform, with the Session ID Field as a foreign key, has one or more Payment records on the frm_Payment subform.

The frm_Payments sub-form (2nd subform) is directly linked to the frm_Session 1st sub-form on the common field Session ID.

Two sub-forms cannot be linked together directly because a sub-form cannot be considered a Master Form by another Sub-Form. When this type of link (or relationship) is required, as in the case of the subforms discussed above, we can establish it simply by transferring the key-field value from the first subform to an Unbound TextBox on the Main Form (=[frm_Session].Form!SessionID). The Unbound TextBox containing the Session ID value then becomes part of the Main Form.

The Name of this TextBox can be used in the Link Master Field property of the second subform to establish a relationship with the first subform.

Check the following Design View of the above Forms:

Right-click to open the Large Image in a New Tab/Window.

You can see an Unbound TextBox with a yellow background, specifically created to link the 2nd subform (frm_Payments) to the 1st subform (frm_Session) through the Unbound Textbox on the frm_Students main Form. This unbound TextBox's name is Session_ID, which is slightly different from the actual field name SessionID in the subforms. The Child Label caption is Session_ID for clarity.

The Session_ID TextBox’s Control Source Property is set with the following expression to copy the SessionID value automatically from the current record on the frm_Session sub-form:

=[frm_Session].Form!SessionID

Once this is done, you can link the frm_Payments sub-form with the frm_Session sub-form through the Session_ID TextBox by setting the Link Master Field and Link Child Field properties of the frm_Payments sub-form, as shown below.

Link Master Field = Session_ID (the unbound Textbox name)
Link Child Field = SessionID  (the Payments Form’s Foreign Key field name)

Tackling the Real Problem

Now that we know how the subforms are linked, we can concentrate on the real issue that I pointed out at the beginning of this Article. We must be able to transfer control from the last field of a record in the first sub-form to its corresponding record in the second sub-form, without changing the current record on the first sub-form. We need a small VBA program to do that job successfully.

Tip: You can try this with three simple tables (Students, Session & Payments) having sample fields as shown on the Forms. You may download a sample database, with the sample tables and forms, from the download link given at the bottom of this Article.

If you have the sample Tables and Forms design shown above, you may open the frm_Students Main Form to perform a sample run without the VBA Code.

Tip: If you have downloaded the sample database from the link at the end of this page, open frm_Session in the design view, press F4 to display the Property Sheet, and click the School Year Field. You will find [Event Procedure] in the On Got Focus event property. Select this property and click on the Build (. . .) Button to open the VBA Module. Highlight and delete the entire code, except the first two lines: Option Compare Database & Option Explicit. Save and close the form. You can copy and paste the deleted code from this page.

Open the frm_Students in Normal View.
Select the first record on the frm_Session form to select the record with Session ID value 1.
Check for the related record on the frm_Payments subform with Session ID value 1.
Press the Tab Key to move the focus to the last field, School Year.

Note: When you press the Tab Key one more time, the focus should jump from the current record on the frm_Session sub-form to its corresponding record with Session ID 1 on the frm_Payments sub-form, which must become active.
Now, press the Tab Key to move out of the last field, School Year, on frm_Session to go to the Amount field on frm_Payments.

But it didn’t happen as we expected; instead, the cursor moved down to the next record on frm_Session, with SessionID value 2. The related records in frm_Payments also changed the foreign key to SessionID value 2 to match the record in frm_Session.

We can do this task only with VBA Code. The steps of our program are given below:

When the focus is set on the School Year field on frm_Session, save the Session ID value into a memory variable SID.
When the Focus is Lost (i.e., when the user presses the Tab Key again) from the School Year, the Focus moves to the next record, and at this point the VBA Code searches the form’s RecordsetClone for the SessionID value in the SID variable.
When the record is found, the RecordsetClone Bookmark is saved into the bkmk String Variable.
Copy the recordset Bookmark into the frm_Session’s Bookmark control.
These steps reset the focus back to the previous record, from the second record, on the frm_Session subform. The frm_Payment record changed earlier, and before executing the code, it returns to the one with the Session ID on the frm_Session record.
Set Focus on the frm_Payments.

When this happens, the frm_Payments field with Tab Index value 0 receives Focus. At this point, we can move the Focus to another field, if needed. We will try this by setting the focus on the Amount field.
Set focus on the Amount field on frm_Payments.

If you have the above Form ready, then copy and paste the VBA Code given below into the frm_Session’s VBA Module.

Open the frm_Session form in Design View.
Click on the School Year field to select it.
Press F4 to display the Property Sheet of the School Year field.
Find the On Got Focus event property and click to select it.
Select [Event Procedure] from the drop-down control.
Click on the Build (...) Button to open the Form’s VBA Module (Class Module). You may find the following lines of Code in the Class Module.

The VBA Code.
```
Option Compare Database
Option Explicit

Private Sub SchoolYear_GotFocus()

End Sub
```

Copy and paste the following lines of code, overwriting the existing lines of code in the Module:

Option Compare Database
Option Explicit
'SID is declared as a global variable
Dim SID As Long

Private Sub SchoolYear_GotFocus()
'Save the SessionID value in a Global variable
SID = Me!SessionID
End Sub

Private Sub SchoolYear_LostFocus()
'----------------------------------------------
'This subroutine runs when the Focus is shifted 
'from the SchoolYear field.
'----------------------------------------------
'Author : a.p.r. pillai
'Date   : Jan/2013
'All Rights Reserved by www.msaccesstips.com
'----------------------------------------------
Dim ctrl As Control, ctrl2 As Control
Dim bkmk As String, rst As Recordset, j As Long
Dim rstSID As Long

'The following lines of code prevents shifting the focus
'to the next record on the frm_Session sub-form, when the focus
'is lost from the last field of frm_Session, in preparation to set focus
'on a particular field on the corresponding record on the
'frm_Payments sub-form.
Set rst = Me.RecordsetClone 'frm_session's recordset
rst.MoveFirst
For j = 1 To rst.RecordCount
rstSID = rst![SessionID]
If rstSID = SID Then 'find the record matching the current record on frm_session
   'when match found save the record's recordset bookmark
   bkmk = rst.Bookmark
   'copy the recordset bookmark to the form
   'this will set the focus back on the same record
   'this will also ensure that the SessionID related Payment record
   'will be current on the frm_Payment form
   Me.Bookmark = bkmk
   'set focus on the first field
   Me.SessionID.SetFocus
   'break the loop
   Exit For
End If
rst.MoveNext
Next
rst.Close
'transfer control to the frm_Payments Sub-form
'Now the field with Tabindex number 0 have the default focus
Set ctrl = Forms![frm_Students].Controls("frm_Payments")
ctrl.SetFocus
'Once the focus is shifted on the field with tabindex 0 within frm_Payment sub-form
'we can move the focus to any other field within that form, if required
Set ctrl2 = Forms![frm_Students]![frm_Payments].Form.Controls("Amount")
ctrl2.SetFocus

End Sub

Save and Close the Form.

Try out your Forms.
Open the frm_Students in the normal view.
Click on the first record on the frm_Session Form.
Press the Tab Key to move focus to the School Year field and check the corresponding record on the frm_Payments form.
Press the Tab Key one more time to jump to the frm_Payments subform record, with the same Session ID value, and set the focus directly on the Amount field.