Une gestion des stocks avec Access

Claude Leloup

Ceci comme point de départ pour amorcer la discussion lors de demandes d'aide sur le forum Access de DVP.

- Titre : Une gestion des stocks avec Access

- Auteur : Claude Leloup

- Parution : 15 novembre 2013

- Mise à jour : 5 septembre 2017

- Licence : 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 ® 2013 Claude Leloup. 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. L'idée de départ

- II. Avantage d'une telle approche : la simplicité

- III. Contexte

- IV. Alimenter la table des entrées

- V. Alimenter la table des sorties

- VI. Et la suite ?

- VII. Télécharger

I - L'idée de départ

Trois tables :

- une pour référencer les articles : tArticles ;

- une pour comptabiliser les entrées en stock : tEntrees ;

- une pour comptabiliser les sorties : tSorties.

Si, lors de la mise en place, un stock existe déjà, il est comptabilisé comme une entrée.

La colonne « CMUP » (Coût Moyen Unitaire Pondéré) est ajoutée pour les besoins de l'exemple qui va suivre.

On pourrait reprocher la colonne « CMUP » dans la table tSorties : elle est redondante puisque cette notion est déjà incluse dans tEntrees.
Le souci de faciliter les calculs de prix de revient (en aval) justifie ce choix.

II - Avantage d'une telle approche : la simplicité

Le stock à un moment donné s'obtient :

Somme des entrées - somme des sorties
à cette date

Voici le contenu du module mFonctions :

Option Compare Database
Option Explicit

Public Function EntreesADate(Article As Long, DateAng As Date) As Single
EntreesADate = Nz(DSum("EntreeQuant", "tEntrees", _
"format(EntreeDate,""yyyymmdd"")<=" & Format(DateAng, "yyyymmdd") & " and tArticlesFK=" & Article), 0)
End Function
Public Function SortiesADate(Article As Long, DateAng As Date) As Single
SortiesADate = Nz(DSum("sortieQuant", "tSorties", _
"format(SortieDate,""yyyymmdd"")<=" & Format(DateAng, "yyyymmdd") & " and tArticlesFK=" & Article), 0)
End Function

Public Function StockADate(Article As Long, DateAng As Date) As Single
StockADate = EntreesADate(Article, DateAng) - SortiesADate(Article, DateAng)
End Function

La fonction Nz() renverra zéro si aucun enregistrement pour cet article.

.

Pour tester ces fonctions, rendez-vous dans la base de données qui nous sert d'exemple, nous avons :

et ouvrez la fenêtre d'exécution (<CRTL + G>) et saisissez :

? EntreesADate(961,#10/04/2013#)

et enfoncez <ENTER>, vous obtiendrez ceci :

soit 12 + 25.

De même :

III - Contexte

Pour la suite, nous partons de l'hypothèse que l'entreprise veut valoriser les sorties au CMUP pour le calcul du prix de revient de sa fabrication.

La valorisation des sorties de marchandise au CMUP impose que :

- les entrées de marchandise sont comptabilisées à leur coût réel ;

- les sorties se font au coût unitaire moyen pondéré de la quantité totale du stock disponible.

Voici un exemple pour illustrer la méthode :

Dans un tel contexte, il est impératif que les comptabilisations se fassent dans un ordre chronologique rigoureux : quand un CMUP a été calculé après une entrée, il n'est plus possible de comptabiliser des sorties antérieures à cette date.

IV - Alimenter la table des entrées

- L'utilisateur choisit un article dans la liste déroulante.

- S'affiche alors l'historique des entrées dans l'ordre chronologique décroissant (en fait le sous-formulaire sfEntreesDetail)

1. rivate Sub CboArticle_AfterUpdate()

2. 'Afficher l'historique

3. Me.CTNRsfEntreesDetail.Form.Requery

4. 'Rendre visibles les contrôles pour l'encodage d'une nouvelle entrée

5. Me.txtDate.Visible = True

6. Me.txtQuant.Visible = True

7. Me.txtPU.Visible = True

8. Me.txtDate = Null

9. Me.txtQuant = Null

10. Me.txtPU = Null

11. 'Afficher le stock actuel de cet article

12. Me.txtStock = StockADate(Me.CboArticle, Format(Date, "mm/dd/yyyy"))

13. 'Se positionner sur la date à encoder

14. DoCmd.GoToControl "txtDate"

15. End Sub

Remarquez la syntaxe à la ligne 12

Me.txtStock = StockADate(Me.CboArticle, Format(Date, "mm/dd/yyyy"))

Dans une version Access francophone la fonction Date() renvoie la date d'ajourd'hui sous la forme jj/mm/aaaa.

Or quand on traite une date en VBA, il faut que celle-ci soit sous la forme anglo-saxone (mm/dd/yyyy), d'où ce reformatage nécessaire.

- Introduction de la date de la nouvelle entrée. Pour respecter la méthode CMUP, cette date doit être :

- postérieure à celle de la dernière entrée ;

- postérieure à celle de la dernière sortie ;

- et au plus, égale à aujourd'hui.

Private Sub txtDate_AfterUpdate()
'la date doit être postérieure à la dernière entrée et à la date de dernière sortie
If Me.txtDate <= DMax("EntreeDate", "tEntrees", "tArticlesFK=" & Me.CboArticle) Then
MsgBox "La date doit être postérieure à la dernière entrée de cet article"
txtDate = Null
Exit Sub
Else
If Me.txtDate <= DMax("SortieDate", "tSorties", "tArticlesFK=" & Me.CboArticle) Then
MsgBox "La date doit être postérieure à la dernière sortie de cet article"
txtDate = Null
Exit Sub
Else
DoCmd.GoToControl "txtQuant"
End If
End If
End Sub

et

- L'utilisateur complète la quantité et le prix unitaire et clique sur

1. Private Sub btEnregistrer_Click()

2. Dim AvantDernDate As Date

3. Dim AvantDernCMUP As Double

4. Dim StockFinal As Double

5. Dim dCMUP As Double

6. Dim sSql As String

8. 'Est-ce complet ?

9. If IsNull(Me.txtDate) Or Nz(Me.txtQuant, 0) = 0 Or Nz(Me.txtPU, 0) = 0 Then

10. MsgBox "Un des champs obligatoires n'est pas rempli", vbCritical

11. Exit Sub

12. End If

14. 'Déterminer les éléments pour le calcul du CMUP

16. 'AvantDernDate : date de l'entrée précédente

17. AvantDernDate = Nz(DLookup("EntreeDate", "rAvantDernEntree"), Me.txtDate)

18. If AvantDernDate = Me.txtDate Then

19. dCMUP = Me.txtPU

20. GoTo MajtEntrees

21. End If

24. 'AvantDernCMUP : CMUP après l'entrée précédente

25. AvantDernCMUP = DLookup("CMUP", "rAvantDernEntree")

27. 'Stock final : stock après cette dernière entrée

28. StockFinal = StockADate(Me.CboArticle, Format(Date, "mm/dd/yyyy")) + Me.txtQuant

30. '  Calcul CMUP

31. dCMUP = StockADate(Me.CboArticle, Format(Date, "mm/dd/yyyy")) * AvantDernCMUP + Me.txtQuant * Me.txtPU

32. dCMUP = dCMUP / StockFinal

34. MajtEntrees:

35. sSql = "INSERT INTO tEntrees ( EntreeDate, EntreeQuant, EntreePU, tArticlesFK, CMUP ) " _

36. & "SELECT #" & Format(Me.txtDate, "mm/dd/yy") & "# AS Expr1," _

37. & Replace(Me.txtQuant, ",", ".") & " AS Expr2, " _

38. & Replace(Me.txtPU, ",", ".") & " AS Expr3, " _

39. & Me.CboArticle & " As Expr4, " _

40. & Replace(dCMUP, ",", ".") & " as Expr5;"

41. DoCmd.SetWarnings False

42. DoCmd.RunSQL sSql

43. DoCmd.SetWarnings True

45. 'Remettre à zéro

46. Me.txtDate = Null: Me.txtQuant = Null: Me.txtPU = Null

47. Me.CTNRsfEntreesDetail.Requery

48. Me.txtStock = StockADate(Me.CboArticle, Format(Date, "mm/dd/yyyy"))

49. End Sub

Lignes

Commentaire

2=>6

Définition des variables.

9=>12

On vérifie la présence d'une date, d'une quantité et d'un prix unitaire non nuls.
Sinon, message d'alerte et blocage.

16=>28

On recueille les données nécessaires au calcul du CMUP.

17=>21

On traite le cas particulier de la 1re entrée pour cet article.

28 et 31

Remarquez la mise de la date au format anglo-saxon.

25

Pour trouver le CMPU précédent, on utilise la requête rAvantDernEntree

31-32

Calcul proprement dit du CMUP

35-43

On construit une requête « Ajout » pour insérer la nouvelle entrée dans tEntrees.
Remarquez aux lignes 37, 38 et 40 le reformatage (un point comme symbole décimal au lieu de la virgule).

46-48

Mise en place pour l'entrée suivante éventuelle.
Stock et historique sont mis à jour.
Même remarque que plus haut, pour le format de la ligne 48 Me.txtStock = StockADate(Me.CboArticle, Format(Date, "mm/dd/yyyy"))

V - Alimenter la table des sorties

- L'utilisateur choisit un article dans la liste déroulante.

- S'affiche alors l'historique des sorties dans l'ordre chronologique décroissant (en fait le sous-formulaire sfSortiesDetail)

- Introduction de la date de la nouvelle sortie. Pour respecter la méthode CMUP, cette date doit être :

- postérieure à celle de la dernière entrée ;

- postérieure à celle de la dernière sortie ;

- et au plus, égale à aujourd'hui.

Private Sub txtDate_AfterUpdate()
'la date doit être postérieure ou au moins égale à la dernière entrée
If Me.txtDate < Me.txtDernEntree Then
MsgBox "La date doit être égale ou postérieure à la dernière entrée de cet article"
txtDate = Null
Else
DoCmd.GoToControl "txtQuant"
End If
End Sub

et

- L'utilisateur complète la quantité et clique sur

1. Private Sub btEnregistrer_Click()

2. Dim sSql As String

3. 'Est-ce complet ?

4. If IsNull(Me.txtDate) Or Nz(Me.txtQuant, 0) = 0 Or Nz(Me.txtImputation, 0) = 0 Then

5. MsgBox "Un des champs obligatoires n'est pas rempli", vbCritical

6. Exit Sub

7. End If

8. 'Maj tSorties

9. sSql = "INSERT INTO tSorties ( SortieDate, SortieQuant, SortieImputation, CMUP, tArticlesFK ) " _

10. & "SELECT #" & Format(Me.txtDate, "mm/dd/yy") & "# AS Expr1, " _

11. & Replace(Me.txtQuant, ",", ".") & " AS Expr2, " _

12. & """" & Me.txtImputation & """ AS Expr3, " _

13. & Replace(Me.txtCMUP, ",", ".") & " AS Expr4, " _

14. & Me.CboArticle & " As Expr5;"

16. DoCmd.SetWarnings False

17. DoCmd.RunSQL sSql

18. DoCmd.SetWarnings True

19. 'Réinitialiser pour une nouvelle sortie éventuelle

20. Me.CTNRsfSortiesDetail.Requery

21. Me.txtDate = Null: Me.txtQuant = Null: Me.txtImputation = Null

22. Me.txtStock = StockADate(Me.CboArticle, Format(Date, "mm/dd/yyyy"))

23. End Sub

Lignes

Commentaire

4=>5

On vérifie la présence d'une date, d'une quantité et d'une imputation non nulles.
Sinon, message d'alerte et blocage.

9=>18

On construit une requête « Ajout » pour insérer la nouvelle sortie dans tSorties.
Remarquez aux lignes 11 et 13 le reformatage (un point comme symbole décimal au lieu de la virgule).

22

Même remarque que plus haut, pour le format Me.txtStock = StockADate(Me.CboArticle, Format(Date, "mm/dd/yyyy"))

VI - Et la suite ?

Voilà pour l'aspect valorisation de l'inventaire au CMUP, reste à développer les différentes fonctionnalités que l'on juge utiles dans le cadre d'une application :

contrôle des inventaires ;

calcul des prix de revient de la fabrication ;

statistiques d'achat et de vente...

VII - Télécharger

La base de données qui m'a servi à tester est ici.