Back Tracking Open Forms

Introduction

On the Internet, when we browse from one webpage to another, the browser provides Back and Forward buttons to move through previously visited pages one at a time.

A similar approach can be applied in Microsoft Access. During startup, we can open several forms sequentially and keep them hidden in memory. The question is: how do we move back and forth between these forms—just like navigating web pages? Fortunately, there is a way to achieve this.

When multiple forms are required for day-to-day operations, it is often a good practice to open them all at once (immediately after the application starts) and keep them hidden. Although this may introduce a slight delay during startup, it significantly improves performance later, since forms are displayed directly from memory rather than being opened and closed repeatedly. All hidden forms can also be closed easily with a short VBA routine before shutting down the application.

If navigation from one form to another is needed in a predictable sequence, you can automate the process with a simple macro by arranging the forms in the desired order. The sample macro shown below demonstrates how to open multiple forms in sequence.

Right-click to open Large Image in New Tab/Window.

In the sample macro, the first OpenForm action opens the Employees4 form in Normal View mode, while the other forms are opened in Hidden mode so they remain in memory. When needed, any of these hidden forms can be made visible, while the currently active form can be set to hidden at the same time. This ensures that only one form is visible at a time, preventing the application window from becoming cluttered with multiple open forms.

Prepare for a Trial Run.

Let us try an example before exploring other aspects of this interesting method.

1. Import the Employees Table and Employees Form from Northwind.mdb (or Northwind) sample database.

   Check the sample image given below.

   

   Right-click to open Large Image in New Tab/Window.

2. Rename the Employees form as Employees1.

3. Open the 'Employees1' Form in Design View.

4. Add a label control in the Header of the Form and change the Caption value to 1, change the font size to 16, and the foreground color to White or any other bright color suitable for the background.

5. Expand the Footer of the Form.

6. Add two Command Buttons in the Form Footer as shown above.

7. Click on the left side Command Button to select it.

8. Display its Property Sheet (F4).

9. Change the Name property value to Back and change the Caption property value to << (two less than symbols).

   Two Button-Click Event Sub-Routines.

10. Select the OnClick Event property and select [Event Procedure] from the drop-down list, and click on the build (...) Button to open the VBA editing window with the empty Sub-routine stub: Private Sub Back_Click() . . . End Sub.

11. Copy the following Code and paste it, overwriting the sub-routine lines in the VBA Module:

    Private Sub Back_Click()
      ForwardBack "B", Me.Name
    End Sub

    Note: ForwardBack() is a Function we will write and add to the Standard Module.

12. Repeat steps 7 to 10 for the right-side Command Button by changing the Name property value to Forward() and the Caption property value to >> (two greater than symbols).

13. Copy the following Code and paste it, overwriting the sub-routine starting and ending lines in the VBA Module:

    Private Sub Forward_Click()
      ForwardBack "F", Me.Name
    End Sub

14. Save and close the Form.

    The Move ForwardBack() Function

15. Copy and paste the following Code into a Standard Module in your Database and save it:

    Public Function ForwardBack(ByVal strStatus As String, ByVal strForm As String)
    Dim frmCount As Integer, j As Integer
    
    On Error GoTo ForwardBack_Err
    
    'get count of open forms in memory
    frmCount = Forms.Count - 1
    For j = 0 To frmCount
    Select Case strStatus
          Case "B" 'Move Back
            If Forms(j).Name = strForm And j - 1 >= 0 Then
               DoCmd.SelectObject acForm, Forms(j - 1).Name, False
               Forms(strForm).Visible = False
               Forms(j - 1).Visible = True
               Exit For
            End If
         Case "F" 'Move Forward
            If Forms(j).Name = strForm And frmCount > j Then
               DoCmd.SelectObject acForm, Forms(j + 1).Name, False
               Forms(strForm).Visible = False
               Forms(j + 1).Visible = True
               Exit For
            End If
    End Select
    Next
    
    ForwardBack_Exit:
    Exit Function
    
    ForwardBack_Err:
    MsgBox Err & ":" & Err.Description, , "ForwardBack()"
    Resume ForwardBack_Exit
    End Function

    The ForwardBack() Function needs two parameters when called:

    The first parameter can be either "B" or "F".

    • Use “B” as the first parameter value when called from the << (Go Back) labeled Command Button Click Event Procedure and with "F" for >> (Go Forward) labeled Command Button Click Event Procedure.

    • The second parameter is the active Form's name, which can be passed with the 'Me.Name' statement.

16. Make 4 more copies of the Employees1 Form and name them as Employees2 to Employees5. The index in the header labels should also change to 2, 3, 4, and 5 on their respective Forms.

    Since all the forms are copies of the same form, this number will help us to distinguish one from the other.

17. Create a Macro similar to the sample image shown at the top of this page to open the forms, and keep them hidden in memory except for one.  You may define any one of the form’s Window Mode as Normal to make that form visible in the Application Window, while all other forms stay hidden.

18. Save the Macro with the name Macro1.

Test Run our Creation and Program.

Now, it is time to test our Project.  First, let us test our Project manually without using Macro1.

1. Open Forms Employees1 to Employees5 manually, one by one, from the Navigation Pane.

2. You now have all the Forms opened in the Application Window.  The Employees5 form is on top, with the form header label displaying the number 5.

   When multiple forms are opened in this manner, they are stored in memory within the Forms Collection Object, arranged in the order they were opened. The first form opened can be accessed using index 0 (e.g., Forms(0) or Forms("Employees1") in VBA). You can retrieve the form’s name with the Name property (Forms(0).Name), and in the same way, access other properties of the form. The second form opened will have index 1, the third will have index 2, and so on.

   Keep in mind that the suffix numbers we added to the Employees forms (e.g., Employees1, Employees2, …) have nothing to do with these internal index numbers. Forms can be opened in any order you like; you don’t need to start with Employees1 and end with Employees5. However, following a simple naming sequence at the beginning can make it easier to test and understand the program.

   Click on the Command Button with the >> (Go Forward) symbols on it to move forward to the next form, but nothing will happen because this is the last form in the opened Forms Collection now.

3. Click on the Command Button with the << (Go Back) symbols to make the Employees4 form visible and to place the current form Employees5 in the hidden state in memory.

4. Repeat step 2 to make the Employees3 form visible and continue doing this till you reach Form Employees1. 

   At this stage, clicking the Back button (<<) on this form will not produce any response because it is the first form we opened. However, if the forms were opened in a different order, the button would work as expected. When you arrive at the Employee1 form, all the other forms remain in memory in a hidden state, since our main program is designed to keep them that way.

5. Try to move forward by clicking on the >> button to make the other forms become visible one by one, hiding the earlier forms.

Closing All Open Forms - The CloseAllForm() Function

Tip: If you want to make changes to any of these forms while they are hidden in memory, simply right-click the form’s name in the Navigation Pane and select Design View. The form will open directly in Design View.

To close all the open forms (both hidden and visible) in one go while shutting down the application, you can use a simple VBA routine named CloseAllForms(). This routine can be called from a command button’s Click event procedure, right before executing the DoCmd.Quit statement. You may also run the program directly from the VBA window while testing the procedure.

Copy and paste the following code into a Standard Module of your project:

Public Function CloseAllForms()

Dim j
'when no forms are in open state
'then forms.count -1 returns -1 and
'the For...Next loop is not executed

'When a form is closed
'other forms in memory are re-indexed automatically
For j = 0 To Forms.Count - 1
  DoCmd.Close acForm, Forms(0).Name
Next
End Function

Click anywhere inside the code and press F5 to run it. This will close all the open forms.

In the earlier test, we opened all the forms manually. Instead, you can run the macro (Macro1) we created earlier to open all the forms at once, keeping all of them hidden except one.

If you want this macro to run automatically when the database opens—so that all forms are loaded into memory and hidden—rename Macro1 to AutoExec.

During normal operations, users can open forms in any order, and they will remain in memory in the sequence in which they were opened. Users can then navigate through the open forms by clicking command buttons: >> (Go Forward) or << (Go Back).

• VBA Module Object and Methods
• Writing VBA Code with VBA
• Control Setfocus on Tab-Page Click
• Adding Data directly into an external Database
• Users and Groups Listing from Workgroup Information File