Tuesday, May 3, 2011

Create a report generated by combo box and user input - Access and VBA

So...results are in for another race event and you want to create a report/certificate that any runner can print that will list their name and completed race time. The user should be able to print this report/certificate by typing their name into a textbox and selecting the race/event name from a combobox. This is just an example! Any type of report can be created easily in Access as long as you have the data available and a litte know-how.

1) You need to have at least one table of data imported or created within Access. This tutorial uses MS Access 2010 contains three tables of data. The first is a table called tblBridgeToBrews8k - these are confirmed runners for a race. The second table is tblBridgeToBrews8k_results which contains the results for the runners that participated. The unique identifier for these tables is a field called Race_No. If you know anything about relational databases, you understand that realationships can be created from one table to another using a unique identifier or Primary/Foreign Key. The third table is called tblRaces, and it holds event/race name and date. A field called EVENT_NAME cooresponds with the name of the event and is a primary key of this table. The same field (EVENT_NAME) is in tblBridgeToBrews8k_results.

2)  Create a form. To do this click on the Create tab. In the Forms group, Click on Blank Form- there are other options, but this seems to work the best! A blank form appears on your screen. We will create a form that will contain two combo boxes, and a button. These will allow the user to choose report criteria, and then click to open report.

3) Right click on the Title bar of the form, and click Design View. Your form appears in design mode with a grid like interface.


4) Drag and drop a text box and insert a Title and/or Image onto your form. The textbox and combo box will add next are referred to as Controls. In case this is new for you, controls can be found on the Design tab within the Controls group. Place the textbox as pictured below and use the textbox label to enter Caption: Enter Name. Arrows can be used on the textbox abd textbox label to reposition the control on the form. Click on Property Sheet button on the Design tab in the Tools group to format any control on the form. Click on Title in the Header/Footer group on the Design Tab, nearby is a button to add an image to the from.


5) Next add a combo box control from the Design tab. When you select and place the control on your form a wizard will walk you through populating the combo box. Choose "I want combo box to get values from another table...." Then choose the table or query you will pull data from, what fields you would like to show in the combox box, and if you would like to order the items in the field ascending, etc. Resize or reposition the combo box. The data for this tutorial combo box will be from tblRaces using the field Event Name sorted ascending.


6) Add a label control and give your user directions on how to use the form.
7) Finally drag and drop a button command onto the form. Click Cancel when the macro wizard appears. Use the Property Sheet, All Tab to add a caption to the button and rename the control. I used btnCreate as my button name, and set the caption to "Create Certificate".

8) Final formatting. The Format tab has text and other formatting options.
In Access 2010, the Design tab has a group called Themes. This can help you customize the look and feel of the form, font, colors, etc.


9) Form Properties.
Click to select your form. Go to the Design tab and click on Property Sheet from the Tools group. When the Property Sheet opens make sure Form is selected in the combo box. Change the property values on the listed tab to the following:
FORMAT TAB
Record Selectors - No
Navigation Buttons - No
Scroll Bars - Neither
DATA TAB
Data Entry - Yes
OTHER TAB
Modal - Yes


10) Set the form to open when the Access file opens:
Click on File, Options, and set Display Form to the form in your access database file that you are working on. For this tutorial I chose frmCreateReport.



Here is my completed form:


11) Create a query that will have all the data necessary to populate report certificate.

Click on the Create tab and click on Query Design. Select data tables and create a query. I will select two tables, and create the relationship link between the EVENT_NAME fields.See Below. This lets me bring in matching data from tblRaces. We will set parameters in this query after the report is created. Save the query. mine is called qryReport.



12) Design the Report
Click on the Create tab, and click the Blank Report button. Right click on the title bar and select Design view. Click on the Property Sheet from the Design tab or Report Properties can be accessed on the Design Tab or by right clicking and selecting Report Properties from the menu. Click on the data tab, and find Record Source. Select the qry created in the previous step. This will be your record source for the report.

On the Design Tab, Click on Add Existing Fields - this will open a window with all the fields from the query. They can be dragged and dropped onto the report. Arrange the fields as you wish.

The report can be formatted for color, size, text, images, etc.

Save the report. Mine is called rptCertificate.

13) Set parameters within query
Open the query in Design View. I set parameters on the Name and Event fields. This way whatever the user enters on the form dictates what report/certificate is generated.
See picture. Code Parameters are:
Name: [Forms]![frmCreateReport]![txtName]
Event: [Forms]![frmCreateReport]![cmbEvent]




14) Put Code on form and open Report. -- The final stage!!

Open form in Design view. Right Click on Button, Select Build Event from the pop-up menu, then click Code Builder from the window that opens.

Here is the VBA for the button click event:

Option Compare Database
Option Explicit
Private Sub btnCreate_Click()
DoCmd.OpenReport "rptCertificate", acViewPreview
End Sub

Time to try it out!!




3 comments:

  1. Crissy -

    How about filters on reports themselves - do you know how that is done? For example, displaying a cert and in the header have a combobox allowing you to select a user and then displaying the resulting cert with that persons name.

    ReplyDelete
  2. Crissy,

    Nice tutorial, thanks! I'm having an issue with my report not displaying any data, though. When I run the report by opening it directly, it shows up without a problem after prompting me for the data that is normally entered in the form that the background query is supposed to reference, so I don't think it's an issue with the report. It's as if, when the button on the form is clicked, the query is not "seeing" the data that is selected in the combo box (I am not using a text box and am only referencing one field in one table). Do you have any ideas about what could be causing this problem? Thanks!

    ReplyDelete
  3. AWESOME!!! You saved me!

    ReplyDelete