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

Stretching Controls when resizing Form View

Introduction

A sample image of an Employee's Form Design is given below:

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

When you view this screen in a maximized Application Window, the view will be something like the following image:

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

The normal view of the Employees Form, in maximized Application Windows, shows an empty stretch of space to the right of the data fields.  The Form Title Employees stay where it is placed in a Label control, centralized over the data field controls.

Now, look at the following image taken after the Anchor Settings to the controls, which respond dynamically to move or stretch across the screen based on the resizing of the screen:

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

Compare the two images shown above. Notice how the layout changes automatically when the form window is maximized:

• The second column of controls shifts neatly to the right edge of the screen.

• The controls on the left side expand horizontally, filling up unused space and giving more room for data entry or viewing.

• The form heading (“Employees”) realigns itself to the center of the screen.

• The Note field (a memo field) stretches both downward and across, making it much easier to view and edit larger amounts of text.

Curious to know how this neat effect works in MS Access 2007? If you already have a form with a design similar to the one shown in the first image, you can try it out yourself — and here’s the best part: this trick works with any form!

Design a Form.

Let us design a Form with the Employees Table from the sample database Northwind.

1. Import the Employees Table from the Northwind sample database.

2. Create a form with a similar Design shown at the top of this page.  You can use the Form Wizard to create the form in Column format and rearrange the controls.

3. Select all the controls of the second column together and move them down to get enough space for the Fax Number and Address fields, which we will bring from the first column, and place them on top of the second column.

4. Select Fax Number and Address Fields, and right-click on them to display the shortcut menu.

5. Select Cut from the menu to remove both text boxes and their child labels from the first column controls group.

6. Right-click somewhere in the Detail Section and select Paste to paste them back into the detail section.

7. Move the pasted controls and place them on top of the second column of controls.

8. Select and cut the Note Field and its child label from the second column and place them below the first column of controls.

Up to this point, everything we did was part of a standard form design process — arranging controls into columns as usual. However, there’s one important exception: the Note field. Instead of keeping it in the second column’s control group, we deliberately moved it below the first column as a standalone control. This way, it is no longer tied to the group’s layout behavior and can be assigned a different Anchoring property, allowing it to stretch independently when the form is resized.

1. Save the Form and open it in Form View to check what the current design looks like.

   Implementing the Trick.

2. Change the Form in Design View.

   Let’s begin applying these tricks to the controls on our form, starting from the top.

   Step 1: Centering the Heading Label
   We want the form heading to remain horizontally centered whenever the form is resized. If the text “Employees” in the header label is not already centered within its current width, select the label and click the Center button on the Design tab of the ribbon.

   Step 2: Making the Heading Responsive
   Next, we want the label itself to stretch across the available width of the form so that the caption “Employees” always stays centered—whether the form is maximized or manually resized. To achieve this:

   1. Select the heading label control.

   2. Open the Property Sheet (if not already open).

   3. Locate the Anchoring property.

   4. Change the setting to Stretch Across Top.

   With this setting, the label automatically resizes with the form, and its caption remains centered no matter how the window is adjusted.

3. Click on the heading label to select it.

4. Select Arrange ->Anchoring -> Stretch Across Top.

   Testing the Heading Label Behavior

   1. Open the form in Normal View.

   2. Maximize the window and check whether the form title Employees moves to the center across the expanded screen.

   3. Next, manually resize the form:

      • Move the mouse pointer to the right edge of the form until it changes to the horizontal sizing arrow.

      • Hold down the left mouse button and slowly drag the edge inward.

      • Watch how the heading label automatically adjusts its width and keeps the caption centered.

   4. For a quicker test, minimize the Navigation Pane and then display it again. The form window will expand and shrink instantly, showing the anchoring effect in action.

   Moving On

   Now that the header label behaves as expected, let’s play some anchoring tricks with the other controls in the Detail Section. This is where we’ll make the second column “stick” to the right edge and stretch the first column controls for better usability when resizing.

5. Place the Form back into Design View.

6. Highlight and select all the second column TextBoxes.

7. Select Arrange ->Anchoring -> Top Right or right-click on the selected controls and select Anchoring -> Top Right from the shortcut menu.

8. Change the form in Form View and preview the effect of our setting. Change the form back into Design View again.

9. Select the TextBoxes in the left column, except the Note field.

10. Right-click on the controls and select Anchoring -> Stretch Across Top.

11. Select the Notes field, right-click on the control, and select Anchoring -> Stretch Down and Across.

All the anchoring settings are now complete. Save the form and switch to Normal View. Try resizing the form window manually—dragging the edges slowly—to watch how each control responds in slow motion. You’ll see the title label, the right-side controls, and the Note field all adapt smoothly to the changing form size.

• 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
• Access Security Key Diagram