Forms Interaction

It is quite frequently needed to open an auxiliary form from a main one to enter some information. Data entered in the auxiliary form is expected to appear in the main one and optionally treated in some way, the aixiliary form is expected to give some feedback to the main one etc.

This howto displays several approaches.

• The first method is applicible when the auxiliary form is expected to provide an enhanced interface to a record the main form is pointed to. For example a bigger Textbox to enter long text, Calendar control etc.
  The main idea of the method is to bind the aixiliary form and its control(s) to the same datasource as that of the main one.
  
  Main form code
  
  Expand|Select|Wrap|Line Numbers

  1. 'opens child form and binds its control to the same datasource
  2. 'as the main one
  3. 'data in both forms synchronized because they both bound
  4. 'to the same data source
  5.  
  6. Private Sub btnOpenChildForm1_Click()
  7.  
  8.     DoCmd.OpenForm "frmChild1"
  9.  
  10.     With Forms!frmChild1
  11.         'set ControlSource of child form control to
  12.         'the same as im main one
  13.         .txb.ControlSource = Me.txb.ControlSource
  14.         'set child form Recordset to the same as the main one
  15.         Set .Recordset = Me.Recordset
  16.         'make child form modal
  17.         .Modal = True
  18.     End With
  19.  
  20. End Sub
  21.  
  Child form (frmChild1) code
  
  Expand|Select|Wrap|Line Numbers

  1. Private varOldValue As Variant 'holds control original value
  2.  
  3. Private Sub btnCancel_Click()
  4.     Me.txb = varOldValue    'restore original value
  5.     CloseMe
  6. End Sub
  7.  
  8. Private Sub btnSubmit_Click()
  9.     CloseMe
  10. End Sub
  11.  
  12. Private Sub CloseMe()
  13.     DoCmd.Close acForm, Me.Name
  14. End Sub
  15.  
  16. Private Sub txb_BeforeUpdate(Cancel As Integer)
  17.     'store control original value before first update occur
  18.     If IsEmpty(varOldValue) Then varOldValue = Me.txb.OldValue
  19. End Sub
  20.  
  Note: Child form could be optionally bound to a clone of main form recordset. That gives an opportunity to effectively use Me.Undo method in the child form but requires additional logic to refresh main form manually.
  
• The second method gives more opportunities for data validation performed in a main form. The main idea of the method is to pass reference(s) to the main form control(s) to a child form via property to give it an opportunity to write back to the main form.
  
  Main form code
  
  Expand|Select|Wrap|Line Numbers

  1. 'opens child form and passes reference to main form control
  2. 'via child form property
  3. 'data in main form will be updated by child form
  4.  
  5. Private Sub btnOpenChildForm2_Click()
  6.  
  7.     DoCmd.OpenForm "frmChild2"
  8.  
  9.     With Forms!frmChild2
  10.         'pass reference to main form control
  11.         'to child form via its property
  12.         Set .ConnectedTextBox = Me.txb
  13.         'make child form modal
  14.         .Modal = True
  15.     End With
  16.  
  17. End Sub
  18.  
  Child form (frmChild2) code
  
  Expand|Select|Wrap|Line Numbers

  1. 'form global variable to store reference to main form control
  2. Private objConnectedTextBox As Access.TextBox
  3.  
  4. 'code to get property value
  5. Public Property Get ConnectedTextBox() As Access.TextBox
  6.     Set ConnectedTextBox = objConnectedTextBox
  7. End Property
  8.  
  9. 'code to set property value
  10. Public Property Set ConnectedTextBox(ByRef objNewValue As Access.TextBox)
  11.     Set objConnectedTextBox = objNewValue
  12.     'set child form control value to that of main form
  13.     Me.txb.Value = objNewValue.Value
  14. End Property
  15.  
  16. Private Sub btnCancel_Click()
  17.     CloseMe
  18. End Sub
  19.  
  20. Private Sub btnSubmit_Click()
  21.     With Me
  22.         'write child form control value back to main form one
  23.         .ConnectedTextBox.Value = .txb.Value
  24.     End With
  25.     CloseMe
  26. End Sub
  27.  
  28. Private Sub CloseMe()
  29.     DoCmd.Close acForm, Me.Name
  30. End Sub
  31.  
• The main idea of the third method is to let a child form to invoke code in the main one via events and to pass values to it. The main form code makes the rest which provides high flexibility to how a data entered in the child form is being treated in the main form.
  
  Main form code
  
  Expand|Select|Wrap|Line Numbers

  1. 'object of Form_frmChild3 type is declared with events
  2. Dim WithEvents frmChild3 As Access9db.Form_frmChild3
  3.  
  4. 'opens child form and waits for its events
  5. 'data in main form will be updated according to what
  6. 'child form will return via events
  7.  
  8. Private Sub btnOpenChildForm3_Click()
  9.  
  10.     DoCmd.OpenForm "frmChild3"
  11.     Set frmChild3 = Forms!frmChild3
  12.     With frmChild3
  13.         'pass main form control value to child form control
  14.         .txb = Me.txb
  15.         'make child form modal
  16.         .Modal = True
  17.     End With
  18.  
  19. End Sub
  20.  
  21. Private Sub frmChild3_InputCancelled()
  22.  
  23.     MsgBox "Input cancelled"
  24.     'destroy reference to frmChild3 no longer valid
  25.     Set frmChild3 = Nothing
  26.  
  27. End Sub
  28.  
  29. Private Sub frmChild3_ValueSubmitted(varValue As Variant)
  30.  
  31.     'set main form control value to what child form returned
  32.     Me.txb.Value = varValue
  33.     MsgBox "Input submitted"
  34.     'destroy reference to frmChild3 no longer valid
  35.     Set frmChild3 = Nothing
  36.  
  37. End Sub
  38.  
  Child form (frmChild3) code
  
  Expand|Select|Wrap|Line Numbers

  1.  
  2. 'events declaration
  3. Event ValueSubmitted(varValue As Variant)
  4. Event InputCancelled()
  5.  
  6. Private Sub btnCancel_Click()
  7.     RaiseEvent InputCancelled
  8.     CloseMe
  9. End Sub
  10.  
  11. Private Sub btnSubmit_Click()
  12.     RaiseEvent ValueSubmitted(Me.txb.Value)
  13.     CloseMe
  14. End Sub
  15.  
  16. Private Sub CloseMe()
  17.     DoCmd.Close acForm, Me.Name
  18. End Sub
  19.  
  Note: Child form control(s) and/or form object could be declared with events in the main form module to handle correspondent event(s). That however goes slightly off size of this single howto. :D

The examples has been implemented in the attached database.
A particular implementation of the approaches described above or combinations thereof depends on particular circumstances allowing to reveal advantages and to tolerate drawbacks of each one.

Attached Files

FormsInteractions.zip (24.3 KB, 35 views)