How to freeze Parent Form while Child Form is open

Question

I have a situation where, in certain circumstances, i need to open another form and keep that form focused (modal = true) and when they are dont and it closes, a control on the parent form is refreshed with possible data that might have changed.

Originally i had a method that would DoEvents while the child form was open but it cause several of the child forms to be unusable (they werent databound at the form level) for data-entry/edits.

ShowForm Method - Originally

Public Sub ShowForm(par As Form, nm As String, _
                    Optional whr As String = "", _
                    Optional args As String = "", _
                    Optional mode As AcFormOpenDataMode = acFormPropertySettings)
    DoCmd.OpenForm nm, acNormal, , whr, mode, , args

    While IsOpen(nm)
        DoEvents
    Wend
End Sub

In order for me to get the Child Forms to be usable consistently, i had to comment out the While...Wend loop.

Is there another logic pattern i could use either inside this method or the OnClick of the calling control, so that way when they close the Child Form i could have code execute after the closing of the childform?

Answer 1

Here is the final form of the method in my original question: Works just as i wanted (for now ;) ), thanx to @Remou & @mwolfe02.

Final Form - Tenative of course

'@frm - String value, Name of the form to Open
Public Sub OpenForm(frm As String, _
                    Optional vw As AcFormView = acNormal, _
                    Optional whr As String = "", _
                    Optional mode As AcFormOpenDataMode = acFormPropertySettings, _
                    Optional args As String = "")
    If FormExists(frm) Then
        DoCmd.OpenForm frm, vw, , whr, mode, acDialog, args
    Else
        RaiseError "Form ( " & frm & " ) does not exist!" & vbCrLf & vbCrLf & _
                    "Alert your IT Support for further assistance."
    End If
End Sub

Answer 2

I think I understand your issue now. Unfortunately, there is no simple way to do what you want. I would suggest the following algorithm:

'Grandparent/parent form module
Dim ChildForm As String

Private Sub ShowFormBtn_Click()
    DoCmd.OpenForm "MyForm"
    ChildForm = "MyForm"
End Sub    

Private Sub ShowOtherFormBtn_Click()
    DoCmd.OpenForm "OtherForm"
    ChildForm = "OtherForm"
End Sub

Private Sub Form_Activate()
    On Error Resume Next
    Forms(ChildForm).SetFocus
End Sub

Private Sub Form_Unload(Cancel As Integer)
    On Error Resume Next
    Forms(ChildForm).SetFocus
    If Err.Number = 0 Then Cancel = True
End Sub

Basically, each time you try to switch to a parent form it will try to send the user to its child form (if one exists). If it has no child, there is an error generated that is silently ignored. If the child does exist, it switches it to that form.

This supports multiple levels as well. So say you have a grandparent form that spawns a parent form that spawns a child form. All three forms are open. You click on the Grandparent form which sends focus to the Parent form which sends focus on to the Child form.

Answer 3

You don't want to lock the FORM itself, you want to lock the controls that are a part of the FORM. Below explains my preferred method for doing control lock-downs. The goal is to make every user-editable control read-only

in the VBA code for your parent FORM create this sub:

public sub LockControls_[Enter Form Name Here]()

  ME.txt1.Locked = True
  ME.txt2.Locked = True
  ME.txt3.Locked = True
...

end sub

You can lock down Text Boxes, Combo Boxes, Buttons, etc...

If your controls follow a naming structure like I outlined above, you can actually loop this by doing the following:

public sub LockControls_[Enter Form Name Here]()

  Dim s_ctrl As String
  Dim s_obj As String
  Dim i as Int

  i = 0
  s_ctrl = "txt"
  s_obj = s_ctrl & i

  do while me(s_obj).Name <> ""

      Me(s_obj).Locked = True
      i = i+1
      s_obj = s_ctrl & i

  loop
end sub

Call this method from the action which launches your child form. It will lock down (make read only) all controls on the form, while leaving the form accessible to the user.

This same method can be used to unlock your parent FORM when called from the closing event on the child form. Just be sure to NOT use Me(s_obj).Locked = False. Instead, fully qualify your form by using the syntax:

public sub UnLockControls_[Enter Parent Form Name Here]()

  Dim s_ctrl As String
  Dim s_obj As String
  Dim i as Int

  i = 0
  s_ctrl = "txt"
  s_obj = s_ctrl & i

  do while me(s_obj).Name <> ""

      FORMS![form_name](s_obj).Locked = False
      i = i+1
      s_obj = s_ctrl & i

  loop
end sub

Answer 4

The simplest approach is to simply open the form in dialog mode. For example,

DoCmd.OpenForm nm, acNormal, , whr, mode, acDialog, args

This will pause execution of the code in the calling module until the form is closed. It will also prevent the user from interacting with any other forms until the "dialog" form is closed.

---

I have found that opening bound forms in dialog mode, updating the data, closing the form, then refreshing an object on the calling form (for example, a combo box's row source) is not always reliable.

What follows is a generic function that I've written to "pause" the calling code without opening the form in dialog mode and without noticeably affecting performance of the user interface. It works for both forms and reports. Note that the Sleep API declaration must go at the top of the code module (in the module declaration section).

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

'-------------------------------------------------------------------------------
' Procedure : WaitTilObjClosed
' Author    : Mike
' Date      : 1/7/2009
' Purpose   : Halts program execution until user closes object.  User is 
'               generally unaffected by the loop.
' Requires  : Sleep API sub
' Notes - while Sleeping other programs can use processor but access cannot;
'       - during DoEvents, other parts of Access can use processor;
'       - without the Sleep call, processor usage stays at 100% for MSACCESS.EXE
'       - with a long Sleep call, Access becomes noticeably sluggish
'-------------------------------------------------------------------------------
'
Sub WaitTilObjClosed(ObjType As AcObjectType, ObjName As String)
    Do
        DoEvents
        Sleep 1
        If (SysCmd(acSysCmdGetObjectState, ObjType, ObjName) = 0) Then Exit Do
    Loop
End Sub

You would use it as follows:

DoCmd.OpenForm "MyForm"
WaitTilObjClosed acForm, "MyForm"
MsgBox "MyForm is now closed"

DoCmd.OpenReport "MyReport", acViewPreview
WaitTilObjClosed acReport, "MyReport"
MsgBox "MyReport is now closed"