Advanced Report and Form Techniques – Project 7

1. Advanced Report and Form Techniques – Project 7

2. Project 7 Overview This project shows how to create queries for reports, add command buttons to forms, and modify these using VBA. We will learn … • how to create reports from scratch using Design view rather than the Report Wizard • how to use grouping in a report and include a subreport. • how to create mailing labels • how to add command buttons to a form • how to create combo boxes that can be used for searching • how to modify a command button and a combo box using Visual Basic for Applications

3. Project 7 Perspective (AC 401) AJC would like a master list of trainers. • For each trainer, details on clients assigned should be listed • For each of these clients, list courses scheduled to take. Include course hours and hours already taken for course. • Produce mailing labels for clients – sorted by zip code. AJC would like improvements to the Client Update Form. • Buttons added to assist in moving to the next record, previous record, adding a record, deleting a record, and closing the form. • Add a “Name to Find” combo box so the user can retrieve the client name which can then be automatically entered into the form.

4. Introduction (AC 403) Advanced Reports and Forms • Figure 7-1a • We will create this type of report which includes a subreport for the course offerings data. • Figure 7-1b • This figure shows the mailing labels we will create. • Figure 7-1c • This form shows the “Name to Find” combo box we will add. • We will also use the wizard to create the command buttons shown at the bottom. • Once those are created, we will modify the event procedure for the Add Record button using Visual Basic for Applications (VBA).

5. Creating a Report (AC 405) Subreport • A subreport is a report contained within another report • A main report can contain more than one subreport • If the main report is based on a table or query, each subreport must contain information related to the information in the main report. • If the main report is not based on a table or query, it simply serves as a container for the subreports, which then have no restrictions on the information they must contain. Now, let’s get started … Open Access and AJC database

6. Creating a Report (AC 405) Creating a report from scratch • A few report sections are automatically included by Access when you create a report from scratch • Page Header, Page Footer, and Detail • You must add other report sections manually  Group Header/Footer • Our report will be grouped by trainer number • Within each trainer, the records will be sorted by client number • To keep parts of the group on the same page -- group header, detail section, and group footer – change the Keep Together property to Yes. • This report includes a group header but no group footer. Now, let’s start … Open Access and the AJC database

7. Creating Mailing Labels (AC 419) • You create labels just as you create reports. • There is a wizard, the Label Wizard that assists. • You can specify the type and the dimensions of the label, the font used for the label, and the content of the label. • Now, let’s get back to Access and create labels.

8. Enhancing a Form (AC 422) • Command Buttons • Use the Control Wizards tool and Command Button • These tools make creating command buttons easy • Using the Wizard, you provide the action to be taken when button is clicked  several categories of actions are available • Many DBMS packages require programming knowledge to place a command button • Command buttons can display either text or a picture • Combo boxes • Notice the (&) in Figure 7-50 (AC 429) • The ampersand (&) in front of the letter N indicates that users can select the combo box by pressing alt+n • Now, let’s get back and edit the Client Update Form.

9. Modifying the Add Record Buttonand Issues with the Combo Box (AC 434) • Add Record Button • To display an insertion point automatically when you click the Add Record button, you need to change focus • To do so, we will use Visual Basic for Applications (VBA) • First change the name of the control for the Client Number field to be without spaces since VBA does not permit spaces. Instead, use an underscore (_) as in Client_Number. • Next add a statement to the VBA code (Access created for the button click event). • The statement, Client_Number.SetFocus, will move the focus to the control for the Client Number field as soon as the button is clicked.

10. Modifying the Add Record Buttonand Issues with the Combo Box (AC 434) • Combo Box • First, if you look at the list, the names are not in alphabetical order (Center Services comes before Calder Plastics) • Second, when you move to a record without using the combo box (i.e. Next Record), the name in the combo box does not change to reflect the client name currently on the screen • Third, when you TAB through the fields on the form, you should not move to the combo box, because it does not represent a field to be updated. • Now, let’s get back and edit the Client Update Form.

11. Advanced Report and Form Techniques – Project 7 Any Questions?