Module: SubForms in Category Microsoft Access : Forms from Total Visual SourceBook

' Example of the modSubForms module
'
' To use this example:
'
' This example has been configured in database named Sample.mdb in the TVSB Sample directory
' (usually C:\Total Visual SourceBook 2013\Samples\Sample.mdb). To use the existing sample, follow these steps:
' 1. Open Sample.mdb from the directory where you installed Total Visual SourceBook samples (e.g. C:\Total Visual SourceBook 2013\Samples\Sample.mdb)
' 2. Export the module modSubForms into Sample.mdb.
' 3. Ensure that the database has no missing references, and that the code compiles properly.
' 4. Run the form named frmDepartment, which contains the example for modSubForms.
'
' -----------------------------------------------------------------------------------------------------------------------
' To create the sample database from scratch, create a database with the following objects:
'
' 1. Table "tblDepartment" with the following fields:
'     DepartmentID - AutoNumber; PrimaryKey
'     DepartmentName - Text; 255
'
' 2. Table "tblPeople" with the following fields:
'     PeopleID - AutoNumber; PrimaryKey
'     FK_DepartmentID - Number
'     FirstName - Text; 255
'     LastName - Text; 255

' 3. Form "frmDepartment"
'     - Set the Record Source property of "frmDepartment" to "tblDepartment"
'     - Create a TextBox control named "txtDepartmentID"
'       - Set the Record Source property of "txtID" to "DepartmentID"
'     - Create a TextBox control named "txtDepartmentName"
'       - Set the Record Source property of "txtDepartmentName" to "DepartmentName"
'     - Create a TextBox control named "txtSubMaxRecordsAllowed"
'       - Set the Format property to "General Number"
'     - Create a CheckBox named "chkPreventAdditionsIfMaxRecordsMet"
'     - Create a Command Button named "cmdShowRecordCount"
'     - Create a SubForm control named "subPeopleInDepartment"
'     - Create a Command Button named "cmdJumpToRecord"
'
' 4. Form "frmPeople"
'     - Set the Record Source property of "frmPeople" to "tblPeople"
'     - Set the Default View property to "Datasheet"
'     - Create a TextBox control named "txtPeopleID"
'       - Set the Record Source property of "txtPeopleID" to "PeopleID"
'     - Create a TextBox control named "txtFK_DepartmentID"
'       - Set the Record Source property of "txtFK_DepartmentID" to
'         "FK_DepartmentID"
'     - Create a TextBox control named "txtFirstName"
'       - Set the Record Source property of "txtFirstName" to "FirstName"
'     - Create a TextBox control named "txtLastName"
'       - Set the Record Source property of "txtLastName" to "LastName"
'
'     - CLOSE "frmPeople"
'
' 5. Open "frmDepartment" in design view
'     - Select the SubForm Control named "subPeopleInDepartment"
'       - Set the Source Object property to "frmPeople"
'       - Set the Link Master Fields property to "DepartmentID"
'       - Set the Link Child Fields property to "FK_DepartmentID"
'
' 6. For each event on the subform you want to subclass, you should have a
'    placeholder for the event in the subform or the event may not fire properly.
'    By "placeholder" we mean, you must have the Stub for the event in the
'    subform. Paste the following into "frmPeople" module. e.g.:
'
'Private Sub Form_Unload(Cancel As Integer)
''This comment character is important to have
''in the stub event or the compiler may remove the event
'End Sub
'
'    With the code above in the subform, you can now catch the Form_Unload
'    from the parent when it fires for the subform. You should repeat this
'    for each event on the subform you want to catch from the parent. We have
'    included all of the stub events at the bottom of this module. They are
'    commented out, and you can simply cut and paste, then uncomment as needed.
'    NOTE: You will need to uncomment the stubs for at least the following events:
'    Form_Activate(), Form_AfterUpdate(),Form_BeforeDelConfirm(),Form_Current()
'
' 7. Paste the entire contents of this example code into the frmDepartment form module.

Public WithEvents mSubForm As Access.Form

Private Sub chkPreventAdditionsIfMaxRecordsMet_Click()
  ' Disallow adding subrecords if the max allowable is met
  AllowDisallowAdditionsToForm Me.subPeopleInDepartment.Form, CLng(txtSubMaxRecordsAllowed), Nz(chkPreventAdditionsIfMaxRecordsMet, 0)
End Sub

Private Sub cmdJumpToRecord_Click()
  ' Example for ResyncSubformRecord
  ResyncSubformRecord Me, "subPeopleInDepartment", "PeopleID", InputBox("Enter the PeopleID of the record to jump to:")
  ' Example for ResyncSubformRecordADP.
  ' ResyncSubformRecordADP Me, "subPeopleInDepartment", "PeopleID", InputBox("Enter the PeopleID of the record to jump to:")
End Sub

Private Sub cmdShowRecordCount_Click()
  ' Return the number of records in the subForm
  MsgBox "Number of records in subPeopleInDepartment: " & GetRecordCount(Me.subPeopleInDepartment.Form.RecordsetClone)

End Sub

Private Sub txtSubMaxRecordsAllowed_AfterUpdate()
  ' Disallow adding subrecords if the max allowable is met
  AllowDisallowAdditionsToForm Me.subPeopleInDepartment.Form, CLng(txtSubMaxRecordsAllowed.Text), Nz(chkPreventAdditionsIfMaxRecordsMet, 0)
End Sub

Private Sub Form_Current()
  ' Disallow adding subrecords if the max allowable is met
  AllowDisallowAdditionsToForm Me.subPeopleInDepartment.Form, CLng(txtSubMaxRecordsAllowed), Nz(chkPreventAdditionsIfMaxRecordsMet, 0)
End Sub

Private Sub Form_Open(Cancel As Integer)
  ' Set the mSubForm variable to the name of the subform control and .Form property
  ' You can now use the mSubForm variable and events throughout the parent form.

  Set mSubForm = Me.subPeopleInDepartment.Form

  ' Set the Max records allowed
  Me.txtSubMaxRecordsAllowed = 4
  Me.chkPreventAdditionsIfMaxRecordsMet = True

  ' Disallow adding subrecords if the max allowable is met
  AllowDisallowAdditionsToForm Me.subPeopleInDepartment.Form, CLng(txtSubMaxRecordsAllowed), Nz(chkPreventAdditionsIfMaxRecordsMet, 0)
End Sub

' All of the events from here down are the subclassing in action. e.g. When the mSubForm event fires, you are trapping for it on the parent now.

Private Sub mSubForm_Activate()
  ' NOTE: Because the Activate Event of the SubForm fires before the Open Event of the Parent, this will not fire when the subform is first
  ' opened UNLESS you set the mSubForm variable from the subform. Subsequent Activate events raised will fire here appropriately.

  MsgBox "Subclassed Activate Event"
End Sub

Private Sub mSubForm_AfterUpdate()
  ' Disallow adding subrecords if the max allowable is met

  AllowDisallowAdditionsToForm Me.subPeopleInDepartment.Form, CLng(txtSubMaxRecordsAllowed), Nz(chkPreventAdditionsIfMaxRecordsMet, 0)
End Sub

Private Sub mSubForm_BeforeDelConfirm(Cancel As Integer, Response As Integer)
  ' Supress the standard delete confirmation dialog box

  Response = acDataErrContinue

  If MsgBox("Are you sure you want to delete this record?", vbYesNoCancel) <> vbYes Then
    Cancel = True
  Else
    'Do nothing
  End If
End Sub

Private Sub mSubForm_Current()
  ' NOTE: Because the Load Event of the mSubForm fires before the Open Event of the Parent, this will not fire when the subform is first
  '       opened UNLESS you set the mSubForm variable from the subform. Subsequent Activate events raised will fire here appropriately.
  '       Disallow adding subrecords if the max allowable is met

  AllowDisallowAdditionsToForm Me.subPeopleInDepartment.Form, CLng(txtSubMaxRecordsAllowed), Nz(chkPreventAdditionsIfMaxRecordsMet, 0)

End Sub

Private Sub mSubForm_Load()
  ' NOTE: Because the Load Event of the mSubForm fires before the Open Event of the Parent, this will not fire when the subform is first
  '       opened UNLESS you set the mSubForm variable from the subform.
End Sub

Private Sub mSubForm_Open(Cancel As Integer)
  ' NOTE: Because the Open Event of the mSubForm fires before the Open Event of the Parent, this will not fire when subclassing a subform.
End Sub

Private Sub mSubForm_Resize()
  ' NOTE: Because the Resize Event of the mSubForm fires before the Open Event of the Parent, this will not fire when the subform is first
  ' opened UNLESS you set the mSubForm variable from the subform. Subsequent Resize events raised will fire here appropriately.
End Sub

' STUB EVENTS TO PLACE IN THE SUBFORM'S MODULE (NOT THE PARENT!).
' Uncomment the Private Sub and End Sub lines to enable the event to be subclassed properly.
'
'Private Sub Form_Activate()
'  ' This comment character is important to have in the stub event or the compiler may remove the event
'End Sub
'
'Private Sub Form_AfterDelConfirm(Status As Integer)
'  ' This comment character is important to have in the stub event or the compiler may remove the event
'End Sub
'
'Private Sub Form_AfterInsert()
'  ' This comment character is important to have in the stub event or the compiler may remove the event
'End Sub
'
'Private Sub Form_AfterUpdate()
'  ' This comment character is important to have in the stub event or the compiler may remove the event
'End Sub
'
'Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
'  ' This comment character is important to have in the stub event or the compiler may remove the event
'End Sub
'
'Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
'  ' This comment character is important to have in the stub event or the compiler may remove the event
'End Sub
'
'Private Sub Form_BeforeInsert(Cancel As Integer)
'  ' This comment character is important to have in the stub event or the compiler may remove the event
'End Sub
'
'Private Sub Form_BeforeUpdate(Cancel As Integer)
'  ' This comment character is important to have in the stub event or the compiler may remove the event
'End Sub
'
'Private Sub Form_Click()
'  ' This comment character is important to have in the stub event or the compiler may remove the event
'End Sub
'
'Private Sub Form_Close()
'  ' This comment character is important to have in the stub event or the compiler may remove the event
'End Sub
'
'Public Sub Form_Current()
'  ' This comment character is important to have in the stub event or the compiler may remove the event
'End Sub
'
'Private Sub Form_DblClick(Cancel As Integer)
'  ' This comment character is important to have in the stub event or the compiler may remove the event
'End Sub
'
'Private Sub Form_Deactivate()
'  ' This comment character is important to have in the stub event or the compiler may remove the event
'End Sub
'
'Private Sub Form_Delete(Cancel As Integer)
'  ' This comment character is important to have in the stub event or the compiler may remove the event
'End Sub
'
'Public Sub Form_Dirty(Cancel As Integer)
'  ' This comment character is important to have in the stub event or the compiler may remove the event
'End Sub
'
'Private Sub Form_Error(DataErr As Integer, Response As Integer)
'  ' This comment character is important to have in the stub event or the compiler may remove the event
'End Sub
'
'Private Sub Form_Filter(Cancel As Integer, FilterType As Integer)
'  ' This comment character is important to have in the stub event or the compiler may remove the event
'End Sub
'
'Private Sub Form_GotFocus()
'  ' This comment character is important to have in the stub event or the compiler may remove the event
'End Sub
'
'Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
'  ' This comment character is important to have in the stub event or the compiler may remove the event
'End Sub
'
'Private Sub Form_KeyPress(KeyAscii As Integer)
'  ' This comment character is important to have in the stub event or the compiler may remove the event
'End Sub
'
'Private Sub Form_KeyUp(KeyCode As Integer, Shift As Integer)
'  ' This comment character is important to have in the stub event or the compiler may remove the event
'End Sub
'
'Public Sub Form_Load()
'  ' This comment character is important to have in the stub event or the compiler may remove the event
'End Sub
'
'Private Sub Form_LostFocus()
'  ' This comment character is important to have in the stub event or the compiler may remove the event
'End Sub
'
'Private Sub Form_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
'  ' This comment character is important to have in the stub event or the compiler may remove the event
'End Sub
'
'Private Sub Form_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
'  ' This comment character is important to have in the stub event or the compiler may remove the event
'End Sub
'
'Private Sub Form_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
'  ' This comment character is important to have in the stub event or the compiler may remove the event
'End Sub
'
'Public Sub Form_Open(Cancel As Integer)
'  ' This comment character is important to have in the stub event or the compiler may remove the event
'  ' If you want the Form Events to fire on the parent when this form is loaded
'  ' as a subform, you can uncomment the code below AND comment out frmDepartment's
'  ' Form_Open line of code: "Set subForm = Me.subPeopleInDepartment.Form"
'  '
'  ' NOTE: The subclassed event "subForm_Open" on frmDepartment will not fire because
'  ' this function is already firing prior to setting the subForm variable on the parent.
'  ' However, the subForm_Load, subForm_Current, etc., events will fire appropriately.
'
'  ' First check to see if the Parent is open. If it is not, the code will not execute.
'  If SysCmd(acSysCmdGetObjectState, acForm, "frmDepartment") <> 0 Then
'    If Forms("frmDepartment").CurrentView > 0 Then
'      Set Me.Parent.subForm = Me
'    End If
'  End If
'End Sub
'
'Private Sub Form_Resize()
'  ' This comment character is important to have in the stub event or the compiler may remove the event
'End Sub
'
'Private Sub Form_Timer()
'  ' This comment character is important to have in the stub event or the compiler may remove the event
'End Sub
'
'Private Sub Form_Unload(Cancel As Integer)
'  ' This comment character is important to have in the stub event or the compiler may remove the event
'End Sub

The source code in Total Visual Sourcebook includes modules and classes for Microsoft Access, Visual Basic 6 (VB6), and Visual Basic for Applications (VBA) developers. Easily add this professionally written, tested, and documented royalty-free code into your applications to simplify your application development efforts.

Total Visual SourceBook is written for the needs of a developer using a source code library covering the many challenges you face. Countless developers over the years have told us they learned some or much of their development skills and tricks from our code. You can too!

Additional Resources

• List of All Modules in Total Visual SourceBook
• List of All Code Categories in Total Visual SourceBook
• Sample Module Code from Total Visual SourceBook (Example of Date, Time and Age Calculations)