Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Shifting Focus from one sub-form to the other.

Sample Form with two Sub-Forms:

Last week we have seen how to set focus to a particular field in a sub-form from the main form.  I have not mentioned anything about the relationships between these three forms in the earlier example.  But, here it is important to know before we attempt to leave Focus from a record on the first sub-form and set focus to it’s corresponding record in the second sub-form.

Let us look into a particular issue between these two sub-forms that warrants our special attention so that we will know what kind of solution we have to device to solve it.

Assume that the current record on the first sub-form (frm_Session) is with the SessionID (Primary Key) field value 1.  Since, the first sub-form is directly linked to the second sub-form (we will explore this aspect, i.e. establishing direct links between two sub-forms, little later) frm_Payments, the payment record with the SessionId (Foreign Key) field value 1 is displayed on that form too.  We want to transfer Focus from the first sub-form Session record to it’s corresponding Payment record in the second sub-form.  When you press Tab-Key on the last field of the current record, to move out from the frm_Session Form (without the control of a program), the cursor moves to the next record (with SessionID value 2). Automatically the Payment record, linked to the Session record, also changes to synchronize with the SessionID value 2.  So the control is lost from the first record with SessionID field value 1.

So, the question is how to keep the Focus on the first record itself, when focus is lost from the last field, and transfer Focus to it’s corresponding record field on the second sub-form?

Before going into that let us learn how to link both sub-forms directly, to synchronize related records on both sub-forms?

The first sub-form (frm_Session) is directly linked with the Main-Form (frm_Students) on the common field StudentID (Primary Key) field on Students Table and StudentID (Foreign Key) on Session Table.

The current student record on the main form can have several session records on the frm_Session Form.  Each Session record on the Session sub-form will have one or more Payment record on the frm_Payment sub-form and have SessionID as Foreign Key.

The frm_Payments sub-form is directly linked to the frm_Session sub-form on common SessionID field value. 

Two sub-forms cannot be linked together directly because a sub-form cannot be considered as a Master Form.  When these kind of links (relationships) become necessary, like the above sub-forms, we can do it by simply transferring the first sub-form Key-field value into an Unbound Textbox on the Main-form and this Unbound Textbox that holds the SessionID value become part of the Main Form.  The Name of this Textbox can be used in the Link Master Field property of the second sub-form to establish a relationship with the first sub-form.

Check the following Design View of the above Forms:

You can see an Unbound Textbox with yellow background, created specifically for linking the second sub-form (frm_Payments) to the first sub-form (frm_Session) through the Unbound Textbox on the frm_Students main form.  This Textbox’s Name Property value is set as Session_ID, somewhat different than the actual field names on the sub-forms: SessionID. The child Label Caption I have changed to Session_ID for information.

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:


Once this is done, you can link the frm_Payments sub-form with the frm_Session sub-form through the Session_ID Texbox 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)

If you understood the sub-form’s relationships then we can proceed with the real issue I have 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 it’s 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 out with three simple tables (Students, Session & Payments) with 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 organized as per the design shown above you may open the frm_Students in normal view to do a sample run of what we are trying to achieve, without the help of VBA Code.

Tip:  If you have downloaded the sample database from the link given at the bottom of this article, then open the frm_Session in design view, press F4 to display the Property Sheet, Click on the SchoolYear Field.  You will find [Event Procedure] in the On Got Focus event property.  Click on 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.

  1. Open the frm_Students in normal View.
  2. Click on the first record on the frm_Session form to select the record with SessionID value 1.
  3. Check the presence of the related record on the frm_Payments sub-form, with the SessionID value 1.
  4. Press Tab Key to move control to the last field SchoolYear. 

    Note:  What we want at this point is, when you press Tab Key one more time the control should jump from the current record on the frm_Session sub-form and set focus on it’s corresponding record with SessionID value 1, on the frm_Payments sub-form. 

  5. Now, press Tab Key to move out from the last field SchoolYear from frm_Session to go to frm_Payments.

But, it didn’t happen as we have expected, instead the cursor moved down to the next record on the frm_Session, with SessionID value 2.  The related record on the frm_Payments also changed, with Foreign Key SessionID value 2 to match with the record on frm_Session.

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

  1. When the Focus arrives on the SchoolYear field on frm_Session, save the SessionID value into a memory variable SID.
  2. When the Focus is Lost (i.e. when the user presses Tab Key again) from the SchoolYear, the Focus moves to the next record and at this point the VBA Code searches through the form’s RecordsetClone for the SessionID value in memory Variable SID.
  3. When the record is found, save the RecordsetClone Bookmark into a String Variable bkmk.
  4. Copy the recordset Bookmark into the frm_Session’s Bookmark control. 

    These steps resets the focus back to the previous record, from the second record, on the frm_Session sub-form. The frm_Payment record earlier changed, before executing the code, also returns to the one matching the SessionID on the frm_Session record.

  5. Set Focus on the frm_Payments.

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

  6. Set focus on Amount field on frm_Payments.

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

  1. Open the frm_Session form in design view.
  2. Click on the SchoolYear field to select it.
  3. Press F4 to display the Property Sheet of the SchoolYear field.
  4. Find the On Got Focus event property and click to select it.
  5. Select [Event Procedure] from the drop-down control.
  6. Click on the Build (. . .) button to open the Form’s VBA Module (known as Class Module).  You may find the following lines of Code in the Class Module.
    Option Compare Database
    Option Explicit
    Private Sub SchoolYear_GotFocus()
    End Sub
  7. Copy and paste the following lines of Code overwriting the existing above 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
    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
       'break the loop
       Exit For
    End If
    '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")
    '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")
    End Sub
  8. Save and Close the Form.
  9. Open the frm_Students in normal view.
  10. Click on the first record on the frm_Session Form.
  11. Press Tab Key to move the focus to the SchoolYear field and check the corresponding record on the frm_Payments form.
  12. Press Tab Key one more time to jump the control on to the frm_Payments sub-form record, with the same SessionID value,  and to set the Focus directly on the Amount field.

Download Demo SubForm.zip


No comments:

Post a Comment

Comments are subject to moderation before publishing.


Subscribe in a reader
Your email address:

Delivered by FeedBurner

Blog Directory Visit blogadda.com to discover Indian blogs


Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email


Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Class Module Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Array Queries msaccess reports External Links msaccess tips Accesstips Downloads Objects Property Menus and Toolbars Controls Event MsaccessLinks Process Controls Art Work Collection Object WithEvents msaccess How Tos Combo Boxes Graph Charts List Boxes VBA msaccessQuery Command Buttons Command Button Data Dictionary Object Emails and Alerts Form Query RaiseEvent Report Calculation Custom Wizards DOS Commands Data Type ms-access functions msaccess functions msaccess graphs msaccess reporttricks Custom Functions Item Object Reference msaccessprocess security advanced Access Security Add Field Type Fields Form Instances Key Macros Menus SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Copy Excel Expression Join Methods Microsoft Numbering System Records Recordset Security Split Table Time Difference Utility Workgroup Wrapper Classes database function ms-access msaccess wizards reference text tutorial vba code Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Export External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Tables Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif msaccess msaccess alerts pdf files restore switch toolbar updating upload

Featured Post

ROUNDDOWN Function of Excel

We have introduced ROUNDUP() Function of Excel in Microsoft Access earlier.  It works like ROUND() Function except that the number is alway...


Blog Archive

Recent Posts