I have a Microsoft Access form with four command buttons that display specific forms using the DoCmd.OpenForm action. I want to hide the main database switchboard form whenever one of the four buttons is clicked on and then restore the main form when the user closes the form opened by the command button.
I was expecting to use "Me" or "Screen.ActiveForm" to hide the active form, but neither provided the Hide method. Only the RunCommand acCmdWindowHide worked, however, I did find a command that would automatically unhide a form by specifying the form name in code. RunCommand acCmdWindowUnHide displays a dialog, and the user has to choose the form to unhide.
Answer »
Good database application design often involves tightly controlling the user interface so the user only sees the form that's currently needed by the application. A cluttered screen can lead to confusion on the user's part, and make the application more difficult than necessary to work with.
I can think of two different ways to hide the main switchboard form. Which technique you choose depends on your objectives and the complexity of your application.
The first technique is the simplest. Close the switchboard form in the Click event procedure that opens the second form. Use the second form's Unload event procedure to reopen the main form. VBA code is procedure-oriented. This means the main form doesn't actually close until the procedure has finished, so there's no danger that you'll somehow process part of a procedure before the form closes and disappears from the screen. Figure 1 shows an example of the code I have in mind.
Figure 1: Open and shut case - The main form closes itself as it opens the second form while the second form opens the main form as it's unloaded.
'----------------------------------------
'In the main (switchboard) form:
'----------------------------------------
Private Sub cmdOpen_Click()
DoCmd.Close acForm, Me.Name
DoCmd.OpenForm "Form1"
End Sub
'----------------------------------------
'In the second form:
'----------------------------------------
Private Sub Form_Unload(Cancel As Integer)
DoCmd.OpenForm "frmSwitchboard"
End Sub
The only problem with this technique is that opening and closing the switchboard form may be slow if it's complicated, contains bound data, or has some other feature (such as a large graphic).The second technique hides the main form by setting its Visible property to False, avoiding performance issues if the main form is slow to open. The second form restores the main form by setting its Visible property back to True (Figure 2).
Figure 2: Now you see it - The main form is effectively hidden or displayed by changing its Visible property.
'----------------------------------------
'In the main form:
'----------------------------------------
Private Sub cmdOpen2_Click()
Me.Visible = False
DoCmd.OpenForm "Form2"
End Sub
'----------------------------------------
'In the second form:
'----------------------------------------
Private Sub Form_Unload(Cancel As Integer)
Forms!frmSwitchboard.Visible = True
End Sub
The only thing I don't like about these techniques is they reference the main switchboard form by name. You might consider using the OpenArgs argument to pass the name of the main switchboard form to the second form, and use the second form's OpenArgs property to restore the main form, as shown in Figure 3.
Figure 3: Now you don't - Avoid hard coding a form' s name.
'------------------------------------------------------
'In the main form:
'------------------------------------------------------
Private Sub cmdOpen3_Click()
Me.Visible = False
DoCmd.OpenForm "Form3", OpenArgs:=Me.Name
End Sub
'------------------------------------------------------
'In the second form:
'------------------------------------------------------
Private Sub Form_Unload(Cancel As Integer)
Forms(Me.OpenArgs).Visible = True
End Sub
I really like Figure 3. It lets you avoid hard-coding a form's name, which means the second form can be opened by any other form in the application without having to worry about which form needs to be restored. The name of the form to be restored is in the OpenArgs property.