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 FunctionLa 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)
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
rivate Sub CboArticle_AfterUpdate()
'Afficher l'historique
Me.CTNRsfEntreesDetail.Form.Requery
'Rendre visibles les contrôles pour l'encodage d'une nouvelle entrée
Me.txtDate.Visible = True
Me.txtQuant.Visible = True
Me.txtPU.Visible = True
Me.txtDate = Null
Me.txtQuant = Null
Me.txtPU = Null
'Afficher le stock actuel de cet article
Me.txtStock = StockADate(Me.CboArticle, Format(Date, "mm/dd/yyyy"))
'Se positionner sur la date à encoder
DoCmd.GoToControl "txtDate"
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 Subet
- L'utilisateur complète la quantité et le prix unitaire et clique sur
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
Private Sub btEnregistrer_Click()
Dim AvantDernDate As Date
Dim AvantDernCMUP As Double
Dim StockFinal As Double
Dim dCMUP As Double
Dim sSql As String
'Est-ce complet ?
If IsNull(Me.txtDate) Or Nz(Me.txtQuant, 0) = 0 Or Nz(Me.txtPU, 0) = 0 Then
MsgBox "Un des champs obligatoires n'est pas rempli", vbCritical
Exit Sub
End If
'Déterminer les éléments pour le calcul du CMUP
'AvantDernDate : date de l'entrée précédente
AvantDernDate = Nz(DLookup("EntreeDate", "rAvantDernEntree"), Me.txtDate)
If AvantDernDate = Me.txtDate Then
dCMUP = Me.txtPU
GoTo MajtEntrees
End If
'AvantDernCMUP : CMUP après l'entrée précédente
AvantDernCMUP = DLookup("CMUP", "rAvantDernEntree")
'Stock final : stock après cette dernière entrée
StockFinal = StockADate(Me.CboArticle, Format(Date, "mm/dd/yyyy")) + Me.txtQuant
' Calcul CMUP
dCMUP = StockADate(Me.CboArticle, Format(Date, "mm/dd/yyyy")) * AvantDernCMUP + Me.txtQuant * Me.txtPU
dCMUP = dCMUP / StockFinal
MajtEntrees:
sSql = "INSERT INTO tEntrees ( EntreeDate, EntreeQuant, EntreePU, tArticlesFK, CMUP ) " _
& "SELECT #" & Format(Me.txtDate, "mm/dd/yy") & "# AS Expr1," _
& Replace(Me.txtQuant, ",", ".") & " AS Expr2, " _
& Replace(Me.txtPU, ",", ".") & " AS Expr3, " _
& Me.CboArticle & " As Expr4, " _
& Replace(dCMUP, ",", ".") & " as Expr5;"
DoCmd.SetWarnings False
DoCmd.RunSQL sSql
DoCmd.SetWarnings True
'Remettre à zéro
Me.txtDate = Null: Me.txtQuant = Null: Me.txtPU = Null
Me.CTNRsfEntreesDetail.Requery
Me.txtStock = StockADate(Me.CboArticle, Format(Date, "mm/dd/yyyy"))
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 Subet
- L'utilisateur complète la quantité et clique sur
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
Private Sub btEnregistrer_Click()
Dim sSql As String
'Est-ce complet ?
If IsNull(Me.txtDate) Or Nz(Me.txtQuant, 0) = 0 Or Nz(Me.txtImputation, 0) = 0 Then
MsgBox "Un des champs obligatoires n'est pas rempli", vbCritical
Exit Sub
End If
'Maj tSorties
sSql = "INSERT INTO tSorties ( SortieDate, SortieQuant, SortieImputation, CMUP, tArticlesFK ) " _
& "SELECT #" & Format(Me.txtDate, "mm/dd/yy") & "# AS Expr1, " _
& Replace(Me.txtQuant, ",", ".") & " AS Expr2, " _
& """" & Me.txtImputation & """ AS Expr3, " _
& Replace(Me.txtCMUP, ",", ".") & " AS Expr4, " _
& Me.CboArticle & " As Expr5;"
DoCmd.SetWarnings False
DoCmd.RunSQL sSql
DoCmd.SetWarnings True
'Réinitialiser pour une nouvelle sortie éventuelle
Me.CTNRsfSortiesDetail.Requery
Me.txtDate = Null: Me.txtQuant = Null: Me.txtImputation = Null
Me.txtStock = StockADate(Me.CboArticle, Format(Date, "mm/dd/yyyy"))
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.