October 2013 ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction

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 a caption, something like Advanced, or More indicating that more options are 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 made an attempt to mimic this trick on a Form. The Screenshot of the Form, before and after it is in expanded modes, is given below.

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

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

Check the following video to see how it works.

Now, you know what we are trying to do here. We will design two Forms, create two Macros, and a few 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 them.

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 as you can store in a table. You can do several tricks with this method. The links to the Articles, published on this topic earlier, are given 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 on 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, doesn’t matter.
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 it 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.
Click on 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, on the Detail Section, big enough to add four lines of options.

Check the Design View of the Form given below:
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 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.
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 think that you need some changes, to the design of the Form, then you may do it and save and close the Form with the name FormA1.
Make a Copy of FormA1.
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 do this trick with a single Form, but it was not at all successful. If anybody could do this with a single Form I would be very happy to know.

Open Form FormB1 in the design view.
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 the top position values, with the command button on FormA1, if it is not the same then take the value from FormA1 and change it to match. See that you don't make any other changes to the Detail or Header Section designs on FormB1. The Form should be an exact clone of FormA1 when we incorporate some extra features into this Form.
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) with 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.

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 will present 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 having its 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 show the second form on the same position on the screen it will look like the first form's footer section dropped down to reveal the Preview Command Button there. The command button's caption text More... changes to Less.

The Preview command button click opens a report. Next click the Less command button opens the first Form FormA1, and closes FormB1. Both form's will open on the same coordinates on the Database window interchangably.

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.

The Option-Group Issues.

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:

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: As you can see in the above program that the collection of Forms is referred to as a Container Object. Tables (Queries also come under Tables), Forms, Reports, and 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 objects, 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 published earlier, using the custom property on the 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 Create Menu to open a new macro in the design view.
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 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 things left to do on both forms to complete our design tasks. Two VBA Subroutines (Form_Load() and Form_Unload() Event Procedures) are 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.

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 Sub-Routines, below the existing Program codes on the module of this Form also.
Save and Close FormB1.

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 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 of the new settings.
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 the 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 run the macro directly from the navigation panel.

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, on the exact location we specified in the macro.
Select any one of the options from the Reports Option Group.

Remember, we have added one of your Reports on the Preview Command Button click the 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 options from there just to see how the selected option value is transferred to the second form FormB1. You may select any value from both Option Groups (Frame1 and Frame2) to watch the change 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 FormB1 in the Reports Option Group also. The command button with More... caption 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 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.

This is all about two simple Forms Magic.

I know it took a lot of explaining in a very lengthy post, but it is all about two forms, with the almost same design, showing one after the other in 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 published earlier: Create your own color palette.

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