Application Development in MS Access 97
LIS 558
by Vijaya Chevendra
Input Forms for Queries......
Overview......
Create an Input Form for criteria......
Create a query that gets its Criteria from the Input Form......
Using the Builder......
Command Buttons......
Command Buttons to Display Specific Record(s)......
Macros......
Overview......
Creating a Macro......
Editing a macro......
Events......
Macro Groups......
Conditions......
Identifying Errors in a Macro......
Section One
LEARNING OUTCOMES
At the end of this section, students will be able to:
- Create a form to input query criteria
- Create a query that references controls on the input form
- Use the Builder to create expressions
- Create command buttons and connect them to forms and reports using the Command Button Wizard
Input Forms for Queries
Overview
You already know how to create parameter queries. Parameter queries allow the user to supply criteria for a form. This is a good design if there are only a couple of fields in the query for which criteria is entered. However, if there are multiple fields, a more appropriate design may be to create one form in which the user can enter criteria for numerous fields in the underlying query.
The following example retrieves a list of specific books from the Books table in the Wealth of Knowledge Bookstore based on the user’s input. The figure shows a form that contains no bound controls. Instead, unbound controls are used to collect criteria. These criteria are used by a query that is connected to this form. However, the user does not see the query directly. Instead, the user chooses to view the resulting dynaset either in a form (unrelated to the input form) or in a report.
Once the user enters criteria, the user may choose to view the results in a form (e.g., if the records need to be edited). If the Browse button is clicked, the user is presented with the results of the search in a form similar to the following figure.
Instead of browsing the actual records, the user may want to view the results in a report format. If the Preview button is clicked after criteria have been entered, the user is presented with a report in a print preview window similar to the following figure.
Note that the preceding form and the following report contain the same data, they are simply presented in different formats.
To automate a query in this way, the following steps must be completed:
- Create an input form.
- Create a query that gets its criteria from the input form.
- Design a form that is based on the query dynaset.
- Design a report that is based on the query dynaset.
- Modify the input form to add buttons that access the form and report.
Create an Input Form for criteria
To create the input form, create a blank form that does not have an underlying data source.
In design view, create six text boxes. Modify the labels appropriately.
Modify the Name property of each of the text boxes to more accurately reflect the content. Suggested names for the text boxes in the following example are Title, Author, CostLow, CostHigh, DateLow and DateHigh.
Because there are only a limited number of subjects for our books, it would be convenient to select a subject from a drop-down box on the input form.
Create a combo box that displays current values in the Subjects table, as you learned to do in the demo of Lecture 8. Edit the label and name the control. It will appear similar to the following figure.
Create an option group as you learned to do in the demo during Lecture 8. Assign a value of -1 (true, yes) for the option button with the ‘Fiction’ label and a value of 0 (false, no) for the option button that has the Non-fiction label. Information provided here will be used as criteria for the Fiction? field in the Books table, which has a yes/no data type.
Save the form (e.g., Query Input form for Books) but do not close it. Leave it open (minimized is ok) while the query is designed.
Create a query that gets its Criteria from the Input Form
Create a query that selects the fields desired for the dynaset.
The following diagram shows the query in design view that will eventually get its criteria from the Input form. The criteria to be entered will be an expression.
Expressions are a fundamental part of many Microsoft Access operations. An expression is a combination of symbols, identifiers, operators, and values that produces a result.
Click in the criteria row for Author.
If the user will enter the entire field content into the appropriate control on the input form, the criteria should be entered using the following syntax:
[Object type]![Name of the object]![Name of the control]
In reality, users will usually enter only part of the field content. Users may also leave the criteria blank, indicating that there are no criteria. However, Access will not find any records if the control on an input form is left blank.
To accommodate these possibilities, concatenate the criteria with a wildcard (*). When this is done, relevant records are found even when only part of the criteria is entered. For example, entering Atwood in the Author field will find Margaret Atwood - this is not an exact match but the wildcard (*) will accommodate such an entry. Furthermore, if no author is entered, then any author is found.
Thus, modify the syntax for criteria similar to the following example:
Like "*" & [Forms]![Query Input Form for Books]![Author] & "*"
Using the Builder
Rather than having to type long expressions (increasing the possibility of typing errors), Access includes an Expression Builder that assists the user in creating expressions.
Click in the criteria row of the field for which you want to build the expression.
To invoke the Expression Builder, click the Build tool on the Query Design View toolbar. The Expression Builder will be invoked and appear similar to the following diagram.
In the first pane, double click to select the object that provides the criteria for the field in the query. (Loaded objects refer to open objects).
In the second pane, select the control that provides the criteria for the field in the query.
Click the Paste button to enter the reference to the control in the top pane of the builder with the proper syntax.
Edit the expression in the top pane as necessary and click the OK button when done. The expression will be transferred to the criteria row in the query design.
Use the Expression Builder to enter expressions using wildcards for all of the text fields and they will appear similar to the following examples:
Like "*" & [Forms]![Query Input Form for Books]![Title] & "*"
Like "*" & [Forms]![Query Input Form for Books]![Subject] & "*"
Because radio buttons are used in the option group and a value has been set as the default, the Fiction field will never be left blank. Therefore, wildcards are unnecessary in this case and the criteria expression can be entered as shown in the following example. If you did not identify a default value while creating the option group, you may still do so, following the instructions described for the price field.
[Forms]![Query Input Form for Books]![Fiction or Non-fiction]
In the case of the Year and Price fields (numeric), upper and lower limits of criteria will be entered and, therefore, both criteria for each field must be referenced in the criteria expression as shown in the following examples:
Between [Forms]![Query Input Form for Books]![YearLow] And [Forms]![Query Input Form for Books]![YearHigh]
Between [Forms]![Query Input Form for Books]![CostLow] And [Forms]![Query Input Form for Books]![CostHigh]
Save (e.g., Query with criteria from Input Form for Books) and close the query.
For text fields, we accommodated the possibility of partial or null entries by using wild cards. In the case of numeric fields, the default value property for the control on the form must be set to a value to account for the possibility that these controls may be left blank.
For example, none of the booksin our database cost as little as $0 and none will cost as much as $1000, thus these were used as the default values for the PriceLow and PriceHigh controls. Similarly, the limits of 1800 and 2000 were used for YearLow and YearHigh controls because the publication date of all of the books in our database fall within these years.
Select the control on the input form that will have numeric/date data entered. Set the default value property of the control, found under the Data tab of the Properties box and appears similar to the following figure. Set the values outside of the limits of the data.
Save the input form again and close (or minimize) it.
Command Buttons
After the user enters criteria into the input form, the user requires a method to access the form or report that shows the dynaset. The form and/or report should first be created. Command buttons, which are ideal to access other objects from a form are then created and connected to those objects.
Create a form based on the query created in the previous step. Design it using all of the skills you learned in the laboratory component of LIS 558. Save (e.g., Books found with query input form) and close it.
Create a report based on the query created in the previous step. Design it using all of the skills you learned in the laboratory component of LIS 558. Save (e.g., Books found with query input form) and close it.
Open the input form in design view and show the toolbox.
Ensure that the Control Wizards is selected.
Select the Command Button tool from the toolbox and click in the location on the form where the button is to appear.
The Control Wizard will appear similar to the following figure.
In the first step, select the category of Form Operations. In the Action pane, select Open Form.
In the second step, select the appropriate form that shows the dynaset using criteria on the input form.
In the third step, select Open the form and show all the records.
In the fourth step, choose whether the command button should show text or a picture.
In the last step, provide a name for the button that will help you refer to it later, if required.
View the form in form view and try it out!
Create another command button to open a report and proceed through the steps as described for the form. For this button, select the Report Operations category and Preview Report as the action. Connect this button to the appropriate report that shows the dynaset using criteria on the input form.
A form should have a way that the user can close the form, such as the Cancel button on our input form.
Create another command button. Select Form Operations for the category and Close Form for the action.
The buttons on the form will appear similar to the following figure.
Note that the On Click property for each command button says [Event Procedure]. The command button Wizard has created programming instructions that will either open the appropriate object (form or report named) or close the form when the button is clicked.
Your input form will now be finished.
As you have seen, the Command Button Wizard makes a variety of Event Procedures available that can be used to automate a wide variety of tasks. The following are some other common operations that can be simplified by using the Command Buttons.
In the Books Inventory form, automate the following procedures with the Command Button Wizard: add new record, delete record, undo changes (Record Operations), find records (Record Navigation) and close form (Form Operations). In the following diagram, pictures were selected rather than text. When the user pauses the mouse pointer over the buttons, a tool tip (similar to that of tools on toolbars) will appear with the name of the function that the button performs.
Command Buttons to Display Specific Record(s)
The Command Button Wizard can create a button that shows all records in forms or reports, as you have seen previously. Sometimes, you may wish to view only specific records. For example, you may have designed an invoice. However, when the report is opened, an invoice is generated for all orders in the database, but you probably just want the invoice for the current order. This section will describe how to create a command button that shows only a report or form only for the current record on a form.
In the following example, clicking the View orders for Book button, shows a monthly summary of orders but only for the current record (book on which cursor was placed).
To create a command button that displays only specific records in a form (or report), (a) create the query that displays the desired information; (b) create the form or report to be viewed; and, (c) connect the form or report to a button on the form from which this object is to be opened. Perform the following detailed steps.
Create a query that selects the desired fields - do not enter any criteria. Save the query (e.g, Monthly order summary by book). It will appear similar to the following figure.
In our specific example, we are adding up the orders and grouping by month within a year for each book. The date appears twice: first for display purposes (text format) and again for sorting purposes (numeric format). The format function for dates is useful for this and its syntax is format([field name], “date picture”) (e.g., format ([order date], “mmm- yyyy”) displays as Dec-1998).
Create a form or report based on the query. This form or report will show all records at this stage. Save this object (e.g., Monthly order summary by book).
On the form from which you wish to access the related form, create a command button, using the Wizard. Select Form or RecordOperations and identify the object to be opened. In the next step, indicate that the form or report is to display only particular records. The Wizard will appear similar to the following figure.
In the next step of the Wizard, indicate the basis on which records are to be displayed on the form that will open. Click the field in each table that is equivalent then click the button with bi-directional arrows between the two field lists.
In the Books example, select only orders (from the Monthly order summary by book form) for the book that is the active record in the Books form.
Finish the remaining steps of the Wizard.
To create a title for the orders form that displays the name of the currently selected object, create a text box and enter a reference to the original form.
In the Monthly order summary by Book formexample, title text has been concatenated with the name of the book from the active record of the Books form. In design view, the text box will appear similar to the following figure.
In form view, the text box will appear similar to the following figure.
The completed form will appear similar to the following in design view.
Section Two
LEARNING OUTCOMES
At the end of this section, students will be able to:
- Design, save and execute a multi-step macro
- Edit a macro upon finding errors by stepping through actions
- Create macros that contain macro groups
- Create and use basic conditional expressions correctly
- Connect macros to events on forms
Macros
Overview
A macro is an Access object, as are tables, queries, forms and reports. A macro is a set of tasks that are automatically carried out in the order that you list them when the macro is created. Macros save time and limit human error related to performing repetitive tasks.
Each task in a macro is called an action (e.g., open form). Access provides built-in actions. The user assigns the arguments for actions (specific information for the action), some of which are required and others which are optional. For example ‘open form’ is an action; the form’s name is a required argument. Actions are connected to events (e.g., click) associated with an object (e.g., command button). Events are occurrences that trigger macros.
Macros are used to:
- Make forms and reports work together. Macro actions such as OpenForm, PreviewReport can be used to connect one object to another.
- Find and filter records automatically. FindRecord, ApplyFilter are actions that display or locate only specific records.
- Ensure data accuracy. Macro actions such as MsgBox may be used to check data and send appropriate messages to the user.
- Set values in controls. Macros such as SetValue may be used to transfer values from one form to another form or report.
- Automate data transfers. Macro actions such as TransferSpreadsheet are used to export or import Access data.
- Create a customized working environment. ShowToolbar, RunCommand, SendKeys, SetMenuItem are all macro actions that can be used to automate menu options.
Creating a Macro
To create a macro, perform the following steps.