Azzedine ALFAIDY

[Link]@[Link]

SUPPORT
ACCESS
 SUMMARY

I. ACCESS AND TABLE CREATION............................................................3

1. The launch

2. The creation and modification of a table....................................................................................................4

3. The relationships between tables

4. General method for creating an ACCESS database ..................................................................................13

II. GRAPHICAL REQUESTS ACCESS ........................................................15

1. Selection queries ..............................................................................................................................15

2. Add criteria to the query..................................................................................................................17

3. Specify other sets of criteria using the OR operator ................................................................18

4. Add calculations to the query...................................................................................................................18

5. Synthesize the values of the request ...........................................................................................................19

6. The configuration of queries.......................................................................................................................19

III. ACCESS FORMS...........................................................................21

1. The ACCESS form................................................................................................................................21

2. Create a form using the Form Tool....................................................................................21

3. Add controls to your form....................................................................................................22

4. Save your work ..............................................................................................................................32

5. Create a form containing a sub-form (one-to-many form)...................................32

IV. IMPORTER, ADD OR LINK DATA FROM A SOURCE

EXTERNAL

2
 ACCESS: DATABASE MANAGEMENT SYSTEM
DATA.
ACCESS is a software in the category of 'relational database management system.'
which offers numerous graphical interfaces for building tables, queries, and
screen forms or printed reports.

A database (DB) is a homogeneous collection of related data stored together with

as little redundancy as possible to serve one or more applications optimally.

A database management system is software that ensures the functions of storage, updating
up to date, research and presentation of this data.

In Access, we use the following objects:

Tables for data storage

Les requêtes pour la recherche des données
The forms for data entry and consultation
States for presentation to the printer

I. ACCESS AND TABLE CREATION

1. The launch
The launch of ACCESS is done through the Start menu – Programs of WINDOWS.
Next, you need to follow the software instructions to open an existing database or create a new one.
new base.

The Microsoft Office Access Getting Started page appears when Access is first started or
lorsque vous fermez une base de données sans fermer Access.

3
 2. The creation and modification of a table
An ACCESS database contains one or more relational tables, in which
data is stored.
To create a table:

On the ribbon, go to table creation using the following button:

Creation of fields

Creating the structure of a table involves defining the fields of this table. The names of the fields
it must start with a letter, not exceed 64 characters, it is advised to avoid all
special characters.

It is possible to use uppercase or lowercase letters, but Access does not distinguish between them:
NomAssuré, nomassuré or NOMassuré are equivalent.

It is also necessary to determine the types of data and to configure the properties of
each field using the table creation mode.

When assigning field names, be careful not to use reserved words in Access.
this could get you into trouble later on. It's a case of 'name' and 'date' for example.

Also avoid giving names that are made up of multiple words, or that contain a space.
Access accepts them but this is not the case for all software and you could get into trouble.
during an export for example.

Define data types

The types of data are declared in the highlighted part of the screenshot below.

One primarily distinguishes text data from numeric data but

Each of the groups is subdivided as indicated on the data types page.

4
 Define properties of the fields

Creating the structure of a table involves defining the fields of that table.
It is then necessary to determine the types of data and to set the properties of each.
champ using the table creation mode.

The properties and their settings differ from one data type to another, as we
Let's move to the Fields Properties page.

Field data types

We define the type of data ...

5
and their size.

Type de données Alphanumérique

Alphanumeric data includes all characters, numbers, uppercase letters and

lowercase letters and signs.

Data type minimum maximum length default length

Text 1 to 255 50
1 to 1,000,000,000 of No default value
Memo
characters specify in one!

Numeric data type

Numeric type is assigned to all data that takes a numerical value on which it is
possible to perform calculations.

The dates are part of the integer of a number representing a date indicating the number of days.
elapsed since a date that depends on the Windows settings. The decimal part corresponds to the hour.
length in
Type of
possible values name
data
of bytes
yes/no -1 et 0 ; oui/non ; vrai/faux ; actif/inactif 1 octet
octet 0 to 255 1 octet
whole -32768 to +32767 2 octets
9 9
long integer -2 times 10 at +2x10 4 octets
simple real decimal number in floating point with 7 significant figures 4 octets
real double decimal number in floating point with 14 significant digits 8 octets
-922337203685.0000
Monetary 8 octets
477.5808 to +922 337 203 685 477.5807
Date dates range from 1/1/100 to 21/12/9999 8 octets
Auto Number auto-incrementing long integer 8 octets
28 28
-7.9x10 +7.9x10 without decimal or 28 significant digits to the right
decimal 14 bytes
of the decimal separator

Other types of data

Data type usage

Objet OLE Allows the incorporation of an object such as sound, image, video, etc.

A hyperlink allows you to specify a link to a web page by entering the url alone or UnTexte#url#.
Attachment allows specifying an attached file of type Word, Excel, or otherwise

6
 Properties of fields

The properties of fields will not be examined exhaustively here, but we will...
we will focus on those that are essential for optimization:
the congestion of the base
data security
the speed of query execution

Likewise, we will not see all the possible values for each property, but
only the most commonly used.
List of field properties
Field size
Format
Input mask
Legend
Default value
Valid if
Error message
Null prohibited
Empty chain allowed
Indexed
Unicode compression allows encoding each character in a text area with a single character.
instead of two as long as it is about Latin characters. The default value is yes.
The properties 'IME mode' and 'IME composition mode' are useful for working in the
Eastern languages, we will not discuss them here.
Active tags are useful for saving time by performing actions in Access that
should normally be executed in other programs. We will not address them for
the moment.
List areas and drop-down lists

Properties of numeric fields

Text field properties

7
 Field size

Text data type

The default value of the size property for a text field is 50 and the maximum size is 255.
characters.

It is advisable to define the size of this field more precisely to avoid reserving too much space.
needlessly this significantly slows down research. For example, reserving 50 characters for a
Phone number takes up 5 times too much space.

In the same way, it is necessary to limit the size of Memo fields.

Numeric data type

This property was seen at the same time as numerical data types.

Format
The format governs the display mode of the data; it does not affect the content itself.

Type text

symbol meaning example

for the postal code: @@@@@
@ alphanumeric character must be displayed
allows displaying 01000
& optional alphanumeric character
example: for the country > & allows
> CAPITALIZE THE CHARACTERS THAT FOLLOW
to display FRANCE
< put the following characters in lowercase

Numeric type

symbol meaning
. or , decimal separator (indicated in the Windows regional settings)
space or . thousand separator (indicated in Windows regional settings)
# display the numbers if they are significant and display nothing if they are not
0 display the entered numbers or 0 when they are not significant
# ##0"text" displays the text literally after the number

Examples:
Format input Display
# ##0.00 ["1957.5589","125.2","1","957.56","125.20"]

0 litres 125.2 125 liters

0 km 12480.26 12 km
In the last example, the space after the zero is considered a thousand separator. The
the number is therefore expressed to the nearest thousand. In any case, the actual content of the data will be
the one who was seized.
Distance application: # ##0" km"

8
 Type date

The symbols used to present a date are d m y (for day, month, year) and h m (for hours)
minutes). The current separator is '/' for the date, '-' is also possible and ':' for the time.

1/1/2008 31/12/2008
Format
02:05 12:30
j 1 31
jj 01 31
jjj sea sea
jjjj Tuesday Wednesday
m 1 12
mm 01 12
mmm jan dec
mmmm January December
aa 08 08
aaaa 2008 2008
h 2 12
hh 02 12
n 5 30
nn 05 30

Examples:
Format Display
Friday, October 10
JJJJ J MMMM AAAA
2008
HH hours and NN 10 hours and 25
minutes minutes
JJ-MMM-YYYY 10-oct-2008
HH:NN 09:30

Input mask
The input mask allows determining the nature of the characters that will be accepted, it performs
sort of a first level of control over the information entered.
It consists of three parts separated by ';' all of which are optional, if the first one does not exist.
but the others are irrelevant.
The mask properly said (made up of the symbols below)
0 if Access needs to store delimiters, 1 otherwise
Character to replace (to be used when expecting a specific number of characters)

Character Description
0 Digit (0 to 9, input required, plus (+) and minus (-) signs not accepted)
9 Number or space (optional entry, plus and minus signs not accepted)
# Number or space (optional input, plus and minus signs accepted)
L Letter (A to Z, required entry)
? Letter (A to Z, optional entry)
A Letter or number (mandatory entry)

9
 a Letter or number (optional input)
& Any character or space (mandatory entry)
C Any character or space (optional input)
.,:;-/ Separators (the character to use depends on the regional settings of Windows)
< convert all characters to lowercase
> CONVERTIT TOUS LES CARACTÈRES EN MAJUSCULES
! Allows filling the input mask from the right

Application to Phone Number: 00-00-00-00-00;0;_

In case of an entry not matching the mask, a beep sounds for an inappropriate entry.
(letter when a number is required) to validate the area, if characters are missing
mandatory an error message appears

Legend

If the legend does not exist, the field name will be used in the reports and forms to refer to the
concerned data. If it is believed that the field name is not sufficiently clear for the operator
In data entry, it can be replaced by a clearer label by filling in the legend property.

Default value

Allows specifying a default value. If a value is common for a field, it can be set to
default value in order to save time in input.

Valid if

This property allows you to specify a validity condition for the input. For example, you can impose
that a date of birth be between 1/1/1880 and the current date of entry.

Error message

The specified message is displayed in cases where the condition specified in the valid if is not met.
Prévoyez un message qui aide l'utilisateur à saisir les champs convenablement. Éviter par exemple un
Input error
between 5000 and 10000

Null prohibited

The default parameter is 'no'. If 'yes' is entered, it will be impossible to leave the field blank.
of information.

10
 Empty string allowed

By default, the parameter is set to 'yes'. We get an empty string, '' by validating the field without entering anything.
in the area. If we set the parameter to "no", we will be required to enter something in the area.

Indexed

Apart from primary keys which are special indexes, two types of indexes can be distinguished: with or
sans doublons. Les index sont utilisés pour optimiser les recherches sur un champ. Le nom d'un contact
It will be an indexed field with duplicates (2 people in the file can be called Dupont). The number
INSEE will be a unique index, the same number cannot be assigned to two people.
different.

Remarks:
ACCESS automatically creates an index when a field has a name suggesting a
identifier (number_xxx, code_xxx ...). Therefore, if necessary, adapt or
remove this index;
To facilitate the updating of composite key tables with multiple fields, it is
strongly recommended to assign them an automatic number as a key
primary. The fields that make up the 'natural' key of the table may constitute
one or more secondary indexes with or without duplicates, as the case may be.

List zone and dropdown lists

The 'Choice List Assistant' type allows, when entering data in the field, to
choose the field value from a list displaying values from another table.

3. The relationships between tables

The links (pointers) existing between tables are called 'relationships' in ACCESS.

These relationships between tables can be represented graphically and are defined through the
operations that follow:

From the workspace, click on the iconor choose the menu options
"Tools" then "Relationships";

- a "Relations" window appears. Use the "Add a table" frame to add

the tables necessary for defining the relationships. If this framework is absent, it can be
displayed by the options 'Relationships' and 'Show table...';

link the related fields in each table by a 'held click' starting from the
field that is a primary key or unique index, leading to the duplication of this
champ. The starting table (source table) is the "Table/query" and the destination table
the "Linked table/query" according to ACCESS terminology (in the example, starting from the
"client code" in "CLIENT" and go to the "client code" in "INVOICE";

A 'Relationships' dialog box appears, which allows you to specify the constraints.
to apply. Click on 'Apply Referential Integrity' and 'Create'. ACCESS
indicate by the "linked" side (multiple invoices can be linked to a client) and by 1
the "source" side (the unique origin of the client code) of the integrity constraint
referential.
11
Note:
A referential integrity constraint is a rule that requires that any value entered in
le champ lié soit présente dans le champ source. ACCESS fait automatiquement cette
verification during data input, as soon as the referential integrity constraint is
defined.

Other options concern relationships:

- "Cascading update" allows a change in the primary key value of the

The source table is automatically reflected in the rows of the linked tables.

"Cascade delete" triggers the automatic deletion of related records when one is deleted.
remove the source.

A la fermeture de la fenêtre « relations », celles ci sont enregistrées.

Remark:
in the 'Relations' screen, the fields that constitute or participate in a key
primary appears in bold characters;
the ACCESS notation on the links is the INVERSE of the cardinalities of the SCD
(1 --> and N ---> 1);
A relationship cannot be established between an ACCESS field of type 'AutoNumber'
and a corresponding digital field in another table only if the latter is
defined as a "Long Integer". Generally, the related fields
must have compatible definitions.
Example:

Modification of existing relationships

To access the already registered 'Relationships', proceed as for their creation.

Displaying all the relationships presents a complete graphical image on the screen of the
structure of relational tables.

A particular relationship can be selected by 'simple click' on the line that represents it,
then deleted by the 'Delete' key. A 'double click' on this line allows you to access the
framework for defining the relationship and modifying it.

12
 The input and processing of data from a table

The datasheet view is reserved for data entry in tables. One can create...
additional fields in datasheet mode but I do not recommend it. It is better to
define the data types and the properties of the fields with precision.

Switch to data sheet mode

On the ribbon, go to the Data tab using the following button:

Other actions in datasheet mode

A table can be opened in 'Data Sheet' to work on its content: adding

lines, modifications or deletions. One or more lines can be selected by
"simple click" or "held click" on the left column of the data sheet.
In an open table (and containing data), it is possible to switch from a
recording to the other using simple standard tools:

- by sequential sweeping, by clicking on to go to the first recording, on for

go to the last recording, on or on to jump to the previous recording or to
following;

by selective search, by clicking on the selection field and then on the icon .
Then follow the instructions in the "Search in the field: ..." window, enter the value
sought and then chosen and close the search window. ACCESS positions itself
on the corresponding record if it exists.

4. General method for creating an ACCESS database

To create a base, the following tasks must be performed in order:

1. Create the ACCESS tables (clearly specifying the description of each)

field and by choosing a primary key) ;

13
 Create the relationships between the tables as well as the integrity constraints
referentials;

3. Create a test set (data intended to test the database) or collect the data
real

4. Enter the data while respecting the order imposed by the constraints (a value
must be entered in the 'source' field before being in the 'linked' field).

Note: it is essential not to enter data (6°) in the tables before having defined
all constraints applying to the data, including integrity constraints
references. This will avoid inconsistencies that may later require deletion of
certain relationships, certain data, or even certain tables to be corrected.

14
 II. GRAPHIC REQUESTS ACCESS

1. The selection queries

A Select query is a type of database object that displays information in mode
Data sheet. The data of a query comes from one or more tables, queries, or
from a combination of the two. The tables or queries from which a query obtains its data
constitutes the "recording source".

Whether you create simple Select queries using an Assistant or from Design mode, the
the steps are essentially the same. Just choose the recording source to use, as well as
the fields to include in the request. You can also specify criteria to refine the
results.

Once the Select query is created, execute it to check that it works correctly. For this,
open it in Creation mode. You can then reuse it at any time, as a source
of registering a form, a statement, or another request, for example.

Access offers several types of queries to allow you to execute different operations.
For example, the Select query displays data, the Action query modifies the data in its source.
of data, or create a table. The Parameterized query asks you to enter criteria when you
you execute it.

We will identify the tools we will mainly be working with.

1. Allows you to change the display mode and switch from creation mode to sheet mode.
data (among others);
2. Allows changing the type of the request being created;
3. Allows executing the query (I will return to the difference between this tool and tool (1)
corresponding to the display in data sheet;
4. Allows adding a table to the current query;
Allows adding the line "Grouping" to generate statistical calculations in the
request;
6. Display the properties window of the selected query or field;
7. Open the expression generator window to facilitate the creation of calculated expressions.

By clicking on the dropdown part of buttons (1) and (2), Access opens submenus revealing
other tools.
In the 'Display' sub-menu, we find in order:

1. Display the creation mode, that is to say the structure of the request;
2. Display the result of the selection query in the form of a data sheet (like in Excel);
(Note that this button will display the relevant records if it is an Action request)
(Update, Delete, Add)
3. Display the SQL syntax generated by Access (very useful for retrieval and implementation
in the VBA code) ;
4. Display a Pivot Table related to the data retrieved by the query;
5. Display a Cross Chart related to the data retrieved by the query.

In the submenu 'Type of request', we find in order:

1. Select Query: This is the basic query used to extract records.

function of one or more criteria ;
2. Cross Analysis Query: Statistical query on two or more levels of grouping;
3. Table Creation Request: As its name indicates, it will allow transforming the extraction
obtained from data of a new table;
4. Update Request: Will allow updating all records of a table in
function of one or more criteria;
5. Add Request: Will allow to retrieve data from one table and add it to another
table of the current database or in another database;
6. Deletion Request: Will allow deleting all records from a table that meet
to the established criteria.

To create a new request

Click on the 'Create' tab in the ribbon (1)

Click on the 'Query Creation' tool (2)

We will identify the different elements we will work with in creating a

request.
Click on the Requests button.
Click on 'Create a query in design mode.'
Select the source tables.
Click on the Add button.
By double-clicking on the field names of the tables located in the upper part of
the screen; these appear in the structure of the request.

We can now move on to identifying our work screen:

16
 Table panel: contains all the source tables of the query;
2. Tables "source": contain the available fields (Note the primary key displayed in
grass);
3. The join: it is the relationship that exists between the two tables.
4. The structure of the query: groups all the fields necessary for extraction.
desired information;
5. The criteria zone: part of the screen where we can combine the different
selection values.

2. Add criteria to the request

To limit the number of records obtained, you can specify one or more criteria.

A query criterion is like a condition that you apply to a field. The criterion defines a
condition, based on field values, expressing what you want to add to the query, by
Example "show only the records where the City field is Paris". (See the Client table)

In the line Criteria of the City field, type Paris.

Add two more criteria to obtain more meaningful results. For example, you do not want
obtain only the records that contain the address and the contact's phone number.

2. In the Criteria line of the Address field, type Is Not Null AND <>"". Do the same for
the Phone field in the lineCriteria.

Note: The criterion Is Not Null AND <>"" is true when a known, non-empty value exists for
the field with a criterion. It is false when there is no known value (Null), or when the value is
empty (""). You can use this expression to check a field for a known value and not
to see.

3. Switch to Datasheet view to display the results.

What to do if you do not want to combine conditions using the AND operator? In other words,
how to specify two or more criteria by including only the records that meet one of them
the two conditions?

17
 3. Specify other criteria sets using the operator
OR
Suppose you want to display all records where the City is Paris and containing in
less one contact information (the address or phone number, for example). You want
combine the criteria using the OR operator, as follows:

To specify substitution criteria, complete the Criteria and Or lines in the creation grid.
All records that meet the criteria defined in the Criteria line or Or are part of the
results.

You will now modify the query by entering substitute criteria in the Criteria lines.
and you.

Switch back to Creation mode.

2. Remove the string Is Not Null AND <>"" from the Criteria line of the Phone field.
In the line Oudu field Phone, type Is Not Null AND <>"".
In the field LineOudu City, type Paris.
5. Switch to Datasheet view to display the results. All records
containing an address, a phone number, or both, and whose city is Paris are
displayed in the query results.

Note: To specify two or more sets of substitution criteria, fill in the lines below.
from the line. Each line represents an independent set of criteria.

4. Add calculations to the query

If you have designed your database well, the tables should not contain simple calculations.
For example, a person's date of birth, but not their age, could be recorded in a
table. Indeed, if you know the date of the day and the person's date of birth, you can
calculate one's age. Therefore, it is unnecessary to store this data in the table. It is better to create
a request that calculates and displays the relevant value. The calculations are updated with each execution of
the request to take into account any possible modifications of the underlying data.

In this exercise, you will modify the query 'Contacts in Paris' so that the date of birth and
the age of each individual should be displayed.

Open the query in Design view.

2. In the Clients table window, drag the DateNaissance field to the first
empty column of the creation grid. You can also double-click on the name of the
field to automatically add it to the first empty column.
3. In the next column, on the rowField, type the expression that will calculate the age.
of each contact. Type Age: DiffDate ("yyyy", [DateOfBirth], Date()).

Age is the name you use for the calculated field. If you do not enter a name, Access uses a
generic name, for example EXPR1. The string that follows the colon (:) is the expression
who provides the values of each record. The functionDiffDate calculates the difference between two
dates and returns the result in the specified format. The format displays the result in years and the elements.
[DateOfBirth] and Date() of the expression indicate the two date values. The DateFunction sends the
Today's date and [DateNaissance] refers to the DateNaissance field of the underlying table.

Note: The calculation used for Age in this example is an approximation that may be
slightly inaccurate, according to the current month.

Switch to Data Sheet mode. The result includes two additional fields: DateOfBirth and
Age.
 5. Synthesize the values of the request
It is easier to summarize the columns of a query in Office Access 2007 than in previous versions.
previous ones from Access. You can add, count, or calculate other aggregate values, and then them
display on a special line (called 'Totals line') which appears under the line marked with a
asterisk (*) in Datasheet mode.

You can use a different aggregate function for each column or choose not to.
synthesize a column.

In this exercise, you will modify the request 'Contacts in Paris' so that the Total line is
displayed.

Open the query in Datasheet view.

2. Under the Home tab, in the Records group, click on Totals.
3. Click on the Total row in the Contact column.

In the dropdown list, select None or Number. As the Contact Name column displays
textual values, functions such as Sum and Average are of no use, and by
consequently, are not available.

4. Select Number to count the number of contacts obtained.

The number 5 is displayed on the Total line.

5. In the Age field, select Average. Since the Age field returns a number, the
Sum Functions, Average, Count, Maximum, Minimum, Standard deviation and Variance are
available.

Access displays the average age in the Total line.

To clear the total of a column, click on the Total row under the relevant column and
selectNone from the dropdown list. To hide theTotal row, under theHome tab, in the
data type group and formatting, click on Totals.

Note: Using the Total line does not modify the underlying SQL instruction.

6. The parameterization of queries

The configuration of a selection query makes it easier to reuse: the value of

the selection criterion is then entered in a frame provided by ACCESS at each execution
from the request.

To set up a query, you need to enter (in brackets) a free wording in the area of
criteria of the considered field (for example = [Enter the item code]).

This label will be displayed by ACCESS when executing the query, to indicate what
value must be entered.

The settings can apply to combined conditions.

(for example >[PU mini] AND <[PU maxi]).

19
 Department
RéfClient Society Address City CodePostal PaysOuRégion Winter. Coordinates DateNaissance
Or Region
Baldwin Museum 7, place 03 80 Stéphane
1 Beaune Golden Coast 21203 France 03.12.45
of Science Beaubernard 203 130 Rogert
(201)
Blue Yonder 52 Avenue des Patrice Dupont-
2 Valenciennes North 59300 France 555- 21.03.59
Airways anemones Roc
0123
05 49 Gilles
3 Coho Winery 31 Sun Street Poitiers Vienna 86201 France 01.04.73
413 201 Fabrouque
Contoso 1 Dead-end of 01 85 04
4 Paris 75003 France Fabrice Grand 16.06.67
Pharmaceuticals valleys 12 35
01 15 56
5 Fourth Coffee Paris 75201 France Julien Dupré 09.08.71
10 63
Consolidated 23 rue de la Ille-et- 02 23 62 Christine
6 Rennes 35031 France 27.05.48
Messenger post Nasty 10 10 Huguette
Graphic Design 151 street of the 04 93 39
7 Cannes 6400 France Denise Barré 12.08.61
Institute beach 11 71
3 Avenue de la 05 56
8 Litware, Inc. Bordeaux Gironde 33080 France John Loved 01.09.75
victory 201 201
Francis
9 Tailspin Toys 22 Church Street Paris 74301 France 15.02.53
Parrèche
01 15 56
10 Woodgrove Bank 37 Main Street Paris 75056 France Martin Gorbot September 25, 1938
10 63
 III. ACCESS FORMS
The ACCESS form
A form is a database object that allows you to enter, modify, or display data.
from a table or a query. Forms also allow for controlling access to the data by
only displaying certain fields or rows of data. For example, it is possible that
some users may need to display only certain fields of a table made up of a multitude
of fields. By providing them with a form that contains only these fields, it will be easier for them
to use the database. You can also add buttons and other features to a
Form to automate frequently performed operations.

Forms can be compared to windows through which people see and

reaches your database. An effective form helps speed up the use of a database.
data insofar as people do not need to search for the items they have
need. An aesthetically pleasing form enhances not only the usability and efficiency of the database
of data, but it also helps to avoid the entry of incorrect data. Microsoft Office Access
2007 contains new tools designed to simplify form creation and offers new
types of forms and new features that enhance the usability of your database
data.

2. Create a form using the Form tool

The Form tool allows you to easily and quickly create a form. When you use this
Tool, all fields from the underlying data source are placed in the form.

The different methods of creating a form:

Use the Form tool to create a form

1. In the navigation pane, click on the table or query that contains the data you
wish to include in the form.
2. Under the Create tab, in the Forms group, click on Form.

Access creates the form and displays it in Page mode. This mode allows you to make changes to
the design of the form while having the displayed data. For example, you can adjust the
Size of text areas according to the data to be displayed, if necessary.

If Access determines that only one table has a one-to-many relationship with the table or the query that has
used to create the form, Access adds a datasheet to the form, based on the table
ou la requête associée. Par exemple, si vous créez un formulaire standard à partir de la table Employés, et
that a one-to-many relationship is defined between the Employees table and the Orders table, the sheet of
data displays all the records from the Orders table related to the record
Active employee. You can delete the data sheet from the form if you feel you do not need it.
need. If there are several tables linked by a one-to-many relationship with the table that was used to create the
form, Access does not add data sheets to the form.
 Create a form using the Form Assistant

To be more selective about the fields to include on your form, you can use
the Form Assistant instead of the various form creation tools mentioned earlier.
You can also define how the data is grouped and sorted, and you can
use fields from multiple tables or queries, provided that you have previously specified them
relations between tables and queries.

1. Under the Create tab, in the Forms group, click on More Forms, then on
Assistant Form .

2. Follow the instructions provided on the Form Assistant pages.

Note: If you want to include fields from multiple tables or queries in your form,
do not click on Next or on Finish after selecting the fields of the first table or
request on the first page of the Form Assistant. Instead, repeat the steps to
select a table or a query, then click on the additional fields you want
include in the form. Then click on Next or Finish to continue.

3. On the last page of the Assistant, click Finish.

3. Add controls to your form

Some controls are created automatically, such as the dependent control that is created when you
add a field from the field list to the form. Many other controls can be
created in Creation mode using the tools from the group Controls, under the tab Create.

Note: Several tools from the Controls group are only accessible when your form is
open in Creation mode. To switch to Creation mode, right-click on the name of the
form in the navigation pane, then click on Design Mode .

Determine the name of a tool

Hover the mouse pointer over the tool.

Access displays the name of the tool.

Use the Control Assistants

You can use Assistants to create command buttons, list boxes, sub-
forms, drop-down list areas and option groups. Under the Create tab, in the group
Checks, if the option Use Assistants control is not enabled, click on it to enable.

If you prefer to create controls without the help of the Assistant, click on the option Use Assistants

control to deactivate it.

22
 Create a control using the tools from the Controls group

1. Click on the tool corresponding to the type of control you want to add. For example,
To create a checkbox, click on the Checkbox tool.
2. Click in the form creation grid where you want to position the corner
upper left of the control. Click once to create a standard size control, or
Click on the tool and drag the mouse pointer in the form creation grid.
to create a custom size control.
3. If you have enabled the Use Assistants control option and the control you want
Pleasure is associated with an Assistant, who starts and guides you throughout the process of
configuration of the control.
4. If you do not position the control perfectly, you can move it by doing as
suit
1. Click on the control to select it.
2. Place the mouse pointer over the control so that it turns into an arrow.
cruciform
3. Click on the control and drag it to the desired location.

If you are using a Control Assistant, it may contain a procedure to link the control to
a field. Failing that, this procedure creates an independent control. If, by its type, the control is in
to display data (for example, a text box or a checkbox), you need to enter
the name of a field or an expression in the property area of the controlSource control for the
the latter can display data. To display the properties of a control, select the control
and press F4.

Display Yes/No values using checkboxes, option boxes and

toggle buttons

In Microsoft Office Access 2007, a Yes/No field stores only two values: Yes or No.
No. If you use a text box to display a Yes/No field, the value shows -1
for Yes and 0 for No. These values are not understandable for most users,
this is why Office Access 2007 provides them with check boxes, option boxes and
the toggle buttons to be used to display and enter Yes/No values. These controls provide
a graphical representation of the Yes/No value that is easy to use and read.

Choose the control to use

In most cases, the checkbox is the best control to represent a value.

Yes/No. It is a type of default control created to add a Yes/No field to a
form or a state. On the other hand, option boxes and toggle buttons are the most
frequently used in a group of options.

The following illustration shows these three controls and their way of representing the values Yes
and No. The Yes column displays the control as it appears when activated, the No column
display the control as it appears when deactivated.

23
 Understanding option groups

A checkbox, an option box, or a toggle button can be dependent,

independent or members of an options group. An options group displays a set
limited choice, you can only select one group option at a time. A group
The options consist of a group frame and a set of checkboxes, toggle buttons.
or option cases.

If a group of options is linked to a field, only the group frame is linked to it; the controls
Contents within the frame are excluded. Rather than defining the propertySource control for
for each control in the options group, set their propertyValue to a number
significant for the field to which the framework is linked. When you select a group of options,
Access defines the value of the field linked to the option group based on the value of the PropertyValue.
control of the selected option.

The value of a group of options can only be numeric. Access stores this number in the
underlying table. You can also set a group of options on an expression or the
make independent. You can use an independent group of options in a box of
custom dialogue to accept user input and execute the associated action.

Checkboxes are commonly used for Yes/No fields, the same goes for
the option boxes or toggle buttons used for option groups. However, it does not
This is just a simple recommendation, you can use the three controls for all these purposes.

Create a group of options

You can quickly create a group of options using the Options Group Assistant.
First of all, you need to know if you want to store the value of the option in a table. If
In this case, you need to have a field with a Numeric data type for this purpose.

1. Open the form in Design mode by right-clicking on it in the panel

navigation, then by clicking on Creation Mode.

2. In the Creation tab, in the Controls group, check that Use Control Wizards
is selected.

3. In the same group, click on Option Group .

4. Click on a location in the form to place the options group there.
5. Follow the Assistant's instructions. On the last page, click Finish.

Access places the group of options on the form.

2. Rename the options group

Although nothing obliges you to do so, it is recommended to give the options group a new
more significant name. This operation does not change the label displayed next to the group
of options, but it is easier to refer to them in the expressions or macros.

1. Make sure that the options group is selected by clicking on the group frame.
2. If the properties sheet is not already visible, press F4 to display it.
3. Type a new name in the property name field of the options group.

24
 3. Add a new option to an existing option group

You can add options to a group, based on your needs. You can either
create a new control in the group, or cut an existing control from another
location on the form to paste it in the group.

1. Open the form in Design mode by right-clicking on it in the panel.

navigation, then by clicking on Creation Mode.

2. Click on the option group frame to select it, then drag the handles of
sizing of this framework to insert the new option.

3. Perform one of the following operations:

Create a new group control

1. In the Controls group on the Design tab, click on the type of control to
add (Checkbox) Option case Toggle button ).
Move the pointer to position it within the options group frame.
frame color changes to indicate that the new control is integrated into the
group of options.
3. Click here to place the control in the group.

Move an existing control to the group

It is not enough to drag a control to a group of options to integrate it there. You must
cut it and paste it into the group so that it becomes an option.

1. Select the control you want to move to the options group.

2. Under the Home tab, in the Clipboard group, click Cut.

3. Select the option group by clicking on the frame that surrounds the group.
4. Under the Home tab, in the Clipboard group, click on Paste.

Access adds the control to the group.

You will likely need to move the control and its label to align them with the controls.
existing and their labels.

1. Select the control you just added, then press F4 to open the sheet of
associated properties.
2. Note the value of the property ControlValue of the control you have just added.
Depending on your needs, you can keep it, as Access suggests, or modify it.

Add a Text Box control to a form or a report

The text box is the standard control used to display and modify data in
forms and statements. Text areas can contain different types of data, which
can also be used to perform calculations. This article explains how to create and
use a text box and describe some of the most common properties of a text box
important.

25
 Add a dependent text area

A dependent text box displays data from a table or query field. In a

form, the use of a text area linked to a modifiable data source proves to be
particularly useful for entering or modifying the data of a field. The changes that
the changes you make in the text box are reflected in the underlying table.

A simple and quick way to create a linked text area is to drag a field from the
field list panel to the form or report. Access automatically creates a zone of
text associated with the following data type fields:

Text
Memo
Digital
Date/Heure
Currency
Hyperlink

By dragging fields associated with other types of data, you create different types
of controls. For example, if you drag a Yes/No field from the field list panel
When creating a form or a report, Access creates a checkbox. If you drag a field
OLE object to a form or a report, Access creates a dependent object frame, and if you
Drag an Attachment field to a form or report, Access creates an Attachment control.
attached.
1. Open the form or the report in Page mode or in Creation mode by clicking on it with the
right-click in the navigation panel and then click on the desired mode.
2. Under the Create tab, in the Tools group, click on Add Existing Fields.

3. In the Fields list pane, expand the table containing the field to bind to the text box.
4. Drag the field from the field list pane to the report or form.

You can also add a dependent text box to a form or state by

first adding an independent text box, then specifying the field to which you
wish to link it to its Source control property.

Add an independent text area

An independent text box is not attached to any table or query field. You
you can use a separate text box to display the results of a calculation or to
accept an input that you do not wish to store directly in a table. Adding a
Independent text zone proves to be simpler in Creation mode.

1. Open the form or report in Design mode by right-clicking on it in the

navigation pane, then by clicking on Create Mode in the Access status bar.
2. Under the Creation tab, in the Controls group, click on Text Box.

3. Place the cursor where you want the text box to appear in the form.
or the state, then click to insert the text box.

Note: Access places a label to the left of the text box. Therefore, plan for it.
place to the left of the pointer for this label, even if you can reposition it later
the label and the text area. You can remove the label by clicking on it and
pressing Delete.
26
There is another way to create an independent text area: first create a text area.
dependent text by dragging a field from the field list pane to the form or
the state, then delete the value of its Source control property. If you do this
operation in Creation mode, the text area will display the mention 'Independent' instead of the
field name. In Page mode, the text area will no longer display data and will be empty.

Add a calculated text box

1. Open the form or report in Design mode by right-clicking on it.

navigation panel, then by clicking on Creation Mode in the status bar of Access.
2. Under the Creation tab, in the Controls group, click on Text Box.

3. Place the pointer where you want to place the text box in the form
or the state, then click to insert the text box.
4. Perform one of the following operations:

Place the cursor in the text area, then type an expression that calculates a total.

Select the text area, press F4 to display the properties sheet, and then type.
the expression in the propertySource control area. To create the expression using the
Expression generator, click on in relation to the property zone Source control.

5. Save the form or the state, then switch to Form mode or Report mode to
check the results.

Remarks

If you need additional space to type an expression in the area of the

Source property control, press Shift+F2 to open the Zoom area.
If your form or report is based on a query, you may eventually place
the expression in the request rather than in a calculated control. This can improve the
performances and, if you plan to calculate totals for groups of records, it
it is simpler to use the name of a calculated field in an aggregation function.

Create a choice list using a list box or drop-down list.

When entering data into forms, it is often faster and easier to

select a value from a list rather than just remembering it. A selection list allows
also to ensure the accuracy of the value entered in a field. A list control allows
a connection to existing data or the display of fixed values entered during creation
of your control.

Characteristics of list control types

Office Access 2007 provides you with two list controls, the list box and the combo box.
drop-down list.

List area: The list area displays a list of values or choices. It

contains data lines and its size usually allows simultaneous display of
multiple lines. The lines can have one or more columns, which can
appear with or without title. If the list is too long to display all the lines in the
27
 control, Access displays a scrollbar. The user's choices are limited to
occurrences in the list area; any value entry is prohibited.

Dropdown area: The dropdown area presents a list of choices

in a more compact way; the list is hidden until you click on the arrow
dropdown. Thanks to the dropdown list area, you can also enter a value not
listed in the list. Thus, a dropdown area combines the features of a
text area and a list area.

Click on the arrow to display the dropdown list.

Click on an option in the dropdown list.

List boxes and drop-down list boxes can constitute dependent controls or
independent. These controls can search for values in a fixed list that you have
typed, or values in a table or a query. To create a list box or a box of
dependent drop-down list searching for values in a table or a query, check that
the form is based on a data source that includes a foreign key field or
a field Choice List. This is how you can create relationships to link the data
in the list box or the drop-down list to the data in the form.

Create a list box or a drop-down list using an Assistant

1. Right-click on the form in the navigation pane, then click on Mode

Creation.

Note: This procedure assumes that the form is linked to a table or a

request. Some steps are not applicable if the form is independent. For
determine if the form is linked to a table or a query, press F4 to display the
Property sheet. Under the Data tab of the property sheet, the property area
The data source displays the table or query to which the form is linked.

28
 2. Under the Creation tab, in the Controls group, check that Use Control Wizards
is selected.

3. Click on the List Zone tool or on the dropdown list tool .

4. In the form, click where you want to place the list box or the list box
scrolling.

Depending on your choice, the List Zone Assistant or the Dropdown List Zone Assistant
start.

5. When the Assistant asks you how you want to obtain the values for the control,
perform one of the following operations:
If you want to display the current data in a record source, click
I want the list box/drop-down list to search for values in
a table or query.
Si vous voulez afficher une liste fixe de valeurs qui changeront rarement, cliquez surJe
you will type the desired values.
If you want the control to perform a search rather than play the role of a
data entry tool, click on Find a record in my
form based on the value I selected in the list box/list box
rolling. This is how the system creates an independent control with a macro
incorporated which performs a search based on the value entered by the user.
6. Follow the instructions to specify how to display values.
7. If you selected one of the first two options on the first page of the Assistant,
This one asks you what you want Access to do when you select a
value. Perform one of the following operations:
To create an independent control, click on Save the value for use
Thus, Access retains the selected value as long as the user does not
modifie pas ou tant qu'il ne ferme pas le formulaire. Cependant, Access ne l'inscrit pas
on a table.
To create a dependent control, click on Store this value in this field
select the field you want to link the control to.
8. Click the Next button and type a label for the control. This label will be
displayed in view of the control.
9. Click Finish.

Create a list box or a drop-down list by adding a List field

of choice to a form

You can create a list box or a dependent drop-down list by adding a

field Choice list in a form.

1. Create a Choice List field in a table. The Choice List field you create can
contain a single or multiple values.

2. Perform one of the following operations:

Create a form based on a data source that contains the List field
choice. For example, select a table or a query in the navigation pane
containing the search field, then, in the Form Group of the Create tab,
click on Form .

Access automatically creates a dropdown list area for the Choice List field.

Add a list box or a drop-down list to a form:

29
 In Creation mode, open a form based on a data source
including the Choice List field.
2. If the field list pane does not appear, press ALT+F8 to display it.
3. Double-click on the Choice List field or drag it from the list panel.
champ towards the form. Access automatically creates a dropdown list.
dependent on the field.

Tip: To change a drop-down list area to a list area (or vice versa), click
Right-click on the control, select Replace in the context menu, then
click on the desired control type.

Customize a list box or a drop-down list

Avec le formulaire ouvert en mode Création, assurez-vous que la zone de liste ou la zone de liste
The dropdown is enabled, then press F4 to open the properties sheet for this control.
Perform one of the following operations:

Change the sorting order in a list box or a drop-down list If you have
used an Assistant to create the list box or drop-down list, Access sorts
automatically the rows that make up the list based on the first visible column. If
you want to specify a different sorting order or if you have set the propertyContent
control over a recorded request, use the following procedure:
Click on the Data tab, then click on the Content property area.
In the Data tab of the properties sheet, click on to open the
Query generator.
In the Trip line for the column to sort, specify your sorting order.

Link a column from a list box or a dropdown list

In the property area, Linked column of the list box or drop-down list area, specify
a name that corresponds to the position of the column in the list box or list area
scrolling. For example, type 1 to link the first column to the specified underlying field
in the propertySource control. Include the hidden columns when you count the
columns.

If you set the Column property to 0, Access saves the list index instead of the
value of one of the columns. This is particularly useful for storing a sequence of numbers
rather than the list of values.

Hide a column in a list box or drop-down list on a

form
In the property zone Column widths, type 0 for the columns to hide.

Let's take the example of a two-column dependent dropdown area, one titled
Supplier number with a width of 0.5 inches and the other titled SupplierName with a width of 2.
inches. The supplier number column is the first column of the list, thus the property
Column widths are set to 0.5"; 2". To hide the latter, set the property
Column widths 0"; 2". The supplier column No. constitutes the dependent column.
even if it is masked.

Note: In a drop-down list area, the first visible column is displayed in the
text box part of the drop-down list when the list does not appear. For example, the
the SupplierName column in the previous example is displayed because the Supplier No. column is
masked. If that were not the case, it would be displayed in place of the SupplierName column.
30
 Add column headers to a dropdown list in a form
In the property zone, under Column headers, click Yes to display the headers
of columns. The headers of the dropdown areas only appear if the list is
open.

If the drop-down list or the list box depends on a record source, Access
use the field names from the source record as column headers. If the area
the dropdown list or the list box depends on a list of fixed values, Access uses the
first data elements of the value list (propertyContent) as headers of
columns, knowing that n = the number defined in the property Number of columns.

Désactiver le remplissage en cours de frappe pour une zone de liste déroulante dans un
form
In the property area, expand Auto, click on No.

When the propertyAuto extend is set to No, you must select a value in
the list or type it out completely.

Define the width of the list area of a dropdown list in a

form
In the property area Width list, type the desired width using the unit of
current measure (defined in the Windows Control Panel). To use
another unit of measurement than the one defined by default, insert a measurement indicator.
For example, type 2 cm. Make sure to leave enough space for
display a scrollbar.

The width of the list box area of a drop-down list can be larger, but
never less than the text area part. The default setting (Auto) assigns to the area of
list the width of the text area of the dropdown list.

Define the maximum number of lines that a dropdown list can display in a
form
In the property zone, type a number in the displayed lines.

If the number of actual rows exceeds the number defined in the property Displayed rows,
A vertical scrollbar appears in the dropdown list area.

Limit the drop-down list entries to items from a part of a list area.
the dropdown list on a form
In the ownership area, limit to list, click on Yes.
If the first column displayed in a dropdown list is not the column
linked, Access limits entries to the list items even if the property limits to
the list is defined as No.
If the property Limit to list is set to No and you type a value that does not
The number does not fit in the list, if the dropdown area is dependent, the entry is stored.
in the underlying field but is not added to the list. To add new
for entries to the list, use the property on absence in the list and the absence event
in the list.

31
 4. Save your work
As soon as you save the structure of your form, you can also run the form.
often as you wish. If the structure does not change, the data is updated each time that
you display the form. If your needs change, you can modify the structure of the form,
or create a new form based on the original.

Save the structure of a form

1. Click on the Microsoft Office button , then click on Save or press
Ctrl+S.
Click on Save in the Quick Access Toolbar or press Ctrl+S.

2. If the form is untitled, type a name in the form Name box, then click on
OK.

Enregistrer la structure d'un formulaire sous un nouveau nom

1. Click on the Microsoft Office button , then on Save As.
In the Save As dialog box, type a name in the Save box.
form in, select Form from the list As, then click OK.

5. Create a form containing a sub-form

(one-to-many form)
When you use relational data (linked data stored in separate tables), it is
It is often necessary to display data from multiple tables or queries in the same form. By
For example, you may want to check a customer's data at the same time as the
details regarding the orders of this. The subform is a practical tool that allows to
perform this operation. Microsoft Office Access 2007 offers several methods to quickly create
sub-forms.

Presentation of sub-forms

A sub-form is a form inserted into another form. The base form is called
main form and the form it contains, sub-form. A form/sub-form combination.
form is sometimes referred to as hierarchical form or parent/child form.

Sub-forms are particularly useful when you want to display the data of
tables or queries that have a one-to-many relationship.. For example, you can create a form
containing a sub-form to display data from a Categories table and data from a table
Products. The data in the Categories table represent the 'one' side of the relationship, while the
data from the Products table represents the 'many' side of the relationship. In other words, each
category can have multiple products.

The main form displays the data from the 'one' side of the relationship.
The sub-form displays the data on the 'many' side of the relationship.
In this type of form, the main form and the sub-form are linked, so that the sub-
The form only displays the records associated with the active record of the main form.
Thus, when the main form displays the Beverages category, the sub-form displays
only the products of this category. If the form and the subform were not linked, the
The sub-form would present all products, not just those in the Beverages category.

The following table defines some terms related to subforms. Even though Access handles most
details related to the use of the procedures described in this article, it is useful to know what is happening
"behind the scenes" to make subsequent changes.

Term Definition
Control that allows you to embed a form within another form. You can
compare the control of a subform to a "view" of another object in your database
Control of
data, whether it is another form, a table, or a query. The control of
sub-form
The sub-form has properties that allow you to link the displayed data.
in the control of the data of the main form.
Property of the subform control that indicates the object being displayed in the
source control.
Simple display of data in rows and columns, in the manner of a
spreadsheet. The subform control displays a datasheet when
Sheet of
its source object is a table or a query, or even a form whose property
data
The default display is set to Data Sheet. In these cases, the sub-
The form is sometimes called a data sheet and not a sub-form.
Link Property
Property of the sub-form control that indicates the field(s) of the sub-
the fields
forms that are linked to the main form.
children
PropertyLink
Subform control property that indicates the field(s) of the form
the fields
main ones that are linked to the sub-form.
fathers

To optimize results, start by establishing all relationships. Access can thus create
automatically the links between the sub-forms and the main forms. To display or
modify the existing relationships between the tables in your database or to create new ones,
Under the Advanced Tools tab, in the Analyze group, click on Relationships.

When a subform control has a source object of a form, it contains the fields that you
place in this form, and it can be displayed as a standard form, continuous form or
Data sheet. Basing a subform on a form object allows you to add fields.
calculated in the sub-form, for example, [Quantity] * [Unit price].

You can also insert a data sheet into a form by creating a sub-control.
Form whose source object is a table or a query. A datasheet cannot be
customized like a sub-form; for example, you cannot add a calculated field to
a data sheet. However, you can create a subform based on a form that
contains a calculated field, then set the default Display property of this form to Sheet
data. You thus obtain a sub-form of the data sheet type that contains a field
calculated.

Create a form containing a subform using the Form Wizard

This procedure allows you to create a form/subform combination using the Wizard.
Form.

33
 1. Under the Create tab, in the Forms group, click on More Forms, then on
Assistant Form .

2. In the dropdown list Tables/Queries on the first page of the Assistant, select a
table or a query. In this example, to create a Categories form that
present the products of each category in a subform, select Table:
Categories (the 'one' side of the one-to-many relationship).

Note: It doesn't matter which table or query you choose first.

3. Double-click on the fields of this table or query that you want to include.
4. On the same page of the Assistant, select another table or query from the dropdown list.
Tables/Queries. For this example, select the Products table (the 'many' side of the
one-to-many relationship.
Double-click on the fields of this table or query that you want to include.
6. When you click Next, assuming you have correctly configured the relationships.
Before starting the Assistant, it asks you the following question: How would you like to...
Do you display your data? (that is, based on which table or query). In the context
from this example, to create the Categories form, click on Categories. The Assistant
displays a reduced image of the form. The area in the lower part of the form
represents the sub-form.
7. At the bottom of the Assistant's page, select the option Form with sub-form(s) and
then Next.
8. On the page What layout do you want for your subform? of the Assistant,
click on Table or Data sheet according to the desired layout for your sub-
form. The two styles display the data of the sub-form in the form of rows and
columns, but the tabular layout is more customizable. You can add some
color, graphs and other formatting elements, while a data sheet
is more compact, just like the Datasheet view of a table. When you have
make your selection, click on Next.
9. On the page What style do you want? of the Assistant, select a formatting style
for the form. If you chose Tabular on the previous page, the formatting style
choose is also applied to the sub-form. Once you have made your selection, click on
Following.
10. On the last page of the Assistant, enter the titles of the forms. Access names the forms.
based on the entered titles and create the sub-form label according to the assigned title
this one.

Also indicate on this page if you want to open the form in Form mode in order to
be able to display or modify the information or if you want to open it in Creation mode in order to
modify your design. Once you have made your selection, click Finish.

Access creates two forms: one for the main form including the subform control.
and one for the sub-form itself.

Create a subform by dragging a form onto another

Follow this procedure if you have two existing forms and want to use one of them.
as a sub-form of the other form.

In Creation mode, open the form that you want to use as the main form.
2. In the Controls group of the Create tab, if the Use Control Wizards tool is
selected, click on it to select.

3. Drag a form from the navigation pane to the main form.

34
Access adds a subform control to the main form and binds the control to the form that
you have dragged from the navigation pane. Access also tries to link the subform to
main form, depending on the relationships that have been defined in your database. If Access
is not able to link the sub-form to the main form, properties Link fields
children and linking the parent fields of the sub-form controls are empty, and you need to define them
manually by proceeding as follows:

1. Right-click on the main form in the navigation pane, then click

surMode Creation .
2. Click on the sub-form control to select it.
3. If the properties sheet is not already displayed, press F4 to display it.
4. In the Data tab of the properties sheet, click on in regard to the box of
propertiesLink the child fields.

The dialog box for the Link Editor of subform fields appears.

5. In the dropdown lists Parent fields and Child fields, select the fields to which
You want to link the forms. If you are unsure about which fields to choose, click on
Suggest for Access to try to determine the linkage fields. When you are done,
click on OK.

If you do not see the field you wish to use to link the forms, you may need to
modify the source of the registration of the child form or the child form so that the field of
the liaison is there. For example, if the form is based on a request, you must ensure that the
the linking field is present in the query results.

6. Save the main form and switch to Form mode to check that the form
works as expected.

Open a sub-form in a new window in Design mode

If you want to make changes to the structure of a subform while you are using
main form in Creation mode, you can open the sub-form in its own
window

1. Click on the sub-form to select it.

2. Under the Creation tab, in the Tools group, click on Sub-form in a new
window .

35
 IV. IMPORTER, ADD OR LINK TO
DATA FROM AN EXTERNAL SOURCE
If you have data recorded in another program, you can import it into a
new table or add them to a table in Access. You can also use in Access the
data that your collaborators keep in other programs by creating a link to this data.
Whatever the case may be, manipulating data from an external source resembles a
child's play in Access. Indeed, you can import data from a spreadsheet.
Excel, from a table of another Access database, from a Windows SharePoint Services 3.0 list or
many external sources. The procedure below is a good starting point, even if the
the process varies slightly depending on the source used.

1. In Access, External Data tab, Import group, click on the command

corresponding to the type of file to be imported.

For example, if you import data from an Excel spreadsheet, click on Excel. If you do not
If you cannot find the desired type of program, click on More.

Note: If the appropriate format type does not appear in the Import group, start the
program in which you created the data, then use it to save the data in a
current file format (delimited text file, for example) to be able to import this data into
Access.

2. In the External Data dialog box, click Browse to search for the file.
source data or type the full path of this file in the Filename field.
3. Click on your chosen option (all programs allow you to import some
data, some also allowing to add or link to them) specify the mode and
the storage location of the data in the active database. You can create
a table that uses the imported data, add the data to an existing table (with
certain programs) or create a linked table that contains a link to the data in the
source program.
4. If an Assistant starts, follow the instructions displayed on the following pages. On the last one
On the Assistant page, click Finish.

If you import objects or link tables from an Access database, the dialog box
Import objects or attach the tables displayed. Select the items of your choice, then
click on OK.

The exact process depends on the operation being performed: importing, adding, or linking data.

Access asks if you want to save the details of the import operation that you
come to execute. If you think you will re-execute this import later,
click on Save the import steps, then enter the details. You will then be able to
repeat the operation later easily by clicking on Imports
recorded in the group Import under the External Data tab. If you do not wish to
to not save the operation details, click on Close.
If you import a table, Access imports the data into a new table, then displays the table.
in the groupTables of the navigation pane. If you add data to a table, it is
added to the table. If you create a link to data, Access creates a linked table in the group
Navigation panel tables.
36