The calculated values in Forms

Frequently, we need to do some mathematical calculations in the form or the

report. These calculations sometimes depend on the stored data in the data tables.

For example, if data in the fields of the table include the items of the salary for an

 

employee, there is no need after calculating the net salary to store it in table of

data.· It is useful here, to calculate the net salary on loading the (Employee Form)

in the computer's memory only.

· To calculate the net salary, insert [Textbox] from the [Toolbox] and we

insert also the mathematical expression that calculates the net salary.

Exercise:

Find the ratio of 25% of the salary to refer to the monthly incentive in the

(Employee Form).

Steps:

· Open [Employee] Form in [Design View]

· Select [textbox and insert it in [Details] as in the following figure:

 

Notice the appearance of a new element [unbound] for the fields of (Employee-

Table) as shown in the previous figure.

Select the new element and click [properties] in toolbar of (Form Design) so the

following figure appears.

 

 

 

 

 

· Select [Data] in front of [Control Source] click the button "…" To open

[Expression Builder).

· With the help of [Expression Builder] edit [salary] * 0.25 as in the

following figure:

 

 

Click [Ok] so "Properties" appears this time, but it contains the required expression

as in the following figure:

· Close "Properties" and change the title [New Text] to form the" monthly

incentive"

· Select [Form View]. So the form will be shown as follows:

 

 

 

Compare between the salary value as stored entity and the monthly incentive value

as a calculated statement?

Sub Forms

It is a form inserted in another form (the maim form). We call (The form inside the

main form) a (Sub-Form). We refer to it as (Frame Form- or subordinate form)

The effectiveness of (Sub – Forms) appears when we want to view Data from

tables bearing the relationship of (One-Many) for example; you can create a form

with a sub-form inside it to view data from (Department-Employee) tables.

When we display the name of a certain department, the data of the employees who

work in this department appear in this department only but on viewing another

department name, data of the employees appear in the second section only. This is

only when the relationship is (One to Many) of (Department Table).

The primary form clarifies the data of (One) a (linking relation) where as the sub

form clarifies the data of (Many) of the same relationship.

The main form is linked to the sub form so that the latter can display only the

records connected to the present record in the main form.

Nested levels:

If you put every sub form in a main form, the main form can include any number

of sub forms.With the maximum of seven nested levels of sub forms.

This means that you can put a sub form in a main form; and put another sub form

inside another sub form and so on. For example, you can make the main form

display the clients. While the sub form displays the orders. The sub form displays

the last details of requests.

Exercises:

We will try to use the main/ the sub forms to display (Department – Name) from

(Department Table) and display the employees of this department from

(employees) table.

Steps:

· Select creating a new form whose record source is (Employee) table

Insert the table fields in (Details Section)

· Select the sub form tool from toolbox as follows:

 

 

· Insert the tool in [Details section], so the following figure appears:

 

 

· Select (Table or Query) and click (Next) and notice the appearance of the

following table:

 

 

· Select the Table of (Employee) so its fields appear in the (Available

Field)window

· Click the button ">>" to insert all the fields of (Employee- Table) in the

selected field box.

· Click (Next) and select (Employee- View) for each department according

to the (Department- Number) as in the following figure.

 

 

· Click (Finish) so the wizard terminates and we return to(Design View)

Display the form in (Form View) so the (Sub Form View) inside the Primary

Form appears as shown:

 

 

Notice that there are two navigating buttons, one for the main form and the other

for the sub form.

On changing the department name using the main form navigating buttons, the

sub form will display only the employees of this department.

Questions

1. What are the (Form types) provided by the Ms Access and how is each type

used?

 

 

2. What is the difference between the data retrieved by the form and the stored

elements in (Form Design)?

 

 

3. What are the ways for creating a form and what is the difference between each

of them?

 

 

4. Mention the importance of dealing with (Form Sections)

 

 

5. Illustrate that Switching between the ways of (Form View) has its importance at

the stage of designing?

 

 

6. Does navigating across records affect the data of tables?

 

 

7. In what way are adding and deleting records considered updating for the tables'

data?

 

 

8. What is the importance of dealing with the calculated values in forms?

 

 

9. Define the concept of the main and sub forms?

 

 

Exercises

1. Create a form whose source records are (Employee Table, Projects) using

the design view, and then display this form in (form View – datasheet).

2. Insert a (mathematical expression) to know the (Employee Salary) in every

project then modify in creating (Employee – Project) table if there is a need

for that.

3. Create a form to display (Employees names) and the projects where

everyone works in.