Facturation Access et gestion de stocks VBA

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


Sujets que vous pourriez aussi aimer :


Facturation Clients avec Gestion de Stocks

Nous proposons ici de bâtir une interface capable de facturer les clients en fonction de leurs achats, tout en réalisant la mise à jour des stocks, une fois la commande validée.



Comme l'illustre la capture ci-dessus, les informations du client sont placées dans la partie supérieure du formulaire Access. La section située juste en dessous permet de sélectionner les articles achetés par le client et de les ajouter à la commande. Un sous formulaire résume l'ensemble des achats. Cette application est l'enchaînement logique de la formation précédente sur l'approvisionnement des stocks d'un magasin.

La base de données de facturation
Pour concentrer nos travaux sur la validation des commandes et la mise à jour des stocks par le code VBA Access, nous partons d'une base de données existante, volontairement simplifiée.

• Télécharger la base de données facturation-et-stocks.accdb en cliquant sur son lien,
• L'ouvrir dans Access et cliquer sur le bouton Activer le contenu si nécessaire,
• Double cliquer sur le formulaire Entrees_magasin depuis le volet de gauche,

Nous ouvrons ainsi la console permettant d'approvisionner le magasin en fonction des livraisons des fournisseurs. Elle est tout à fait fonctionnelle comme l'illustre la capture ci-dessus. L'ossature de cette base de données est construite sur 4 tables reliées entre elles. La table Detail_temp sert uniquement de source de données au sous formulaire de la console de facturation. Elle devra être remplie et vidée par le code VBA Access, en fonction des commandes passées.

• Fermer le formulaire en cliquant sur la croix de son onglet,

Le processus de facturation
Nos travaux doivent désormais se concentrer sur le formulaire Validation_commande, situé juste en dessous dans le volet des objets Access.

• Double cliquer sur le formulaire Validation_commande depuis le volet de gauche,

Ce dernier, bien que préconçu n'est pas encore fonctionnel. Son objectif est de permettre de bâtir le bon de commande en fonction des achats passés par le client. Ce client peut déjà être référencé dans la table Clients de la base de données. C'est la raison pour laquelle le caissier peut le sélectionner par sa référence à l'aide de la toute première liste déroulante Réf. Client. Son contenu est donc lié au premier champ de cette table, celui des références. S'il n'est pas encore connu, le client doit pouvoir être créé à la volée et inscrit en base de données, grâce au bouton Créer le client.

Une fois le client sélectionné, les articles achetés peuvent être ajoutés tour à tour à la commande, grâce à la liste déroulante Réf. Produit. C'est la raison pour laquelle le contenu de cette dernière est lié au premier champ de la table Catalogue, celui des codes articles. Le caissier saisit alors la quantité commandée et ajoute le produit à la facture grâce au bouton dédié, et ainsi de suite jusqu'à ce que tous les produits soient scannés.

Lorsque la commande est complète, il ne reste plus qu'à cliquer sur le bouton Valider la facture. La commande est alors archivée en base de données et reliée au client. De même les stocks sont mis à jour pour déduire les quantités achetées des quantités initiales, référencées dans la table Catalogue.

Récupérer les informations des clients
Bien sûr la première tâche que doit réaliser ce formulaire est de récupérer les informations du client correspondant à sa référence, choisie par le caissier à l'aide de la liste déroulante.

• Cliquer sur la flèche du bouton Affichage dans le ruban Accueil,
• Dans la liste, choisir Mode création,
• Cliquer sur le bouton Feuille de propriétés du ruban Création, si elle n'est pas visible,
• Sélectionner la première liste déroulante (liste_clients) sur le formulaire,
• Activer l'onglet Données de sa feuille de propriétés,

La capture ci-dessus confirme le lien existant entre la liste déroulante et le premier champ de la table Clients. C'est la raison pour laquelle, en mode exécution, cette liste propose déjà toutes les références des clients archivés.

Au choix d'une référence dans cette liste, les informations du client doivent être rapatriées dans les zones de saisie prévues à cet effet. Il s'agit donc d'associer un code VBA Access à l'événement déclenché par le choix d'une référence.

• Activer l'onglet Evénement de la feuille de propriétés pour la liste sélectionnée,
• Cliquer ensuite sur son événement Sur changement pour l'activer,
• Cliquer alors sur le petit bouton qui apparaît sur l'extrémité droite,
• Dans la boîte de dialogue qui surgit, choisir Générateur de code et valider par Ok,

Nous basculons ainsi dans l'éditeur de code Visual Basic Access, entre les bornes de la procédure événementielle liste_clients_Change. Tout code VBA saisi entre ces bornes se déclenchera donc au choix d'une nouvelle référence client dans la liste déroulante.

Le principe consiste à récupérer le nom, le prénom et la civilité du client, selon la référence choisie. Nous devons donc réaliser une requête SQL sélection sur la table de la base de données afin d'isoler les bons enregistrements. Et pour manipuler les objets de base de données, nous devons préalablement ajouter au projet la référence ADO, comme nous l'avait appris la formation VBA Access pour accéder aux données.

• Dérouler le menu Outils en haut de l'éditeur et choisir Références dans la liste,
• Dans la boîte de dialogue qui apparaît, cocher la référence à Microsoft ActiveX Data Objects 6.1 Library si elle n'est pas encore active,
• Puis, valider par Ok,

Le numéro (6.1) dépend de la version de votre système d'exploitation et de ses librairies de classes associées.

Pour instancier les classes permettant de manipuler les objets de base de données, nous devons commencer par déclarer les variables nécessaires.

• Entre les bornes de la procédure événementielle, ajouter les deux déclarations suivantes :

Dim ligne As Recordset: Dim base As Database

Nous déclarons ainsi une variable nommée ligne en tant qu'objet Recordset, soit un objet capable de manipuler les enregistrements d'une base de données. Bien sûr, nous déclarons une variable nommée base, comme un objet Database capable de manipuler la base de données en cours. Ces deux déclarations ont été rendues possibles grâce à l'ajout précédent de la référence à ADO. Il faut désormais affecter ces objets afin qu'ils héritent des méthodes permettant d'accéder aux données de la base.

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

Set base = Application.CurrentDb
Set ligne = base.OpenRecordset("SELECT * FROM Clients WHERE num_client=" & liste_clients.Value, dbOpenDynaset)

La méthode CurrentDb de l'objet Application permet de faire pointer l'objet base sur la base de données courante. Dès lors, la méthode héritée OpenRecordset permet à l'objet ligne d'accéder aux enregistrements de la base, selon la syntaxe de la requête SQL passée en paramètre. Le début de la syntaxe (SELECT * FROM Clients) indique que nous sélections tous les champs (SELECT *) de la table Clients (FROM Clients). Et comme il faut isoler l'enregistrement de la référence choisie dans la liste, nous restreignons cette sélection grâce à une clause WHERE (WHERE num_client=" & liste_clients.Value). C'est la raison pour laquelle cette clause WHERE réalise l'égalité entre la valeur du champ num_client de la table et la valeur choisie dans la liste déroulante. Le deuxième argument de la méthode OpenRecordset (dbOpenDynaset) permet d'accéder aux enregistrements résultants de façon dynamique, afin de prélever le contenu des champs.

Dans la foulée, il s'agit donc de placer le pointeur sur l'enregistrement isolé par la requête, afin d'extraire les données champ à champ pour les restituer dans les Textbox du formulaire.

• A la suite, ajouter les instructions de code suivantes :

ligne.MoveFirst
n_client.Value = liste_clients.Value
civilite.Value = ligne.Fields("civilite_client").Value
nom_client.Value = ligne.Fields("nom_client").Value
prenom_client.Value = ligne.Fields("prenom_client").Value

La méthode MoveFirst d'un objet Recordset place en effet le pointeur de lecture sur l'enregistrement fournit par la requête. Ensuite, la propriété Fields avec le nom du champ en paramètre permet de pointer sur ce dernier. La propriété dérivée Value permet d'accéder à son contenu pour l'affecter dans la zone de texte correspondante. Notez que nous inscrivons la référence du client dans une zone de texte située en dehors du formulaire (n_client.Value = liste_clients.Value). Cette dernière sera masquée à l'issue. Nous l'utiliserons comme variable afin de rattacher les informations au client.

Nous avons récupéré les données nécessaires. Mais comme toujours, avant de terminer, nous devons fermer les connexions à la base de données et décharger les variables objets de la mémoire.

• Pour cela, ajouter les instructions suivantes :

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

• Enregistrer les modifications (CTRL + S) et basculer sur le formulaire (ALT + F11),
• Exécuter ce dernier à l'aide de la touche F5 du clavier par exemple,
• Puis, choisir l'une des références grâce à la liste déroulante Réf. Client,

Comme le montre la capture ci-dessus, au choix d'une référence, toutes les informations correspondant au client sont rapatriées dans les zones de texte dédiées, grâce au code VBA Access déclenché sur événement. Nous pouvons de même remarquer la présence de la référence répliquée dans le Textbox n_client situé sur la droite du formulaire. Le code compet de la procédure événementielle permettant de récupérer les données d'un client par requête SQL, est le suivant :

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

Set base = Application.CurrentDb
Set ligne = base.OpenRecordset("SELECT * FROM Clients WHERE num_client=" & liste_clients.Value, dbOpenDynaset)

ligne.MoveFirst
n_client.Value = liste_clients.Value
civilite.Value = ligne.Fields("civilite_client").Value
nom_client.Value = ligne.Fields("nom_client").Value
prenom_client.Value = ligne.Fields("prenom_client").Value

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

• Cliquer sur la flèche du bouton Affichage dans le ruban Accueil,
• Puis, choisir Mode création dans la liste,

Créer un client et l'inscrire en base de données
Il n'est bien sûr pas interdit que de nouveaux clients passent commande. Ces derniers ne sont donc pas encore inscrits dans la table et ne peuvent pas être sélectionnés par le biais de la liste déroulante. Leurs informations doivent être saisies dans les zones de texte. Le bouton Créer le Client doit alors permettre d'enregistrer ces données dans la base pour que la commande puisse lui être attachée.

• Sélectionner le bouton Créer le Client,
• Activer l'onglet Evénement de sa feuille de propriétés,
• Cliquer sur le bouton de son événement Au clic,
• Dans la boîte de dialogue qui apparaît, choisir Générateur de code et valider par Ok,

Nous voici de retour dans l'éditeur de code Visual Basic Access mais cette fois-ci entre les bornes de la procédure événementielle creer_client_Click(). Il s'agit donc du code qui sera exécuté au clic sur le bouton créer_client.

C'est une fois de plus une requête SQL qui va permettre d'accéder aux données mais pour les modifier cette fois, ou plutôt les ajouter dans la table Clients, en fonction des informations saisies par l'utilisateur. Nous avons donc besoin d'une variable Database pour manipuler la base de données mais aussi d'une variable de type String pour mémoriser la syntaxe de la requête Action qui va permettre d'insérer les données.

• Ajouter les déclarations de variables suivantes entre les bornes de la procédure :

Dim base As Database: Dim ligne As Recordset
Dim nb_clients As Byte: Dim requete As String

Nous retrouvons bien les variables que nous venons d'énoncer, soit la variable base de type Database et la variable requete de type String. Mais en plus de cela, nous avons déclaré les variables nb_clients comme un entier court et ligne comme un Recordset pour manipuler les enregistrements. En effet, avant d'ajouter un client dans la table de la base de données, nous devons nous assurer qu'il n'existe pas déjà. Donc nous devons réaliser une requête capable de compter les enregistrements correspondant aux informations saisies. Si le retour vaut 0, nous saurons que le client n'existe pas et que nous pouvons l'inscrire sans risque de doublon.

• A la suite du code, ajouter les bornes de l'instruction conditionnelle suivante :

If (civilite.Value <> "" And nom_client.Value <> "" And prenom_client.Value <> "") Then

Else
MsgBox "Pour créer un nouveau client, toutes les informations des champs doivent être renseignées"
End If

Bien entendu, avant d'entamer toute procédure de vérification et d'insertion, il est utile de vérifier que toutes les informations du client ont été saisies, afin de ne pas déclencher un code VBA inutilement. C'est la raison pour laquelle nous recoupons tous les critères (And), pour vérifier que chacune des zones de texte contient bien une information à insérer. Si l'une d'entre elles est vide, la procédure est abandonnée.

• Dans l'instruction conditionnelle If, ajouter le code suivant :

Set base = Application.CurrentDb
Set ligne = base.OpenRecordset("SELECT COUNT(num_client) AS nb_client FROM Clients WHERE nom_client='" & nom_client.Value & "' AND prenom_client='" & prenom_client & "'", dbOpenDynaset)

ligne.MoveFirst
nb_clients = ligne.Fields("nb_client").Value

If (Int(nb_clients > 0)) Then

End If

Avant l'insertion des données, vient donc la vérification de potentiels doublons par le code VBA Access. C'est pourquoi nous initialisons notre objet Database sur la base de données en cours. Puis, grâce à sa méthode OpenRecordset héritée, nous accédons aux enregistrements qui correspondraient à un client existant, selon les informations saisies. Il s'agit d'une requête sélection (SELECT) mais capable de retourner le nombre d'enregistrements (fonction SQL COUNT) correspondant aux critères. Ces critères sont recoupés dans la clause WHERE grâce au mot clé AND. Comme les homonymes peuvent exister, nous cherchons à vérifier l'égalité en même temps sur le nom et sur le prénom. Si le résultat de cette requête retourne 0, nous saurons qu'aucun enregistrement ne correspond et donc que le client n'existe pas. Notez dans la clause SELECT avant le FROM l'emploi du mot clé AS (AS nb_client) pour donner un nom au champ résultant du décompte des résultats. C'est par ce nom que nous allons récupérer les données.

Nous plaçons donc le pointeur de lecture sur le premier enregistrement issu de la sélection (ligne.MoveFirst) et donc le seul, s'il existe. Puis nous récoltons l'information comptabilisée grâce au champ nommé, dans la variable nb_clients. Si la valeur stockée n'est pas nulle (If(Int(nb_clients > 0)) Then), alors nous savons que le client existe déjà. Dans le cas contraire, nous pouvons inscrire le client dans la table. Pour ce faire, nous devons créer une requête d'ajout (INSERT INTO) et l'exécuter sur la base de données en cours.

• Dans les bornes de l'instruction If, ajouter le code suivant :

MsgBox ("Le client existe déjà, il ne peut donc être créé une deuxième fois")
Else
requete = "INSERT INTO Clients (civilite_client,nom_client,prenom_client) VALUES ('" & civilite.Value & "','" & nom_client.Value & "','" & prenom_client.Value & "')"
base.Execute requete
MsgBox "Le client a été créé avec succès"

DoCmd.Requery
liste_clients = liste_clients.ItemData(liste_clients.ListCount - 1)
n_client.Value = liste_clients.Value

Tout d'abord, un message dans une boîte de dialogue (MsgBox) permet d'informer le caissier que pour créer un nouveau client, toutes ses informations doivent être renseignées. Dans le cas contraire (Else), l'insertion est enclenchée. Nous mémorisons la syntaxe SQL d'ajout dans la variable requete. La table Clients doit être précédée des mots clés pour l'insertion de nouvelles données (INSERT INTO Clients). Puis sont listés les champs qui doivent être renseignés et pour chacun d'eux dans le même ordre, leur valeur précédée du mot clé VALUES. Pour cela nous concaténons l'instruction statique SQL aux valeurs inscrites dans les zones de saisie du formulaire.

Alors nous exécutons la requête grâce à la méthode Execute de l'objet Database. Puis nous en informons le client par le biais, une fois encore d'un MsgBox. Nous n'oublions pas de rafraîchir les sources de données grâce à la méthode Requery de l'objet DoCmd. Ainsi la liste déroulante intègre la nouvelle référence du client fraîchement créé. Mais pour qu'une commande puisse lui être attachée, son code doit être activé automatiquement. Nous sélectionnons la dernière valeur de la liste déroulante (liste_clients.ItemData(liste_clients.ListCount - 1)). Comme le comptage part de 0, il se termine au niveau de l'avant dernier élément (liste_clients.ListCount - 1). Enfin, nous réactualisons le numéro du client choisi dans la zone de texte située sur la droite du formulaire (n_client.Value = liste_clients.Value).

Comme toujours, avant de tester le code, il convient de fermer les connexions et décharger les objets utilisés. Pour ce faire :

• A la suite du code, dans l'instruction If en cours, ajouter les instructions suivantes :

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

• Enregistrer les modifications et basculer sur le formulaire,
• Exécuter ce dernier à l'aide de la touche F5 par exemple,
• Taper une civilité, un nom et un prénom dans les zones de saisies,
• Puis, cliquer sur le bouton Créer le Client,

D'une part, comme l'illustre la capture ci-dessous, nous remarquons l'apparition du MsgBox confirmant que l'opération de création s'est bien déroulée. Et d'autre part, lorsque nous validons le message, nous constatons que la zone de liste se cale automatiquement sur la dernière référence, si bien que le dernier client est sélectionné et prêt à être facturé.



D'ailleurs, si nous ouvrons la table Clients, en dernière position, nous notons bien la présence des informations que nous venons de renseigner pour le nouveau client. Le code complet permettant de créer un nouveau client est le suivant :
Private Sub creer_client_Click()
Dim base As Database: Dim ligne As Recordset
Dim nb_clients As Byte: Dim requete As String

If (civilite.Value <> "" And nom_client.Value <> "" And prenom_client.Value <> "") Then
Set base = Application.CurrentDb
Set ligne = base.OpenRecordset("SELECT COUNT(num_client) AS nb_client FROM Clients WHERE nom_client='" & nom_client.Value & "' AND prenom_client='" & prenom_client & "'", dbOpenDynaset)

ligne.MoveFirst
nb_clients = ligne.Fields("nb_client").Value
If (Int(nb_clients > 0)) Then
MsgBox ("Le client existe déjà, il ne peut donc être créé une deuxième fois")
Else
requete = "INSERT INTO Clients (civilite_client,nom_client,prenom_client) VALUES ('" & civilite.Value & "','" & nom_client.Value & "','" & prenom_client.Value &"')"
base.Execute requete

MsgBox "Le client a été créé avec succès"
DoCmd.Requery
liste_clients = liste_clients.ItemData(liste_clients.ListCount - 1)
n_client.Value = liste_clients.Value

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

Else
MsgBox "Pour créer un nouveau client, toutes les informations des champs doivent être renseignées"
End If
End Sub

Récupérer les informations du catalogue
A l'instar de la liste déroulante des clients, la liste Réf. Produit est reliée au premier champ de la table catalogue. Elle permet de sélectionner un code article comme s'il était scanné au moment de passer en caisse. Au changement de référence, comme précédemment, nous devons donc bâtir un code VBA capable de rapatrier les informations de l'article correspondant à la référence choisie.

• Cliquer sur la flèche du bouton Affichage dans le ruban Accueil,
• Dans la liste, choisir Mode création,
• Sélectionner la deuxième liste déroulante (Réf. produit),
• Activer l'onglet Evénement de sa feuille de propriétés,
• Cliquer sur le petit bouton de son événement Sur changement,
• Dans la boîte de dialogue qui suit, choisir Générateur de code et valider par Ok,

Nous retournons dans l'éditeur de code VBA Access, entre les bornes de la procédure événementielle ref_produit_Change, afin d'écrire le code capable de récupérer les données correspondant au code article choisi. Le code est strictement identique à celui qui a permis de récupérer les informations clients, sauf que la requête doit cette fois pointer sur la table Catalogue avec une clause WHERE construite sur la référence de la liste des produits.

• En conséquence, entre les bornes de la procédure, ajouter le code suivant :

Dim ligne As Recordset: Dim base As Database

Set base = Application.CurrentDb
Set ligne = base.OpenRecordset("SELECT * FROM Catalogue WHERE code_article='" & ref_produit.Value & "'",dbOpenDynaset)

ligne.MoveFirst
Qte_stock.Value = ligne.Fields("Quantite").Value
designation.Value = ligne.Fields("Designation").Value
prix_unitaire.Value = ligne.Fields("Prix_unitaire_HT").Value

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

qte_commandee.Value = 1

• Enregistrer les modifications et basculer sur le formulaire,
• Exécuter ce dernier, choisir un client puis une référence article,

Notez que nous réinitialisons la zone de la quantité commandée après chaque changement de référence (qte_commandee.Value= 1), afin d'inciter le caissier à redéfinir la quantité si elle dépasse l'unité.

Ajouter les articles au bon de commande
Dès lors que le client est connu et qu'une référence article est choisie ou scannée avec une quantité définie, elle doit être ajoutée à la commande. C'est la raison pour laquelle figure un bouton Ajouter à la facture sur l'interface. Son objectif est d'insérer les références du produit dans la table Detail_temp. Comme cette dernière fait office de source de données du sous formulaire, après un rafraîchissement (DoCmd.Requery), ces informations apparaissent dans la partie inférieure du formulaire et d'autres articles peuvent être ajoutés à la facture, et ainsi de suite.

• Afficher le formulaire en mode création et sélectionner le bouton Ajouter à la facture,
• Activer l'onglet Evénement de sa feuille de propriétés,
• Puis, 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 voici de retour dans l'éditeur de code Visual Basic Access, entre les bornes de la procédure ajouter_facture_Click. Bien sûr nous avons besoin d'une variable Database pour manipuler la base de données en cours ainsi que d'une variable de type String pour mémoriser la requête Action à exécuter. Mais nous avons aussi besoin d'une variable de type Recordset afin d'accéder aux enregistrements de la table en cours, résumant le bon de commande, pour calculer la somme des articles achetés.

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

Dim base As Database: Dim ligne As Recordset
Dim requete As String: Dim total As Integer: Dim total_achat As Integer

La variable total doit permettre de stocker et manipuler la valeur achetée pour chaque produit, en fonction de la quantité. La variable total_achat doit permettre de synthétiser la valeur de l'ensemble des articles achetés pour la commande en cours. Les variables ligne, requete et base correspondent aux besoins que nous avons définis précédemment.

Avant de commencer tout traitement, nous devons nous assurer qu'une référence article a bien été désignée et qu'une quantité a bien été définie. Comme toujours nous utilisons donc l'instruction conditionnelle If afin de recouper plusieurs conditions à satisfaire ensemble.

• A la suite du code, ajouter la vérification suivante :

If (IsNumeric(qte_commandee.Value) And qte_commandee.Value > 0 And ref_produit.Value <> "") Then

Else
MsgBox "Pour ajouter un article à la commande, vous devez définir une quantité supérieure à 0"
End If

Dans la foulée, nous devons aussi nous assurer que la quantité demandée est bien disponible, qu'elle n'est pas supérieure à la quantité en stock. Donc il faut ajouter un critère supplémentaire.

• Dans les bornes de l'instruction If, ajouter le code traduisant la condition sur le stock :

If (Int(qte_commandee.Value) <= Int(Qte_stock.Value)) Then

Else
MsgBox "Le stock ne permet pas d'ajouter ce produit à la facture pour la quantité demandée"
End If



Les données sur la quantité achetée, la désignation et le prix unitaire de l'article, issus de la table Catalogue doivent être insérés dans la table temporaire, utilisée comme source de données du sous formulaire. De plus, le prix total hors taxe pour l'article selon la quantité, doit être calculé à la volée, d'où la déclaration de la variable total. Une fois tous ces éléments consolidés, une requête INSERT INTO peut se charger d'ajouter les données dans la table Detail_temp.

• Dans les bornes de la seconde instruction If, ajouter le code suivant :

total = Int(prix_unitaire.Value) * Int(qte_commandee.Value)
total_achat = 0

Set base = Application.CurrentDb
requete = "INSERT INTO Detail_temp (ref_det, qute_det, Designation, Prix_unitaire_HT, Prix_total_HT) VALUES ('" & ref_produit.Value & "'," & qte_commandee.Value & ",'" & designation.Value & "'," & prix_unitaire.Value & "," & total & ")"
base.Execute requete

Après l'insertion du nouveau produit, nous devons accéder à l'ensemble des enregistrements présents dans le bon de commande (table Detail_temp) afin de calculer le montant global de la facture, soit la somme de tous les totaux par article. C'est la raison pour laquelle nous avons déclaré un objet Recorset capable d'accéder aux données de la table par le biais d'une requête SQL.

• A la suite du code, ajouter les instructions suivantes :

Set ligne = base.OpenRecordset("SELECT Prix_total_HT FROM Detail_temp", dbOpenDynaset)

ligne.MoveFirst
Do
total_achat = total_achat + Int(ligne.Fields("Prix_total_HT").Value)
ligne.MoveNext
Loop Until ligne.EOF

total_commande.Value = total_achat

La requête sélection ne s'intéresse qu'au champ Prix_total_HT pour l'ensemble des enregistrements de la table (SELECT Prix_total_HT). Comme toujours, après avoir positionné le pointeur de lecture sur le premier enregistrement résultant, nous initialisons une boucle Do Loop capable de parcourir chacun d'entre eux. Ainsi à chaque passage, nous consolidons la somme des achats par incrémentation de la variable total_achat avant de passer à l'enregistrement suivant (ligne.MoveNext). Enfin, une fois tous les enregistrements de la commande parcourus, soit après l'exécution de la boucle, nous inscrivons ce résultat dans la zone de texte (total_commande) prévue à cet effet sur le formulaire.

Comme toujours avant de terminer, nous devons fermer les connexions ouvertes et décharger les objets de la mémoire. Pour ce faire :

• Ajouter les instructions suivantes à la suite du code :

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

DoCmd.Requery

Il s'agit désormais d'instructions classiques. Notez néanmoins l'emploi de la méthode Requery de l'objet DoCmd à l'issue, afin de forcer le rafraichissement des sources de données et notamment de l'affichage du sous formulaire puisque nous venons d'enrichir la commande de nouvelles données.

• Enregistrer les modifications et basculer sur le formulaire,
• Exécuter ce dernier avec la touche F5 du clavier par exemple,
• Choisir un client à l'aide de la première liste déroulante,
• Choisir une référence produit à l'aide de la seconde liste et saisir une quantité achetée,
• Cliquer alors sur le bouton Ajouter à la facture,

Comme nous pouvons le voir, grâce à la méthode Requery de l'objet DoCmd, la source de données est effectivement réactualisée sur les nouveaux enregistrements insérés. Ainsi les produits ajoutés à la commande apparaissent dans la partie inférieure du sous-formulaire. Si nous continuons d'ajouter des références sans changer de client, ceux-ci se cumulent les uns sous les autres. Les totaux par articles sont parfaitement calculés. De même, le montant global de la facture est parfaitement consolidé comme l'illustre la zone entourée sur la capture ci-dessous.

• Afficher de nouveau le formulaire en mode Création,

Le code complet permettant d'ajouter chaque article à la facture et de consolider le total de la commande est le suivant :

Private Sub ajouter_facture_Click()
Dim base As Database: Dim ligne As Recordset
Dim requete As String: Dim total As Integer: Dim total_achat As Integer

If (IsNumeric(qte_commandee.Value) And qte_commandee.Value > 0 And ref_produit.Value <> "") Then
If (Int(qte_commandee.Value) <= Int(Qte_stock.Value)) Then
total = Int(prix_unitaire.Value) * Int(qte_commandee.Value)
total_achat = 0

Set base = Application.CurrentDb
requete = "INSERT INTO Detail_temp (ref_det, qute_det, Designation, Prix_unitaire_HT, Prix_total_HT) VALUES ('" & ref_produit.Value & "'," & qte_commandee.Value & ",'" & designation.Value & "'," & prix_unitaire.Value & "," & total & ")"
base.Execute requete

Set ligne = base.OpenRecordset("SELECT Prix_total_HT FROM Detail_temp", dbOpenDynaset)

ligne.MoveFirst
Do
total_achat = total_achat + Int(ligne.Fields("Prix_total_HT").Value)
ligne.MoveNext
Loop Until ligne.EOF

total_commande.Value = total_achat

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

DoCmd.Requery
Else
MsgBox "Le stock ne permet pas d'ajouter ce produit à la facture pour la quantité demandée"
End If
Else
MsgBox "Pour ajouter un article à la commande, vous devez définir une quantité supérieure à 0"
End If
End Sub

Valider la facture et mettre les stocks à jour
Une fois tous les articles scannés à la caisse, la facture doit être validée pour être livrée au client. Cela signifie que la commande doit être inscrite et archivée dans la table commandes sous un nouveau numéro, et rattachée au client par sa référence dans la table Clients. Tout le détail de la facture doit être inscrit quant à lui dans la table Detail_commandes et rattaché au numéro de la commande ainsi créée dans la table parent. Bien entendu, les stocks doivent être mis à jour en déduisant les quantités achetées des quantités en stock, pour les articles commandés. Enfin la table Detail_temp doit nécessairement être purgée pour permettre l'édition d'une nouvelle facture.

• Sélectionner le bouton Valider la facture du formulaire,
• 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, choisir Générateur de code et valider par Ok,

Nous voici de retour dans l'éditeur VBA Access, entre les bornes de la procédure événementielle valider_facture_Click cette fois. Nous avons besoin de plusieurs requêtes action, d'une part pour ajouter les données (INSERT INTO) dans les tables Commandes et Detail_commandes mais aussi pour mettre à jour les stocks (UPDATE) de la table Catalogue. De plus, nous avons besoin d'une requête sélection (SELECT) afin de récupérer le dernier numéro de commande auquel doit être rattaché le détail de la commande ainsi créé. En effet, la clé primaire de la table Commandes est un champ NuméroAuto, si bien que les références sont créées automatiquement et auto-incrémentées.

C'est la raison pour laquelle nous avons besoin d'une variable Database ainsi que d'une variable Recordset et d'un string pour la requête sélection. D'autres variables sont nécessaires pour mémoriser les différentes données récupérées ou à insérer.

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

Dim num_com As Long: Dim requete As String
Dim ligne As Recordset: Dim base As Database
Dim ref_produit As String: Dim qte_produit As Integer

La variable num_com doit servir à stocker le dernier numéro de commande créé pour pouvoir rattacher le détail de la commande dans la table liée. Les variables ref_produit et qte_produit sont nécessaires pour inscrire le détail de la commande mais aussi pour réaliser la mise à jour des stocks. Bien sûr, la commande ne peut être validée que si un client a correctement été sélectionné.

• En conséquence, à la suite du code, ajouter l'instruction conditionnelle suivante :

If (n_client.Value <> "") Then

End if

Dès lors la commande peut être créée. Pour cela, l'objet base doit préalablement être affecté.

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

Set base = Application.CurrentDb
requete = "INSERT INTO Commandes (cli_com, montant_com) VALUES ("& n_client.Value & "," & Int(total_commande.Value) & ")"
base.Execute requete

Deux champs seulement doivent être renseignés dans la table Commandes. Il y a le numéro de client de la table Clients (n_client.Value) auquel la commande doit être rattachée et le montant total de la facture (total_commande.Value).



Comme nous l'avons dit précédemment, le champ num_com quant à lui, s'incrémente automatiquement. Et donc, nous devons être en mesure de le récupérer pour l'inscrire dans la table Detail_commandes, afin de rattacher ses données complémentaires. Il s'agit donc de réaliser une requête sélection sur la table Commandes à la recherche du dernier numéro créé.

• A la suite du code, ajouter les instructions suivantes :

Set ligne = base.OpenRecordset("SELECT MAX(num_com) as dernier_num FROM Commandes", dbOpenDynaset)

ligne.MoveFirst
num_com = Int(ligne.Fields("dernier_num").Value)
n_commande.Value = num_com

La fonction SQL MAX sur le champ num_com permet de retourner la plus grande valeur numérique recensée. Nous nommons le champ statistique résultant dernier_num afin de pouvoir en extraire la valeur grâce à la propriété Fields de l'objet Recordset (ligne.Fields("dernier_num").Value). Accessoirement, nous inscrivons cette valeur dans la zone de texte n_commande, située sur la droite du formulaire.

Puisque nous connaissons désormais le numéro de commande auquel rattaché le détail soit l'ensemble des articles, nous pouvons commencer l'insertion dans la table Detail_commandes. Mais cette insertion doit se produire pour chaque article acheté. Nous devons donc premièrement accéder à l'ensemble des enregistrements de la table Detail_temp. Et pour chacun d'eux, donc dans une boucle Do Loop, nous devons produire une requête d'insertion.

• A la suite du code, ajouter les instructions suivantes :

Set ligne = base.OpenRecordset("SELECT * FROM Detail_temp", dbOpenDynaset)

ligne.MoveFirst
Do

ligne.MoveNext
Loop Until ligne.EOF

Il est important de ne pas oublier l'instruction ligne.MoveNext sous peine de rester bloqué sur le premier enregistrement et ainsi produire une boucle infinie. A l'intérieur de cette dernière, nous devons récupérer les informations par article, à insérer comme nouvel enregistrement rattaché à la commande précédente, dans la table Detail_commandes.

• Pour ce faire, dans la boucle Do Loop, ajouter les lignes de code suivantes :

ref_produit = ligne.Fields("ref_det").Value
qte_produit = ligne.Fields("qute_det").Value

requete = "INSERT INTO Detail_commandes (com_det, ref_det, qute_det) VALUES (" & n_commande.Value & ",'" & ref_produit & "'," & qte_produit & ")"
base.Execute requete

requete = "UPDATE Catalogue SET Quantite = Quantite - " & Int(qte_produit) & " WHERE code_article='" & ref_produit & "'"
base.Execute requete

Toujours grâce à la propriété Fields de l'objet Recordset, nous récupérons les informations de champ isolés par la requête sélection et nous les stockons dans les variables que nous avions déclarées à cet effet. Dans la foulée, nous exécutons deux requêtes action. La première permet d'insérer ces éléments dans la table Detail_commandes (INSERT INTO Detail_commandes), en les rattachant précisément à la commande précédemment créée (n_commande.Value). Comme toujours, la méthode Execute de l'objet Database se charge d'exécuter cette requête sur la base de données. Ensuite nous lançons une requête Update sur la table Catalogue (UPDATE Catalogue) afin de mettre à jour les stocks. Pour ce faire, nous déduisons les quantités achetées (Quantite = Quantite -" & Int(qte_produit)) pour l'article commandé (WHERE code_article='" & ref_produit & "'").

Le traitement est terminé mais comme à chaque fois, il ne faut pas oublier de fermer les connexions et de décharger les objets.

• Après la boucle Do Loop et avant le End If, ajouter les instructions suivantes :

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

• Enregistrer les modifications et basculer sur le formulaire,
• Double cliquer sur la table Detail_temp depuis le volet Access pour l'ouvrir,
• Sélectionner tous les enregistrements puis les supprimer (Touche Suppr),
• Fermer la table et exécuter le formulaire,
• Choisir un client puis ajouter tour à tour des articles avec des quantités différentes,
• A l'issue, cliquer sur le bouton Valider la facture,
• Ouvrir alors la table Commandes pour constater la présence de la nouvelle facture,
• Puis ouvrir la table Detail_commandes pour visualiser le détail rattaché,

Les articles ont correctement été inscrits et rattachés. Ainsi par le jeu des relations nous remontons jusqu'au client pour lequel chaque commande est archivée. Enfin si vous ouvrez la table Catalogue, vous notez la variation des quantités dont les stocks ont été mis à jour en fonction des quantités commandées.

Le code VBA complet permettant de valider la facture et d'archiver les données est le suivant :

Private Sub valider_facture_Click()
Dim num_com As Long: Dim requete As String
Dim ligne As Recordset: Dim base As Database
Dim ref_produit As String: Dim qte_produit As Integer

If (n_client.Value <> "") Then
Set base = Application.CurrentDb
requete = "INSERT INTO Commandes (cli_com, montant_com) VALUES ("& n_client.Value & "," & Int(total_commande.Value) & ")"
base.Execute requete

Set ligne = base.OpenRecordset("SELECT MAX(num_com) as dernier_num FROM Commandes", dbOpenDynaset)

ligne.MoveFirst
num_com = Int(ligne.Fields("dernier_num").Value)
n_commande.Value = num_com

Set ligne = base.OpenRecordset("SELECT * FROM Detail_temp",dbOpenDynaset)

ligne.MoveFirst
Do
ref_produit = ligne.Fields("ref_det").Value
qte_produit = ligne.Fields("qute_det").Value

requete = "INSERT INTO Detail_commandes (com_det, ref_det, qute_det) VALUES(" & n_commande.Value & ",'" & ref_produit & "'," & qte_produit & ")"
base.Execute requete

requete = "UPDATE Catalogue SET Quantite = Quantite - " & Int(qte_produit) & " WHERE code_article='" & ref_produit & "'"
base.Execute requete
ligne.MoveNext
Loop Until ligne.EOF

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

Purger les données temporaires
Comme vous l'avez remarqué, avant de créer une nouvelle commande, nous avons eu besoin de supprimer les informations contenues dans la table Detail_temp. Cela signifie que nous devons bâtir un code VBA capable de remplacer cette action manuelle. Il s'agit d'exécuter une requête action de suppression (DELETE) cette fois. Nous allons la créer dans une procédure indépendante. Ainsi elle pourra être appelée par différents événements pour être sûr de bien purger les données, par mesure de sécurité.

• Fermer les tables ouvertes et afficher le formulaire en conception,
• Basculer dans l'éditeur de code VBA Access (ALT + F11),
• Créer la procédure indépendante purger_table, comme suit :

Private Sub purger_table()

End Sub

Comme toujours, pour exécuter une requête action, nous avons besoin d'une variable Database et d'une variable String.

• En conséquence, ajouter les déclarations suivantes dans les bornes de la procédure :

Dim base As Database: Dim requete As String

Nous pouvons désormais instancier la classe permettant de pointer sur la base de données en cours afin d'exécuter la requête de suppression.

• A la suite du code, ajouter les instructions suivantes :

Set base = Application.CurrentDb
requete = "DELETE FROM Detail_temp"
base.Execute requete

base.Close
Set base = Nothing

Il s'agit d'un code classique. Notez la simplicité de la requête Suppression qui ne requiert pas de précision sur les noms des champs. De plus, en l'absence de clause Where, elle supprime tous les enregistrements désignés dans la table de la clause From. En l'état ce code est inoffensif puisqu'il n'est appelé par aucune procédure, donc il n'est jamais déclenché.

• Enregistrer les modifications et basculer sur le formulaire,
• Cliquer dans l'angle supérieur gauche au-dessus de la section Détail pour sélectionner le formulaire,
• Activer l'onglet Evénement de sa feuille de propriétés,
• Cliquer sur le petit bouton de son événement Sur chargement,
• Choisir Générateur de code dans la boîte de dialogue qui suit et valider par Ok,

De retour dans l'éditeur de code, nous nous retrouvons cette fois entre les bornes de la procédure événementielle Form_Load. Les instructions que nous y ajouterons se déclencheront donc au chargement (A l'ouverture) du formulaire.

• Entre les bornes de la procédure, ajouter l'appel à la procédure de nettoyage :

Private Sub Form_Load()
purger_table
End Sub
Ainsi avant de commencer toute nouvelle facturation, nous savons que la table Detail_temp sera purgée. Si vous créez une nouvelle facture, vous remplissez la table Detail_temp. Si vous fermez le formulaire et que vous le rouvrez, vous constatez que la table Detail_temp est effectivement vidée. Il est important de négocier cet appel sur différents événements comme la fermeture du formulaire, le changement de client ou encore à la fin de la validation de la facture.

• Pour le formulaire sélectionné en mode création, choisir cette fois son événement Sur fermeture puis cliquer sur le bouton et valider l'option Générateur de code,
• Ajouter l'appel au code de nettoyage entre les bornes de la procédure ainsi créée :

Private Sub Form_Close()
purger_table
End Sub

• Faire de même au début de la procédure liste_clients_Change, après la déclaration de variables,
• Ajouter enfin cet appel à la fin de la procédure valider_facture_Click, avant le End If,
• Puis, enregistrer les modifications.

Notre application de facturation Access avec gestion des stocks et archivage des commandes par le code VBA fonctionne parfaitement. Dans une prochaine étape, nous nous concentrerons sur les actions qui permettront de produire la facture, sous forme d'un état dans un premier temps. La facture pourra ainsi être éditée juste après la validation et la mise à jour des stocks. Puis nous verrons comment en produire un document PDF dans le but par exemple, d'en joindre une copie au client par mail.