Actualiser les stocks et créer la facture PDF

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


Sujets que vous pourriez aussi aimer :


Valider la facture et actualiser les stocks

Nous atteignons le dernier volet sur la conception de l'application de facturation avec gestion des stocks en VBA Word.



Grâce aux travaux précédents, notre interface sait déjà faire beaucoup de choses. Elle questionne la base de données Access à l'ouverture du document pour remplir une liste déroulante des références à acheter. Au choix de l'une d'entre elles, les champs du formulaire se remplissent automatiquement des informations attachées. Au clic sur le bouton Ajouter, les données sont insérées dans la facture Word en arrière-plan. C'est ainsi qu'elle se construit au fur et à mesure. Dans le même temps, une table TempProduits est renseignée dans la base de données Access. Elle mémorise les quantités achetées pour chaque produit ajouté à la facture. Si cette dernière est validée en cliquant sur le bouton Créer, ce sont ses informations qui doivent être utilisées pour valider la facture et mettre à jour les stocks dans la table Produits. Dans l'enchaînement, nous devons produire la facture au format PDF, à archiver dans le sous dossier local archives. Voilà tout l'enjeu de ce dernier volet.

Document VBA Word à télécharger
Pour finaliser cette solution, nous devons récupérer les travaux au dernier indice de développement.

• Télécharger le fichier compressé actualiser-stocks-vba.rar en cliquant sur ce lien,
• Puis, le décompresser dans le dossier de votre choix,

Comme vous pouvez le voir, la décompression livre le document Word pour la facturation, la base de données Access des articles à acheter mais aussi un sous dossier nommé archives. C'est lui qui doit accueillir toutes les factures validées et archivées au format PDF.

• Double cliquer sur le document Word (actualiser-stocks-vba.docm) pour l'ouvrir,
• Cliquer sur les boutons des bandeaux de sécurité pour libérer les ressources,

Dès lors et comme vous le constatez, le formulaire de facturation apparaît. Les références issues de la base de données sont proposées dans la liste déroulante. Si vous choisissez l'une d'entre elles, la liaison est établie avec la source et toutes les informations correspondantes sont rapatriées dans les champs du UserForm. Et si vous cliquez sur le bouton Ajouter après avoir renseigné une quantité, la nouvelle ligne d'achat est ajoutée à la facture du document Word, en arrière-plan.

• Cliquer sur la croix en haut à droite du formulaire pour le fermer,

La procédure de validation
Pour poursuivre et terminer les travaux largement ébauchés sur cet outil de facturation, nous devons commencer par créer la procédure de code attachée au bouton Créer du formulaire.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur de VBA Word,
• Dans l'explorateur de projet sur la gauche, double cliquer sur l'élément Facturation,

Nous affichons ainsi le UserForm en mode conception.

• Sur ce Formulaire, double cliquer sur le bouton Créer,

De cette manière, nous basculons dans la feuille de code entre les bornes de la procédure événementielle Creer_Click :

Private Sub Creer_Click()

End Sub

Son code se déclenchera au clic sur ce bouton.

Les variables de base de données
Maintenant et puisqu'il est question d'accéder à la base de données pour mettre à jour les stocks dans la table Produits en fonction des quantités achetées pour les références inscrites dans la table TempProduits, nous devons déclarer des variables permettant de piloter cette base et ses tables.

• Dans les bornes de la procédure, ajouter les déclarations de variables suivantes :

Dim cheminBd As String: Dim requete As String
Dim enr As Recordset: Dim base As Database

Nous déclarons tout d'abord deux variables de type texte (As String). La première (cheminBd) doit mémoriser le chemin d'accès à la base de données pour pouvoir y accéder. La seconde (requete) doit mémoriser la syntaxe SQL de mise à jour (Update) à exécuter sur cette base de données.

Ensuite, nous déclarons deux variables de base de données. Et nous rappelons que ces deux déclarations sont rendues possibles par l'ajout de la référence Microsoft Office 16.0 Access Database au projet. Nous nous en étions chargés dans les volets précédents. La première variable (enr) est typée comme un objet de type Recordset. Son rôle est en effet de manipuler les enregistrements de tables. Et pour cela, nous déclarons un objet de type Database (base) pour précisément pouvoir piloter la base de données hébergeant ces enregistrements.

Maintenant, nous devons commencer par affecter ces variables.

• A la suite du code de la procédure, ajouter les trois affectations suivantes :

cheminBd = ThisDocument.Path & "\articles.accdb"
Set base = DBEngine.OpenDatabase(cheminBd)
Set enr = base.OpenRecordset("SELECT * FROM TempProduits", dbOpenDynaset)

Nous le savons, c'est la propriété Path de l'objet ThisDocument qui renseigne sur le chemin d'accès au document en cours. Nous lui concaténons le nom de la base de données préfixé d'un antislash pour bien entrer dans le dossier. En effet, la base de données Access est située dans le même emplacement que le document Word.

Ensuite, nous instancions la classe permettant de prendre possession d'une base de données grâce à la méthode OpenDatabase de l'objet DBEngine en lui passant le chemin d'accès en paramètre. Dès lors, l'objet base ainsi affecté, hérite des propriétés et méthodes permettant de piloter cette base. C'est ainsi que nous exploitons sa méthode OpenRecordset pour initialiser l'objet enr destiné à manipuler les enregistrements de table. A cette méthode, nous lui passons une requête SQL classique en premier argument. En l'absence de clause WHERE, elle est destinée à récupérer toutes les informations (*) contenues dans cette table aux informations temporaires. En effet, nous devons parcourir toutes les références stockées pour connaître les quantités achetées. En second argument, la valeur dbOpenDynaset définit un accès dynamique pour pouvoir accéder à ces enregistrements par le code.

Parcourir les enregistrements de table
Pour passer en revue les enregistrements de cette table tour à tour, nous avons besoin d'une boucle particulière. Il s'agit d'une boucle Do Loop. Son rôle est de poursuivre son traitement tant que la dernière ligne de la table n'est pas atteinte. On l'utilise aussi dans le cas des fichiers à accès séquentiels pour parcourir toutes les lignes de texte une à une. Mais avant cela, nous devons placer le pointeur de lecture sur le premier enregistrement grâce à la méthode MoveFirst de l'objet enr.

• A la suite du code de la procédure, ajouter les instructions VBA suivantes :

enr.MoveFirst
Do

Loop Until enr.EOF

La propriété booléenne EOF de l'objet enr est intéressante. Elle signifie End Of File (Fin du fichier). Avec cette syntaxe, la boucle poursuit son traitement tant que la fin du fichier n'est pas atteinte, en d'autres termes, tant que le dernier enregistrement n'est pas atteint.

Dans cette boucle, il s'agira de ne pas oublier de déplacer le pointeur de lecture sur l'enregistrement suivant après chaque traitement. Le cas échéant, elle tournera sur elle-même sans ne jamais s'arrêter puisque figée sur le premier enregistrement.

Mettre à jour les stocks
Nous arrivons à l'étape cruciale. A chaque passage, nous devons prélever la quantité achetée pour la référence en cours de lecture. Grâce à cette référence, nous devons exercer une clause WHERE sur la table Produits. C'est ainsi que nous isolerons la ligne de l'article à actualiser dans une requête Update.

• Dans les bornes de la boucle, ajouter les instructions VBA suivantes :

requete = "UPDATE Produits SET produit_stock=produit_stock - " & enr.Fields("produit_stock") & " WHERE produit_ref='" & enr.Fields("produit_ref") & "'"
base.Execute requete
enr.MoveNext

Nous construisons donc la syntaxe SQL d'une requête Update classique sur la table Produits. Sur le champ produit_stock, nous enlevons la quantité achetée à la quantité existante (produit_stock - " & enr.Fields("produit_stock")) mais pas pour tous les enregistrements. C'est en effet une clause WHERE qui permet de réaliser cette mise à jour uniquement pour l'enregistrement de la référence en cours de lecture dans la table TempProduits. Attention de bien respecter l'alternance des simples et doubles côtes (produit_ref='" & enr.Fields("produit_ref") & "'") dans cette clause. Le champ produit_ref est textuel. Donc sa valeur doit être encadrée.

Ensuite, nous exécutons cette requête grâce à la méthode Execute de l'objet base en lui passant la syntaxe mémorisée dans la variable requete. Dès lors et comme nous l'annoncions, nous n'oublions pas de déplacer le pointeur de lecture sur l'enregistrement suivant de la table TempProduits. C'est ainsi au prochain passage que la requête Update adaptera sa clause WHERE sur la nouvelle référence pour actualiser le stock en fonction de la nouvelle quantité.

Une fois le traitement de la boucle terminé et comme il est de coutume, nous ne devons pas oublier de fermer les objets de base de données et de les détruire. C'est ainsi que nous libèrerons la mémoire pour un codage propre et optimisé.

• Pour cela, ajouter les instructions VBA suivantes après la boucle :

enr.Close
base.Close
Set enr = Nothing
Set base = Nothing

Simuler une facturation
Il est temps de tester ce code et de constater que la mise à jour des stocks s'opère parfaitement.

• Enregistrer les modifications (CTRL + S) et exécuter le code (F5),
• Sur le formulaire, choisir une référence avec la liste déroulante,
• Définir une quantité puis cliquer sur le bouton Ajouter,
• Réitérer avec quelques références supplémentaires à ajouter à la facture,

Désormais, si vous consultez la table TempProduits dans la base de données Access, vous constatez que les références demandées y sont bien présentes, accompagnées de leurs quantités. Et si vous consultez la table Produits, vous pouvez identifier les valeurs des stocks, pas encore mis à jour pour ces mêmes références.

Avant de poursuivre, il convient de fermer ces tables.

• Dès lors et de retour sur le formulaire Word, cliquer sur le bouton Créer,

Si vous ouvrez de nouveau la table Produits, vous avez le plaisir de constater que les stocks des références commandées ont parfaitement été mis à jour en fonction des quantités achetées.

Vous pouvez donc fermer le formulaire. Et pour la suite des opérations, il convient de ne conserver qu'une seule ligne vierge en-dessous des titres de la facture et au-dessus des cellules des totaux.

Editer la facture au format PDF
Pour parfaire cette application, nous devons éditer la facture au format PDF et la stocker dans le sous dossier archives. Suite à cela, nous n'aurons plus qu'à commander la fermeture du document Word sans l'enregistrer, pour préserver le modèle intact.

• A la suite de la procédure, ajouter les deux instructions VBA suivantes :

ActiveDocument.ExportAsFixedFormat ThisDocument.Path & "\archives\facture" & Replace(Replace(Replace(Now, "/", "-"), ":", "-"), " ", "-") & ".pdf", wdExportFormatPDF
ThisDocument.Close wdDoNotSaveChanges

Ce code aurait d'ailleurs pu être livré en partie par une macro automatique enregistrant les simulations de l'utilisateur. Pour l'exportation, l'objet ActiveDocument propose la méthode ExportAsFixedFormat. En premier paramètre, nous lui indiquons le chemin d'accès et le nom du fichier à enregistrer pour générer la facture. Comme nous souhaitons attribuer des noms de fichier uniques, nous nous basons sur la date et l'heure précises grâce à la fonction Now. Puis, nous purgeons la chaîne résultante des caractères indésirables comme le slash, l'espace ou le tiret avec des fonctions Replace imbriquées en cascade. En deuxième paramètre de la méthode, nous définissons le format de sortie en PDF. Enfin, nous fermons le document sans l'enregistrer grâce à la méthode Close de l'objet ThisDocument. Comme nous l'avons dit, c'est ainsi que nous préservons le modèle de facturation tout en générant la facture PDF.

• Enregistrer les modifications et exécuter le code VBA,
• Ajouter quelques articles à la facture,
• Puis, cliquer sur le bouton Créer,

Comme vous pouvez le voir, le modèle de facturation se ferme effectivement. Les stocks sont toujours bien mis à jour dans la base de données Access. De plus, si vous ouvrez le sous dossier archives, vous constatez la présence de la facture PDF avec son nom unique.

Le code VBA complet que nous avons bâti pour cette actualisation des stocks et cette exportation au format PDF, est le suivant :

Private Sub Creer_Click()
Dim cheminBd As String: Dim requete As String
Dim enr As Recordset: Dim base As Database

cheminBd = ThisDocument.Path & "\articles.accdb"
Set base = DBEngine.OpenDatabase(cheminBd)
Set enr = base.OpenRecordset("SELECT * FROM TempProduits", dbOpenDynaset)

enr.MoveFirst
Do
requete = "UPDATE Produits SET produit_stock=produit_stock - " & enr.Fields("produit_stock") & " WHERE produit_ref='" & enr.Fields("produit_ref") & "'"
base.Execute requete
enr.MoveNext
Loop Until enr.EOF

enr.Close
base.Close
Set enr = Nothing
Set base = Nothing

ActiveDocument.ExportAsFixedFormat ThisDocument.Path & "\archives\facture" & Replace(Replace(Replace(Now, "/","-"), ":", "-"), " ", "-") & ".pdf", wdExportFormatPDF
ThisDocument.Close wdDoNotSaveChanges

End Sub