Command Buttons to Open Reports Using VBA

Locked lesson.

Upgrade

About this lesson

Create a command button to open a report using a macro or VBA and create a Combo Box using the wizard to show Customers and Store CustID.

Quick reference

Command Buttons to Open Reports using VBA

Application Terminology

VBA Procedure to Open a Report in Print Preview on a Click event

This VBA procedure opens a report called "R_Customers".  acViewPreview is the VBA constant to open the report in Print Preview mode so it goes to the screen instead of the printer. The name of the control is cmd_RptCustomers. The event is Click.

Intellisense Shortcut

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

Help for OpenReport

This is the Help from Microsoft for the OpenReport Macro Action.
https://msdn.microsoft.com/EN-US/library/ff834462.aspx

The OpenReport action has the following arguments.

Remarks

The Print setting for the View argument prints the report immediately by using the current printer settings, without bringing up the Print dialog box. You can also use the OpenReport action to open and set up a report and then use the PrintOut action to print it. For example, you may want to modify the report or use the PrintOut action to change the printer settings before you print.

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

The OpenReport action is similar to double-clicking the report in the Navigation Pane, or right-clicking the report in the Navigation Pane and selecting a view or the Print command.

Tips

  • To print similar reports for different sets of data, use a filter or a WHERE clause to restrict the records printed in the report. Then edit the macro to apply a different filter or change the Where Condition argument.
  • You can drag a report from the Navigation Pane to a macro action row. This automatically creates an OpenReport action that opens the report in Report view.

 

Steps

Command Button to Open a Report 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 Report Operations from Categories on the left.
  4. Choose Preview Report from Actions.
  5. Click Next.
  6. From the list of report, choose the report 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., "Customers Report").
  10. Click Next.
  11. For the name of the button, type cmd_something (i.e., cmd_CustomersReport).
  12. Click Finish.
  13. Resize the button and change other properties, if desired.

Command Button to Open a Report 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.OpenReport "ReportName", acViewPreview
    WHERE ReportName is the name of the report you want to open.
  9. From the menu in VBA, choose Debug, Compile and then Save.

Create a Combo Box Using the Wizard to Show Customers and Store CustID

  1. Pick the Combo Box tool from the Controls group, then click on the form where you want it to go.
  2. When the Combo Box wizard comes up, choose "I want the combo box to get the values from another table or query".
  3. Click Next
  4. Choose the Customers table
  5. Click Next.
  6. For the fields, double-click CustID and Customer
  7. Click Next.
  8. Choose to sort by Customer.
  9. Click Next.
  10. Hide Key is checked, which means that CustID will not show, even though it is what will be stored.
  11. In this dialog box, drag the right border of the column header to resize since a customer name may be longer.
  12. Click Next
  13. Choose a Caption for the label.
  14. Click Finish.
  15. Since this combo will contain CustID, change the Name property to CustID.
  16. Change the Name property of the label to have CustID in it too.
Login to download
  • 00:04 This is Access 2013, Lesson 36.
  • 00:08 In the last lesson, we created a main menu.
  • 00:10 In this lesson,
  • 00:12 we will create command buttons to open reports
  • 00:16 Hello, this is Crystal.
  • 00:18 This is the menu form we have so far.
  • 00:20 These buttons open forms
  • 00:22 so let's indicate that with a rectangle and a label.
  • 00:26 Drop the list of Controls to choose the Rectangle tool.
  • 00:30 Draw a box around the controls
  • 00:32 and leave room on the left for a label.
  • 00:34 Get the Label tool
  • 00:36 and create a (label with) Caption of Forms.
  • 00:39 Copy the rectangle and label using Ctrl-C,
  • 00:43 then paste with Ctrl-V.
  • 00:45 Change the label caption in the new control to say Reports.
  • 00:51 Copy the Services command button and paste.
  • 00:54 Change the Caption to Customer Services By Year.
  • 00:59 Make the rectangle wider and stretch the new button
  • 01:02 to show all the text, plus a little more room.
  • 01:05 Copy and paste the new button with Ctrl-C, then Ctrl-V.
  • 01:10 Change the Caption to Customers.
  • 01:13 Copy and paste the command button and
  • 01:15 change the Caption to Services By Type.
  • 01:19 Make the rectangle tall enough for another button.
  • 01:22 Copy and paste and set the new Caption to Simple Invoice.
  • 01:27 Copy the name of the r_CustomerServicesByYear report.
  • 01:33 Change the name of the first report command button to
  • 01:37 cmd_RptCustomerServicesByYear.
  • 01:43 In the click event, type [ and then press Ctrl-F2.
  • 01:48 When you write VBA,
  • 01:50 it is good to add a comment with the date.
  • 01:53 I use the YYMMDD format but you can do as you like.
  • 01:58 Press TAB to indent the lines within the procedure.
  • 02:02 To open objects, start with DoCmd.
  • 02:07 and then the macro action.
  • 02:09 If we picked OpenForm to open a form,
  • 02:12 what are we going to pick to open a report?
  • 02:16 You guessed it, OpenReport.
  • 02:19 Intellisense prompts you with the arguments you need.
  • 02:23 What is the report name?
  • 02:25 We have it on the clipboard so it can be pasted.
  • 02:28 Because the name is text,
  • 02:31 it must be surrounded with what?
  • 02:33 Double quote marks, so type space "
  • 02:38 then paste, then " again.
  • 02:41 Arguments are separated with commas.
  • 02:44 I use the terms Argument and Parameter interchangeably.
  • 02:48 The next argument is how the report should open.
  • 02:52 If you skip this argument,
  • 02:54 it will automatically go to the printer.
  • 02:57 We want the report to display on the screen.
  • 03:00 If the user wants to print it, they can.
  • 03:03 Type a comma after the first argument,
  • 03:06 then choose acViewPreview.
  • 03:09 Most of the built-in constants for Access start with "ac".
  • 03:13 The statement should read:
  • 03:15 DoCmd.OpenReport "r_CustomerServicesByYear", acViewPreview
  • 03:27 Go back to the form design.
  • 03:29 The next report is for Customers, so copy
  • 03:32 the name of the report from the Navigation Pane.
  • 03:35 In the Click event, press [ then Ctrl-F2 to go to VBA.
  • 03:41 Ctrl-Drag the previous OpenReport statement
  • 03:44 to the new procedure
  • 03:46 and replace the name of the report to open.
  • 03:50 Oh no! What did I forget to do?
  • 03:52 I forgot to give the control a good name
  • 03:55 before attaching code to it.
  • 03:58 Go back to the form and change the name to be logical.
  • 04:01 Copy the name to the clipboard.
  • 04:04 Press Alt-F11 to go back to the code.
  • 04:07 In the code, replace the ambigious name with the good name.
  • 04:12 The name of the procedure is controlname_Click,
  • 04:16 whatever your control name is.
  • 04:19 On the form, rename the last 2 command buttons
  • 04:22 so we don't forget again.
  • 04:25 To be consistent, I am going to change cmd_ReportCustomers
  • 04:30 to cmd_RptCustomers, then create a new procedure,
  • 04:35 move the lines from the other one,
  • 04:38 and delete the old procedure, which is now empty.
  • 04:41 Select the next report, r_Services_by_Type and copy the name.
  • 04:49 Remember, F2 is the shortcut to rename.
  • 04:52 Again, create a Click event, ctrl-drag lines,
  • 04:56 and change the name of the report in VBA.
  • 05:00 Do the same thing for the last report.
  • 05:03 See, VBA is not hard. It makes sense.
  • 05:07 If you can build an Access database, you can learn VBA too.
  • 05:12 Debug, Compile and Save.
  • 05:14 Click on the form and then go to the Form View.
  • 05:18 My buttons work, how about yours?
  • 05:21 What about criteria?
  • 05:23 Remember on the Customer Services By Year report,
  • 05:26 we have hidden key fields.
  • 05:29 Now you are going to find out why we did that.
  • 05:33 In the Report Header, you see CustID, ServiceID, and ServTyID.
  • 05:39 These correlate to Customer, Service, and Service Type.
  • 05:44 Go to the Design View of the Main Menu form.
  • 05:48 I double-click the floating Property Sheet titlebar to dock it,
  • 05:52 and make the Reports rectangle wider.
  • 05:55 Pick the Combo Box tool from the Controls group,
  • 05:59 then click on the report where you want it to go.
  • 06:02 When the Combo Box wizard comes up, choose
  • 06:04 I want the combo box to get the values from another table or query
  • 06:09 Click Next, choose the Customers table, and click Next.
  • 06:14 For the fields, double-click CustID and Customer,
  • 06:17 and click Next.
  • 06:19 Choose to sort by Customer
  • 06:21 and Click Next.
  • 06:23 Hide Key is checked, which means that CustID will not show,
  • 06:28 even though it is what will be stored.
  • 06:31 In this dialog box,
  • 06:33 drag the right border of the column header to resize,
  • 06:36 which I will since a customer name may be longer.
  • 06:40 Click Next and choose a Caption for the label.
  • 06:43 That's it, click Finish.
  • 06:45 Since this combo will contain CustID, Name it CustID.
  • 06:50 Change the name of the label to have CustID in it too.
  • 06:54 On the Format tab,
  • 06:56 add a colon (:) to the end of the label Caption,
  • 06:58 and right-align the contents within the control.
  • 07:01 Use the QAT to align the label and the combo box.
  • 07:05 Set the Width of the combo box to 1.75"
  • 07:10 Go to the form view and test the combo.
  • 07:13 You can now pick a customer.
  • 07:16 Nothing, however, happens with this information yet.
  • 07:19 So let's make something happen.
  • 07:22 In the next lesson,
  • 07:24 we will implement the customer criteria.

Lesson notes are only available for subscribers.

Gift this course