A form, printed or online, is often the primary interface between you and your clients.
|
Access recognizes this by allowing you to enhance your data presentation and automate your data updating.
|
Using a form, you can gather related information, for example about suppliers.
|
Instead of searching various tables, you can gather all the information and display it in a single form.
|
You can control and enhance the way your data displays, both on screen and in print.
|
For example, you can add shading to improve the appearance of the form.
|
You can display pictures and graphs directly on the form.
|
This is useful when you want to emphasize points, and inform clients about sales trends, future developments, or issues of special interest to your business.
|
You click the ^UForms^u tab in the Database window to see the list of available forms.
|
Let's look at the Products form in the Database window.
|
If you click ^UDesign^u, the selected form opens in Design view.
|
You can alter or modify a form's structure in Design view.
|
Clicking the ^UNew^u button lets you create a new form.
|
Access opens the New Form dialog box in which you can choose to build a form without a wizard or to activate any of the available Form wizards to assist you.
|
Let's say you want to enter data in an existing form, Products.
|
You select the form, and then you click ^UOpen^u.
|
Right-clicking ^UProducts^u, and selecting ^UOpen^u has the same result.
|
The initial default Form Window view for Access is Form view.
|
You can view, change, or delete form data in this view.
|
You click the ^UView^u button to see all the fields in the form arranged in a datasheet.
|
Then you select ^UDatasheet View^u from the drop-down list.
|
Access displays all the fields in the form arranged in a datasheet.
|
Depending on the number of records, you may find that they scroll off the screen.
|
When each record you want to display in a form contains a lot of information, you can design a ^Rmultiple^r ^Rpage^r form.
|
You use the record number box and the buttons in the bottom left-hand corner to view the first page of information for record.
|
^RContinuous^r forms are useful for browsing through a list of records when each record has only a few fields.
|
Rather than showing you one record at a time, a continuous form displays formatted records back-to-back, much like a datasheet.
|
Access also provides a special type of form, called a ^Rmodal^r form, that requires a response before you can continue working.
|
An example of a modal form is a specially designed option dialog box which allows you to choose between various options.
|
A modal form is the same as a modal message box - you can't do anything else in Access until you've responded to it.
|
Generally forms are designed so that you can insert a new record, change fields, or delete records in Form view or in Datasheet view.
|
Let's suppose that you want to enter a new record in the Products form.
|
The way you enter a new record varies, depending on how you have designed the form.
|
To open the Products form, you double-click it.
|
You click the ^UNew record^u button to enter a new record.
|
Or choosing ^UEdit - Go To - New Record^u takes you to a new record.
|
As soon as you enter new data in a record that has an AutoNumber as its primary key, Access automatically assigns a new number to the primary key field.
|
You enter the name of the new product in the Name field, and press the ^UTab^u key to move to the next field.
|
The pencil icon in the top left-hand corner of the record indicates that the data is being updated.
|
You enter the relevant data in the Category ID and Unit Price fields.
|
Then you click the ^UView^u button and select ^UDatasheet View^u.
|
Access displays the new record.
|
You can also change or delete existing data in a form.
|
To do this you select the record you want to delete, record number 5 in this case.
|
You can choose ^UEdit - Delete^u to delete the record.
|
Access warns you of the implications of deleting the record, and asks you to confirm that you indeed want to delete it.
|
You click ^UYes^u.
|
Access then removes the record.
|
When you use forms to display and edit your data, you can search for data and sort it in a new order.
|
When you specify a sort order in Form view or Datasheet view, you can sort all records in ascending or descending order (not both).
|
Let's say you want to sort your customers' names in alphabetical order.
|
You sort the Customers form in Datasheet view using the ^UQuick Sort^u command.
|
First, you click the ^UCompany name^u heading.
|
You click the ^USort Ascending^u button on the Standard toolbar.
|
Access automatically sorts the selected field in alphabetical order.
|
Regardless of where you specify the sort order, Microsoft Access saves the sort order when you save the form or datasheet.
|
When you open the form again, the data is displayed in the sort order in which it was last sorted.
@@
Access also allows you the change the criteria by which records are filtered. For example, let's say you want to see a list of employees from different departments.
|
You click the ^UFilter by Form^u button to filter a form by new criteria.
|
Choosing ^URecords - Filter - Filter by^u ^UForm^u or right-clicking the active form, and selecting ^UFilter by Form^u has the same effect.
|
Then you select ^UDepartment Name^u, and you choose ^UMarketing^u in the drop-down list box.
|
You can enter one criterion, and then click the ^UOr^u tab at the bottom of the screen to enter additional criteria.
|
For example, you may want to see the marketing employees as well as the shipping clerks.
|
If you don't like any of the criteria you've entered, you click the ^UClear^u ^UGrid^u button to start over.
|
When you are satisfied with the filter, you click the ^UApply Filter^u button.
|
Access returns only those employees who work as shipping clerks in the Marketing department or whose job title is shipping clerk.
|
To remove the filter you simply click the ^URemove Filter^u button.
|
Choosing ^URemove Filter/Sort^u command from the ^URecords^u menu also has the same result.
|
Excel removes the filter that you've applied.
@@
Access includes features such as AutoForm and the Form Wizard which allow you to create and design forms quickly and easily.
|
For example, the AutoForm facility and the Form Wizard enable you to get a jump-start on creating and designing forms.
|
Suppose you want to create a simple form that will allow employees to verify their personal details.
|
The quickest way to do this is to create an Employees form using the AutoForm facility.
|
You can also create a form based on a query.
|
In the Database window, you click the ^UTables^u tab.
|
Then you click the arrow next to the ^UNew Object^u button on the toolbar, and you choose ^UAutoForm^u.
|
Access automatically builds the form, and gives it the same name as the table you initially selected.
|
All the fields from the selected table are included in the new form.
|
You cannot include fields from another table when you create a form this way.
|
The AutoForm facility is useful for situations where you are not concerned with appearance but with functionality.
|
An AutoForm also serves as a good starting point for a basic form which you can then customize to your specific needs.
|
Much like any form in Access, it can be opened in Design view, formatted, and modified.
|
Suppose you want to build an orders form to be sent to your customers.
|
The Form Wizard gives you more control over the appearance and layout of a form.
|
You click the ^UNew Object^u button.
|
Then you select ^UForm^u in the drop-down list.
|
The wizards and AutoForms available in the New Form dialog box enable you to build forms quickly.
|
For example, the Chart Wizard builds a form containing a graph object.
|
You select a data source for your form by changing the table or query name displayed in the list box.
|
You select ^UForm Wizard^u, and then you click ^UOK^u.
|
In the Form Wizard dialog box, you can select any field in the Available Fields list by double clicking it.
|
To choose a highlighted field, you click the single right arrow.
|
You can also include fields from other related tables by changing the selection in the Table/Queries list box.
|
If you copy the wrong field, you select it in the Selected Fields field, and click the single left arrow to remove it.
|
You can remove all fields and start over by clicking the double left arrow button.
|
Here you click the double right arrow to include all the Orders fields in the new form.
|
You click ^UNext^u.
|
Access gives you a number of form layouts to choose from.
|
You can choose to display the controls on your form as the default setting, "columnar".
|
You can also choose to arrange the controls across the form in a tabular format which will create a continuous form.
|
Alternatively you could choose the Datasheet format.
|
The last available option is Justified format.
|
In this case, you select the ^UColumnar^u radio button, and then you click ^UNext^u.
|
Now you have to choose the style you want your form to appear in. You are shown a sample view of each selection you make in the left pane.
|
This time, you select ^UStone^u.
|
Then you click ^UNext^u.
|
The last step is to enter an appropriate title.
|
If you click the ^UModify the form's^u ^Udesign^u radio button, Access takes you into Design view.
|
Here, you choose ^UOpen the form^u ^Uto view or enter information^u.
|
You click ^UFinish^u to go directly to Form view.
|
The new Orders form is displayed.
@@
@@
You may sometimes need to display from your database in more complex relational manner than is possible using a standard form.
|
You may, for example, want to bring together data from various tables and queries.
|
Using subforms, you can combine information so that you don't have to switch between separate tables or forms.
|
You can create an outer form or main form in the usual way using the Form Wizard, and then embed a subform in it.
|
In most cases, the subform is linked to the main form, so it shows records that are related to the current record on the main form.
|
For example, this subform shows data from an Orders table and a Customers table.
|
Links between a main form and a subform have to meet two conditions.
|
The main form should be based on a table or a query with a primary key.
|
The subform should be based on a table that contains a field with the same name or value as the primary key field in the main form.
|
The main form is based on the Customers table, so the first condition is met.
|
The subform meets the second condition because it is based on the Orders table that also has a Customer ID field.
|
If the two conditions for linking a main form and a subform are not met automatically, you can still link the forms manually.
|
But there must be a field in the underlying table for the main form that has values in common with a field in the underlying table for the subform.
|
This is called a "one-to-many" relationship.
|
The data in the Customers table is the "one" side of the relationship.
|
The data in the Orders table is the "many" side of the relationship since each customer will generally order more than once.
|
The main form and subform are linked so that the subform displays all the related rows from the linking table.
|
For example, where the main form displays the customer's details, the subform displays order details.
|
You can use the navigation buttons in Form view to go to the next customer record.
|
If you need to make a change to a record, you only need to alter it in one place, in a subform for example.
|
Let's say that a vendor name is changed from Southern Distribution to Solution Technologies.
|
To update the entire database on this change, you simply use the Orders/Customers subform to change the company name.
|
You save the change, then switch to the Customers table to test the change.
|
Access reflects the change you made in the subform.
|
A subform can be displayed as a datasheet, or it can be displayed as a single or continuous form.
|
A main form can only be displayed as a single form.
|
It can have any number of subforms if you place each subform on the main form.
|
You can also nest up to two levels of subforms.
|
For example, you could have a main form that displays customers, a subform that displays orders, and another subform that displays order details.
@@
The easiest way to create a form with a subform is to use the Form Wizard.
|
The Form Wizard builds both forms and makes them work together.
|
Lets say that you want to build a form based on the Customers table.
|
On the main form you also want to indicate the total value of orders placed by the customer.
|
The main form is based on a Customers table.
|
You also want to create a subform based on a query which contains the customer's order details that you want displayed on the subform.
|
Both forms have a Customer ID field which means that Access is able to link the two forms automatically.
|
In the database window, you click the ^UForms^u tab to display the list of forms.
|
You click the ^UNew^u button.
|
Then you double-click the ^UForm Wizard^u option in the New Form dialog box.
|
In the Tables/Queries drop-down listbox, you select ^UTable: Customers^u.
|
You then select ^UCompanyName^u, ^UCustomerID^u, and ^UPhone^u to display them in the Selected Fields list box.
|
In the Tables/Queries list box, you select ^UQuery: Order Subtotals^u.
|
You double-click ^UOrderID^u and ^USubtotal^u.
|
Then you click ^UNext^u.
|
You select the ^UForm with^u ^USubform(s)^u radio button, and then you click ^UNext^u.
|
You select ^UDatasheet^u as the layout for your subform, and you click ^UNext^u.
|
Access allows you to select a style for your form, ^UStone^u for example.
|
You click ^UNext^u.
|
You give the form an appropriate title.
|
Then you click the ^UFinish^u button to close the Form Wizard dialog box.
|
Access saves both the main form and the subform.
|
As your business needs change, you can modify your forms to suit your requirements.
|
For example, you may have a Products Form which you are using more frequently than before.
|
To avoid having to switch between forms and tables, you can add a subform which shows the manufacturer's details for each product they supply.
|
You select the form and then you click ^UDesign^u.
|
If the Toolbox is not visible, you click the ^UToolbox^u button to display it.
|
You click the ^USubform/Subreport^u button.
|
Then you drag the crosshair to the size you want for the subform.
|
In the Subform/Subreport Wizard dialog box, you can choose between creating a subform from another form, or creating it from a table.
|
You click the ^UTable/Query^u radio button to use the Manufacturers table in the subform.
|
Then you click ^UNext^u.
|
In the Tables and Queries list box, you select ^UManufacturers^u.
|
In the Available Fields list you click the fields you want to appear in the subform.
|
Then you click ^UNext^u.
|
Access allows you to define the link between a main form and a subform or to choose from a list.
|
You click the ^UChoose from a list^u radio button.
|
Here, you select the link ^UShow^u ^UManufacturers for each record in^u ^UProducts using Product ID^u.
|
Then you click ^UNext^u.
|
You name the new subform you've created.
|
Then you click ^UFinish^u to close the dialog box.
|
Access creates the form you've built and saves it as a separate form.
|
You can open it by double-clicking the subform.
|
It opens in the default view.
|
Let's see what the subform looks like when embedded in the main form.
|
You double-click the main form.
|
Access nests the Products/Manufacturers subform in the main form.
@@
@@
The crosshair has been dragged to the appropriate site and the Subform/Subreport Wizard dialog box is displayed.
