Form Footer Section Dropping Down Magic ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction

This method has always fascinated me, especially after seeing it in other applications. It works like this:

When you open a dialog form, it initially presents a few options to choose from. In addition, the dialog often includes a Command Button labeled Advanced... Or More..., indicating that additional options are available. When you click this button, the form extends downward, revealing more controls in the newly expanded section.

I attempted to replicate this feature in a Microsoft Access form. Below, you can see screenshots of the form in its normal state and in its expanded state.

In the normal view, observe the bottom portion of the form.

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

Look at this Form View; the bottom portion of the form is expanded down to display additional options.

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

Check the following video to see how it works.

Now that you understand our goal, we will design two forms, create two macros, and write a few small procedures in the forms’ VBA modules. This project also demonstrates how to create and use custom properties on a form—beyond the standard properties visible in the Property Sheet—to store values directly on the form and retrieve them when needed.

Keep in mind that data is always ultimately stored in tables, while forms are primarily used to display, edit, and save that data back to the table. You might not have considered that you can store values on the form itself—though not as extensively as in a table—but this approach allows for several clever techniques and shortcuts. Links to previously published articles on this topic are provided at the end of this page for reference.

The Design Task.

Open a new Form in Design View.
Right-click on the Form's Detail Section and select Form Header/Footer to display the Header and Footer Sections of the Form.
Shrink the Footer of the Form so that there is no space showing in the Footer section of the Form.
Select the Header of the Form and display the Property Sheet (F4).
Increase the Height Property value to 0.6”.
Click on the Detail Section and adjust the height of the Detail Section to 2.166”.
Click on the left top corner of the Form (or select Form from the Selection Type control – Access 2007) to select the Form’s Property Sheet and change the Width property value to 4.875”.
Note: If your Form size is a little bigger or smaller, it doesn’t matter.
Create a header label with the caption “CONTROL CENTER” and set the font size to 14, 16, or any size that gives it a clear heading appearance. To add a subtle 3D effect, copy the label and place the duplicate slightly above and to the right of the original. Then, change the font color of the original label to a lighter shade—this will create a visually appealing layered effect for the heading.
Click on the Use Control Wizards button on the toolbar to turn it on if it is not in the selected state.
Select the Options Group and draw a control on the left side of the Form, in the Detail Section, that is big enough to add four lines of options.

Check the Design View of the Form given below:
Right-click to open Large Image in New Tab/Window.
Type four Labels one after the other on the control as shown on the design. You can type any labels; this is for design purposes only.
While the Option Group is in the selected state, display the Property Sheet (F4) and change the Name property value to Frame1.
Create another Option Group control on the right side of the earlier one and add four items. Give the third label caption to the name of a Report in your database.
Change the Name Property Value of the Options Group control to Frame2.
Change the child label captions to Forms and Reports of Frame1 and Frame2, respectively, and position them above those controls.
Create four Command Buttons, as shown on the design above, and change the Caption Property values to Quit, Open, Print, and More… respectively.
Click on the Quit Command Button to select it.
Display the property sheet (F4) and change the Name property value to cmdQuit.
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 Module of the Form.

Copy and paste the following Code, overwriting the existing lines in the VBA Module.

The Form Module VBA Code.

Option Compare Database
Option Explicit
Dim db As Database, doc As Document

Private Sub cmdQuit_Click()
On Error GoTo cmdQuit_Click_Err:
  
  Me![Frame1] = 1
  Me![Frame2] = 1

DoCmd.Close acForm, Me.Name

cmdQuit_Click_Exit:
Exit Sub

cmdQuit_Click_Err:
MsgBox Err & " : " & Err.Description, , "cmdQuit_Click()"
Resume cmdQuit_Click_Err

End Sub

If you prefer some changes to the design of the Form, implement them, save and close the Form with the name FormA1.
Make a Copy of Form A1.
Right-click on FormA1 and select Copy from the Context Menu.
Right-click on the Navigation pane Header and select Paste from the displayed menu.
Change the name of the copy to FormB1.

NB: I tried to create this trick earlier using a single Form and was not successful at all. If anybody could do this with a single Form, please share the idea with me.

Open Form B1 in the design view.
Click on the right-most Command Button (with the Caption: More...) and change the caption to …Less.
Ensure that the position of the command button remains exactly the same. If you’re unsure, compare the Left and Top property values with those of the command button on FormA1. If they differ, update them to match FormA1. Do not make any other changes to the Detail or Header sections of Form B1. The goal is for FormB1 to be an exact clone of FormA1 before incorporating any additional features.
Expand the Footer Section down (check the second image from the top) to give enough space to create a Command Button.
Create a Command Button, at the right side of the footer section, display the property sheet (F4), change the Caption property value to Preview, and change the Name property value to cmdPreview.
Select the On Click Event Property and select [Event Procedure] from the drop-down list, and click the build button (...) to open the VBA Module.
Copy and paste the middle line Code and paste it between Private Sub cmdPreview_Click() and End Sub:
```
Private Sub cmdPreview_Click()
    DoCmd.OpenReport "myReport", acViewPreview
End Sub
```
Change the report name (myReport) to the name of one of your own Reports.
Press ALT+Q to close the VBA Editing Window.
You may change the Footer background color to something different, or leave it as it is.

How it Works.

By now, you probably understand that we need two nearly identical forms to create this user interface trick. Here’s how it will be presented to the user:

FormA1 opens through a Macro (macFormA1) at a specific location on the screen.
Both forms must open at the same screen coordinates to give the appearance of a seamless transition. They are identical in size and design, except that FormB1 has its footer section extended with an additional command button.
When the user clicks the command button labeled More…, a second Macro (macFormB1) opens FormB1 directly on top of FormA1 and simultaneously closes FormA1. The transition is so fast that the user perceives it as if the footer section of FormA1 simply drops down to reveal the new button.
The command button caption changes from More… to Less. Clicking this button reverses the process: FormA1 is reopened at the same coordinates, and FormB1 is closed.
The Preview command button opens a report, providing additional functionality without breaking the illusion of a single, expanding form.

I initially attempted to achieve this effect using only one form by dynamically expanding and collapsing the footer section with VBA and macros. However, this approach failed to work reliably during repeated actions, and the macros would sometimes not execute as expected.

The Option-Group Issues.

We need a one-time run of a program to create the custom properties Frame1 and Frame2 on Form A1. Copy and paste the following program into a new Standard VBA Module:

The Custom Property Creation.

Public Function createProperty()
Dim db As Database, doc As Document
Dim prp1 As Property, prp2 As Property

Set db = CurrentDb
Set doc = db.Containers("Forms").Documents("FormA1")
Set prp1 = doc.CreateProperty("Frame1", dbInteger, 1)
Set prp2 = doc.CreateProperty("Frame2", dbInteger, 1)
doc.Properties.Append prp1
doc.Properties.Append prp2
doc.Properties.Refresh

Set doc = Nothing
Set prp1 = Nothing
Set prp2 = Nothing
Set db = Nothing
End Function

Click somewhere in the middle of the Code and press F5 to run the program to create the Frame1 and Frame2 Custom properties on form FormA1. The CreateProperty() method accepts three parameters.

The first parameter "Frame1" is the Name of the custom property. You can use any text value as a name.
The dbInteger second parameter is a constant that indicates the custom property Frame1 will store Integer Type data.
The third parameter is the initial value stored in the custom property Frame1.

Two custom properties are created and appended to the properties collection.

Don't run the above program a second time; you will end up with errors. You cannot view the user-defined properties on the Property Sheet of the Form.

We don’t have to create these custom properties on the other form: FormB1.

Container and Document Objects.

Note: In the program above, the collection of Forms is referred to as a Container Object. Other examples of container objects include Tables (Queries also fall under Tables), Reports, and Scripts (Macros).

Each container object consists of multiple Documents. For instance, every form you create is treated as a document within the Forms container. Similarly, tables, reports, and macros appear as documents under their respective containers.

These are just a few examples of container objects—there are others as well. To explore the complete hierarchy of Access objects, properties, and methods, refer to the Access Object Model Reference in VBA Help and select Object Model Map from the Table of Contents.

For a practical example, you may also check our previously published method: [Saving Data on Forms Not in a Table], which demonstrates using a custom property to store data directly on a form.

The Macros.

Now, we have to create two Macros to control the open/close events of FormA1 and FormB1. With macros, we can define where exactly the form should appear on the Screen.

Select Macro from the Create Menu to open a new macro in the design view.
Add the following Actions and parameters to the macro:
- Select Echo from the Action column and set No in the Echo On parameter in the Arguments column.
- Select OnError from the Action column in the next row and set Next in the Go to Argument.
- Select Close from the Action column in the next row and select Prompt in the Save Argument.
- Select OpenForm from the Action column in the next row and set FormA1 in the Form Name control, set Form in the View argument, and set Normal in the Window Mode argument.
- Select MoveSize from the Action column in the next row and set the values 1”, 1”, 4.875” in the Right, Down, and Width arguments, respectively.
Save and close the Macro with the name macFormA1.
Make a copy of the macro macFormA1 and rename the new macro macFormB1.
Open macro macFormB1 in design view and make only one change in the OpenForm Action line Form Name argument.
Change the Form Name to FormB1.
Save and close the macro.

VBA Code on Both Form Modules.

Now, only two tasks remain to complete the design of both forms:

Copy and paste the two VBA Subroutines—Form_Load() and Form_Unload()—into the VBA modules of both forms.
Assign the macros we created earlier to the On Click events of the command buttons labeled More… and …Less.

Once these steps are completed, the forms will be fully functional with the interactive expand/collapse behavior.

Open FormA1 in Design View.
Click on the Command Button with the More… caption to select it.
Press F4 to display the Property Sheet and select On Click Event Property.
Select macFormB1 from the drop-down list of the property.
Press ALT+F11 to display the Form’s VBA editing window.

Copy and paste the following Sub-Routines, below the existing Program codes on the module:

Private Sub Form_Load()
'Load values from the custom properties of FormA1
'into Frame1 and Frame2
On Error GoTo Form_Load_Err

  Set db = CurrentDb
  Set doc = db.Containers("Forms").Documents("FormA1")
  
  Me!Frame1 = doc.Properties("Frame1").Value
  Me!Frame2 = doc.Properties("Frame2").Value
  Me.Repaint

Form_Load_Exit:
Exit Sub

Form_Load_Err:
MsgBox Err & " : " & Err.Description, , "Form_Load()"
Resume Form_Load_Exit
End Sub

Private Sub Form_Unload(Cancel As Integer)
'Before closing the Form, save the Option selections
'of the User into the custom properties
On Error GoTo Form_Unload_Err

Set db = CurrentDb
Set doc = db.Containers("Forms").Documents("FormA1")

doc.Properties("Frame1").Value = Me!Frame1
doc.Properties("Frame2").Value = Me!Frame2
doc.Properties.Refresh

Form_Unload_Exit:
Exit Sub

Form_Unload_Err:
MsgBox Err & " : " & Err.Description, , "Form_Unload()"
Resume Form_Unload_Exit

End Sub

Save and Close FormA1.
Open FormB1 in Design View.
Click on the Command Button with the caption Less to select it.
Press F4 to display the Property Sheet and select On Click Event Property.
Select macFormA1 from the drop-down list of the property.
Press ALT+F11 to display the Form’s VBA editing window.
Copy and paste the above Subroutines, below the existing Program codes on the module of this Form also.
Save and Close Form B1.

Test Running the Project.

Now, it is time to test our form footer drop-down trick. But, before we open the forms, we must change some Global settings of Microsoft Access.

Click on the Office Button and select Access Options.
Select Current Database, and select Overlapping Windows Radio Button under Document Window Options.
A message is displayed to close Microsoft Access and open it again to take effect the new settings.
Restart Microsoft Access and open your database.

If FormA1 is opened directly from the Navigation Pane, it may not appear at the exact location specified in the macros (i.e., 1″ from the top and 1″ from the left of the window). To ensure it opens in the correct position, always run the first macro, macFormA1, either from the On Click event of a command button on another form or by running the macro directly from the Navigation Pane.

To try the trick, we will run the macro macFormA1 directly from the navigation panel.

Display the macros list in the navigation panel.
Double-click on the macro macFormA1 to run it and open FormA1 in Normal view, at the exact location we specified in the macro.
Select any one of the options from the Reports Option Group.

Remember, we have attached one of your Reports to the Preview command button’s OnClick event procedure just to verify that the report runs from there. At this stage, we are not performing any validation on the Reports Option Group to determine which option the user has selected. I asked you to select an option only to observe how the selected value is transferred to the second form, Form B1. You can choose any value from both option groups (Frame1 and Frame2) to see the changes reflected on the second form.
Click the More... Command Button to extend the form down to show the Preview Command Button.
Check whether the selected report option appears on Form B1 in the Reports Option Group also. The command button with the More... caption is now changed to …Less.
Click on the Preview command button to open your report in print preview.
Click on the Less command button to hide the footer section of the form. This will change the command button caption to More... again.
Click on the Quit Command Button to close the Form and the Report also.

Well, how’s that for a trick? After all, it’s all about surprising the user right before their eyes. From the user’s perspective, they won’t have a chance to catch the action in slow motion or figure out how it really works.

This is all about two simple Forms Magic.

I realize this was a lengthy explanation, but at its core, it’s all about using two forms with nearly identical designs, displayed one after the other in the same location. You also learned how to store program parameter values directly on the form itself—a powerful feature that can be leveraged to create many clever effects. For another example of this, see our earlier trick: Create Your Own Color Palette.

I’m not sure about later versions of Access (I’m still using Access 2007), and whether they offer any simpler methods to achieve the same effect.

Download Demo Database

You may download the sample database from the Links given below.