Une gestion des stocks avec Access

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

Article lu   fois.

L'auteur

Profil ProSite personnel

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

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.

Image non disponible

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 :

 
Sélectionnez
Option Compare Database
Option Explicit
            
            
Public Function EntreesADate(Article As Long, DateAng As Date) As Single
 EntreesADate = Nz(DSum("EntreeQuant", "tEntrees", _
                  "EntreeDate<=#" & DateAng & "# and tArticlesFK=" & Article), 0)
End Function
Public Function SortiesADate(Article As Long, DateAng As Date) As Single
 SortiesADate = Nz(DSum("sortieQuant", "tSorties", _
                  "SortieDate<=#" & DateAng & "# 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 vous documenter sur la fonction Dsum(), voyez le tutoriel de Philippe JOCHMANS : Les Fonctions de Domaine dans Access.

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

Image non disponible

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

 
Sélectionnez
? EntreesADate(961,#10/04/2013#)

et enfoncez <ENTER>, vous obtiendrez ceci :

Image non disponible

soit 12 + 25.

De même :

Image non disponible

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 :

Image non disponible

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

Image non disponible
  • 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)
 
Sélectionnez
1.
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.

 
Sélectionnez
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 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 postérieure à la dernière sortie de cet article"
          txtDate = Null
          Exit Sub
        Else
          DoCmd.GoToControl "txtQuant"
        End If
  End If
End Sub

et

Image non disponible
  • L'utilisateur complète la quantité et le prix unitaire et clique sur Image non disponible
 
Sélectionnez
1.
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.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
Private Sub btEnregistrer_Click()
  Dim AvantDernDate As Date
  Dim AvantDernStock As Single
  Dim AvantDernCMUP As Single
  Dim SortiesPeriode As Single
  Dim StockFinal As Single
  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
   
  'AvantDernStock : stock après l'entrée précédente
  AvantDernStock = StockADate(Me.CboArticle, Format(AvantDernDate, "mm/dd/yy"))

  'AvantDernCMUP : CMUP après l'entrée précédente
  AvantDernCMUP = DLookup("CMUP", "rAvantDernEntree")

  'SortiesPeriode : sorties depuis entrée précédente
  SortiesPeriode = SortiesADate(Me.CboArticle, Format(Date, "mm/dd/yyyy")) - SortiesADate(Me.CboArticle, Format(AvantDernDate, "mm/dd/yy"))
  
  'Stock final : stock après cette dernière entrée
  StockFinal = StockADate(Me.CboArticle, Format(Date, "mm/dd/yyyy")) + Me.txtQuant

  '  Calcul CMUP
  dCMUP = (AvantDernStock - SortiesPeriode) * 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," _
                       & Me.txtQuant & " AS Expr2, " _
                       & 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=>8 Définition des variables.
11=>13 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=>35 On recueille les données nécessaires au calcul du CMUP.
20 On traite le cas particulier de la 1re entrée pour cet article.
26 Remarquez la mise de la date au format anglo-saxon.
29 Pour trouver le CMPU précédent, on utilise la requête rAvantDernEntree
Image non disponible
38-39 Calcul proprement dit du CMUP
45-53 On construit une requête « Ajout » pour insérer la nouvelle entrée dans tEntrees.
Remarquez à la ligne 50 le reformatage (un point comme symbole décimal au lieu de la virgule).
56-58 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 58 Me.txtStock = StockADate(Me.CboArticle, Format(Date, "mm/dd/yyyy"))

V. Alimenter la table des sorties

Image non disponible
  • 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)

     
    Sélectionnez
    1.
    2.
    3.
    4.
    5.
    6.
    7.
    8.
    9.
    10.
    11.
    12.
    13.
    14.
    15.
    16.
    17.
    Private Sub CboArticle_AfterUpdate()
      'Afficher l'historique
      Me.CTNRsfSortiesDetail.Form.Requery
     'Rendre visibles les contrôles pour l'encodage d'une nouvelle sortie
      Me.txtDate.Visible = True
      Me.txtQuant.Visible = True
      Me.txtImputation.Visible = True
      Me.txtDate = Null
      Me.txtQuant = Null
      Me.txtImputation = Null
      DoCmd.GoToControl "txtDate"
      'Afficher le stock actuel de cet article
      Me.txtStock = StockADate(Me.CboArticle, Format(Date, "mm/dd/yyyy"))
      'Afficher le CMUP actuel et la dernière entrée de cet article
      Me.txtDernEntree = DLookup("EntreeDate", "rDernEntree")
      Me.txtCMUP = DLookup("CMUP", "rDernEntree")
    End Sub
    
  • 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.

 
Sélectionnez
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 égale ou postérieure à la dernière entrée de cet article"
      txtDate = Null
    Else
      DoCmd.GoToControl "txtQuant"
  End If
End Sub

et

Image non disponible
  • L'utilisateur complète la quantité et clique sur Image non disponible
 
Sélectionnez
1.
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, " _
           & 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 à la ligne 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.

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

  

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.