Évaluation des stocks avec ACCESS (aller plus loin)

Objectif : afficher sous forme tabulaire les entrées/sorties et la valorisation du stock.

Niveau requis : avancé.

Commentez cet article : 5 commentaires

Article lu fois.

L'auteur

Denis Hulo

L'article

Publié le 7 mars 2018

Public visé : confirmé

Version PDF Version hors-ligne

ePub, Azw et Mobi

Liens sociaux

I. Introduction▲

On se propose de calculer et d'afficher dans des tableaux les coûts moyens des sorties de stock, en détaillant les quantités et prix unitaires impliqués dans ces calculs, suivant les trois méthodes généralement utilisées en comptabilité :

- premier entré, premier sorti (FIFO) ;
- dernier entré, premier sorti (LIFO) ;
- le coût moyen unitaire pondéré, recalculé après chaque entrée (CMUP).

Pour ce faire, on se basera sur le travail effectué par Claude Leloup dans son article Évaluation des stocks avec ACCESSÉvaluation des stocks avec ACCESS.

II. Entrées et sorties du stock▲

Nous aurons besoin tout d'abord de sélectionner les entrées de stock à l'aide d'une requête rEntrees et les sorties grâce à une autre nommée rSorties.

Il nous suffit pour cela de filtrer les données de la table tMouvementsPK sur le champ Mvt :

mouvements en entrée : Mvt>0 ;
mouvements en sortie : Mvt<0.

III. Requêtes sélection basées sur les entrées et sorties du stock▲

III-A. rQteSortiesFIFO▲

On relie les entrées et sorties sur le champ identifiant le produit en ne conservant pour chaque sortie du produit que les entrées antérieures à elle.

Cette requête va nous permettre d'évaluer, suivant la méthode FIFO, pour chaque entrée associée à une sortie, la quantité que l'on retire pour cette sortie.

Voici le détail des données et des résultats par mouvement en entrée, accompagné de la formule associée au PU en sortie :

Voici la requête en mode création :

Filtres sur les données

On filtre sur le produit choisi dans le formulaire fParamPeriode.
On filtre sur la date de sortie en fonction du choix effectué dans ce formulaire.
On affiche, pour chaque sortie, uniquement les entrées antérieures à elle.

Critères correspondants

Comme VraiFaux([Forms]![fParamPeriode]![ParamProduit]="[Tous]";"*";[Forms]![fParamPeriode]![ParamProduit]).
[rSorties].MvtDate between [Forms]![fParamPeriode]![ParamDateDebut] and [Forms]![fParamPeriode]![ParamDateFin].
rEntrees.tMouvementsPK < [rSorties].[tMouvementsPK].

Champs calculés

IndiceSortie : renvoie l'indice de sortie en comptant le nombre de sorties depuis la date entrée en paramètre ([Forms]![fParamPeriode]![ParamDate]).
QteSortie : renvoie la quantité sortie pour l'entrée affichée.
FormulePUSortie : donne la formule utilisée pour le calcul du prix unitaire en sortie.

Formules des champs calculés

Sélectionnez

IndiceSortie: "S" & Format(CpteDom("*";"rSorties";"tProduitsFK=" & [rSorties].[tProduitsFK] & " and tMouvementsPK<=" & [rSorties].[tMouvementsPK] & " and MvtDate>=#" & Format([Forms]![fParamPeriode]![ParamDateDebut];"mm/jj/aaaa") & "#");"00")
QteSortie: CalculerQteSortieFiFo([rEntrees].[tProduitsFK];[rEntrees].[tMouvementsPK];[rSorties].[tMouvementsPK];-[rSorties].[mvt])
FormulePUSortie: FormulePUSortieFiFo([rSorties].[tProduitsFK];[rSorties].[tMouvementsPK];-[rSorties].[mvt])

Fonctions utilisées dans les champs calculés

CalculerQteSortieFifo : utilisée pour le calcul de la quantité en sortie pour chaque entrée de produit impliquée dans le calcul.
FormulePUSortieFifo : utilisée pour générer la formule employée pour le calcul de la quantité en sortie.

fonction CalculerQteSortieFifo

Cacher/Afficher le codeSélectionnez

Public Function CalculerQteSortieFifo(Produit As Long, _
                      NumMVTEntree As Long, NumMVTSortie As Long, Nombre As Double) As Double
  Dim oRst As Recordset
  Dim dStockAVentiler As Double, dStockRecalcule As Double
  Dim sSQL As String
  Dim dResteAImputer As Double
  'Calcul du stock avant la sortie
  dStockAVentiler = -Nz(DSum("Mvt", "tMouvements", "tMouvementsPK<" & NumMVTSortie _
            & " AND (tProduitsFK=" & Produit & ") AND (Mvt<0)"), 0)
  'Création du recordset des entrées
  sSQL = "SELECT Mvt, PUEntree FROM tMouvements " _
            & "WHERE tMouvementsPK<=" & NumMVTEntree _
            & " AND tProduitsFK=" & Produit _
            & " AND Mvt>0;"
  Set oRst = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)
  'Ventilation du stock par poste d'entrée
  'À ce stade, on se trouve sur l'entrée la plus ancienne encore provisionnée
  'On va émarger la sortie sur ce poste et les suivants (si nécessaire)
   Do While (dStockAVentiler > oRst("Mvt"))
      dStockAVentiler = dStockAVentiler - oRst("Mvt")
      oRst.MoveNext
      If oRst.EOF Then Exit Do
   Loop
  
  'Valorisation de la sortie FIFO
   dResteAImputer = Nombre
     
   If Not oRst.EOF Then
      dStockRecalcule = oRst("Mvt") - dStockAVentiler
     
      Do While Not oRst.EOF
      
         If dResteAImputer > dStockRecalcule Then ' Si le reste à imputer est supérieur à la qté calculée
           CalculerQteSortieFifo = dStockRecalcule ' On affecte à la fonction la qté calculée
           dResteAImputer = dResteAImputer - dStockRecalcule ' On la déduit du reste à imputer
           oRst.MoveNext
    
           If Not oRst.EOF Then dStockRecalcule = oRst("Mvt")
        
         Else ' Sinon on affecte le reste à imputer.
           CalculerQteSortieFifo = dResteAImputer
           oRst.MoveNext
           Exit Do
         End If
    
     Loop
     
   End If
     
    'S'il reste un enregistrement, comme tout a déjà été imputé, on le met à 0
   If Not oRst.EOF Then
      CalculerQteSortieFifo = 0
   End If
     
  'Libérer la mémoire
  oRst.Close
  Set oRst = Nothing
End Function

fonction FormulePUSortieFifo

Cacher/Afficher le codeSélectionnez

Public Function FormulePUSortieFifo(Produit As Long, _
                      NumMVT As Long, Nombre As Double) As String
  ' On détermine la formule employée pour le calcul du PU en sortie
  Dim oRst As Recordset
  Dim dStockAVentiler As Double, dStockRecalcule As Double
  Dim sSQL As String
  Dim dResteAImputer As Double
  'Calcul du débit avant la sortie
  dStockAVentiler = -Nz(DSum("Mvt", "tMouvements", "tMouvementsPK<" & NumMVT _
            & " AND (tProduitsFK=" & Produit & ") AND (Mvt<0)"), 0)
  'Création du recordset des entrées
  sSQL = "SELECT Mvt, PUEntree FROM tMouvements " _
            & "WHERE tMouvementsPK<" & NumMVT _
            & " AND tProduitsFK=" & Produit _
            & " AND Mvt>0;"
  Set oRst = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)
  'Ventilation du stock par poste d'entrée

  'À ce stade, on se trouve sur l'entrée la plus ancienne encore provisionnée
  'On va émarger la sortie sur ce poste et les suivants (si nécessaire)
  Do While (dStockAVentiler > oRst("Mvt"))
     dStockAVentiler = dStockAVentiler - oRst("Mvt")
     oRst.MoveNext
  Loop
  
  'Valorisation de la sortie FIFO
  dResteAImputer = Nombre
  dStockRecalcule = oRst("Mvt") - dStockAVentiler
  
  Do While Not oRst.EOF
    
    If dResteAImputer > dStockRecalcule Then
        FormulePUSortieFifo = FormulePUSortieFifo _
                             & "(" & Round(dStockRecalcule, 2) & "*" & Round(oRst("PUEntree"), 2) & ") + " ' (Qté Sortie * PU Entrée)
        dResteAImputer = dResteAImputer - dStockRecalcule
        oRst.MoveNext
        dStockRecalcule = oRst("Mvt")
      Else
        FormulePUSortieFifo = FormulePUSortieFifo _
                             & "(" & Round(dResteAImputer, 2) & "*" & Round(oRst("PUEntree"), 2) & ")" ' (Qté Sortie * PU Entrée)
        Exit Do ' On sort de la boucle
    End If
  Loop
  'Formule sortie FIFO
  ' Si la formule est composée d'au moins 2 termes, on encadre la formule avec des parenthèses
     If InStr(FormulePUSortieFifo, "+") <> 0 Then
        FormulePUSortieFifo = "(" & FormulePUSortieFifo & ")" & "/" & Nombre
     Else ' Sinon
        FormulePUSortieFifo = FormulePUSortieFifo & "/" & Nombre
     End If
  'Libérer la mémoire
  oRst.Close
  Set oRst = Nothing
End Function

Function FormulePUSortieLifo(Produit As Long, NumMVT As Long, Nombre As Double) As String
  ' On détermine la formule employée pour le calcul du PU en sortie
  Dim oRst As Recordset
  Dim dResteAImputer As Double
  Dim sSQL As String
  Dim dCumulSorties As Double
  'Créer le recordset pour lire les mouvements qui précèdent
  sSQL = "SELECT Mvt, PUEntree FROM tMouvements" _
        & " WHERE tMouvementsPK <" & NumMVT & " And tProduitsFK =" & Produit & ";"
  Set oRst = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset)
  oRst.MoveLast
  dResteAImputer = Nombre
  Do While Not oRst.EOF
    If oRst("Mvt") < 0 Then
        'Sortie => il faut cumuler pour l'imputer à l'entrée qui précède
        dCumulSorties = dCumulSorties + oRst("Mvt")
      Else
        'C'est donc une entrée.
        'Reste-t-il du disponible ?
        If oRst("Mvt") + dCumulSorties > 0 Then
        'si oui, de 2 choses l'une :
        '- ou bien elle suffit pour alimenter la sortie
            If dResteAImputer <= oRst("Mvt") + dCumulSorties Then
                FormulePUSortieLifo = FormulePUSortieLifo & _
                              "(" & Round(dResteAImputer, 2) & "*" & Round(oRst("PUEntree"), 2) & ")" ' (Qté Sortie * PU Entrée)
                Exit Do
        '- ou bien,il n'y a pas encore assez et il faut continuer
              Else
                FormulePUSortieLifo = FormulePUSortieLifo & _
                              "(" & Round((oRst("Mvt") + dCumulSorties), 2) & "*" & Round(oRst("PUEntree"), 2) & ") + " ' (Qté Sortie * PU Entrée)
                dResteAImputer = dResteAImputer - (oRst("Mvt") + dCumulSorties)
               dCumulSorties = 0
            End If
          Else
        'Sinon, il faut retenir le solde négatif dans dSortiesImputées et passer au suivant
            dCumulSorties = oRst("Mvt") + dCumulSorties
        End If
    End If
    oRst.MovePrevious
  Loop
   'Formule sortie LIFO
     If InStr(FormulePUSortieLifo, "+") <> 0 Then ' Si la formule est composée d'au moins 2 termes, on encadre la formule avec des parenthèses
        FormulePUSortieLifo = "(" & FormulePUSortieLifo & ")" & "/" & Nombre
     Else ' Sinon
        FormulePUSortieLifo = FormulePUSortieLifo & "/" & Nombre
     End If
  'Libérer la mémoire
  oRst.Close
  Set oRst = Nothing
End Function

III-B. rQteSortiesLIFO▲

On relie les entrées et sorties sur le champ identifiant le produit, en ne conservant pour chaque sortie de produit que les entrées antérieures à elle.

Cette requête va nous permettre de calculer, suivant la méthode LIFO, pour chaque entrée associée à une sortie, la quantité que l'on retire pour cette sortie.

Voici le détail des données et résultats par mouvement en entrée accompagné de la formule associée au PU en sortie :

Voici la requête en mode création :

Filtres sur les données

On filtre sur le produit choisi dans le formulaire fParamaPeriode.
On filtre sur la date de sortie en fonction du choix effectué dans ce formulaire.
On affiche, pour chaque sortie, uniquement les entrées antérieures à elle.

Critères correspondants

Comme VraiFaux([Forms]![fParamPeriode]![ParamProduit]="[Tous]";"*";[Forms]![fParamPeriode]![ParamProduit]).
[rSorties].MvtDate between [Forms]![fParamPeriode]![ParamDateDebut] and [Forms]![fParamPeriode]![ParamDateFin].
rEntrees.tMouvementsPK < [rSorties].[tMouvementsPK].

Champs calculés

IndiceSortie : renvoie l'indice de sortie en comptant le nombre de sorties depuis la date entrée en paramètre ([Forms]![fParamPeriode]![ParamDate]).
QteSortie : renvoie la quantité sortie pour l'entrée affichée.
FormulePUSortie : donne la formule utilisée pour le calcul du prix unitaire en sortie.

Formules des champs calculés

Sélectionnez

IndiceSortie: "S" & Format(CpteDom("*";"rSorties";"tProduitsFK=" & [rSorties].[tProduitsFK] & " and tMouvementsPK<=" & [rSorties].[tMouvementsPK] & " and MvtDate>=#" & Format([Forms]![fParamPeriode]![ParamDateDebut];"mm/jj/aaaa") & "#");"00")
QteSortie: CalculerQteSortieLiFo([rEntrees].[tProduitsFK];[rEntrees].[tMouvementsPK];[rSorties].[tMouvementsPK];-[rSorties].[mvt])
FormulePUSortie: FormulePUSortieLiFo([rSorties].[tProduitsFK];[rSorties].[tMouvementsPK];-[rSorties].[mvt])

Fonctions utilisées dans les champs calculés

CalculerQteSortieLifo : utilisée pour le calcul de la quantité en sortie pour chaque entrée de produit impliquée dans le calcul.
FormulePUSortieLifo : utilisée pour générer la formule employée pour le calcul de la quantité en sortie.

fonction CalculerQteSortieLifo

Cacher/Afficher le codeSélectionnez

Public Function CalculerQteSortieLifo(Produit As Long, _
                       NumMVTEntree As Long, NumMVTSortie As Long, Nombre As Double) As Double
  Dim oRst As Recordset
  Dim dCumulSorties As Double
  Dim sSQL As String
  Dim dResteAImputer As Double, NumMVT As Long
  'Création du recordset des entrées/sorties
  sSQL = "SELECT tMouvementsPK, Mvt, PUEntree FROM tMouvements " _
            & "WHERE tMouvementsPK<" & NumMVTSortie _
            & " AND tMouvementsPK>=" & NumMVTEntree _
            & " AND tProduitsFK=" & Produit

  Set oRst = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)
  
  'On se positionne sur le dernier enregistrement en entrée/sortie
  oRst.MoveLast
  
  'Valorisation de la sortie LIFO
  dResteAImputer = Nombre
  dCumulSorties = 0
  
   Do While Not oRst.BOF ' On parcourt la liste des mouvements du dernier au premier
  
    If oRst("Mvt") > 0 Then
        'C'est donc une entrée.
        'Reste-t-il du disponible ?
        If oRst("Mvt") + dCumulSorties > 0 Then
        'si oui, de 2 choses l'une :
        '- ou bien elle suffit pour alimenter la sortie
            If dResteAImputer <= (oRst("Mvt") + dCumulSorties) Then
                CalculerQteSortieLifo = (dResteAImputer)
                NumMVT = oRst("tMouvementsPK")
                Exit Do
        '- ou bien,il n'y a pas encore assez et il faut continuer
              Else
                CalculerQteSortieLifo = oRst("Mvt") + dCumulSorties
                dResteAImputer = dResteAImputer - (oRst("Mvt") + dCumulSorties)
                dCumulSorties = 0
                NumMVT = oRst("tMouvementsPK")
            End If
        Else
        'Sinon, il faut retenir le solde négatif dans dCumulSorties et passer au suivant
            dCumulSorties = dCumulSorties + oRst("Mvt")
        End If
    Else
       dCumulSorties = dCumulSorties + oRst("Mvt")
    End If
    
    oRst.MovePrevious ' On passe au précédent
    
  Loop
  
     If NumMVT <> NumMVTEntree Then ' Si on n'est pas sorti sur l'entrée à valoriser
        CalculerQteSortieLifo = 0
     End If
  
  'Libérer la mémoire
  oRst.Close
  Set oRst = Nothing
End Function

fonction FormulePUSortieLifo

Cacher/Afficher le codeSélectionnez

Public Function FormulePUSortieFifo(Produit As Long, _
                      NumMVT As Long, Nombre As Double) As String
  ' On détermine la formule employée pour le calcul du PU en sortie
  Dim oRst As Recordset
  Dim dStockAVentiler As Double, dStockRecalcule As Double
  Dim sSQL As String
  Dim dResteAImputer As Double
  'Calcul du débit avant la sortie
  dStockAVentiler = -Nz(DSum("Mvt", "tMouvements", "tMouvementsPK<" & NumMVT _
            & " AND (tProduitsFK=" & Produit & ") AND (Mvt<0)"), 0)
  'Création du recordset des entrées
  sSQL = "SELECT Mvt, PUEntree FROM tMouvements " _
            & "WHERE tMouvementsPK<" & NumMVT _
            & " AND tProduitsFK=" & Produit _
            & " AND Mvt>0;"
  Set oRst = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)
  'Ventilation du stock par poste d'entrée

  'À ce stade, on se trouve sur l'entrée la plus ancienne encore provisionnée
  'On va émarger la sortie sur ce poste et les suivants (si nécessaire)
  Do While (dStockAVentiler > oRst("Mvt"))
     dStockAVentiler = dStockAVentiler - oRst("Mvt")
     oRst.MoveNext
  Loop
  
  'Valorisation de la sortie FIFO
  dResteAImputer = Nombre
  dStockRecalcule = oRst("Mvt") - dStockAVentiler
  
  Do While Not oRst.EOF
    
    If dResteAImputer > dStockRecalcule Then
        FormulePUSortieFifo = FormulePUSortieFifo _
                             & "(" & Round(dStockRecalcule, 2) & "*" & Round(oRst("PUEntree"), 2) & ") + " ' (Qté Sortie * PU Entrée)
        dResteAImputer = dResteAImputer - dStockRecalcule
        oRst.MoveNext
        dStockRecalcule = oRst("Mvt")
      Else
        FormulePUSortieFifo = FormulePUSortieFifo _
                             & "(" & Round(dResteAImputer, 2) & "*" & Round(oRst("PUEntree"), 2) & ")" ' (Qté Sortie * PU Entrée)
        Exit Do ' On sort de la boucle
    End If
  Loop
  'Formule sortie FIFO
  ' Si la formule est composée d'au moins 2 termes, on encadre la formule avec des parenthèses
     If InStr(FormulePUSortieFifo, "+") <> 0 Then
        FormulePUSortieFifo = "(" & FormulePUSortieFifo & ")" & "/" & Nombre
     Else ' Sinon
        FormulePUSortieFifo = FormulePUSortieFifo & "/" & Nombre
     End If
  'Libérer la mémoire
  oRst.Close
  Set oRst = Nothing
End Function

Function FormulePUSortieLifo(Produit As Long, NumMVT As Long, Nombre As Double) As String
  ' On détermine la formule employée pour le calcul du PU en sortie
  Dim oRst As Recordset
  Dim dResteAImputer As Double
  Dim sSQL As String
  Dim dCumulSorties As Double
  'Créer le recordset pour lire les mouvements qui précèdent
  sSQL = "SELECT Mvt, PUEntree FROM tMouvements" _
        & " WHERE tMouvementsPK <" & NumMVT & " And tProduitsFK =" & Produit & ";"
  Set oRst = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset)
  oRst.MoveLast
  dResteAImputer = Nombre
  Do While Not oRst.EOF
    If oRst("Mvt") < 0 Then
        'Sortie => il faut cumuler pour l'imputer à l'entrée qui précède
        dCumulSorties = dCumulSorties + oRst("Mvt")
      Else
        'C'est donc une entrée.
        'Reste-t-il du disponible ?
        If oRst("Mvt") + dCumulSorties > 0 Then
        'si oui, de 2 choses l'une :
        '- ou bien elle suffit pour alimenter la sortie
            If dResteAImputer <= oRst("Mvt") + dCumulSorties Then
                FormulePUSortieLifo = FormulePUSortieLifo & _
                              "(" & Round(dResteAImputer, 2) & "*" & Round(oRst("PUEntree"), 2) & ")" ' (Qté Sortie * PU Entrée)
                Exit Do
        '- ou bien,il n'y a pas encore assez et il faut continuer
              Else
                FormulePUSortieLifo = FormulePUSortieLifo & _
                              "(" & Round((oRst("Mvt") + dCumulSorties), 2) & "*" & Round(oRst("PUEntree"), 2) & ") + " ' (Qté Sortie * PU Entrée)
                dResteAImputer = dResteAImputer - (oRst("Mvt") + dCumulSorties)
               dCumulSorties = 0
            End If
          Else
        'Sinon, il faut retenir le solde négatif dans dSortiesImputées et passer au suivant
            dCumulSorties = oRst("Mvt") + dCumulSorties
        End If
    End If
    oRst.MovePrevious
  Loop
   'Formule sortie LIFO
     If InStr(FormulePUSortieLifo, "+") <> 0 Then ' Si la formule est composée d'au moins 2 termes, on encadre la formule avec des parenthèses
        FormulePUSortieLifo = "(" & FormulePUSortieLifo & ")" & "/" & Nombre
     Else ' Sinon
        FormulePUSortieLifo = FormulePUSortieLifo & "/" & Nombre
     End If
  'Libérer la mémoire
  oRst.Close
  Set oRst = Nothing
End Function

III-C. rPUSortiesCMUP▲

Cette requête basée sur les sorties de stock va nous permettre de calculer, suivant la méthode CMUP, le prix unitaire du produit pour chaque sortie.

Voici des exemples de valeurs obtenues pour le prix unitaire de chaque sortie de stock suivant la méthode CMUP :

Requête rQteSortiesCMUP — Requête rPUSortiesCMUP

Voici la requête en mode création :

Filtres sur les données

On filtre sur le produit choisi dans le formulaire fParamPeriode.
On filtre sur la date de sortie en fonction du choix effectué dans ce formulaire.

Critères correspondants

Comme VraiFaux([Forms]![fParamPeriode]![ParamProduit]="[Tous]";"*";[Forms]![fParamPeriode]![ParamProduit]).
[rSorties].MvtDate between [Forms]![fParamPeriode]![ParamDateDebut] and [Forms]![fParamPeriode]![ParamDateFin].

Champs calculés

IndiceSortie : renvoie l'indice de sortie en comptant le nombre de sorties depuis la date entrée en paramètre ([Forms]![fParamPeriode]![ParamDate]).
PUCMUP : renvoie le prix unitaire moyen pondéré (CMUP) fonction des mouvements antérieurs.

Formules des champs calculés

Sélectionnez

IndiceSortie: "S" & Format(CpteDom("*";"rSorties";"tProduitsFK=" & [rSorties].[tProduitsFK] & " and tMouvementsPK<=" & [rSorties].[tMouvementsPK] & " and MvtDate>=#" & Format([Forms]![fParamPeriode]![ParamDateDebut];"mm/jj/aaaa") & "#");"00")
PUCMUP: CalculerPUSortieCMUP([tProduitsFK];[tMouvementsPK])

Fonction utilisée dans le champ calculé

CalculerPUSortieCMUP : utilisée pour calculer le prix unitaire en sortie suivant la méthode CMUP.

fonction CalculerPUSortieCMUP

Cacher/Afficher le codeSélectionnez

Public Function CalculerPUSortieCMUP(Produit As Long, _
                      NumMVT As Long) As Double
  ' Évaluation du prix unitaire en sortie suivant la méthode CMUP
  Dim oRst As Recordset
  Dim dStockValeur As Double, dStockQte As Double, dStockPU As Double
  Dim sSQL As String
   
  'Création du recordset des entrées
  sSQL = "SELECT Mvt, PUEntree FROM tMouvements " _
            & "WHERE tMouvementsPK<" & NumMVT _
            & " AND tProduitsFK=" & Produit
           
  Set oRst = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)
  
  'Valorisation de la sortie CMUP

  Do While Not oRst.EOF
    
     If oRst("Mvt") > 0 Then ' Si le mouvement est une entrée
         dStockValeur = dStockValeur + oRst("Mvt") * oRst("PUEntree") ' Valeur du stock
         dStockQte = dStockQte + oRst("Mvt") ' Quantité en stock
         dStockPU = dStockValeur / dStockQte ' Prix Unitaire CMUP
     Else ' Si c'est une sortie
        dStockValeur = dStockValeur + oRst("Mvt") * dStockPU ' Valeur du stock
        dStockQte = dStockQte + oRst("Mvt") ' Quantité en stock
     End If
     
     oRst.MoveNext
     
  Loop
  
  'Prix unitaire sortie CMUP
  CalculerPUSortieCMUP = dStockPU
  'Libérer la mémoire
  oRst.Close
  Set oRst = Nothing
End Function

IV. Requêtes analyse croisée▲

IV-A. racFIFO▲

On croise les données issues de la requête rQteSortiesFIFO avec les entrées en entêtes de ligne, les indices des sorties en colonnes, et les quantités sorties à l'intersection.

Voici le détail de la requête en mode création :

formule du champ valeur

Sélectionnez

TotSortieFIFO: Somme(VraiFaux([QteSortie]=0;Null;[QteSortie]))

IV-A-1. Gros plan sur le champ IndiceSortie▲

Ce champ permet de générer des entêtes de colonne fixes en figeant les noms des sorties avec les termes successifs « S01 »,« S02 »,« S03 », …,« S10 » appliqués à la propriété « entêtes des colonnes ».

Image non disponible — entêtes des colonnes

IV-B. racLIFO▲

On croise les données issues de la requête rQteSortiesLIFO avec les entrées en entêtes de ligne, les indices des sorties en colonnes, et les quantités sorties à l'intersection.

Voici le détail de la requête en mode création :

formule du champ valeur

Sélectionnez

TotSortieLIFO: Somme(VraiFaux([QteSortie]=0;Null;[QteSortie]))

IV-B-1. Gros plan sur le champ IndiceSortie▲

IV-C. racCMUP▲

On croise les données issues de la requête rPUSortiesCMUP avec les sorties en entêtes de lignes, les indices des sorties en colonne, et les quantités sorties à l'intersection.

Voici le détail de la requête en mode création :

formule du champ valeur

Sélectionnez

TotSortieCMUP: Somme(VraiFaux([MvtSortie]=0;Null;[MvtSortie]))

IV-C-1. Gros plan sur le champ IndiceSortie▲

V. Formulaire de paramétrage des tableaux▲

Il va servir à définir la période de filtrage des sorties et le ou les produits à afficher sur les tableaux de synthèse.

V-A. Bouton de commande pour l'ouverture des états▲

Permet d'afficher la synthèse des sorties suivant la méthode FIFO, LIFO ou CMUP et la période choisies sur le formulaire.

Code sur l'événement clic du bouton CmdValider

Sélectionnez

Private Sub CmdValider_Click()
' On ouvre le tableau de synthèse de la valorisation des stocks suivant la méthode choisie dans la liste

DoCmd.Minimize ' On minimise le formulaire de paramétrage
   
Select Case Me.ParamMethode.Value
   
Case "FIFO" ' Si la méthode FIFO a été choisie
DoCmd.Close acReport, "eFIFO" ' On ferme l'état si déjà ouvert
DoCmd.OpenReport "eFIFO", acViewPreview ' On ouvre l'état en mode aperçu avant impression
   
Case "LIFO"
DoCmd.Close acReport, "eLIFO"
DoCmd.OpenReport "eLIFO", acViewPreview
   
Case "CMUP"
DoCmd.Close acReport, "eCMUP"
DoCmd.OpenReport "eCMUP", acViewPreview
   
End Select
   
End Sub

V-B. Boutons de commandes pour se déplacer d'une période à l'autre▲

Permet de se déplacer dans le temps et d'afficher l'état correspondant à la période choisie.

La base jointe au format accdb dispose en plus de boutons de commande sur le ruban lié aux états pour se déplacer dans le temps.

V-B-1. Bouton CmdPremier▲

Il permet d'afficher les premières sorties sur les tableaux de synthèse.

Procédure événementielle sur clic

Sélectionnez

Private Sub CmdPremier_Click()
' On passe aux premières sorties
CmdPrem ' Exécution de la procédure CmdPrem
End Sub

Procédure CmdPrem

Cacher/Afficher le codeSélectionnez

Public Sub CmdPrem()
' Commande l'affichage des premières sorties sur les tableaux de synthèse
Dim datDebut As Date, datFin As Date

datDebut = DMin("MvtDate", "rSorties") ' Plus ancienne date de sortie
   
   If (Forms!fParamPeriode.ParamProduit = "[Tous]") Then ' Si tous les produits
      datFin = EvaluerDateFin(0, datDebut) ' Évaluation de la date de fin de la période
   Else ' Sinon
      datFin = EvaluerDateFin(Forms!fParamPeriode.ParamProduit, datDebut) ' Évaluation de la date de fin de la période
   End If
   
' Affectation des dates aux zones de texte
Forms!fParamPeriode.ParamDateDebut = datDebut
Forms!fParamPeriode.ParamDateFin = datFin

DoCmd.Minimize ' On minimise le formulaire de paramétrage
   
'Ouverture de l'état correspondant à la méthode choisie
Select Case Forms!fParamPeriode.ParamMethode.Value
   
Case "FIFO"
DoCmd.Close acReport, "eFIFO"
DoCmd.OpenReport "eFIFO", acViewPreview
   
Case "LIFO"
DoCmd.Close acReport, "eLIFO"
DoCmd.OpenReport "eLIFO", acViewPreview
   
Case "CMUP"
DoCmd.Close acReport, "eCMUP"
DoCmd.OpenReport "eCMUP", acViewPreview
   
End Select

End Sub

V-B-2. Bouton CmdPrecedent▲

Il permet d'afficher les sorties précédentes sur les tableaux de synthèse.

Procédure événementielle sur clic

Sélectionnez

Private Sub CmdPrecedent_Click()
' On passe aux sorties précédentes
CmdPrec ' Exécution de la procédure CmdPrec
End Sub

Procédure CmdPrec

Cacher/Afficher le codeSélectionnez

Public Sub CmdPrec()
' Commande l'affichage des sorties précédentes sur les tableaux de synthèse
Dim datDebut As Date, datFin As Date

datFin = CDate(Forms!fParamPeriode.ParamDateDebut) - 1 ' Date de fin de la période
   
   If (Forms!fParamPeriode.ParamProduit = "[Tous]") Then ' Si tous les produits
      datDebut = EvaluerDateDebut(0, datFin) ' Évaluation de la date de début de la période
   Else ' Sinon
      datDebut = EvaluerDateDebut(Forms!fParamPeriode.ParamProduit, datFin) ' Évaluation de la date de début de la période
   End If
   
   If IsNull(DLookup("MvtDate", "rSorties", "MvtDate between #" & Format(datDebut, "mm/dd/yyyy") _
                                                          & "# and #" & Format(datFin, "mm/dd/yyyy") & "#")) Then
      ' Si pas de sortie pour cette période
      MsgBox ("Pas de sorties disponibles !")
      Exit Sub
   End If
   
' Affectation des dates aux zones de texte
Forms!fParamPeriode.ParamDateDebut = datDebut
Forms!fParamPeriode.ParamDateFin = datFin

DoCmd.Minimize ' On minimise le formulaire de paramétrage

'Ouverture de l'état correspondant à la méthode choisie
Select Case Forms!fParamPeriode.ParamMethode.Value
   
Case "FIFO"
DoCmd.Close acReport, "eFIFO"
DoCmd.OpenReport "eFIFO", acViewPreview
   
Case "LIFO"
DoCmd.Close acReport, "eLIFO"
DoCmd.OpenReport "eLIFO", acViewPreview
   
Case "CMUP"
DoCmd.Close acReport, "eCMUP"
DoCmd.OpenReport "eCMUP", acViewPreview
      
End Select
      
End Sub

V-B-3. Bouton CmdSuivant▲

Il permet d'afficher les sorties suivantes sur les tableaux de synthèse.

Procédure événementielle sur clic

Sélectionnez

Private Sub CmdSuivant_Click()
' On passe aux sorties suivantes
CmdSuiv ' Exécution de la procédure CmdSuiv
End Sub

Procédure CmdSuiv

Cacher/Afficher le codeSélectionnez

Public Sub CmdSuiv()
' Commande l'affichage des sorties suivantes sur les tableaux de synthèse
Dim datDebut As Date, datFin As Date

datDebut = CDate(Forms!fParamPeriode.ParamDateFin) + 1 ' Date de début de la période
   
   If (Forms!fParamPeriode.ParamProduit = "[Tous]") Then ' Si tous les produits
      datFin = EvaluerDateFin(0, datDebut) ' On évalue la date de fin de la période
   Else ' Sinon
      datFin = EvaluerDateFin(Forms!fParamPeriode.ParamProduit, datDebut) ' On évalue la date de fin de la période
   End If
   
    If IsNull(DLookup("MvtDate", "rSorties", "MvtDate between #" & Format(datDebut, "mm/dd/yyyy") _
                                                          & "# and #" & Format(datFin, "mm/dd/yyyy") & "#")) Then
      ' Si pas de sortie pour cette période
      MsgBox ("Pas de sorties disponibles !")
      Exit Sub
   End If
   
' Affectation des dates aux zones de texte
Forms!fParamPeriode.ParamDateDebut = datDebut
Forms!fParamPeriode.ParamDateFin = datFin

DoCmd.Minimize ' On minimise le formulaire de paramétrage

'Ouverture de l'état correspondant à la méthode choisie
Select Case Forms!fParamPeriode.ParamMethode.Value
   
Case "FIFO"
DoCmd.Close acReport, "eFIFO"
DoCmd.OpenReport "eFIFO", acViewPreview
   
Case "LIFO"
DoCmd.Close acReport, "eLIFO"
DoCmd.OpenReport "eLIFO", acViewPreview
   
Case "CMUP"
DoCmd.Close acReport, "eCMUP"
DoCmd.OpenReport "eCMUP", acViewPreview
   
End Select
   
End Sub

V-B-4. Bouton CmdDernier▲

Il permet d'afficher les dernières sorties sur les tableaux de synthèse.

Procédure événementielle sur clic

Sélectionnez

Private Sub CmdDernier_Click()
' On passe aux dernières sorties
CmdDern ' Exécution de la procédure CmdDern
End Sub

Procédure événementielle sur clic

Cacher/Afficher le codeSélectionnez

Public Sub CmdDern()
' Commande l'affichage des dernières sorties sur les tableaux de synthèse
Dim datDebut As Date, datFin As Date

datFin = DMax("MvtDate", "rSorties") ' Date de la dernière sortie

   If (Forms!fParamPeriode.ParamProduit = "[Tous]") Then ' Si tous les produits
      datDebut = EvaluerDateDebut(0, datFin) ' Évaluation de la date de début de la période
   Else ' Sinon
      datDebut = EvaluerDateDebut(Forms!fParamPeriode.ParamProduit, datFin) ' Évaluation de la date de début de la période
   End If
   
' Affectation des dates aux zones de texte
Forms!fParamPeriode.ParamDateDebut = datDebut
Forms!fParamPeriode.ParamDateFin = datFin

DoCmd.Minimize ' On minimise le formulaire de paramétrage

'Ouverture de l'état correspondant à la méthode choisie
Select Case Forms!fParamPeriode.ParamMethode.Value
   
Case "FIFO"
DoCmd.Close acReport, "eFIFO"
DoCmd.OpenReport "eFIFO", acViewPreview
   
Case "LIFO"
DoCmd.Close acReport, "eLIFO"
DoCmd.OpenReport "eLIFO", acViewPreview
   
Case "CMUP"
DoCmd.Close acReport, "eCMUP"
DoCmd.OpenReport "eCMUP", acViewPreview
      
End Select

End Sub

VI. Tableaux de synthèse des données▲

Nous présentons les données dans des états, sous forme tabulaire avec un regroupement par produit :

en entêtes de ligne les entrées du stock ;
en colonnes les indices des sorties du stock ;
à l'intersection des deux les quantités sorties ;
dans le pied du groupe les valeurs sorties du stock par produit (Qté, PU, Total en sortie).

VI-A. eFIFO▲

Tableau de synthèse de la valorisation des stocks (méthode FIFO) :

Le tri se fait par produit et mouvement en entrée du stock.

VI-A-1. Gros plan sur l'entête de groupe de l'état▲

On réalise dans les états un regroupement des données par produit sur le champ ProduitsFK. On dispose donc d'une section de groupe dans laquelle on peut afficher dans son entête les identifiants des mouvements en sortie (tMouvementsPK) pour chaque produit :

On met à jour les entêtes de colonne du groupe au moyen d'une procédure événementielle exécutée sur Impression de l'entête de groupe.

entêtes de colonne

Étiquettes pour les numéros des mouvements en sortie : « EnteteS01 », « EnteteS02 », …,« EnteteS10 » ;
Étiquettes pour les dates des sorties : « EnteteDS01 », « EnteteDS02 », …,« EnteteDS10 ».

Procédure événementielle sur impression de l'entête de groupe

Cacher/Afficher le codeSélectionnez

Private Sub EntêteGroupe0_Print(Cancel As Integer, PrintCount As Integer)
Dim i As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sSQL As String

Set db = CurrentDb

'Définition du sql permettant de sélectionner pour chaque produit, les mouvements des sorties comprises dans la période saisie en paramètre.
sSQL = "select tMouvementsPK, MvtDate from rSorties " & _
       "where (tProduitsFK=" & [tProduitsFK] & ") and MvtDate between #" & Format(Forms!fParamPeriode!ParamDateDebut, "mm/dd/yyyy") & "# and #" & Format(Forms!fParamPeriode!ParamDateFin, "mm/dd/yyyy") & "# " & _
       "order by tMouvementsPK;"

Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot) ' Ouverture du recordset

i = 1

  If Not rs.EOF Then ' Si des sorties ont été trouvées dans cette période
     Do While (i <= 10) ' On parcourt les entêtes
        ' Mise à jour des entêtes (identifiants et dates des sorties)
        Me("EnteteS" & Format(i, "00")).Caption = rs!tMouvementsPK
        Me("EnteteDS" & Format(i, "00")).Caption = rs!MvtDate
        rs.MoveNext: i = i + 1 'Sortie suivante
        If rs.EOF Then Exit Do
     Loop
  End If
  
  Do While (i <= 10) ' On vide les entêtes supplémentaires
     Me("EnteteS" & Format(i, "00")).Caption = ""
     Me("EnteteDS" & Format(i, "00")).Caption = ""
     i = i + 1
  Loop
  
'Fermeture et libération des variables
rs.Close
Set rs = Nothing

Set db = Nothing

End Sub

VI-A-2. Gros plan sur le pied de groupe de l'état▲

On réalise dans les états un regroupement des données par produit sur le champ ProduitsFK. On dispose donc d'un pied de groupe dans lequel on peut afficher les valeurs du stock (Qté, PU, Total) pour chaque sortie de produit :

Formules employées pour calculer les différentes valeurs en sortie de stock suivant la méthode FIFO :

QteSortie_i :« =Somme([S0i]) » ;
PUSortie_i :« =Somme([S0i]*[PUEntree])/Somme([S0i]) » ;
Total_i :=« Somme([S0i]*[PUEntree]) ».

i désigne l'indice de la colonne de sortie, il est compris entre 1 et 10 dans notre cas.

VI-B. eLIFO▲

Tableau de synthèse de la valorisation des stocks (méthode LIFO) :

Le tri se fait par produit et mouvement en entrée du stock.

VI-B-1. Gros plan sur l'entête de groupe de l'état▲

On met à jour les entêtes de colonne du groupe au moyen d'une procédure événementielle exécutée sur Impression de l'entête de groupe.

entêtes de colonne

Étiquettes pour les numéros des mouvements en sortie : « EnteteS01 », « EnteteS02 », …,« EnteteS10 » ;
Étiquettes pour les dates des sorties : « EnteteDS01 », « EnteteDS02 », …,« EnteteDS10 ».

Procédure événementielle sur impression de l'entête de groupe

Cacher/Afficher le codeSélectionnez

Private Sub EntêteGroupe0_Print(Cancel As Integer, PrintCount As Integer)
Dim i As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sSQL As String

Set db = CurrentDb

'Définition du sql permettant de sélectionner pour chaque produit, les mouvements des sorties comprises dans la période saisie en paramètre.
sSQL = "select tMouvementsPK, MvtDate from rSorties " & _
       "where (tProduitsFK=" & [tProduitsFK] & ") and MvtDate between #" & Format(Forms!fParamPeriode!ParamDateDebut, "mm/dd/yyyy") & "# and #" & Format(Forms!fParamPeriode!ParamDateFin, "mm/dd/yyyy") & "# " & _
       "order by tMouvementsPK;"

Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot) ' Ouverture du recordset

i = 1

  If Not rs.EOF Then ' Si des sorties ont été trouvées dans cette période
     Do While (i <= 10) ' On parcourt les entêtes
        ' Mise à jour des entêtes (identifiants et dates des sorties)
        Me("EnteteS" & Format(i, "00")).Caption = rs!tMouvementsPK
        Me("EnteteDS" & Format(i, "00")).Caption = rs!MvtDate
        rs.MoveNext: i = i + 1 'Sortie suivante
        If rs.EOF Then Exit Do
     Loop
  End If
  
  Do While (i <= 10) ' On vide les entêtes supplémentaires
     Me("EnteteS" & Format(i, "00")).Caption = ""
     Me("EnteteDS" & Format(i, "00")).Caption = ""
     i = i + 1
  Loop
  
'Fermeture et libération des variables
rs.Close
Set rs = Nothing

Set db = Nothing

End Sub

VI-B-2. Gros plan sur le pied de groupe de l'état▲

Formules employées pour calculer les différentes valeurs en sortie de stock suivant la méthode LIFO :

QteSortie_i :« =Somme([S0i]) » ;
PUSortie_i :« =Somme([S0i]*[PUEntree])/Somme([S0i]) » ;
Total_i :=« Somme([S0i]*[PUEntree]) ».

i désigne l'indice de la colonne de sortie, il est compris entre 1 et 10 dans notre cas.

VI-C. eCMUP▲

Tableau de synthèse de la valorisation des stocks (méthode CMUP) :

Le tri se fait par produit et mouvement de sortie du stock.

VI-C-1. Gros plan sur l'entête de groupe de l'état▲

On réalise dans l'état un regroupement des données par produit sur le champ ProduitsFK. On dispose donc d'une section de groupe dans laquelle on peut afficher dans son entête les indices des mouvements en sortie pour chaque produit :

On met à jour les entêtes de colonne du groupe au moyen d'une procédure événementielle exécutée sur Impression de l'entête de groupe.

entêtes de colonne

Étiquettes pour les indices des mouvements en sortie : « EnteteS01 », « EnteteS02 », …,« EnteteS10 » ;
Étiquettes pour les dates des sorties : « EnteteDS01 », « EnteteDS02 », …,« EnteteDS10 ».

Procédure événementielle sur impression de l'entête de groupe

Cacher/Afficher le codeSélectionnez

Private Sub EntêteGroupe0_Print(Cancel As Integer, PrintCount As Integer)
Dim i As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sSQL As String

Set db = CurrentDb

'Définition du sql permettant de sélectionner pour chaque produit, les mouvements des sorties comprises dans la période saisie en paramètre.
sSQL = "select tMouvementsPK, MvtDate from rSorties " & _
       "where (tProduitsFK=" & [tProduitsFK] & ") and MvtDate between #" & Format(Forms!fParamPeriode!ParamDateDebut, "mm/dd/yyyy") & "# and #" & Format(Forms!fParamPeriode!ParamDateFin, "mm/dd/yyyy") & "# " & _
       "order by tMouvementsPK;"

Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot) ' Ouverture du recordset

i = 1

  If Not rs.EOF Then ' Si des sorties ont été trouvées dans cette période
     Do While (i <= 10) ' On parcourt les entêtes
         ' Mise à jour des entêtes (indices et dates de sorties)
        Me("EnteteS" & Format(i, "00")).Caption = "S" & Format(i, "00")
        Me("EnteteDS" & Format(i, "00")).Caption = rs!MvtDate
        rs.MoveNext: i = i + 1 'Sortie suivante
        If rs.EOF Then Exit Do
     Loop
  End If
  
  Do While (i <= 10) ' On vide les entêtes supplémentaires
     Me("EnteteS" & Format(i, "00")).Caption = ""
     Me("EnteteDS" & Format(i, "00")).Caption = ""
     i = i + 1
  Loop
  
'Fermeture et libération des variables
rs.Close
Set rs = Nothing

Set db = Nothing

End Sub

VI-C-2. Gros plan sur le pied de groupe de l'état▲

On réalise dans les états un regroupement des données par produit sur le champ ProduitsFK. On dispose donc d'une section de groupe dans laquelle on peut calculer les valeurs du stock (Qté, PU, Total) pour chaque sortie de produit :

Formules employées pour calculer les différentes valeurs en sortie de stock suivant la méthode CMUP :

QteSortie_i:« =Somme([S0i]) » ;
PUSortie_i:« =[PUCMUP] » ;
Total_i:« =Somme([S0i]*[PUCMUP]) ».

i désigne l'indice de la colonne de sortie, il est compris entre 1 et 10 dans notre cas.

VII. Les bases de données à télécharger▲

Les bases jointesstockeval sont au format mdb et accdb. La version accdb utilise le fichier ruban.xml pour afficher les boutons de commande sur le ruban.

VIII. Remerciements▲

Je tiens à remercier Claude Leloup pour m'avoir conseillé pour la réalisation de cet article, ainsi que pour sa relecture.

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants :

Copyright © 2017 Denis Hulo. Aucune reproduction, même partielle, ne peut être faite de ce site ni 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. Droits de diffusion permanents accordés à Developpez LLC.