Créer un formulaire Access pour afficher la disponibilité du matériel de location

Denis Hulo

Objectif : créer un formulaire Access permettant d'afficher la disponibilité du matériel de location.

Niveau requis : confirmé.

Commentez cet article : Commentez

- Titre : Créer un formulaire Access pour afficher la disponibilité du matériel de location

- Auteur : Denis Hulo

- Parution : 1 septembre 2015

- Mise à jour : 30 octobre 2019

- Licence : Copyright ® 2015 Denis Hulo. 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.

Sommaire

- I. Introduction

- II. Rendu final du formulaire

- III. Procédure de génération des disponibilités du matériel

- IV. Tables

- IV-A. Tables de la base

- IV-A-1. T_Materiel

- IV-A-2. T_Location

- IV-A-3. T_DetailLocation

- IV-B. Tables temporaires

- IV-B-1. T_Calendrier

- IV-B-2. T_PeriodeDispo

- V. Requêtes

- V-A. R_DetailLocation

- V-B. R_MaterielLoueJour

- V-C. R_MaterielDispoJour

- V-C-1. Expression donnant le nombre d'unités disponibles

- V-D. R_MaterielDispoPeriode

- V-D-1. Expression donnant le nombre d'unités disponibles

- V-D-2. Champ calculé indiquant la date de début de la période

- V-D-3. Champ calculé indiquant la date de fin de la période

- VI. Formulaires

- VI-A. Sous-formulaire SF_DispoMateriel

- VI-B. Formulaire F_DispoMateriel

- VI-B-1. Les contrôles paramètres

- VI-B-2. Bouton de commande cmdActualiser

- VI-B-3. Procédure sur ouverture du formulaire

- VII. Module M_GenererDispo

- VII-A. Procédure MajCalendrier

- VII-B. Procédure MajPeriodesDispos

- VIII. Adaptation à un cas plus simple

- IX. Téléchargement

- X. Remerciements

I - Introduction

Dans une gestion de stock classique, on souhaite généralement déterminer, pour chaque article, sa quantité disponible en fonction des mouvements (entrées/sorties) effectués précédemment sur ce stock. Dans notre cas, nous souhaitons connaître les quantités de matériels disponibles dans le futur, en fonction des locations déjà planifiées.

L'objectif est d'expliquer comment créer un formulaire permettant d'afficher le nombre d'unités disponibles par matériel et par période.

Pour ce faire, après avoir donné un aperçu du résultat souhaité et avoir décrit la procédure de génération des disponibilités du matériel, on détaillera la structure des différents objets de l'application exemple, dans l'ordre de leur création :

- tables nécessaires ;

- requêtes ;

- formulaires ;

- module M_GenererDispo.

II - Rendu final du formulaire

Le formulaire affichant les disponibilités du matériel :

Aperçu du formulaire F_DispoMateriel

Il permet de visualiser, pour le matériel et la période choisis, les quantités successives disponibles.

Nous décrivons par la suite la procédure de génération des disponibilités du matériel, et les objets nécessaires pour aboutir à ce résultat.

III - Procédure de génération des disponibilités du matériel

On enregistre dans une table les jours compris dans la période choisie, puis on évalue la quantité de matériels loués pour chacun des jours. On retranche ensuite ces quantités de matériels au nombre d'unités en stock au départ pour obtenir le nombre d'unités disponibles par jour.
Enfin, on sauvegarde, pour chaque matériel et chaque période, ses quantités disponibles.

Résumé des différentes étapes

- Enregistrement dans la table T_Calendrier des jours compris dans la période choisie ;

- Évaluation dans la requête R_MaterielLoueJour de la quantité de matériels loués pour chacun des jours ;

- Retranchement dans la requête R_MaterielDispoJour des quantités de matériels loués au nombre d'unités en stock au départ ;

- Enregistrement dans la table T_PeriodeDispo, pour chaque matériel, de ses quantités disponibles par période ;

- Enfin, affichage du résultat dans le sous-formulaire.

L'affichage des disponibilités dépend de la période choisie sur le formulaire principal.

IV - Tables

IV-A - Tables de la base

IV-A-1 - T_Materiel

Cette table permet de sauvegarder les données relatives au matériel.

Nom du champ

Type de données

Description

IDMateriel

Entier long

Identifiant du matériel

RefMateriel

Texte

Référence du matériel

DesignationMateriel

Texte

Désignation du matériel

QteStock

Entier long

Quantité en stock

…

…

…

On conserve uniquement les champs utilisés dans notre exemple.

IV-A-2 - T_Location

Elle sert à sauvegarder les informations sur la location du matériel.

Nom du champ

Type de données

Description

IDLocation

Entier long

Identifiant de la location

IDClient

Entier long

Identifiant du client

dtDebutLoc

Date/Heure

Début de la location

dtFinLoc

Date/Heure

Fin de la location

IV-A-3 - T_DetailLocation

Elle permet d'enregistrer le détail de la location du matériel.

Nom du champ

Type de données

Description

IDDetailLocation

Entier long

Identifiant du détail de la location

IDLocation

Entier long

Identifiant de la location

IDMateriel

Entier long

Identifiant du matériel loué

QteMateriel

Entier long

Quantité louée

…

…

…

Si vous ne disposez que d'un seul article par référence, alors vous pouvez mettre 1 comme valeur par défaut pour le champ QteMateriel. Dans ce cas la quantité disponible par article ne peut prendre que les valeurs 0 ou 1.

IV-B - Tables temporaires

Pour sauvegarder les dates des jours du calendrier et les disponibilités du matériel, nous aurons besoin de quelques tables.

IV-B-1 - T_Calendrier

Elle contient la dates comprises dans la période choisie sur le formulaire.

Nom du champ

Type de données

Description

dtCalendrier

Date/heure

Date comprise dans la période choisie

On va utiliser une procédure VBA pour alimenter dynamiquement cette table.

IV-B-2 - T_PeriodeDispo

Elle sert à enregistrer les quantités de matériels disponibles par période.

Nom du champ

Type de données

Description

IDPeriode

Entier long

Numéro d'ordre de la période

IDArticle

Entier long

Identifiant de l'article

dtDebutPeriode

Date/Heure

Date de début de la période

dtFinPeriode

Date/Heure

Date de fin de la période

QteDispo

Entier long

Quantité de matériel disponible pour la période

Là encore, on va utiliser une procédure pour alimenter dynamiquement cette table.

V - Requêtes

Afin de pouvoir afficher pour chaque période le matériel disponible, on a besoin de réaliser quelques requêtes.

V-A - R_DetailLocation

Cette requête relie les tables T_Location, T_DetailLocation et T_Materiel, en sélectionnant les locations enregistrées dans la période choisie sur le formulaire.

Requête R_DetailLocation en mode création

V-B - R_MaterielLoueJour

Cette requête réalise le produit cartésien entre la table T_Calendrier et la requête R_DetailLocation, pour afficher les quantités de matériels loués par jour.

Requête R_MaterielLoueJour en mode création

On constate que la requête comprend, dans la partie encadrée, un champ calculé que nous allons détailler.

Champ calculé affichant la quantité de matériel louée

QteLouee: -[R_DetailLocation].[QteLouee]*([dtCalendrier] Entre [dtDebutLoc] Et [dtFinLoc])

Cette formule renvoie la quantité louée à la date du calendrier, si cette date est comprise dans la période de location (entre [dtDebutLoc] et [dtFinLoc]), sinon elle renvoie zéro.

Champs concernés :

- [R_DetailLocation].[QteLouee] : champ affichant la quantité de matériel loué sur cette période ;

- [dtCalendrier] : champ affichant la date du jour du calendrier ;

- [dtDebutLoc] : champ indiquant la date de début de la location ;

- [dtFinLoc] : champ indiquant la date de la fin de la location.

V-C - R_MaterielDispoJour

Cette requête « regroupement » basée sur la requête précédente affiche les quantités de matériels disponibles par matériel et par jour.

Requête R_MaterielDispoJour en mode création

On constate que la requête comprend dans la partie expression un champ calculé que nous allons détailler.

V-C-1 - Expression donnant le nombre d'unités disponibles

Nous avons utilisé dans la requête les fonctions de regroupement Premier et Somme :

Formule donnant la quantité de matériel disponible

QteDispo: Premier([QteStock])-Somme([QteLouee])

Pour obtenir la quantité disponible à une date donnée, on retranche la somme des quantités louées pour ce jour à la quantité en stock au départ.

Champs concernés :

- [QteStock] : champ indiquant la quantité de l'article en stock au départ ;

- [QteLouee] : champ indiquant la quantité de matériel loué.

V-D - R_MaterielDispoPeriode

Cette requête reliant les tables T_PeriodeDispo et T_Materiel affiche les quantités de matériels disponibles par période.

Requête finale en mode création

On constate que la requête comprend dans la partie encadrée des champs calculés que nous allons détailler plus loin.

V-D-1 - Expression donnant le nombre d'unités disponibles

Formule donnant la quantité de matériel disponible

QteDispo: Nz([Qte];[QteStock])

Champs concernés :

- [QteStock] : champ indiquant la quantité de l'article en stock au départ ;

- [QteLouee] : champ indiquant la quantité de matériel loué.

La fonction Nz renvoie la valeur du champ [QteStock] si le champ [QteLouee] contient une valeur nulle.

V-D-2 - Champ calculé indiquant la date de début de la période

La formule donnant la date de début de la période

dtDebutPeriode: Nz(T_PeriodeDispo.[dtDebutPeriode];[txtDateMini])

Champs concernés :

- T_PeriodeDispo.[dtDebutPeriode] : champ indiquant la date de début de la période ;

- [txtDateMini] : paramètre affichant la date de début de la recherche.

La fonction Nz renvoie la valeur du paramètre [txtDateMini] si le champ date dtDebutLoc contient une valeur nulle.

V-D-3 - Champ calculé indiquant la date de fin de la période

Formule donnant le début de la période

dtFinPeriode: Nz(T_PeriodeDispo.[dtFinPeriode];[txtDateMaxi])

Champs concernés :

- T_PeriodeDispo.[dtFinPeriode] : champ indiquant la fin de la période ;

- [txtDateMaxi] : paramètre donnant la date de fin de la recherche.

La fonction Nz renvoie la valeur du paramètre [txtDateMaxi] si le champ date T_PeriodeDispo.[dtFinPeriode] contient une valeur nulle.

VI - Formulaires

VI-A - Sous-formulaire SF_DispoMateriel

Il est lié à la requête R_MaterielDispoPeriode et ces contrôles sont donc liés aux champs de cette requête.

VI-B - Formulaire F_DispoMateriel

Nous allons décrire la structure et le code du formulaire principal qui affiche la disponibilité du matériel par période.

Formulaire F_DispoMateriel en mode création

En plus du sous-formulaire, il comporte donc plusieurs contrôles.

VI-B-1 - Les contrôles paramètres

Sur la partie haute du formulaire se trouvent les contrôles paramètres pour la recherche des disponibilités :

- [txtDateMini] : zone de texte indiquant le début de la période de recherche ;

- [txtDateMaxi] : zone de texte affichant la fin de la période ;

- [cmbMateriel] : zone de liste déroulante pour choisir le matériel concerné.

VI-B-2 - Bouton de commande cmdActualiser

Il permet de générer les disponibilités en fonction de la période choisie, et de mettre à jour le sous-formulaire :

Procédure sur Clic

Private Sub CmdActualiser_Click()
' génère les dates comprises dans la période choisie
MajPeriodesDispos CDate(Me.txtDateMini), CDate(Me.txtDateMaxi)
Me.SF_Disponibilites.Requery ' actualise le sous-formulaire
End Sub

VI-B-3 - Procédure sur ouverture du formulaire

Elle permet de générer les périodes de disponibilités sur les quinze prochains jours, puis de mettre à jour le sous-formulaire :

Procédure sur Clic

Private Sub Form_Open(Cancel As Integer)
' génère les dates comprises dans la période choisie
MajPeriodesDispos CDate(Me.txtDateMini), CDate(Me.txtDateMaxi)
Me.SF_Disponibilites.Requery ' actualise le sous-formulaire
End Sub

VII - Module M_GenererDispo

VII-A - Procédure MajCalendrier

Pour mettre à jour la source de données du sous-formulaire, on a besoin d'écrire une procédure qui ajoute dans la table T_Calendrier les dates comprises dans la période choisie sur le formulaire :

Procédure MajCalendrier

Private Sub MajCalendrier(dtDebut As Date, dtFin As Date)
' Met à jour le calendrier en fonction des dates de début et de fin passées en argument
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim dt As Date

' référence à la base courante
Set db = CurrentDb

' Suppression des données contenues dans la table T_Calendrier
db.Execute "delete * from T_Calendrier;", dbFailOnError

'Ouverture du recordset basé sur la table T_Calendrier
Set rs = db.OpenRecordset("T_Calendrier")

dt = dtDebut ' date de début de la période de génération des dates

'Parcours de la période définie par les paramètres de début et de fin passés en argument
Do While dt <= dtFin

rs.AddNew ' ajout de la date dans la table T_Calendrier
rs!dtCalendrier = dt
rs.Update

dt = dt + 1 ' prochain jour

Loop
' Libération des variables objet
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

VII-B - Procédure MajPeriodesDispos

Elle permet d'ajouter dans la table T_PeriodeDispo les périodes successives de disponibilité pour chaque article :

Procédure MajPeriodesDispos

Public Sub MajPeriodesDispos(dtDebut As Date, dtFin As Date)
' Enregistre les périodes et leur quantité en fonction des arguments de la procédure dtdebut et dtfin.
Dim db As DAO.Database
Dim qr1 As DAO.QueryDef
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim dt1 As Date, dt2 As Date
Dim idA As Long, qte As Long

' Met à jour le calendrier en fonction des dates de début et de fin passées en argument
MajCalendrier dtDebut, dtFin

Set db = CurrentDb

' Référence à la requête R_MaterielDispoJour
Set qr1 = db.QueryDefs("R_MaterielDispoJour")

' Paramétrage de la requête
qr1.Parameters("[DateMini]").Value = dtDebut
qr1.Parameters("[DateMaxi]").Value = dtFin

' Ouverture des recordset de la requête R_MaterielsDisposJour et de la table T_PeriodeDispo
Set rs1 = qr1.OpenRecordset()
Set rs2 = db.OpenRecordset("T_PeriodeDispo")

' Suppression des anciennes périodes enregistrées
db.Execute "delete * from T_PeriodeDispo;", dbFailOnError

' Parcours des lignes de la requête R_MaterielsDisposJour
Do Until rs1.EOF

idA = rs1!IDMateriel
dt1 = rs1!dtCalendrier: dt2 = rs1!dtCalendrier
qte = Nz(rs1!qteDispo, 0)

' Tant que l'id. du Materiel et la quantité est la même, mais aussi que les jours se suivent
Do While (rs1!IDMateriel = idA) And (dt2 = rs1!dtCalendrier) And (qte = Nz(rs1!qteDispo, 0))
dt2 = dt2 + 1
rs1.MoveNext
If rs1.EOF Then Exit Do ' sortie de la boucle si on est à la fin du recordset
Loop

' Création d'un enregistrement dans la table T_PeriodeDispo, pour le matériel et la période identifiés
rs2.AddNew
rs2!IDMateriel = idA
rs2!dtDebutPeriode = dt1
rs2!dtFinPeriode = (dt2 - 1)
rs2!qteDispo = qte
rs2.Update

Loop

' Libération des variables objet

Set qr1 = Nothing

rs1.Close
Set rs1 = Nothing

rs2.Close
Set rs2 = Nothing

Set db = Nothing
End Sub

VIII - Adaptation à un cas plus simple

Si vous souhaitez gérer les locations à l'unité, comme pour louer des chambres d'hôtel, il suffit de mettre comme valeur par défaut 1 pour la quantité en stock et la quantité louée de matériel. Dans ce cas, si le matériel est disponible pour une période donnée, la colonne quantité indiquera 1, sinon elle affichera 0.

IX - Téléchargement

La base exemple est au format Access accdb.

Elle a été réalisée en collaboration avec grpoirier.

Pour faciliter les tests et assurer sa portabilité, la base exemple n'utilise pas de composants externes à Access (ActiveX ou autres).

X - Remerciements

Un grand merci à toute l'équipe de Dvp et plus particulièrement à :

Pour m'avoir fourni l'idée de départ :
. grpoirier

Pour ses remarques et conseils avisés :
. claudeleloup

Pour leur relecture :
. milkoseck
. claudeleloup