Gérer l'approvisionnement des stocks en VBA Access

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Approvisionnement des stocks d'un magasin

Cette petite application sert de préambule à celle que nous mettrons en oeuvre ensuite sur la facturation Access avec Gestion de stocks. Ici nous proposons de construire un formulaire capable de gérer l'approvisionnement du magasin afin de mettre à jour les stocks du catalogue dynamiquement.



La capture ci-dessus illustre le résultat à atteindre. Une console permet à l'administrateur de sélectionner une référence issue du catalogue, par le biais d'une liste déroulante. Ce dernier tape alors la quantité des produits entrés en magasin, à ajouter aux stocks existants. Cette quantité peut d'ailleurs être négative si des ajustements sont nécessaires dans la logistique.

Base de données des stocks
Pour les besoins de la mise en oeuvre, nous partons d'une petite base de données existante avec un formulaire préconçu.

• Télécharger la base de données approvisionnement-magasin.accdb en cliquant sur son lien,
• L'ouvrir dans Access et cliquer sur le bouton Activer le contenu si nécessaire,

Comme l'indique le volet des objets Access sur la gauche de l'interface, cette base de données se compose de quatre tables et deux formulaires. Parmi les tables, seules les tables Clients et Catalogue sont renseignées pour l'instant. Les deux autres serviront pour la facturation.



Comme l'illustre la capture ci-dessus, la table Catalogue est réduite à sa plus simple expression. Elle ne comporte volontairement pour l'instant que quelques références afin de simplifier la mise en pratique. Ce sont les quantités de cette table qui doivent être mises à jour par le biais d'un formulaire, afin de simuler l'approvisionnement du magasin. Ces quatre tables sont reliées entre elles pour mettre en oeuvre l'intégrité référentielle incontournable dans un gestionnaire de base de données. Ces relations seront utiles lors de la facturation Clients.

La console d'approvisionnement
La structure du formulaire Entrees_magasin a déjà été conçue. Mais ce dernier n'est pas encore fonctionnel. Il s'agit de l'objectif de cette formation.

• Depuis le volet sur la gauche, cliquer avec le bouton droit sur le formulaire Entrees_magasin,
• Dans le menu contextuel, choisir Mode Création,
• Dans le ruban Création pour le formulaire, cliquer sur le bouton Feuille de propriétés si cette dernière n'est pas visible,
• Sélectionner la liste déroulante sur le formulaire en conception,
• Activer l'onglet Données de sa feuille de propriétés,

Comme nous pouvons le voir, la liste déroulante est liée à la table Catalogue et plus précisément à sa première colonne, soit celle des codes article correspondant aux références. C'est la raison pour laquelle, en mode exécution, la liste propose chacune des références du catalogue.

• Enfoncer la touche F5 du clavier pour exécuter le formulaire,
• Tenter de saisir des informations dans les zones de texte : Désignation du produit, Qté actuelle et Qté après MAJ,

Rien ne se produit car ces zones sont verrouillées par leur feuille de propriétés. La désignation et la quantité en cours doivent être rapatriées par le code, en fonction du choix de la référence par le biais de la liste déroulante. La quantité après mise à jour doit se déduire de la quantité actuelle et de la quantité tapée par l'administrateur.

• Cliquer sur la flèche du bouton Affichage à gauche du ruban Accueil,
• Dans la liste, choisir Mode création,

Récupérer les données par Connexion
Au choix d'une référence dans la liste déroulante, soit sur événement, un code Visual Basic Access doit récupérer la désignation et la quantité correspondant à l'article sélectionné. Plus précisément, le code VBA Access doit ordonner l'exécution d'une requête sélection avec une clause Where sur la table Catalogue de la base de données. La formation sur l'authentification en VBA Access nous avait appris ce genre de requête en VBA, afin de récolter les informations d'identification de l'utilisateur.

• Sélectionner la liste déroulante sur le formulaire,
• Activer l'onglet Evénement de sa feuille de propriétés,
• Cliquer dans la zone vide de son événement Sur changement pour l'activer,
• Puis, à l'extrémité droite, cliquer sur le petit bouton à trois points qui apparaît,
• Dans la boîte de dialogue qui suit, choisir Générateur de code et valider par Ok,

Nous basculons ainsi dans l'éditeur de code VBA Access, entre les bornes de la procédure événementielle liste_ref_Change. Tout code saisi entre les bornes de cette procédure se déclenchera lorsque la référence de la liste déroulante changera, sur choix de l'utilisateur. Le gestionnaire d'événements est très riche en VBA Access, comme nous l'avait appris la formation sur l'interaction des objets de programmation.

Pour exécuter une requête sélection sur la base de données en fonction de la référence choisie, nous avons d'abord besoin de déclarer les variables nécessaires. Il nous faut une variable héritant des propriétés et méthodes d'un objet base de données pour pointer sur la base en cours. Puis il nous faut une variable capable de manipuler les enregistrements de cette base de données. Pour ce faire, nous devons préalablement ajouter la référence nécessaire à l'exploitation de ces objets.

• En haut de l'éditeur, dérouler le menu Outils et choisir Références dans la liste,
• Dans la boîte de dialogue qui suit, cocher la case de la référence à la classe Microsoft ActiveX Data Objects 6.1 Libray, puis valider par Ok,
• Dans les bornes de la procédure, ajouter les deux déclarations suivantes :

Dim ligne As Recordset: Dim base As Database

Nous déclarons donc la variable ligne en tant que Recordet, afin qu'elle puisse manipuler les enregistrements de la base de données par le biais de la variable base déclarée comme telle. Après les déclarations suivent les affectations.

• Après la déclaration des variables, ajouter les instructions suivantes :

Set base = Application.CurrentDb
Set ligne = base.OpenRecordset("SELECT Designation, Quantite FROM Catalogue WHERE Code_article='" & liste_ref.Value & "'", dbOpenDynaset)

Le mot clé Set est nécessaire pour initialiser et affecter les variables objets. De la sorte, elles héritent des propriétés et méthodes permettant de manipuler d'une part la base de données et d'autre les enregistrements de cette dernière. Ainsi la méthode CurrentDb de l'objet VBA Access Application permet de pointer sur la base de données en cours. L'objet base hérite ainsi de la méthode OpenRecordset qui permet d'affecter l'objet ligne, pour manipuler les enregistrements de la table, selon la requête SQL qui lui est passée en paramètre. Selon la syntaxe SQL de cette requête, nous indiquons explicitement les enregistrements des champs Designation et Quantite (SELECT Designation, Quantite) de la table Catalogue (FROM Catalogue) pour lesquels la référence est équivalente à celle choisie dans la liste déroulante (WHERE Code_article='" & liste_ref.Value & "'"). Comme liste_ref est un contrôle de formulaire, nous devons concaténer l'expression SQL (&) avec cette variable VBA. Le deuxième paramètre (dbOpenDynaset) permet de définir un accès dynamique aux enregistrements afin de récupérer leur contenu.

Avant de positionner le pointeur de lecture sur les enregistrements extraits par la requête, nous devons réinitialiser le contenu des zones de saisie qte_maj et qte_cours. En effet, si l'administrateur change de référence entre temps, les anciennes valeurs n'ont plus lieu d'être.

• A la suite du code, ajouter les deux affectations suivantes :

qte_maj.Value = 0: qte_cours.Value = 0

Puis il s'agit de récupérer la désignation et la quantité de la référence choisie dans la liste. Ces données sont respectivement présentes dans les champs Designation et Quantite pour l'enregistrement extrait par la requête grâce à la clause WHERE.

• Toujours à la suite du code, ajouter les instructions suivantes :

ligne.MoveFirst
Designation.Value = ligne.Fields("Designation").Value
qte_cours.Value = ligne.Fields("Quantite").Value

La méthode MoveFirst d'un objet Recordset permet de placer le pointeur de lecture sur le premier enregistrement, même si la requête SQL isole seulement l'un d'entre eux. En effet, la clause WHERE de la requête SQL est posée sur le champ de la clé primaire qui est unique par définition. La propriété Fields d'un objet Recordset permet de pointer sur le champ qui lui est passé en paramètre et d'en extraire le contenu, grâce à la propriété dérivée Value. Ainsi, nous affectons au contenu des deux TextBox concernés (Designation.Value et qte_cours.Value) respectivement les valeurs contenues dans ces champs pour l'enregistrement extrait.

Comme une quantité d'approvisionnement des stocks doit être saisie dans la foulée, il est opportun d'activer la zone de saisie dédiée, afin de faciliter le travail de l'administrateur. Et c'est la méthode SetFocus d'un contrôle de formulaire qui permet de le faire.

• Pour cela, ajouter la ligne de code suivante :

qte_maj.SetFocus

Bien que le code VBA ait déjà réalisé ce que nous souhaitions, il ne faut jamais oublier que toute connexion à une base de données doit être fermée, sous peine d'engorger irrémédiablement les ressources.

• A la suite, ajouter les instructions permettant de fermer les connexions et vider les objets :

ligne.Close
base.Close
Set ligne = Nothing
Set base = Nothing

La méthode Close des objets Recordset et Database permet de fermer les connexions. L'affectation à Nothing des objets de programmation permet de les vider de la mémoire afin de libérer proprement toutes les ressources exploitées.

• Enregistrer les modifications (CTRL + S) et basculer sur le formulaire (ALT + F11),
• Lancer l'exécution de ce dernier (Touche F5 du clavier),
• Choisir une des références dans la liste déroulante,

Comme nous pouvons le constater, le code VBA déclenché au changement de la liste déroulante, fonctionne parfaitement. La désignation et la quantité du produit sont en effet récupérées et affichées dans leur liste déroulante respective, grâce à la clause Where de la requête SQL déclenchée par le code Visual Basic Access. De même, la zone qte_maj est parfaitement activée puisque le focus lui est donné, si bien que l'utilisateur peut saisir la nouvelle quantité à ajouter dans la foulée. Le code complet de la récupération des informations de base de données est le suivant :

Private Sub liste_ref_Change()
Dim ligne As Recordset: Dim base As Database

Set base = Application.CurrentDb
Set ligne = base.OpenRecordset("SELECT Designation, Quantite FROM Catalogue WHERE Code_article='" & liste_ref.Value & "'",dbOpenDynaset)
qte_maj.Value = 0: qte_cours.Value = 0

ligne.MoveFirst
designation.Value = ligne.Fields("Designation").Value
qte_cours.Value = ligne.Fields("Quantite").Value

qte_maj.SetFocus

ligne.Close
base.Close
Set ligne = Nothing
Set base = Nothing
End Sub

Un détail reste à régler avant de concevoir le code VBA capable de réaliser la mise à jour des stocks. La dernière zone de texte qte_aps_maj doit afficher la quantité résultante qui sera effective en base données après mise à jour. Aucun code VBA n'est nécessaire puisqu'il s'agit de calculer la somme des quantités affichées par les deux autres TextBox.

• Cliquer sur la flèche du bouton Affichage et choisir Mode création dans la liste,
• Sélectionner le dernier TextBox (qte_aps_maj) sur le formulaire,
• Activer l'onglet Données de sa feuille de propriétés,
• Dans sa propriété Source de contrôle, saisir l'opération suivante :

=[qte_maj]+[qte_cours]

Dans une formule, les champs reconnus par leur nom doivent être désignés entre crochets.

• Exécuter le formulaire, choisir une référence et saisir une quantité à ajouter,

Dès lors que nous cliquons en dehors de la zone de texte, la quantité annoncée après mise à jour se calcule parfaitement sur la base des deux autres zones de saisie.

Mettre à jour les stocks sur événement
Il s'agit là encore de réaliser une requête SQL. Mais cette requête doit mettre à jour la quantité de l'article dans sa table, en fonction du renseignement précédemment calculé dans la zone qte_aps_maj. Cette requête ne doit donc pas être lue mais exécutée.

• Afficher le formulaire en mode création,
• Sélectionner le bouton du formulaire et activer l'onglet Evénement de sa feuille de propriétés,
• Cliquer sur le petit bouton de son événement Au clic,
• Dans la boîte de dialogue qui suit, choisir générateur de code et valider par Ok,

Nous sommes de retour dans l'éditeur de code Visual Basic Access, mais cette fois entre les bornes de la procédure événementielle : Valider_Click. Le code que nous allons créer se déclenchera donc au clic sur le bouton du formulaire. Son objectif est de mettre à jour les stocks en fonction des éléments préalablement fournis.

Une fois de plus, nous avons besoin d'une variable Database afin d'instancier la classe permettant de manipuler la base de données en cours. Cette fois en revanche, nous n'avons pas besoin d'une variable Recordset dans la mesure où il ne faut pas manipuler les enregistrements, mais exécuter une requête SQL de mise à jour sur la table Catalogue. Donc nous devons déclarer une variable de type chaîne de caractères afin de mémoriser la syntaxe de la requête à exécuter.

• Ajouter les deux déclarations suivantes dans les bornes de la procédure :

Dim base As Database: Dim requete As String

La suite de l'exécution du code dépend d'un double critère à vérifier. La mise à jour des stocks ne peut être entreprise que si d'une part une référence à bien été choisie et si d'autre part, une quantité à bien été saisie dans la zone de texte.

• En conséquence, ajouter l'instruction conditionnelle à la suite du code, comme ci-après :

If (liste_ref.Value <> "" And IsNumeric(qte_aps_maj.Value)) Then

End If

Notez l'emploi de la fonction VBA IsNumeric afin de vérifier que la quantité saisie est bien un nombre. Le mot clé And dans l'instruction conditionnelle If, permet de recouper les deux critères afin qu'ils soient vérifiés ensemble.

Si ces deux critères sont vérifiés alors (Then), nous pouvons initialiser les deux variables et exécuter la requête de mise à jour.

• Dans les bornes de l'instruction conditionnelle, ajouter les lignes de code suivantes :

Set base = Application.CurrentDb
requete = "Update Catalogue SET Quantite = " & qte_aps_maj.Value & " WHERE Code_article='" & liste_ref.Value & "'"
base.Execute requete

Comme nous en avons l'habitude désormais, la méthode CurrentDb de l'objet Application permet de pointer sur la base de données en cours. Nous affectons cet objet résultant à notre variable base. La variable requete quant à elle est affectée à la syntaxe de la requête mise à jour. Update est le mot clé SQL utilisé pour exécuter une action de mise à jour sur une table. C'est la raison pour laquelle nous renseignons ensuite le nom de la table impactée (Catalogue). Il s'agit alors de définir le ou les champs concernés par les modifications grâce au mot clé SET. Puis intervient l'opération sur le champ de la quantité (Quantite = " & qte_aps_maj.Value & "). Nous concaténons l'égalité avec la variable du contenu de la zone de saisie. Notez cette fois l'absence des simples cotes dans la concaténation. Le champ Quantite est en effet numérique et non textuel. Bien sûr cette modification ne doit pas intervenir pour tous les enregistrements de la table. C'est la raison pour laquelle suit la clause WHERE (WHERE Code_article='" & liste_ref.Value & "'"), permettant d'indiquer la condition selon laquelle cette mise à jour est réalisée. Ici en l'occurrence, le champ Quantite est modifié pour l'enregistrement dont la référence article correspond à celle sélectionnée dans la liste déroulante depuis le formulaire. Enfin et comme nous l'avait appris la formation VBA Access pour nettoyer une base de données, la méthode Execute d'un objet Database permet d'exécuter la requête qui lui est passée en paramètre.

Avant de tester ce code VBA de mise à jour, nous devons décharger les objets qui ne sont plus utilisés. Tant qu'à faire, nous allons en profiter pour afficher un message de confirmation à l'utilisateur.

• Toujours dans l'instruction If, ajouter les lignes de code suivantes :

MsgBox "Les stocks pour la référence :" & liste_ref.Value & ", ont correctement été mis à jour pour une quantité désormais égale à : " & qte_aps_maj.Value

base.Close
Set base = Nothing

La fonction VBA MsgBox désormais bien connue, permet d'afficher un message à l'écran. Il s'agit d'une concaténation de textes statiques et de variables issues du formulaire, pour confirmer à l'administrateur la quantité mise à jour et sur quelle valeur. Enfin, nous déchargeons de la mémoire la variable objet (Set base = Nothing), comme nous avons l'habitude de le faire désormais.

• Enregistrer les modifications, basculer sur le formulaire et exécuter ce dernier,
• Choisir une référence dans la liste déroulante,
• Saisir une quantité dans le TextBox situé sur sa droite,
• Puis, cliquer sur le bouton de validation,

Le message ordonné par la fonction MsgBox se déclenche semblant confirmer que la requête mise à jour s'est correctement exécutée.

• Valider ce message en cliquant sur Ok,
• Double cliquer sur la table Catalogue depuis le volet des objets Access pour l'ouvrir,

Comme le confirme la capture ci-dessus, la référence désignée depuis le formulaire, a correctement été mise à jour selon la quantité formulée depuis la zone de saisie. Notre petite console de gestion des stocks et d'approvisionnement fonctionne parfaitement. Le code complet de la procédure événementielle de mise à jour est le suivant :

Private Sub Valider_Click()
Dim base As Database: Dim requete As String

If (liste_ref.Value <> "" And IsNumeric(qte_aps_maj.Value)) Then
Set base = Application.CurrentDb
requete = "Update Catalogue SET Quantite = " & qte_aps_maj.Value & " WHERE Code_article='" & liste_ref.Value & "'"
base.Execute requete

MsgBox "Les stocks pour la référence : " & liste_ref.Value & ", ont correctement été mis à jour pour une quantité désormais égale à :" & qte_aps_maj.Value

base.Close
Set base = Nothing
End If

End Sub

Dans une prochaine étape, nous exploiterons ces solutions pour réaliser la mise à jour des stocks en temps réel, en fonction des commandes passées par les clients enregistrés dans la base de données. Il s'agira par la même occasion d'archiver ces commandes et leur détail dans les tables prévues à cet effet et reliées entre elles.