Forms and Reports Design

Designs Fundamentals

Form Design Fundamentals

A form is the main object that hosts the Windows controls that allow a user to interact with your database. To start a brand new form for design, on the Ribbon, click Create and click Form Design. When a form is displaying in the Design View, you can change many of its aesthetic characteristics. If the form exists already, we already saw how to open it for design.

Practical Learning: Introducing Forms

1. Start Microsoft Accecss
2. In the list of files, click Exercise3 from the previous lesson
3. On the Ribbon, click Create
4. In the Forms section of the Ribbon, click Blank Form

Report Design Fundamentals

A report is the main object used to print the records of a database. Like a form, a report hosts the Windows controls that hold the values to print. To start a report in design, on the Ribbon, click Create and click Report Design.

Practical Learning: Creating a Report

1. On the Ribbon, click Create
2. In the Reports section of the Ribbon, click Report Design

The Tab or the Title Bar

A table, a form, or a report can display with a tab or a title bar. By default, that is, when you just start a database, new tables, forms, or reports display with a tab. If you want a difference, that is, to replace the tab, click File and click Options. In the left frame of the Access Options dialog box, click Current Database. Click the Overlapping Windows radio button:

After making the selection, click OK. You will be asked to close and reopen the database:

Practical Learning: Applying Overlapped Objects

1. On the Ribbon, click File
2. Click Options
3. On the left side, click Current Database
4. On the right side, under Document Window Options, click the Overlapping Windows radio button
5. Click OK
6. Read the message box and click OK
7. On the Ribbon, click File and click Close
8. In the list of files, click Exercise3 used earlier
9. On the Ribbon, click Create
10. In the Forms section, click Blank Form
11. Right-click the title bar of the form and click Save
12. Set the name to Switchboard and press Enter
13. On the Ribbon, in the Design section, click Button and click the form. If a wizard starts, click Cancel
14. Right-click the button and click Build Event...
15. In the Choose Builder dialog box, click Code Builder and click OK
16. Implement the event as follows:

    Private Sub Command0_Click()
        Dim frmCentral As New Form
        
        Set frmCentral = CreateForm()
    
        Set frmCentral = Nothing
    End Sub

Anatomy of a Form

The System Menu

A form has a system menu on its top-left icon. To display this menu, the user can click the form's system icon:

The Control Box

If you display a form as overlapped, it would be equipped with three system buttons , or ,  . Here is an example:

The group of these buttons is called the Control Box in the Property Sheet and the ControlBox in the Properties window.

To let you programmatically minimize a form, the DoCmd object is equipped with a method named Minimize. Call this method to minimize a form. Here is an example:

Private Sub cmdManipulate_Click()
    DoCmd.Minimize
End Sub

To let you programmatically maximize a form, the DoCmd object is equipped with the Maximize() method.

Restoring a Form

If a form is maximized, to restore it, the user can click the Restore button . If you programmatically create a form, it gets automatically minimized.

To let you programmatically restore a form, the DoCmd object is equipped with a method named Restore.

The presence of the form's system icon and system menu is controlled by the Control Box field of the Property Sheet or the the ControlBox field of the Properties window for the form. If you change the field to No or False from its Yes or True default value, the form would display without the control box and the system icon.

Practical Learning: Programmatically Creating a Form

1. Change the code as follows:

   Private Sub Command0_Click()
       Dim frmCentral As New Form
       
       Set frmCentral = CreateForm()
   
       DoCmd.Restore
       
       Set frmCentral = Nothing
   End Sub

2. Return to Microsoft Access and switch the form to Form View
3. Click the button
4. Right-click the title bar of the new form and click Save
5. Set the name to Central and click OK

The Form's Borders

The borders of a form specify where a form starts and where it ends. The borders of a form are controlled by the Border Style (Property Sheet) or the BorderStyle (Properties window) enumerated property. The borders always appear if the form is displaying in the Design View. If a database is not configured to show overlapped windows, when the form displays in a view other that Design View, the borders would appear. The values of the Border Style property are None, Thin, Sizable (the default), and Dialog. If you set it to None, the form would appear without borders.

The Caption of a Form

The caption of a form displays on its tab or its title bar. To support the caption, the Form class is equipped with a property named Caption. To manually set the caption of a form, click the field of that name in the Property Sheet or the Properties and type the text of your choice. To programmatically specify or change the caption of a form, assign the desired text to this property. Here is an example:

Me.Caption = "Exercise"

Practical Learning: Specifying the Caption of a Form

1. Double-click the button in the top-left section under the tab 
2. In the Property Sheet of the form, click the Format tab and click Caption
3. Type Watts' A Loan - Central Processing
4. To save the form, press Ctrl + S

The Sections of a Form or Report

Introduction

A form and a report can be equipped with different sections for better control positioning management. To display the Property Sheet associated with a section, right-click anywhere in that section and click Properties

The Detail Section

The Detail section is the primary host of the controls on a form or report. To programmatically support the Detail section, Microsoft Access provides a class named Detail. It is represented in the Form class by a property named Detail. Therefore, to programmatically access the Detail section of a form, use either Detail or Me.Detail.

The section is primarily characterized by its height. To visually change the height of the Detail section, click and drag the lower border of the Detail section:

The Form Header Section

A form can be equipped with a Form Header section. To support it, Microsoft Access propvides a class named FormHeader. It is represented in the Form class by a property of the same name.

The Form Footer Section

A form can be equipped with a Form Footer section. To support it, Microsoft Access provides a class named FormFooter. It is represented in the Form class by a property of the same name.

To manually add the Form Header and the Form Footer sections, right-click the middle of the form and click Form Header/Footer:

A report can be equipped with a Report Header and a Report Footer sections. To manually add these sections, right-click the body of the report and click Report Header/Footer:

You can heighten or reduce, sometimes completely, a section to your liking. This allows you to keep one section and hide the other.

Practical Learning: Adding Sections to a Form

1. Right-click the body of the form and click Form Header/Footer
2. Press Ctrl + S to save the changes

The Page Header and the Page Footer of a Form or Report

Both the form and the report can be made to display a Page Header and/or a Page Footer sections. By default, when you start a new report in the Design View, it is automatically equipped with a Page Header and a Page Footer sections. If you wants these sections of a form, you must explicitly add them.

Practical Learning: Starting a Report

• On the Ribbon, click Create and click Report Design

  

Enumeration of the Sections of a Form or Report

To support the sections of forms and reports, the VBA language provides an enumeration named AcSection . Its members are:

The Size of a Form or Report

The Width of a Form or Report

The width of a form or that of a report is the distance from its left border to its right border. All sections use the same width. To widen or narrow a form or report, click and drag the right border:

A form or report displays on the screen using its dimensions. The form and report themselves control only their width. To support the width, the Form class is equipped with a property named Width.

To programmatically change the width of a form, access its Width property and assign the desired value. Here is an example:

Private Sub cmdWidth_Click()
    Width = 8650
End Sub

Practical Learning: Setting the Width of a Form

• Position the mouse on the right border of the form. Click and drag left-right until the border aligns with 6 on the horizontal ruler

The Height of a Form or Report

The height of a form or report is controlled by its sections. To support it, the sections classes are equipped with a property named Height. To control the height of a section, assign the desired value to its Height.

Practical Learning: Setting the Height of a Form

1. On the form, double-click the Form Header bar
2. In the Format tab of the Property Sheet, click Height and type 0.4
3. Position the mouse on the top border of the Form Footer bar. Click and drag up-down until the bar aligns with and  3 marks on the vertical ruler

   

4. On the form, click the Form Footer bar
5. In the Format tab of the Property Sheet, click Height and type .42

The Width and Height of a Form or Report

To change both the width and the height of a form or report at the same time, position the mouse on the lower-right corner:

Then click and drag left, right, up, down, or diagonally.

Other common characteristics of sections are the BackColor property used to paint the section, the SpecialEffect property used to raise or sink a section, etc.

Automatically Sizing the Form to Fit Its Content

If you want to resize a form enough to show its whole content, on the Ribbon, click Home. In the Window section, click Size To Fit Form.

Other Characteristics of Forms

The Background Color of a Section

To visually set the background color of a section of a form or report, on the Ribbon, click the arrow the Shape Fill button. To support the background color, the form and the report have a property named Back Color in the Property Sheet or BackColor in the Properties window. In the Property Sheet, the field is equipped with two buttons. The down-pointing arrow allows you to select a common or known color:

The Browse button allows you to select or create a color.

To change the background color of a section of a form or a report:

• On the Ribbon, click Home and, in the Text Formatting section, select a color from the Background Color button
• Right-click a section, position the mouse on Fill/Back Color and click the desired color

• On the Ribbon, click Format. In the Control Formatting section, click the Shape Fill button
• Access the Property Sheet of the section. In the Format or the All tab, click the Back Color field:
  • If you know the Microsoft Access color name, type it
  • Type a hexadecimal number of the color, starting with #
  • Type a Long integer that represents the RGB color
• In the Properties window, click BackColor and type a Long integer that represents the color
• Access the BackColor property of the desired section and assign the desired color to it. The color can be provides as a Long integer, a call to the RGB() function, or a VBA constant. Here are examples:

  Private Sub Command2_Click()
      Detail.BackColor = RGB(128, 0, 0)
      FormHeader.BackColor = vbBlue
      FormFooter.BackColor = 528340
  End Sub

Practical Learning: Setting the Background Color of a Section

1. On the form, click the area below the Detail bar
2. In the Format tab of the Property Sheet, click Back Color, then click the arrow of its combo box and select Background Form
3. In the list of colors, click Blue, Accent 5, Darker 25% (Theme Colors, 9th column, 5th row)
4. On the form, click the Form Header bar
5. In the Format tab of the Property Sheet, click Back Color, then click ellipsis button
6. In the list of colors, click Blue, Accent 5, Darker 25% (Theme Colors, 9th column, 5th row)
7. On the form, click the Form Header bar
8. In the Format tab of the Property Sheet, click Back Color, then click ellipsis button
9. Click Custom and the values as follows:
   Red: 25
   Green: 55
   Blue: 95

Dividing Lines

A form is equipped with dividing horizontal lines used to visually separate sections of a form. To equip a form with dividing lines, add a header and a footer sections. The presence or absence of the horizontal lines is controlled by a Boolean property named DividingLines. Here is an example of using it:

Private Sub cmdProperties_Click()
    Me.DividingLines = True
End Sub

Practical Learning: Setting the Dividing Lines

1. Get to Microsoft Visual Basoc and, in the Project window, make sure Form_Central is selected.
   In the top combo box of the Properties window, select Central Form_Central
2. In the Alphabetic tab of the Properties window, click Dividing Lines. Then click the arrow of its combo box and select True

Form Automatic Centering

To let you automatically center a form when it displays in a view other than Design View, the Form class is equipped with a Boolean property named AutoCenter. To set this characteristic during the design of a form, Access its Property Sheet or its Properties window and set the Auto Center to Yes or the AutoCenter property to True. To programmatically center a form, access its AutoCenter property and assign True or False to it. Here is an example:

Private Sub cmdProperties_Click()
    Me.AutoCenter = True
End Sub

Practical Learning: Automatically Centering a Form

• In the Alphabetic tab of the Properties window, double-click AutoCenter to set its value to True

The Background Picture of a Form

Introduction

You can cover a form with a picture. To use a picture as a background, open the form in Design View, access the Property Sheet for the form. In the Format or the All tab, click Picture and click its browse button. Here is an example:

This would produce:

To let you programmatically specify a picture to use as the background of a form, the Form class is equipped with a property named Picture. Assign the path and name of the picture to it. Here is an example:

Private Sub ButtonWasClidked()
    Me.Picture = "C:\Exercises01\Sky.bmp"
End Sub

Linking or Embedding the Picture

To let you specify whether the picture must be linked or embedded, the Form class is equipped with a property named PictureType. Its values are:

The Picture Alignment

The position of the background picture is controlled by the Picture Alignment property. To support this characteristic, the Form class is equipped with a property named PictureAlignment. Its values are:

Tiling the Picture

If you are using a picture smaller than the form but have it repeat itself on the form, the Form class is equipped with a Boolean property named PictureTiling. Its values are:

The other value is:

Zooming or Stretching a Picture

To let you set the zooming or stretching of the background picture, the Form class is equipped with an enumerated property named PictureSizeMode. Its values are:

The Events of a Form

Loading a Form

To open a form and display it to the user, the computer must allocate memory for it. This is referred to as loading the form into memory. When this happens, the form fires the Load event. The structure of this event is:

Private Sub Form_Load()

End Sub

This event simply allows you to know that the form as been presented to the user so you can take the appropriate action.

Practical Learning: Loading a Form

1. In the Object combo box, select Form
2. Implement the event as follows:

   Private Sub Form_Load()
       MsgBox "The form has been loaded. Its resources are currently occupying the computer memory.", _
              vbOKCancel Or vbInformation, "Forms Events"
   End Sub

Opening a Form

When you open a form, it fires the Open event. This event runs before the form actually displays. The structure of this event is:

Private Sub Form_Open(Cancel As Integer)

End Sub

This event takes one argument. Because this event is fired as the form is about to be opened but before it actually does, the Cancel argument allows you at the last minute to proceed or to stop the process. If you set this argument to False, the form would not be opened.

Practical Learning: Opening a Form

1. In the Procedure combo box, select Open
2. Implement the event as follows:

   Private Sub Form_Open(Cancel As Integer)
       MsgBox "The form is now apened. The user can see its constrols and interact with the database.", _
              vbOKCancel Or vbInformation, "Forms Events"
   End Sub

Activating a Form

In Microsoft Access, you can display many tables, forms, and reports at the same time. When many objects display, only one can geometrically be in the front.To bring another form to the front, you can click it. When a form is brought to the front, it is said to be activated. When this happens, the form fires the Activate event. The structure of this event is:

Private Sub Form_Activate()

End Sub

Practical Learning: Activating a Form

1. In the Procedure combo box, select Activate and implement the event as follows:

   Private Sub Form_Activate()
       MsgBox "The Central form has been activated. It is now in in the front row of Microsoft Access.", _
              vbOKCancel Or vbInformation, "Forms Events"
   End Sub

2. In the Project window, double-click Form_Switchboard
3. In the Object combo box, select Form
4. In the Procedure combo box, select Activate and implement the event as follows:

   Private Sub Form_Activate()
       MsgBox "The Switchboard form is not the top in the Z-coordinate system of Microsoft Access.", _
              vbOKCancel Or vbInformation, "Switchboard Resolutions"
   End Sub

De-Activating a Form

As mentioned already, you can display different forms on the screen and switch to the one you need at one particular time. When you switch from one from to another, the form that goes in the background fires an event named Deactivate. Its structure is:

Private Sub Form_Deactivate()

End Sub

Practical Learning: De-Activating a Form

1. In the Procedure combo box, select Deactivate and implement the event as follows:

   Private Sub Form_Deactivate()
       MsgBox "The Central form has been de-activated. It was sent to the background with regards to the other forms.", _
              vbOKCancel Or vbInformation, "Forms Events"
   End Sub

2. In the Project window, double-click Form_Switchboard
3. In the Procedure combo box, select Deactivate and implement the event as follows:

   Private Sub Form_Deactivate()
       MsgBox "The Switchboard is not the current active form. In the Z-order, it should now be positioned behind the other form(s).", _
              vbOKCancel Or vbInformation, "Switchboard Positioning"
   End Sub

Closing a Form

After using a form, you can close it if it is (still) opened. If there is a structural change that needs to be saved, Microsoft Access would prompt you.

To manually close a form, you can click its close button . You can also press Ctrl + F4.

To programmatically close a form, you can call the Close() method of the DoCmd object. Its syntax is:

DoCmd.Close ObjectType, [object-name], [save]

 The first argument is a member of the AcObjectType enumeration. For a form, the member to use is called acForm:

DoCmd.Close AcObjectType.acForm, [object-name], [save]

Of course, you can omit AcObjectType and use the following syntax:

DoCmd.Close acForm, [object-name], [save]

The first argument must be specified as acForm to indicate that you want to close a form. If you are closing the same form that is calling this method, this is the only argument you would need. Consider the following example:

Private Sub cmdClose_Click()
    DoCmd.Close
End Sub

In this case, the form would be closed.

The second argument can be the name of the form you want to close. This argument is useful if you are trying to close a form other than the one that is making the call. Here is an example:

Private Sub cmdClose_Click()
    DoCmd.Close acForm, "Employees"
End Sub

In this example, a form named Employees is asked to be closed. If you suspect that the form would need to be saved before formally being closed, you can pass a third argument that is a member of the AcCloseSave enumeration. The available values are:

When calling the Close() method to close a form, if the form is not opened or if the specified form does not exist, nothing would happen (you would not receive an error).

Instead of writing your own code, to let Microsoft Visual Basic write it for you, you can use the Command Button Wizard.

As a formis being closed, it fires a Close event. The structure of this event is:

Private Sub Form_Close()

End Sub

Practical Learning: Closing the Form

1. In the Project window, double-click Form_Central
2. In the Procedure combo box, select Close and implement the event as follows:

   Private Sub Form_Close()
       MsgBox "The Central form will now be closed. Once that is done, you can't use it until you open it again. Good Bye!!!", _
              vbOKCancel Or vbInformation, "Forms Events"
   End Sub

Unloading a Form

After using a form, the user can close it. As the form is being closed, it must empty the memory it was using. At this time, the form fires an Unload event. Its structure is:

Private Sub Form_Unload(Cancel As Integer)

End Sub

This event takes one argument: Cancel. If you don't want the form to be closed, set the Cancel argument to False.

Practical Learning: Unloading a Form

1. In the Procedure combo box, select Unload
2. Implement the event as follows:

   Private Sub Form_Unload(Cancel As Integer)
       MsgBox "The form has been unloaded. Normally, its resources are no longer occupying the computer memory.", _
              vbOKCancel Or vbInformation, "Forms Events"
   End Sub

3. Close Microsoft Visual Basic and return to Microsoft Access
4. Close the Central form
5. In the Navigation Pane, double-click the Central form to open it
6. Click OK on the first message box
7. Click OK on the second message box
8. Click OK on the third message box
9. In the Navigation Pane, double-click the Switchboard form to open it
10. Click OK on the message box
11. Click OK on the other message box
12. Close the Switchboard form
13. Click OK on the message box
14. Click OK on the other message box
15. Close the Central form
16. Click OK on the message box
17. Click OK on the other message box
18. Click OK agin on the message box
19. Close Microsoft Access