This method always fascinated me after seeing it in programs. It goes something like this:

When you open a dialog control it is presented with several options to select from. In addition to that the dialog control will have a Command Button with caption, something like Advanced… or More… indicating that more options available to select from, and when you click on the Command Button the bottom portion of the Form extends down showing more options in the newly displayed area of the form.

I have made an attempt to mimic this method on a Form. Screen shot of the Form, before and after expanded modes are given below.

Form in normal view, check the bottom portion of the form.

Form Dropdown Trick image-1

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

Form Dropdown Trick image-2

Check the following video to see how it works.

Now, you have a general idea what we are trying to do here. We have to design two Forms, create two Macros, some small programs on the Form’s VBA module. This Project also demonstrates how to create and use custom properties on the Form (besides the Properties that you see on the Property Sheet) to store values on the Form itself and retrieve them when you need it.

Remember, we always record information on tables and Forms are used to display/edit the data and store them back on the table itself. You never thought of this angle that you can store values on the form itself, but not as much you can store in a table. You can do several tricks with this method. I will give few links of Articles, which I have published earlier, to look at at the end of this page.

  1. Open a new Form in design View.
  2. Right-Click on the Detail Section of the Form and select Form Header/Footer to display the Header and Footer Sections of the Form.
  3. Shrink the Footer of the Form, so that there is no space is showing on the Footer of the Form.
  4. Select the Header of the Form and display the Property Sheet (F4).
  5. Increase the Height Property value to 0.6”.
  6. Click on the Detail Section and adjust the Height of the Detail Section to 2.166”.
  7. 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 little bigger or smaller, doesn’t matter.

  8. Create a Header Label with the Caption ‘CONTROL CENTER’ and change the Font-size to 14 or 16 or whatever size you prefer to look like a heading.  I have copied the label and placed over the first one, slightly up and to the right and changed the Font-color of the first label to a lighter color to give some 3D effect to the heading.
  9. Click on Use Control Wizards button on the toolbar to turn it on, if it is not in selected state.
  10. Select the Options Group and draw a control on the left side of the Form, on the Detail Section, big enough to add four lines of options.

    Check the Design View of the Form given below:

    Form Dropdown Trick image-2

  11. Type four Labels one after the other on the control as shown on the design, you can type any labels, this is for design purpose only. 
  12. While the Option Group is in selected state, display the Property Sheet (F4) and change the Name property value to Frame1.
  13. Create another Option Group control to 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.
  14. Change the Name Property Value of the Option Group control to Frame2.
  15. Change the child label captions to Forms and Reports of Frame1 and Frame2 respectively and position them above those controls.
  16. Create four Command Buttons as shown on the design above and change their caption property values as Quit, Open, Print and More…
  17. Click on the Quit Command Button to select it.
  18. Display the property sheet (F4) and change the Name property value to cmdQuit.
  19. 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.
  20. Copy and Paste the following VBA Code, overwriting the existing lines of Code in the Form Module.
    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
  21. Select Close and Return to Microsoft Office Access from File Menu of VBA Window (or press ALT+Q) to close the VBA Window.

    For now, we will close the Form with a name (FormA1) and we have to do some work outside this form and then we will come back to the Form.

  22. Before closing the Form, if you need any refinement to the design then do it now. We are not supposed to do any design changes to this Form later except copy pasting few programs on the VBA Module. Save and close the Form with the name FormA1
  23. Right-click on FormA1 and select Copy from the context Menu.
  24. Right-Click on the Navigation pane Header and select Paste from the displayed Menu.
  25. Change the name of the copy to FormB1.

NB:  I tried to do this trick with a single Form and VBA Programs, but it was not at all successful.  If anybody could do this with a single Form I would be very happy to know how you did it?

  1. Open Form FormB1 in design view.
  2. Click on the right-most Command Button (with the Caption: More…) and change the caption to …Less.

    See that the position of the command button is not changed at all. If you are not sure then compare the left and top position values with command button on FormA1, if not same then take the value from FormA1 and change to match. See that you don’t make any other changes to the Detail or Header Section designs on FormB1.

  3. Expand the Footer Section down (check the second image from top) to give enough space to create a Command Button.
  4. Create a Command Button, at the right side of footer section, display the property sheet (F4), change the Caption property value to Preview, change the Name property value to cmdPreview.
  5. 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.
  6. Copy and paste the middle line Code and paste it between Private Sub cmdPreview_Click() and End Sub lines:
    Private Sub cmdPreview_Click()
        DoCmd.OpenReport "myReport", acViewPreview
    End Sub
  7. Change the report name (myReport) with the name of one of your own Report in the database.
  8. 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. 

Perhaps, you got the idea by now that we need two identical forms (not exactly identical) to pull this trick to the User.  Before proceeding further I will tell you how we are going to show this trick to the User.

Opens FormA1 through a Macro (macFormA1), on a specific location on the Screen.

It is important that we open both Forms on the same location inter changeably to give it a feel of quick transition. Both Forms are of the same size and design, except the second form is having it’s footer section extended with a command button.

When the Command Button with the Caption More… is clicked; the second form FormB1 is opened through a second Macro (macFormB1), overlaying the first form on the exact position on the screen and closes the first Form.  But, the user will not see this quick change of form, like different frames in a movie. 

Since, we brought the second form on the same position on the screen it will look like the first form’s footer section is dropping down to reveal the Preview Command Button. The Caption More… of the first form Command Button will look like changed to …Less We have written a program to open a Report, from your database, in Print Preview, when the Preview Command Button is clicked.  After that, when the Command Button with the caption …Less is clicked the first Form FormA1 is opened again on the same position of the second form FormB1 and closes FormB1 immediately.  It will look like that the footer section of the form is withdrawn (shrunk).  We are showing only one form to the User at a time. 

I tried to do this with only one Form by expanding/shrinking the Footer Section of the Form with VBA Programs and Macros.  But, could not succeed during the repetition of the same action and the macros refused to run along with other issues.

Now, going back to our current mode of trick, the major issue is with the Option Group controls on both Forms.

For example, the User selects a particular option (say option 3 under Reports to print Preview the Report) on the first Form. Clicks on the More… Command Button to display the Print Preview option.  At this point we have to open the second form, with the Footer Section extended, and we have to ensure that the user’s selection of the option on the first form appears on the second form as well.  The user will not know that the form is changed.  This was the most difficult part in this two form based method.

The big question is where to save the user’s selection of option number(s) (the user may make selections on Option Group-Frame1 and Option Group-Frame2) in order to transfer it to the second form.  We plan to do this by saving these value on form FormA1 itself, in custom made properties, with the names: Frame1 and Frame2.  We can save/retrieve values through VBA and you can do this even when the FormA1 is in closed state. 

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

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 Frame1 and Frame2 custom properties on the form FormA1. The CreateProperty() method accepts three parameters.

  • First parameter “Frame1” is the Name of the custom property.  You can use any text value as name.
  • Second parameter dbInter is a constant indicates that the custom property Frame1 will store Integer Type data.
  • 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 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

Note: As you can see in the above program that the collection of Forms are referred to as a Container Object.  Tables (Queries also comes under Tables), Forms, Reports, Scripts (Macros)  are Container Objects.  Each Container Object consists of several Documents.  Each form you create is referred to as a Document under Forms Container.  These are some of the object, there are others too.  You may search for Access Object Model Reference in VBA Help and select Object Model Map from the Table of Contents, to see the hierarchy structure of Access Objects, Properties etc.  You may visit the link: Saving Data on Forms not in Table for a useful method we have published earlier, using custom property on the form.

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.

  1. Select Macro from Create Menu to open a new macro in design view.
  2. Add the following Actions and parameters on 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 Right, Down, Width arguments respectively.
  3. Save and close the Macro with the name macFormA1.
  4. Make a copy of the macro macFormA1 and name the new macro as macFormB1.
  5. Open macro macFormB1 in design view and make only one change in the OpenForm Action line Form Name argument.
  6. Change the Form Name to FormB1.
  7. Save and close the macro.

Now, only two things left to do on both forms to complete our design tasks.  Two VBA Subroutines (Form_Load() and Form_Unload() Event Procedures) to be copied and pasted into the VBA Modules of both forms.  The macros we have created must be assigned to the On Click Event of the More… and …Less captioned Command Buttons.

  1. Open FormA1 in Design View.
  2. Click on the Command Button, with caption More…, to select it.
  3. Press F4 to display the Property Sheet and select On Click Event Property.
  4. Select macFormB1 from the drop-down list of the property.
  5. Press ALT+F11 to display the Form’s VBA editing window.
  6. 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
  7. Save and Close FormA1.
  8. Open FormB1 in Design View.
  9. Click on the Command Button with caption …Less to select it.
  10. Press F4 to display the Property Sheet and select On Click Event Property.
  11. Select macFormA1 from the drop-down list of the property.
  12. Press ALT+F11 to display the Form’s VBA editing window.
  13. Copy and Paste the above Sub-Routines, below the existing Program codes on the module of this Form also.
  14. Save and Close FormB1.

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.

  1. Click on Office Button and select Access Options.
  2. Select Current Database,  select Overlapping Windows Radio Button under Document Window Options.

    A message is displayed to close Microsoft Access and open it again to take effect of the new settings.

  3. Restart Microsoft Access and open your database.

If we open FormA1 directly from the navigation panel the form may not open at the exact location (i.e. 1” from top and 1” from left side of the window) we have specified in the macros (macFormA1 and macFormB1).  So, we must see that we run the first macro macFormA1 either from a command button click Event Procedure from another form or running the macro directly from the navigation panel.

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

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

    Remember, we have added one of your Report on the Preview Command Button click event procedure just to see that the Report runs from there.  We are not running any validation check on the Reports Option Group to see which option the User selected to run the report.  But, I asked you to select one of the option from there just to see how the selected option value is transferred to the second form FormB1You may select any value from both Option Groups (Frame1 and Frame2) to watch the change on the second Form.

  4. Click the More… Command Button to extend the form down to show the Preview Command Button.
  5. Check whether the selected report option appears on FormB1 in the Reports Option Group also. The command button with More… caption now changed to …Less.
  6. Click on the Preview command button to open your report in print preview.
  7. Click on the …Less command button to hide the footer section of the form. This will change the command button caption to More… again.
  8. Click on the Quit Command Button to close the Form and close the Report also.

Well, how was that for a trick?  After all it is all about playing tricks in front of the user’s unsuspecting eyes. As far as the user is concerned he/she doesn’t have a chance to catch the action in slow motion and to know how it works.

I know it took lot of explaining in a very lengthy post, but it is all about two forms, with almost same design, showing one after the other on the same location.  You learned how to store program parameter values on the form itself.  This is a very powerful feature that you can put to use in programs to play many tricks with it.  Take a look at another trick we have published earlier: Create your own color palette.

I don’t know about later versions of Access (I am still using Access 2007) have anything that could help the trick to achieve the same effect with some lighter methods.

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


Download Demo Database – Access 2007

Download Demo Database – Access 2003