July 2012 ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction

On the Internet, we may open several web pages, by visiting links on other web pages. When we do that we will be provided with buttons on the browser to move from the current page to the previously opened pages or move forward one page at a time from earlier visited pages.

Similarly, we can open several Forms, one after the other, during startup time and keep them hidden. In that case, how do we move from one form to the other, going back or forward, one by one? Yes, there is a way to do it.

When there are several forms to open for normal operations, then it is a good idea to open all those forms once, immediately after the application is open, and keep them in memory in a hidden state. This may cause a slight delay at the time of opening the application to become ready for normal operations, but later on, they will be best performed, when displaying them from memory, rather than opening and closing them every time. All the opened forms in memory can be closed easily with a small VBA program before shutting down the application.

If jumping from one form to the other is required in a predictable sequence, then all the forms can be opened through a simple macro by arranging the opening sequence in the required order. See an image of a sample macro given below that opens several forms in the required order.

The first OpenForm Action opens the first Form (Employees4) in the Normal View mode, others are in hidden mode to keep them in memory. The required form can be made visible, at the same time the active form can be kept hidden in memory too. By doing this, only one form will remain visible at a time rather than crowding all the forms in the application window.

Prepare for a Trial Run.

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

Import the Employees Table and Employees Form from Northwind.mdb (or Northwind) sample database.
Check the sample image given below.
Rename the Employees form as Employees1.
Open the Employees1 Form in Design View.
Add a label control on the Header of the Form and change the Caption value to 1, change the Font-size to 16, and the fore-color to White or any other bright color suitable for the background.
Expand the Footer of the Form.
Add two Command Buttons in the Form Footer as shown above.
Click on the left side Button to select it.
Display its Property Sheet (F4).
Change the Name property value to Back and change the Caption property value to << (two less than symbols).

Two Button-Click Event Sub-Routines.
Select the On Click 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.
Copy the following Code and Paste them over-writing the sub-routine lines in the VBA Module:
```
Private Sub Back_Click()
  ForwardBack "B", Me.Name
End Sub
```
Note: ForwardBack() is a Function we are going to write and add to the Standard Module.
Repeat step-7 to step-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).
Copy the following Code and Paste them over-writing the sub-routine starting and ending lines in the VBA Module:
```
Private Sub Forward_Click()
  ForwardBack "F", Me.Name
End Sub
```
Save and close the Form.

The Move ForwardBack() Function

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.

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

Since all the forms are copies of the same form this number will help us to distinguish one from the other.
Create a Macro similar to the sample image shown on 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.
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.

Open Forms Employees1 to Employees5 manually, one by one, from the Navigation Pane.
You have now all the Forms opened in the Application Window. The Employees5 form is on top, with the form header label showing the number 5.

When you open several forms this way they are placed in memory in the opened FORMS Collection Object in indexed order. The first form opened is addressable with zero index number as Forms(0) or Forms(“Employees1”) in VBA. The Form’s Name can be retrieved from the Name property of the opened form, like Forms(0).Name and similarly other properties of the form can be addressed as well. The second Form opened will have index number 1 and so on. The suffix numbers we have added to the Employees Form have nothing to do with these internal index numbers. You can open those Forms in any order you like, not necessarily that you should start with Employees1 and end with 5. It is better that way initially to test 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.
Click on the Command Button with the << (Go Back) symbols to make the Employees4 form visible and to place the current form Employees5 in a hidden state in memory.
Repeat step 2 to make the Employees3 form visible and continue doing this till you reach Form Employees1.

At this stage clicking on the back button (<<) on this Form will not show any response because this is the first form we have opened. But, if you have opened these forms in a different order then it will work. When you reach the Employee1 form all the other Forms are in a hidden state in memory because our main program is made to do that.
Try to move forward, by clicking on the >> button, to the other forms and make them visible one by one, hiding the earlier forms.

Closing All Open Forms - The CloseAllForm() Function

Tip: If you want to make some changes in any of these forms, while the form is in a hidden state in memory, you may right-click on the Form name in the navigation pane and select Design View. The Form will appear in Design View.

You can easily close all the opened forms (both hidden as well as visible ones) with a single click while shutting down the Application with a simple VBA routine CloseAllForms(). The CloseAllForms() program can be called from a Command Button Click Event Procedure and before executing the DoCmd.Quit statement. You may even run the program directly from the VBA window while testing the above procedures. 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 in the middle of the Code and press F5 to run it and close all the open forms.

For the above test, we have opened all Forms manually. You may run the macro (Macro1), we created earlier, to open all the forms at once, and keep all of them hidden except one.

If you want to run this macro automatically, immediately on opening the database, to open all the forms and keep them hidden then rename the macro (Macro1) as Autoexec.

Opening of Forms can happen during normal operations by Users and they will be in the order of their opening sequence in memory. Users can work with the open Forms by clicking on the Command Button with the symbols >> (to Go Forward) or by clicking on the Command Button with the symbols << (to Go Back) Command Buttons on the Form.

Introduction

A sample image of an Employees Form Design is given below:

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

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 re-sizing of the screen:

Compare both images given above. The second column of controls automatically moved to the right edge of the screen, when the screen is maximized. Left side controls stretched to the right filling empty areas on the form, giving more space for viewing or keying in information. The heading Employees moved to the center of the screen. The Note field (memo field type) stretched down and across to display or edit more text into the field very easily.

Want to find out how this trick works in MS-Access 2007? If you have a form with a design similar to the one shown on top of this page you may use that (this works with any form) to try this simple trick.

Design a Form.

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

Import Employees Table from the Northwind sample database.
Create a form with a similar design shown on top of this page. You can use the Form Wizard to create the form in Column format and rearrange the controls.
Select all the controls of the second column together and move it down, to get enough space for the Fax-Number and Address fields, which we are going to bring from the first column and place them on top of the second column.
Select Fax Number and Address Fields, and right-click on them to display the shortcut menu.
Select Cut from the menu to remove both text boxes and their child labels from the first column controls group.
Right-Click somewhere in the Detail Section and select Paste to paste them back into the detail section.
Move the pasted controls and place them on top of the second column of controls.
Select and cut the Note Field and its child label from the second column and places them below the first column of controls.

So far, what we did was a normal form design and re-arranging controls as we normally do. But, in the Note field, we have removed it from the second column controls group and placed it below the first column, as an independent control (not part of any control group), to apply a different Anchoring action.

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

Implementing the Trick.
Change the Form in Design View.
Let us start implementing the tricks one by one on the controls on the Form from the top. First, let us start with the header Label. We want this Form heading to get centralized within the available width of the form when the Form View expands or shrinks. If the text Employees in the header title label, is not centralized within the current label width, then select the label, and select the Center Ribbon button from Design Menu. Now, we are ready to implement the magic settings on the controls.
When the screen is maximized, we want the heading label, Stretch Across to the width of the screen so that the heading text Employees automatically move and center horizontally on the enlarged screen. If the form is resized manually to make it shrink or expand the label should respond accordingly. To make it happen do the following:
Click on the heading label to select it.
Select Arrange - - >Anchoring - - >Stretch Across Top.
To test the effect of this setting you may open the Form in Normal View, maximize the form, and check whether the form title ‘Employees’ moves across the screen to the center of the expanded screen or not. You may try to manually resize the form window from the right edge of the form, by displaying the sizing control of the mouse, clicking and holding the left mouse button, and dragging the right edge to the left slowly, watching the heading label responds to the manual resizing action. Or minimize the Navigation panel and display it again so that the form display window expands and shrinks quickly. Now let us play a few tricks with other sets of controls on the Detail Section.
Place the Form back into Design View.
Highlight and select all the second column TextBoxes.
Select Arrange - - >Anchoring - - >Top Right or right-click on the selected controls and select Anchoring - - > Top Right from the shortcut menu.
Change the form in Form View and preview the effect of our setting. Change the form back into Design View again.
Select the TextBoxes in the left column, except the Note field.
Right-click on the controls and select Anchoring - - > Stretch Across Top.
Select the Notes field, right-click on the control and select Anchoring - - > Stretch Down and Across.