Command Buttons to Open Forms Using a Macro or VBA

Locked lesson.

Upgrade

About this lesson

Create a form to use as a Main Menu and make a command button to open a form using a macro.

Quick reference

Command Buttons to Open Forms using a Macro or VBA

Application Terminology

Macro to Open a Form

VBA Procedure to Open a Form on a Click event

This VBA procedure opens a form called "frm_SERVICE_TYPES".  The name of the control is cmd_Services. The event is Click.

Intellisense Shortcut

If Intellisense doesn't come on, press  to trigger it

Help for OpenForm

This is the Help from Microsoft for the OpenForm Macro Action.

https://msdn.microsoft.com/EN-US/library/ff823095.aspx

The OpenForm action has the following arguments.

Remarks

This action is similar to double-clicking a form in the Navigation Pane, or right-clicking the form in the Navigation Pane and then selecting a view.

A form can be modal (it must be closed or hidden before the user can perform any other action) or modeless (the user can move to other windows while the form is open). It can also be a pop-up form (a form used to collect or display information that remains on top of all other Access windows). You set the Modal and PopUp properties when you design the form. If you use Normal for the Window Mode argument, the form opens in the mode specified by these property settings. If you use Dialog for the Window Mode argument, these properties are both set to Yes. A form opened as hidden or as an icon returns to the mode specified by its property settings when you show or restore it.

When you open a form with the Window Mode argument set to Dialog, Access suspends the macro until the form is closed or hidden. You can hide a form by setting its Visible property to No by using the SetValue action.

The filter and WHERE condition you apply become the setting of the form's Filter property.

Steps

Create a Main Menu Form

  1. To create a form to use as a Main Menu, choose Form Design from the CREATE ribbon.
  2. Go to the Design View and click in the upper left, where the Rulers intersect, to select the form. 
  3. If the Property Sheet is not showing, turn it on by pressing   (ALT-ENTER).
  4. Set the form Caption property: MAIN MENU
  5. Set the form Record Selectors property: No
  6. Set the form Navigation Buttons property: No
  7. Save the form and call it f_MAINMENU.

Command Button to Open a Form Using a Macro

  1. Click on the Button tool in the Controls group of the FORM DESIGN TOOLS, DESIGN ribbon.
  2. Click on the form where you want to put a button.
  3. When the Command Button Wizard comes up, choose Form Operations from Categories on the left.
  4. Choose Open Form from Actions.
  5. Click Next.
  6. From the list of forms, choose the form name you wish to open.
  7. Click Next.
  8. Choose the default, "Open the form and show all the records", and click Next.
  9. Change the option to Text, instead of Picture, specify the Caption for the button (i.e., "Work").
  10. Click Next.
  11. For the name of the button, type cmd_something (i.e., cmd_OpenWork).
  12. Click Finish.
  13. Resize the button and change other properties, if desired.

Command Button to Open a Form Using VBA

  1. Click on the Button tool in the Controls group of the FORM DESIGN TOOLS, DESIGN ribbon to pick up the button tool.
  2. Click on the form where you want to put a button. If the Command Button Wizard comes up, click Cancel.
  3. Choose the Event tab on the Property Sheet.  In the Click event for the button, type [ and choose [Event Procedure]
  4. Click the Builder button   (…) or press  (Ctrl-F2)
  5. You will be placed in VBA between the procedure declaration statement and the End Sub statement. Press  (Tab) to indent the line.
  6. Type 'date to create a comment with the date
  7. Press  (Enter) to make a new line
  8. Type:
     DoCmd.OpenForm "FormName"
    WHERE FormName is the name of the form you want to open.
  9. From the menu in VBA, choose Debug, Compile and then Save.

Animate Command Button

  1. To animate a command button so when the mouse hovers over it, the colors will change, select the command button in Design View.
  2. On the Format tab of the Property Sheet, set Back Color and Border Color to the same color.
  3. Set Fore Color to a contrasting color
  4. For the Hover colors, reverse the settings for Back Color and Fore Color. Hover Color = Fore Color
  5. Hover Fore Color = Back Color
  6. For the Pressed color, which you probably won't see because a form will open, set Pressed Color = Hover Color (or maybe slightly different)
  7. Set Pressed Fore Color = a color similar but different than Hover Fore Color. 

Naturally, you may choose any colors you want! This is what I generally do.

Login to download
  • 00:03 This is Access 2013, Lesson 35.
  • 00:09 Using a form as a menu gives your users the ability
  • 00:14 to get to choices in your database quickly.
  • 00:17 Today, we will build a main menu where users can
  • 00:20 click buttons to open tables, queries, forms, and reports.
  • 00:26 Hi, this is Crystal.
  • 00:29 From the CREATE ribbon,
  • 00:30 choose Form Design in the Forms group.
  • 00:33 First, we will make buttons to open other forms.
  • 00:37 Click on the Button tool in the Controls group
  • 00:40 on the FORM DESIGN TOOLS, DESIGN ribbon.
  • 00:44 When the Command Button Wizard comes up,
  • 00:47 choose Form Operations from Categories,
  • 00:51 and from Actions, choose Open Form
  • 00:54 and click Next
  • 00:56 From the list of forms, choose frm_WORK,
  • 01:01 and click Next.
  • 01:03 Choose the default, "Open the form and show all the records",
  • 01:07 and click Next.
  • 01:09 Change the option to Text, instead of Picture,
  • 01:13 specify "Work", and click Next.
  • 01:16 For the name of the button,
  • 01:18 type cmd_OpenWork and click Finish.
  • 01:24 Save the form and call it f_MAINMENU.
  • 01:29 Case doesn't matter but I like to capitalize the form name
  • 01:33 when it can be opened on its own
  • 01:35 so it is easy to identify when you look at the Navigation Pane.
  • 01:39 To test it, switch to the form view
  • 01:42 and click on the Work button.
  • 01:44 The work form opens.
  • 01:46 When you click "X" in the upper right
  • 01:49 to close the work form,
  • 01:50 you are back at the main menu.
  • 01:53 Since the main menu does not have a RecordSource,
  • 01:56 it is an unbound form,
  • 01:58 and we do not need the record selector
  • 02:01 or navigation buttons, so turn them off.
  • 02:05 Go to the Design View and click in the upper left,
  • 02:08 where the Rulers intersect, to select the form.
  • 02:11 If the Property Sheet is not showing,
  • 02:14 turn it on by pressing ALT-ENTER.
  • 02:17 Click Format on the Property Sheet and set:
  • 02:21 Caption: MAIN MENU
  • 02:24 Record Selectors: No
  • 02:27 Navigation Buttons: No
  • 02:30 Save and look at the form.
  • 02:33 The record selectors and navigation buttons are gone.
  • 02:37 When you hover over the button,
  • 02:39 its appearance changes slightly.
  • 02:43 Let's make this more pronounced.
  • 02:45 Go back to Design View and select the button.
  • 02:48 On the Format tab of the Property Sheet, set:
  • 02:52 Back Color: Dark Blue 1 (#DFE5ED)
  • 02:56 Border Color : Dark Blue 1 (#DFE5ED)
  • 03:00 Fore Color: Dark Blue (#1F497D)
  • 03:02 For the Hover colors,
  • 03:04 reverse the settings for Back Color and Fore Color:
  • 03:07 Hover Color: Dark Blue (#1F497D)
  • 03:10 Hover Fore Color: Dark Blue 1 (#DFE5ED)
  • 03:14 For the Pressed color, which you probably won't see
  • 03:17 because a form will open, set:
  • 03:19 Pressed Color: Dark Blue (#1F497D)
  • 03:22 Pressed Fore Color: Yellow (#FFF200)
  • 03:25 Go to the Form View
  • 03:26 and see how this changes the command button.
  • 03:30 When you hover over the command button,
  • 03:32 the color change is greater.
  • 03:34 If you pick a dark color for the Back Color,
  • 03:36 then pick a light color for the Fore Color.
  • 03:39 Go back to the Design View
  • 03:41 and look at the macro that the wizard made
  • 03:44 The Click event shows [Embedded Macro].
  • 03:48 If you click on the Builder,
  • 03:50 you will see the actual macro.
  • 03:53 The Action Name is OpenForm.
  • 03:57 The Form Name argument is frm_Work.
  • 04:02 The rest of the arguments
  • 04:03 are default values or not set.
  • 04:07 On the right, you see the Action Catalog
  • 04:10 if it is selected on the MACRO TOOLS, DESIGN ribbon.
  • 04:15 OpenForm is one of the choices
  • 04:17 under the Database Objects category.
  • 04:20 Copy the Work command button.
  • 04:23 On the Property Sheet, choose All.
  • 04:26 Change the new Name to cmd_Invoice,
  • 04:31 and the Caption to Invoices.
  • 04:34 In the Navigation Pane,
  • 04:36 right-click on frm_Invoice and choose Rename.
  • 04:42 Press Ctrl-C to copy the name
  • 04:44 and then press ESCape to not really rename it.
  • 04:49 Choose Event on the Property Sheet
  • 04:51 and delete [Embedded Macro].
  • 04:55 Then type [ and press Ctrl-F2 to go to VBA.
  • 05:01 Type: [ Tab] docmd.openf
  • 05:11 and then pick OpenForm, using the [ Tab] key to select it
  • 05:16 Type space then double quote,
  • 05:19 paste, double quote ("frm_INVOICE").
  • 05:24 This will be DoCmd.OpenForm "frm_Invoice"
  • 05:33 This button does the same thing as the other one
  • 05:36 except instead of using a macro to open a form,
  • 05:39 VBA is used, and the form name is different.
  • 05:43 When you type DoCmd.,
  • 05:45 Intellisense shows you a list
  • 05:47 of the actions available from a macro.
  • 05:50 Press Ctrl-Space if Intellisense doesn't come up by itself.
  • 05:55 From the menu in VBA, choose Debug, Compile and then Save.
  • 06:02 Back on the Design View of the Main Menu,
  • 06:04 select both command buttons and set the Width to 1.5".
  • 06:10 Copy the Invoices button to create a new button with:
  • 06:14 Name: cmd_Services
  • 06:17 Caption: Services
  • 06:20 In the Navigation Pane, select frm_SERVICE_TYPES,
  • 06:28 press F2 to rename, Ctrl-C to copy,
  • 06:33 then ESCape to not really rename it.
  • 06:36 In the Click Event procedure for the new button,
  • 06:39 type [ and press Ctrl-F2.
  • 06:43 In the VBA module,
  • 06:45 Ctrl-drag the statement to open the Invoices form
  • 06:48 to the blank line for cmd_Services_Click()
  • 06:52 and change it to read:
  • 06:54 DoCmd.OpenForm "frm_SERVICE_TYPES"
  • 07:04 and, obviously, you can paste that form name.
  • 07:07 Debug, Compile the code and Save.
  • 07:11 Now let's test what we did.
  • 07:13 Go to the form view and test your buttons.
  • 07:16 Each form should open when you click a button.
  • 07:19 Macros and VBA give us a way to automate our applications.
  • 07:25 In the next lesson, we will add command buttons
  • 07:27 to the menu form to open reports
  • 07:30 and learn how to filter a report for criteria.

Lesson notes are only available for subscribers.

Gift this course