Access : Sélection d'une donnée par l'une ou l'autre de ses propriétés

Dans un formulaire Access, vous souhaitez pouvoir sélectionner une localité par le code postal OU la localité ? Un article par son code OU son libellé ? Une personne par son nom OU son prénom ? Ce tutoriel est fait pour vous. 10 commentaires Donner une note à l'article (5)

Article lu   fois.

L'auteur

Profil ProSite personnel

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

I. Introduction

Lors de la saisie de données, il arrive régulièrement qu'une valeur puisse être déterminée selon plusieurs propriétés qui la caractérisent. On peut être ainsi amené à choisir :
- une localité selon le code postal ou le nom de la localité ;
- un article selon son code ou son libellé ;
- un contact selon son nom ou son prénom ;
- un pays selon son code ISO ou son nom ;
- etc.

Image non disponible
Saisir la ville par son code postal ou son nom : une plus-value ergonomique


Access permet de gérer ces cas de figure très facilement, grâce à des outils de création d'interfaces permettant de mettre en place une ergonomie de saisie optimale. En complément, nous verrons comment récupérer une valeur d'une table liée pour l'afficher dans un formulaire.

Pour arriver à nos fins, nous devrons bien entendu nous concentrer d'abord sur la conception de la base de données, clé de voûte de l'application Access. Une base de données bien structurée nous permettra de gagner du temps dans le développement de l'interface, de profiter au mieux des outils Access, de réduire et faciliter les encodages tout en pérennisant les données.

Nous découvrirons aussi que la maîtrise des outils Access tels que requêtes ou listes déroulantes fait partie intégrante du bagage à posséder pour développer rapidement une application de gestion de données.

II. Objectifs et moyens

II-A. Objectifs

Notre objectif est de mettre en place une ergonomie permettant à l'utilisateur de saisir au sein d'un formulaire une donnée selon l'une ou l'autre de ses caractéristiques, sans utiliser de VBA.

Accessoirement, nous revisiterons :
- certaines propriétés de colonne d'une table ;
- les notions d'intégrité référentielle ;
- les relations entre tables ;
- le concept de clé primaire et clé externe ;
- la création de requêtes ;
- le paramétrage de listes déroulantes ;
- certaines différences entre « moteur de données » et « création d'interface ».

II-B. Moyens

Pour arriver à nos fins, nous mettrons en place une gestion de contacts avec nom, prénom, adresse, code postal, localité et pays.

En partant d'une nouvelle base de données vide, nous créerons les tables comprenant clé primaire, clé(s) externe(s) et index, ainsi qu'un formulaire pour gérer les données des contacts. Ce formulaire contiendra des listes déroulantes alimentées par des requêtes.

Ainsi, l'air de rien, nous balayerons quelques notions fondamentales d'Access en mode rappel.

Le but étant de maîtriser la sélection d'une donnée sur base de plusieurs listes déroulantes, nos exemples seront volontairement simples, mais non simplistes.
Le tutoriel ne constitue pas en lui-même une formation complète à la gestion de contacts avec Access, pas plus qu'une technique de gestion d'adresses postales s'appuyant sur des spécificités nationales.

II-C. Version utilisée pour ce tutoriel

La version utilisée pour ce tutoriel est Access 2010. Néanmoins, au-delà des différences dues aux interfaces spécifiques à une version, tout ce qui est mis en œuvre ici peut être utilisé avec toute version du logiciel depuis la version 97.

II-D. Niveau, prérequis

Dans la mesure où je ne fais que rappeler les concepts de base, une certaine maîtrise des notions élémentaires de conception de base de données et des outils Access permettra de profiter pleinement de ce tutoriel (notamment les notions d'intégrité référentielle et de jointure, ainsi que la manipulation de l'interface Access pour la création des tables, requêtes et formulaires).

Le débutant en Access risque d'être un peu perdu, mais pourra néanmoins tirer profit de ce tutoriel sans trop de problèmes.

III. Tables et propriétés

III-A. Conventions de nommage

Les conventions suivantes sont d'application :
- les tables sont préfixées par t_ ;
- elles reprennent le nom de l'entité au singulier ;
- hormis les clés externes, un champ est préfixé de l'entité au singulier ;
- la clé primaire est constituée du nom de l'entité suffixé par _PK ;
- la clé primaire est toujours un entier long incrémenté automatiquement (type NuméroAuto) ;
- par conséquent, les clés externes sont de type entier long ;
- les clés externes sont constituées du nom de l'entité externe suffixé par _FK ;
- les index sur plusieurs colonnes sont constitués des noms des entités, collés sans espaces.

Les suffixes _PK et _FK utilisés signifient respectivement Primary Key (clé primaire) et Foreign Key (clé étrangère, clé secondaire ou clé externe).

III-B. Pays

Dans cette table, outre la clé primaire, nous trouverons simplement la colonne du nom du pays (voir Fig.1).

Image non disponible
Fig. 1 : Les deux colonnes de la table des pays


Pour les besoins du tutoriel, nous la remplissons avec deux données (voir Fig. 2).

Image non disponible
Fig. 2 : Les noms de deux pays sont saisis dans la table

III-C. Localités

A minima, la localité sera définie par sa clé primaire, le code postal, le nom de la localité et la clé externe pointant vers le pays (voir Fig.3).

Image non disponible
Fig.3 : Les colonnes de la table des localités


Nous devons empêcher la saisie de doublons dans cette table. Effectivement, cela poserait problème de trouver deux fois 75000 Paris - France ou 4910 Theux - Belgique dans la table.

Pour réaliser cela, nous allons placer un index calculé sur plusieurs colonnes, en refusant les doublons. Les colonnes concernées sont CityZip et CityName. En mode création, nous cliquons sur le ruban Outils de table > Création > Afficher/Masquer > Index (voir Fig.4).

Image non disponible
Fig.4 : L'outil du ruban reprenant et permettant de gérer la liste des index


Dans la fenêtre qui s'ouvre, nous voyons la clé primaire, et nous pouvons saisir le nom et les colonnes de l'index que nous voulons ajouter. N'oubliez pas de basculer la propriété Unique à Oui, puisque nous voulons empêcher la saisie de doublons sur les paires CityZip-CityName (voir Fig.5).

Image non disponible
Fig.5 : la fenêtre d'index avec l'index multicolonne nommé ZipName

Pour créer un index multicolonne, on laisse le champ Nom de l'index vide, sauf sur la première colonne de l'index.

Pour faciliter la saisie du pays dans la table des localités, nous allons placer une liste déroulante sur la colonne Country_FK. Cela s'effectue assez simplement. Après avoir sélectionné la colonne concernée, il suffit d'ouvrir l'onglet Liste de choix, de choisir Zone de liste déroulante, puis de cliquer sur le bouton à droite de Contenu pour ouvrir le générateur de requête (voir Fig. 6).

Image non disponible
Fig. 6 : Ouverture du générateur de requête sur une colonne de table


Dans le générateur, il suffit d'ajouter la table des pays, de sélectionner les deux colonnes en veillant à mettre la colonne Country_PK en première position, puis de trier par ordre croissant des noms de pays (voir Fig. 7).

Bien que la colonne Country_PK ne sera pas affichée, nous devons la prendre dans la requête et, par mesure de facilité, la placer en première position. C'est elle qui constituera la colonne liée, c'est-à-dire celle qui contiendra la valeur qui sera effectivement stockée dans la colonne Country_FK de la table des localités, indépendamment de la valeur affichée dans le contrôle.

Image non disponible
Fig 7 : La requête SQL qui alimente la liste déroulante


Nous fermons l'interface de création en acceptant la mise à jour. Il reste quelques propriétés de la colonne à préciser, à savoir Nbre colonnes, Largeurs colonnes et Largeur liste (voir Fig. 8)

Image non disponible
Fig. 8 : Les propriétés de la liste déroulante


Remarquons la propriété Colonne liée qui vaut 1. Cela signifie que c'est la première colonne de la liste, c'est-à-dire Country_PK dans ce cas-ci, qui contiendra la valeur qui sera renvoyée dans la colonne Country_FK de l'enregistrement actif de la table des localités.

Remarquons également la propriété Largeurs colonnes qui vaut 0cm;4cm, ce qui signifie que la première colonne aura une largeur de 0 cm. Elle sera donc « affichée », mais avec une largeur nulle, et donc invisible.

Remarquons aussi que Limiter à liste, par défaut à Non, a basculé à Oui car la colonne liée est de largeur nulle. Comme on ne sait pas ajouter une valeur dans cette colonne puisqu'elle est masquée, Access nous interdit « officiellement » d'y saisir des données.

Dans l'interface de création de la requête, nous devons laisser la case Afficher pour la colonne Country_PK. Le terme est malheureux, mais cela veut dire que la colonne sera disponible dans la liste déroulante qui appellera la requête, alors même qu'elle ne sera pas visible.

Nous pouvons maintenant saisir les localités et leur attribuer un pays (voir Fig. 9).

Image non disponible
Fig. 9 : La saisie d'un pays dans la table des localités

Dans la table des localités, la valeur stockée dans la colonne est bien la clé primaire du pays attaché à la localité, bien que ce soit le nom du pays qui est affiché, grâce aux propriétés de la liste déroulante (voir Fig. 10).

Image non disponible
Fig. 10 : Les Country_PK apparaissent si l'on supprime la liste déroulante

Normalement, les données ne sont jamais saisies directement dans les tables. C'est au travers des formulaires que l'utilisateur visualisera, ajoutera, supprimera ou modifiera des données. Dans le cadre de ce tutoriel, il n'était pas nécessaire de passer par cette construction pour les tables t_Country et t_City.

III-D. Contacts

Nous définirons un contact uniquement par les colonnes nécessaires à la compréhension du tutoriel (voir Fig.11).

Image non disponible
Fig.11 : Les colonnes de la table des contacts

Afin d'éviter trop de modifications au sein du formulaire, notamment en ce qui concerne les étiquettes des contrôles de saisie, il est intéressant de compléter la légende des champs qui seront utilisés dans le formulaire. Ainsi, l'assistant de création du formulaire utilisera la légende comme étiquette du contrôle plutôt que le nom de la colonne (voir Fig. 12).

Image non disponible
Fig. 12 : La colonne ContactFirstName et sa légende Prénom

III-E. Intégrité référentielle et jointures par défaut

III-E-1. Intégrité référentielle

L'intégrité référentielle permet de garantir qu'une valeur mentionnée dans une clé externe est présente comme clé primaire dans la table liée. Ainsi, pour un contact, il faut interdire le référencement d'une localité qui n'existe pas dans la table des localités. De même, tant qu'une localité est référencée dans la table des contacts, il faut interdire sa suppression dans la table des localités.

Cette protection est placée sur le moteur de données, de sorte qu'elle s'applique à tout consommateur des données.

III-E-2. Jointures par défaut

Lors du placement de l'intégrité référentielle, Access place automatiquement une jointure par défaut. Cette jointure est inner join, ce qui implique que seules les lignes des deux tables ayant une paire FK-PK complète seront récupérées par la requête.

Il faut noter que, bien que placée grâce au même outil que l'intégrité référentielle, la jointure par défaut est placée dans le fichier Access, et son périmètre d'action est donc ledit fichier. Les clients externes de la base de données ne peuvent pas « en profiter ».

III-E-3. Placement de l'intégrité référentielle et de la jointure par défaut

Nous accédons à la fenêtre des relations via l'onglet du ruban Outils de base de données > Relations > Relations. À la première ouverture de la fenêtre, la liste des tables de la base apparaît. Si elle n'apparaît pas, nous y accédons via le ruban Outils de relation > Relations > Afficher la table. Nous pouvons aussi glisser-déplacer les tables au départ du volet des objets Access ou procéder par clic droit dans la fenêtre des liaisons (voir Fig. 13).

Image non disponible
Fig. 13 : City_FK est glissée sur City_PK


Pour placer l'intégrité référentielle, faisons glisser la clé externe d'une table vers la clé primaire de la table correspondante (voir Fig. 13). Ensuite, cochons la case Appliquer l'intégrité référentielle. Deux options de l'intégrité peuvent être précisées ici :

Mettre à jour en cascade, qui n'est pas utile ici puisque la clé primaire est de type Numéroauto ;

Supprimer en cascade, qui revient à supprimer les contacts liés à la localité qui sera supprimée. On imagine aisément la catastrophe que cela créerait dans les tables, puisque ce ne sont pas simplement les clés externes qui seront supprimées, mais tous les enregistrements de la table des contacts liés à la localité supprimée. Cette case ne sera cochée que dans certaines circonstances particulières (suppression d'une facture et de ses lignes de détail, par exemple, maintenance de tables…) (voir Fig. 14).

Image non disponible
Fig. 14 : Les cases permettant de préciser l'intégrité référentielle


Par défaut, Access place une jointure inner join. Toute nouvelle requête liant ces deux tables utilisera cette jointure par défaut, ce qui aura pour conséquence que, sans modification manuelle de la jointure, les contacts sans localité ne seront pas ramenés par la requête. Je vous conseille donc de modifier cette jointure par défaut. Pour cela, cliquez sur Type jointure… et choisissez la jointure qui permettra de ramener tous les contacts (voir Fig. 15).

Image non disponible
Fig. 15 : La modification de la jointure par défaut


Après avoir cliqué sur OK puis sur Créer, vous pouvez visualiser l'intégrité référentielle et la jointure par défaut.

L'intégrité est mentionnée par les sigles infini-1. La jointure est matérialisée par la ligne reliant les deux champs. La flèche côté City_PK signifie que la jointure ramènera tous les contacts.

Vous procéderez de la même façon pour placer l'intégrité et la jointure par défaut entre les tables t_City et t_Country, après quoi vous fermerez la fenêtre des relations en enregistrant les modifications (voir Fig. 16).

Image non disponible
Fig. 16 : L'intégrité référentielle et les jointures par défaut sont placées

IV. Formulaire de gestion du contact

IV-A. Création du formulaire


Comme dit plus haut, nous ne saisissons normalement pas les données directement dans les tables, mais dans les formulaires. Nous allons donc passer à la création du formulaire de gestion d'un contact.

Pour créer rapidement un formulaire basé sur la table des contacts, il suffit de sélectionner la table, puis de cliquer sur le ruban > Créer > Formulaires > Formulaire.

Access crée automatiquement le formulaire et l'affiche pour la saisie des données (voir Fig. 17).

Image non disponible
Fig. 17 : L'outil de création du formulaire s'appuyant sur la source sélectionnée t_Contact


Il sera affiché en mode Page ou mode Formulaire, selon les options de la base active. Vous basculerez en mode création par clic droit sur l'onglet de formulaire (voir Fig. 18). Observez que le formulaire a utilisé les légendes des colonnes (voir Fig. 19).

Image non disponible
Fig. 18 : Le basculement d'un mode à l'autre peut se faire par clic droit sur l'onglet du formulaire
Image non disponible
Fig. 19 : Le formulaire créé par l'assistant utilise les légendes plutôt que les noms de colonnes

IV-B. Ajout et adaptation de contrôles

IV-B-1. Mise en place des listes déroulantes

Puisque nous voulons deux listes déroulantes, l'une permettant la saisie de la localité par le code postal, l'autre par le nom, nous allons d'abord transformer le contrôle texte CityZip en liste déroulante, par clic droit (Fig. 20).

Image non disponible
Fig. 20 : Le contrôle texte est transformé en liste déroulante


Nous allons ensuite placer une deuxième liste déroulante, juste à côté de la première, et supprimer son étiquette, celle-ci ne nous servant à rien. Nous ajusterons également les largeurs et alignements des contrôles (voir Fig. 21).

Image non disponible
Fig. 21 : Les deux listes déroulantes côte à côte


Nous devons maintenant faire pointer la liste déroulante ajoutée, qui est pour l'instant un contrôle indépendant, vers le champ City_FK. Cela est réalisé par la feuille des propriétés ou par saisie directe dans la zone du champ (voir Fig. 22).

Image non disponible
Fig. 22 : La propriété Source Contrôle fera pointer le contrôle sur City_FK


Nous avons donc bien deux contrôles qui pointent vers une même colonne de la table, et la modification de la sélection de l'un entraînera automatiquement l'adaptation de l'autre (voir Fig. 23).

Image non disponible
Fig. 23 : Deux contrôles d'un formulaire peuvent pointer vers la même colonne

IV-B-2. Création des requêtes qui alimenteront les listes déroulantes

Nous devons alimenter les listes déroulantes pour que l'utilisateur puisse opérer son choix. Sur le fond, nous procédons de la même manière que pour la création de la liste déroulante des pays dans la table des localités, et nous devrons adapter les mêmes propriétés. Cependant, l'organisation de ces propriétés est différente en mode Édition de formulaire, certaines propriétés se trouvant sur l'onglet Format et d'autres étant placées sur l'onglet Données.

Il faut modifier la propriété Contenu de l'onglet Données dans la feuille de propriété du contrôle (voir Fig.24).

Image non disponible
Fig. 24 : Création de la requête à la volée pour le contrôle qui affichera le code postal


Nous allons ajouter la table t_City pour sélectionner les colonnes des tables dont nous avons besoin, et nous définirons les ordres de tri des enregistrements, c'est-à-dire d'abord sur le code postal, puis sur le nom de la localité. La colonne City_PK ne sera pas visible car sa largeur sera nulle. La première colonne visible sera donc CityZip, et c'est donc sur cette colonne que la sélection pourra être réalisée (voir Fig. 25).

Image non disponible
Fig. 25 : La requête qui alimentera la liste déroulante de sélection du code postal

Rappel : La colonne City_PK doit être prise dans la requête puisque c'est elle qui contiendra la valeur qui sera stockée dans la colonne City_FK de la table des contacts.

Bien entendu, nous procéderons de même pour la deuxième liste déroulante, mais en inversant l'ordre des colonnes présentées par la requête, pour permettre de sélectionner la localité sur base du nom (voir Fig. 26).

Image non disponible
Fig. 26 : La requête qui alimentera la liste déroulante de sélection du nom de la localité


Cette mise en place va permettre de sélectionner la ville par le code postal ou par la localité.

V. Utilisation du formulaire

L'utilisation du formulaire ne demande pas d'explications particulières. Il suffit de choisir la commune selon le code postal ou la localité pour l'attribuer à la fiche du contact. La sélection dans une des deux colonnes modifie automatiquement la valeur de l'autre, puisqu'elles pointent toutes les deux vers City_FK de la table des contacts (voir Fig. 27 à 30).

Image non disponible
Fig. 27 : La sélection de la localité par le code postal...
Image non disponible
Fig. 28 : ...modifie automatiquement la valeur de la liste déroulante affichant les noms.
Image non disponible
Fig. 29 : La sélection de la localité par le nom...
Image non disponible
Fig. 30 : ...modifie automatiquement la valeur de la liste déroulante affichant les codes postaux.


Grâce à cette approche, nous avons donc amélioré l'ergonomie de notre application, en permettant la saisie de données selon deux listes déroulantes. Mission accomplie !

VI. Affichage d'une donnée issue d'une table liée : le nom du pays

L'utilisateur souhaiterait pouvoir afficher le pays lié à la localité. Il n'est bien entendu pas question qu'il doive sélectionner le pays lui-même, ce qui pourrait amener à des incohérences dans les tables. En effet, un Theutois pourrait par inadvertance devenir Français alors qu'un Parisien pourrait devenir Belge à la suite d'une erreur d'encodage.

Lors de la création des tables, nous avons créé une colonne Country_FK dans la table t_City liée à la colonne Country_PK de la table des pays, de manière à ne devoir définir qu'une seule fois un pays pour une localité donnée.

Nous allons nous servir de cette structure pour afficher automatiquement le pays dans le formulaire du contact, lors de la saisie de la localité.

Il existe au moins trois façons de réaliser cela. J'explique ici la plus simple des trois, celle qui demandera le moins de manipulations dans le formulaire.

VI-A. Modification de la source de données du formulaire

Pour afficher le nom du pays lié à la localité du contact, il faut d'abord que la colonne CountryName soit présente dans la requête sous-jacente. En mode création, vous allez accéder aux propriétés du formulaire et afficher l'onglet Données, puis ouvrir le générateur de requête en cliquant sur à droite de la propriété Source (voir Fig. 31).

Image non disponible
Fig. 31 : La propriété Source permet d'accéder à la requête de formulaire


Ajoutez les tables t_City et t_Country. Ici, la table t_City, bien que nous n'en utiliserons pas les champs dans le formulaire, est indispensable à la requête, puisqu'elle sert de passerelle entre les tables t_Contact et t_Country. Il n'y a en effet aucun lien direct entre ces deux tables. Vous visualisez les jointures par défaut, qui vont permettre de ramener dans la requête les lignes comprenant toutes les données liées. Ajoutez le champ CountryName aux colonnes de la requête, par exemple en double-cliquant dessus (voir Fig. 32). Fermez la fenêtre en enregistrant les modifications.

Image non disponible
Fig. 32 : La requête est modifiée pour récupérer le nom du pays


Le formulaire dispose maintenant d'une nouvelle colonne, visible dans la liste des champs (voir Fig. 33).

Image non disponible
Fig. 33 : La requête est modifiée pour récupérer le nom du pays


Vous allez placer le champ dans le formulaire par glisser-déplacer, puis supprimer son étiquette (voir Fig. 34).

Image non disponible
Fig. 34 : Le contrôle pointant vers CountryName est placé dans le formulaire par glisser

Vous devez absolument verrouiller le contrôle pour en interdire toute modification. Ce champ pointe vers la colonne CountryName de la table t_Country. Une modification à ce niveau modifierait donc le nom du pays dans la table des pays. Toute localité, et par ricochet, tout contact lié seraient affectés par cette modification malencontreuse (voir Fig. 35).

Image non disponible
Fig. 35 : N'oubliez pas de verrouiller le contrôle !


Lors de la visualisation, la saisie ou la modification d'une fiche de contact, vous pouvez maintenant voir le nom du pays de résidence de ce contact (voir Fig. 36).

Image non disponible
Fig. 36 : La fiche expose le code postal, la localité et le pays

VII. Conclusions

Ce tutoriel a atteint son objectif. Vous pouvez maintenant sélectionner facilement une donnée selon plusieurs de ses paramètres.

J'ai également souhaité, dans ce tutoriel, vous montrer l'importance d'une bonne conception de base de données, et la facilité avec laquelle vous pouvez manipuler les outils d'Access pour mettre en place à peu de frais et peu d'efforts, une ergonomie conviviale tout en sécurisant vos données.

VIII. Remerciements

Pour l'idée du tuto, merci à Claude Leloup (ClaudeLeloup)

Pour la relecture technique, je remercie Philippe Jochmans (Philippe Jochmans) et Fabien (f-leb).

La relecture orthographique a été assurée par Claude Leloup (ClaudeLeloup) que je remercie vivement.

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

  

Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright © 2012 Pierre Fauconnier. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.