Contrôler la fiabilité d'un inventaire permanent

Soit les données relevées :

on calcule un taux de référence qui est l'inverse du nombre d'occurrences : 

on calcule alors un indice de la manière suivante :

• si la valeur absolue de l'écart est supérieure à 5 %, l'indice vaut 0 ;
• sinon, l'indice vaut le taux de référence :

et le calcul final :

Au plus le SRA approche les 100 %, au plus la fiabilité est grande.

En d'autres mots, quelles sont les entités de l'univers de l'application ?

- Les articles de l'inventaire.

- L'endroit où ils se trouvent.

- Quand et qui a comparé la quantité renseignée par le logiciel avec celle effectivement sur place.

- L'historique des SRA hebdomadaires avant et après actions correctives.

C'est l'objet du modèle de données présenté au chapitre suivant.

Nous présenterons ensuite :

- les constations des contrôleurs sur place dans un formulaire fRapport ;

- les calculs des SRA hebdomadaires avant et après actions correctives dans un formulaire fCorrection ;

- l'historique dans un troisième formulaire fArchives.

Toutes les relations sont du type un à plusieurs et appliquent l'intégrité référentielle :

Voir Comprendre les jointures dans Access de Maxence Hubiche.

tArchiveSRA : source du formulaire fArchives. Elle sert à stocker le SRA initial et le SRA corrigé de chaque semaine.

tCor : elle intervient dans le processus de correction des SRA hebdomadaires.

tStrucCor : c'est la source du formulaire fCorrection. Cette table est systématiquement vidangée et repeuplée à chaque affichage du formulaire.

Pour plus de détails sur ce genre de formulaire, voir ce tutoriel Formulaire de recherche polyvalent sur la base d'une requête enregistrée.

• Il permet d'ajouter, de modifier, de supprimer des enregistrements de la table tRapports.
• Il est conçu pour opérer des recherches multicritères (partie gris foncé).

Sa source est la requête rFiltreRapports :

SELECT tRapports.tRapportsPK, tRapports.RapDate, tRapports.tEquipesFK, tRapports.tAdressesFK, tRapports.tArticlesFK, tRapports.QPhysique, tRapports.QLog, tRapports.Commentaire, [QPhysique]-[QLog]<>0 AS Expr1
FROM tRapports
WHERE (((tRapports.RapDate)>=IIf(IsNull([Formulaires]![fRapport]![filtreDu]),#1/1/1900#,[Formulaires]![fRapport]![filtreDu]) And (tRapports.RapDate)<=IIf(IsNull([Formulaires]![fRapport]![filtreAu]),#1/1/2100#,[Formulaires]![fRapport]![filtreAu])) AND ((tRapports.tEquipesFK) Like IIf([Formulaires]![fRapport]![filtreEquipe].[ListIndex]=-1,"*" & [Formulaires]![fRapport]![filtreEquipe] & "*",[Formulaires]![fRapport]![filtreEquipe])) AND ((tRapports.tAdressesFK) Like IIf([Formulaires]![fRapport]![filtreAdresse].[ListIndex]=-1,"*" & [Formulaires]![fRapport]![filtreAdresse] & "*",[Formulaires]![fRapport]![filtreAdresse])) AND ((tRapports.tArticlesFK) Like IIf([Formulaires]![fRapport]![filtreReference].[ListIndex]=-1,"*" & [Formulaires]![fRapport]![filtreReference] & "*",[Formulaires]![fRapport]![filtreReference])) AND (([QPhysique]-[QLog]<>0)=Yes Or ([QPhysique]-[QLog]<>0)=No));

Un mot toutefois pour commenter la dernière colonne de la requête :

À première vue, cette instruction est farfelue : on teste si l'écart est différent de zéro… et on prend, quelle que soit la réponse !

C'est une ruse !

La fin du SQL de la requête se présentera comme ceci :

Et pour tenir compte de l'état (cochée ou non) de la case « N'afficher que s'il y a une différence : » (ccDifference), il suffirait de supprimer la partie surlignée en orange :

voici comment on y arrive par le code :

Public Sub MaJ()
'Sub appelée à chaque mise à jour d'un filtre
  Dim q As QueryDef
    'si la case "avec différence" n'est pas cochée, on choisit rFiltreRapports comme source
  If Me.ccDifference = 0 Then 'donc pas cochée
      Me.RecordSource = "rFiltreRapports"
    'si elle est cochée, on corrige le SQL de cette requête pour ne reprendre que les différences <> 0
    Else
      Set q = CurrentDb.QueryDefs("rFiltreRapports")
      Me.RecordSource = Replace(q.SQL, "=Yes Or ([QPhysique]-[QLog]<>0)=No", "")
      Set q = Nothing
  End If
  Me.Requery
End Sub

Explication du code (les nombres en gras indiquent les N° de lignes)

3 : définition d'une variable de type « QueryDef » : la bibliothèque « Microsoft DAO x.x Object Library » doit être ajoutée au projet. Pour ce faire, enfoncez <ALT+F11> pour accéder au code et dans la barre des menus, choisissez Outils>Références… et cherchez la référence dans la liste pour la cocher :

5-6 : si la case n'est pas cochée, on prend rFiltreRapports comme source => les enregistrements sont ramenés quel que soit l'état de la case,

9-11 : si la case est cochée,

9 : on va chercher le SQL de rFiltreRapports,
10 : on y supprime =Yes Or ([QPhysique]-[QLog]<>0=No et on l'affecte comme source,
11 : on libère q.

Ce qui correspond à ceci :

SELECT tRapports.tRapportsPK, tRapports.RapDate, tRapports.tEquipesFK, tRapports.tAdressesFK, tRapports.tArticlesFK, tRapports.QPhysique, tRapports.QLog, tRapports.Commentaire, [QPhysique]-[QLog] AS Expr1
FROM tRapports
WHERE (((tRapports.RapDate)>=IIf(IsNull([Formulaires]![fRapport]![filtreDu]),#1/1/1900#,[Formulaires]![fRapport]![filtreDu]) And (tRapports.RapDate)<=IIf(IsNull([Formulaires]![fRapport]![filtreAu]),#1/1/2100#,[Formulaires]![fRapport]![filtreAu])) AND ((tRapports.tEquipesFK) Like IIf([Formulaires]![fRapport]![filtreEquipe].[ListIndex]=-1,"*" & [Formulaires]![fRapport]![filtreEquipe] & "*",[Formulaires]![fRapport]![filtreEquipe])) AND ((tRapports.tAdressesFK) Like IIf([Formulaires]![fRapport]![filtreAdresse].[ListIndex]=-1,"*" & [Formulaires]![fRapport]![filtreAdresse] & "*",[Formulaires]![fRapport]![filtreAdresse])) AND ((tRapports.tArticlesFK) Like IIf([Formulaires]![fRapport]![filtreReference].[ListIndex]=-1,"*" & [Formulaires]![fRapport]![filtreReference] & "*",[Formulaires]![fRapport]![filtreReference])) AND (([QPhysique]-[QLog])<>0));

qui ramènera uniquement les écarts différents de zéro.

VII-B-1. L'écart s'allume en vert ou en rouge selon qu'il est égal ou non à zéro▲

Pour ce faire, on recourt à la mise en forme conditionnelle :

dans le formulaire en construction, on sélectionne le contrôle ;

dans la barre des menus, on clique sur Format/Mise en forme conditionnelle ;

on complète le tableau qui s'affiche.

VII-B-2. Report automatique▲

Lorsqu'on encode un enregistrement, les valeurs de l'équipe, la date et l'adresse deviennent automatiquement les valeurs par défaut de l'enregistrement suivant à encoder (éventuel).

C'est dans l'événement « Après mise à jour » de ces contrôles que cela se passe : on modifie leur propriété Valeur par défaut à la volée :

 

Sélectionnez

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

Private Sub cboEquipe_AfterUpdate()
  Me.cboEquipe.DefaultValue = Me.cboEquipe
End Sub

Private Sub txtRapDate_AfterUpdate()
  Me.txtRapDate.DefaultValue = "#" & Format(Me.txtRapDate, "mm/dd/yy") & "#"
End Sub

Private Sub CboAdresse_AfterUpdate()
  Me.CboAdresse.DefaultValue = Me.CboAdresse
End Sub

Remarquez la syntaxe :

- si la valeur du champ est de type numérique (ligne 2), on écrit par exemple Me.cboEquipe.DefaultValue = Me.cboEquipe ;

- si c'est une date (ligne 6), il faut la présenter sous le format anglo-saxon (mois, jour, année) et l'encadrer de croisillons « # » ;

- si c'était une valeur de type texte, il faudrait l'encadrer d'apostrophes droites, comme ceci : 

Me.DuTexte.DefaultValue = "'" & Me.DuTexte & "'".

VII-B-3. Choisir l'article selon deux critères▲

L'article peut être choisi indifféremment par sa référence ou sa dénomination.

Cette technique est décrite dans le tutoriel de Pierre Fauconnier : Access : Sélection d'une donnée par l'une ou l'autre de ses propriétés.

VII-B-4. Les contrôles cboRef et txtUnite▲

Examinons comment le contrôle (txtUnite) est alimenté.

D'abord, considérons la liste modifiable cboRef. Elle a quatre colonnes, c'est la deuxième qui apparaitra à l'écran (la 1^re qui n'a pas une largeur de 0 cm) et c'est la 1^re qui est liée (tArticleFK).

Considérons maintenant la propriété « Source contrôle » de txtUnite : =[cboRef].column(3). On récupère la quatrième colonne de la liste de cboRef.

Access commence sa numérotation à zéro : la 1^re colonne [cboRef].column(0), la 2^e colonne [cboRef].column(1)…

VII-B-5. Le contrôle txtSemaine▲

 

Sélectionnez

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

Private Sub txtSemaine_AfterUpdate()
  If Me.txtSemaine Like "*/*" Then
      Me.FiltreDu = DebutSemaine(Me.txtSemaine)
      Me.FiltreAu = Me.FiltreDu + 6
      Call MaJ
    Else
      If Me.txtSemaine >= 0 And Me.txtSemaine <= 53 Then
        Me.txtSemaine = Me.txtSemaine & "/" & Format(Date, "yy")
        Call txtSemaine_AfterUpdate
      End If
  End If
End Sub

Explication du code (les nombres en gras indiquent les N° de lignes)

2-5 : si l'utilisateur a saisi un N° semaine/Année, on garnit le contrôle FiltreDu avec la date du lundi qui correspond à cette semaine et FiltreAu avec la date du dimanche suivant. Et on actualise le formulaire.
N.B. La fonction DebutSemaine() est décrite au § suivant.

7-9 : si l'utilisateur a seulement introduit un N° de semaine sans préciser l'année, on ajoute celle en cours par défaut. Et on refait un tour (c'est donc une fonction récursive : elle fait appel à elle-même !)

VII-B-6. La fonction DebutSemaine()▲

Public Function DebutSemaine(Semaine As String) As Date
'        ? DebutSemaine("49/14") pour 49e semaine de 2014 => lundi 1er décembre
  Dim i As Integer
  Dim iSem As Integer 'N° de la semaine du 1er lundi
  Dim t() As String
  t = Split(Semaine, "/")
  For i = 1 To 7
    If Weekday(DateSerial(t(1), 1, i), vbMonday) = 1 Then Exit For
  Next i
  'i/1/t(1) = date du 1er lundi de l'année
  'dans quelle semaine se trouve ce 1er lundi ?
  iSem = DatePart("ww", DateSerial(t(1), 1, i), vbMonday, vbFirstFourDays)
  'rechercher la date de la semaine demandée
  DebutSemaine = DateSerial(t(1), 1, i) + 7 * (t(0) - iSem)
End Function

La table tStrucCor est la source de ce formulaire.

VIII-A-1. À l'ouverture et après chaque choix d'une semaine▲

tStrucCor est vidangée

puis, dans un 1^er temps, repeuplée (exécution de la requête « Ajout » rCreatStrucCor :

 

Sélectionnez

INSERT INTO tStrucCor ( tRapportsPK, CorDate, Denomination, Adresse, QPhysique, QLog, Ecart, TauxRef, IndiceRef, QPhysiqueCor, QLogCor, EcartCor, TauxRefcor, IndiceRefCor )
SELECT tRapports.tRapportsPK, tRapports.RapDate, tArticles.Denomination, tAdresses.Adresse, tRapports.QPhysique, tRapports.QLog, CDbl(IIf([QPhysique]<>0,Abs(([QLog]-[QPhysique])/[QPhysique]),0)) AS pcEcart, 1/DLookUp("Nbre","rOccurrencesCor","tArticlesFK=" & [tArticlesFK]) AS TauxRef, IIf([pcEcart]>0.05,0,[TauxRef]) AS Indice, tRapports.QPhysique AS PhyCor, tRapports.QLog AS LogCor, CDbl(IIf([QPhysique]<>0,Abs(([QLog]-[QPhysique])/[QPhysique]),0)) AS pcEcartCor, 1/DLookUp("Nbre","rOccurrencesCor","tArticlesFK=" & [tArticlesFK]) AS TauxRefCor, IIf([pcEcart]>0.05,0,[TauxRef]) AS IndiceCor
FROM tArticles INNER JOIN (tAdresses INNER JOIN tRapports ON tAdresses.tAdressesPK = tRapports.tAdressesFK) ON tArticles.tArticlesPK = tRapports.tArticlesFK
WHERE (((tRapports.RapDate)>=IIf(IsNull([Formulaires]![fCorrection]![txtDu]),#1/1/1900#,[Formulaires]![fCorrection]![txtDu]) And (tRapports.RapDate)<=IIf(IsNull([Formulaires]![fCorrection]![txtAu]),#1/1/2100#,[Formulaires]![fCorrection]![txtAu])));

et, dans la foulée, modifiée par la requête « Mise à jour » rMajCorStrucCor :

 

Sélectionnez

UPDATE tCor INNER JOIN tStrucCor ON tCor.tRapportsPK = tStrucCor.tRapportsPK SET tStrucCor.[Action] = [tCor].[Action], tStrucCor.QPhysiqueCor = [tCor].[QPhysiqueCor], tStrucCor.QLogCor = [tCor].[QLogCor], tStrucCor.EcartCor = [tCor].[EcartCor], tStrucCor.IndiceRefCor = [tCor].[IndiceRefCor];

et le formulaire affiche :

VIII-A-2. Imaginons que l'utilisateur ajoute une nouvelle correction▲

VIII-A-3. Après fermeture (ou changement de semaine) ▲

Les requêtes rCorAjouts et rCorModif sont exécutées :

 

Sélectionnez

INSERT INTO tCor ( tRapportsPK, [Action], QPhysiqueCor, QLogCor, EcartCor, IndiceRefCor )
SELECT tStrucCor.tRapportsPK, tStrucCor.Action, tStrucCor.QPhysiqueCor, tStrucCor.QLogCor, tStrucCor.EcartCor, tStrucCor.IndiceRefCor
FROM tStrucCor
WHERE (((tStrucCor.QPhysiqueCor)<>[QPhysique])) OR (((tStrucCor.QLogCor)<>[QLog]));

 

Sélectionnez

UPDATE tStrucCor INNER JOIN tCor ON tStrucCor.tRapportsPK = tCor.tRapportsPK SET tCor.[Action] = [tStrucCor].[Action], tCor.QPhysiqueCor = [tStrucCor].[QPhysiqueCor], tCor.QLogCor = [tStrucCor].[QLogCor], tCor.EcartCor = [tStrucCor].[EcartCor], tCor.IndiceRefCor = [tStrucCor].[IndiceRefCor];

La 1^re ajoutera les corrections dans la table tCor. (Pour autant qu'il n'y ait pas encore de corrections pour ce tRapportPK. En l'occurrence, la mise à jour de cet enregistrement serait rejetée.)

Et la seconde actualisera les valeurs des corrections antérieures éventuelles.

la table tCor contient maintenant :

… et un nouveau cycle peut commencer.

Il s'agit de txtDu et txtAu, ils ont leur propriété « Visible » à Non pour ne pas encombrer le formulaire affiché. Ils sont alimentés à la volée, après la mise à jour de txtSemaine. Bien que ces contrôles soient cachés, leur valeur est disponible : on s'en sert dans la requête rCreatStrucCor.

La propriété « Source contrôle » est =Somme([IndiceRefCor]). Cette syntaxe signifie que l'on demande la somme des valeurs de toute la colonne IndiceRefCor de la table/requête source du formulaire.

Ici, la « Source contrôle » est ="(" & Compte([tRapportsPK]) & " rapports)". Cette fois, on compte le nombre de lignes (non Null) dans la colonne tRapportsPK de la source et on concatène le résultat avec du texte.

Ici, on affiche le contenu d'un autre contrôle =[TlIndiceRefCor]. La syntaxe : le signe « = » suivi du nom du contrôle encadré de crochets.

Comme au point précédent, mais cette fois dans un calcul : =[NumCor]/[DenCor].

Un clic sur ce bouton déclenchera l'affichage du formulaire fArchives.

Les requêtes rArchiAjout et rArchiRemplace s'exécutent pour compléter la source du formulaire fArchives. Celui-ci s'affichera juste en dessous du bouton.

INSERT INTO tArchiveSRA ( RsaSemaine, RsaInitial, RsaModif )
SELECT Formulaires!fCorrection!txtSemaine AS Expr1, Formulaires!fCorrection!SRA AS Expr2, Formulaires!fCorrection!SRAcor AS Expr3;

UPDATE tArchiveSRA SET tArchiveSRA.RsaInitial = Formulaires!fCorrection!SRA, tArchiveSRA.RsaModif = Formulaires!fCorrection!SRAcor
WHERE (((tArchiveSRA.RsaSemaine)=[Formulaires]![fCorrection]![txtSemaine]));

(Le code est expliqué ci-dessous en 80-95.)

Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)
  'positionner en haut de l'écran
  DoCmd.MoveSize 200, 200
  'Vidanger tStrucCor
  DoCmd.SetWarnings False
  DoCmd.RunSQL "DELETE * FROM tStrucCor;"
  'Alimenter tStrucCor
  DoCmd.OpenQuery "rCreatStrucCor"
  DoCmd.OpenQuery "rMajCorStrucCor"
  DoCmd.SetWarnings True
  'Afficher
  Me.Requery
End Sub

Private Sub Form_Close()
  'Mettre la tCor à jour
  DoCmd.SetWarnings False
  DoCmd.OpenQuery "rCorAjouts"
  DoCmd.OpenQuery "rCorModif"
  DoCmd.SetWarnings True
  'Fermer fArchives
  If CurrentProject.AllForms("fArchives").IsLoaded Then DoCmd.Close acForm, "fArchives"
End Sub

Private Sub txtSemaine_BeforeUpdate(Cancel As Integer)
  'Sauver les modifs éventuelles de l'affichage précédent
  Call Form_Close
End Sub

Private Sub txtSemaine_AfterUpdate()
  'Fermer l'historique
  If CurrentProject.AllForms("fArchives").IsLoaded Then DoCmd.Close acForm, "fArchives"
  'Habiller txtSemaine
  If Me.txtSemaine Like "*/*" Then
      Me.TxtDu = DebutSemaine(Me.txtSemaine)
      Me.txtAu = Me.TxtDu + 6
    Else
      If IsNull(Me.txtSemaine) Then
          Me.TxtDu = Null
          Me.txtAu = Null
      End If
      If Me.txtSemaine >= 0 And Me.txtSemaine <= 53 Then
        Me.txtSemaine = Me.txtSemaine & "/" & Format(Date, "yy")
        Me.TxtDu = DebutSemaine(Me.txtSemaine)
        Me.txtAu = Me.TxtDu + 6
      End If
  End If
  Call Form_Open(0)
End Sub

Private Sub btTout_Click()
  Me.txtSemaine = Null
  Call txtSemaine_AfterUpdate
End Sub

Private Sub txtQPhysiqueCor_AfterUpdate()
  'Recalculer l'écart
  If Me.txtQPhysiqueCor = 0 Then
      Me.txtEcartCor = 0
    Else
      Me.txtEcartCor = Abs((Me.txtQLogCor - Me.txtQPhysiqueCor) / Me.txtQPhysiqueCor)
  End If
  'Recalculer l'indiceRefCor
  If Me.txtEcartCor > 0.05 Then
      Me.txtIndiceRefCor = 0
    Else
      Me.txtIndiceRefCor = Me.txtTauxRefCor
        End If
  'Actualiser
  Me.Refresh
End Sub

Private Sub txtQLogCor_AfterUpdate()
  Call txtQPhysiqueCor_AfterUpdate
End Sub

Private Sub btHistoriser_Click()
  'Vérifier qu'une semaine a été choisie
  If IsNull(Me.txtSemaine) Then
      MsgBox "Vous devez choisir une semaine"
      Exit Sub
  End If
  'Archiver les 2 SRA de cette semaine
  DoCmd.SetWarnings False
  DoCmd.OpenQuery "rArchiAjout"
  DoCmd.OpenQuery "rArchiRemplace"
  DoCmd.SetWarnings True
  'Afficher l'historique (Méthode Arkham46)
  If CurrentProject.AllForms("fArchives").IsLoaded Then DoCmd.Close acForm, "fArchives"
  DoCmd.OpenForm "fArchives", , , , , acHidden
  PositionForm Forms("fArchives"), Me.btHistoriser
End Sub

Explication du code (les nombres en gras indiquent les N° de lignes)

6 : à l'ouverture, on positionne le formulaire en haut de l'écran. Pour vous documenter sur la méthode Docmd.moveSize, voir Comment positionner un formulaire à un endroit déterminé.

8-13 : on crée la structure comme expliqué plus haut.

19-23 : à la fermeture, le processus décrit plus haut.

25 : on referme le formulaire fArchive si celui-ci est encore ouvert.

30 : avant d'afficher les enregistrements d'une nouvelle sélection de « Semaine », on sauve les éventuelles corrections apportées durant la précédente sélection. En fait, on exécute les mêmes instructions que lors de la fermeture du formulaire (voir 18-26).

35 : on referme le formulaire fArchive si celui-ci est encore ouvert.

37-51 : on aménage txtDu et txtAu en fonction de la semaine choisie et on exécute les mêmes instructions que lors de l'ouverture du formulaire (voir 4-16).

55-56 : au clic sur le bouton « Autre semaine » (btTout), on met à Null txtSemaine et on exécute les mêmes instructions que si ce changement avait été opéré manuellement (35-52).

59-78 : on recalcule l'écart et l'indice en cas de modification d'une quantité.

80-95 : au clic sur le bouton « Archiver les SRA de cette semaine », on vérifie que l'utilisateur a choisi une semaine, sinon on affiche un message(82-85). On exécute les requêtes rArchiAjout et rAchiRemplace, la première ajoute la semaine à l'historique (dans l'hypothèse où elle n'y est pas déjà) et la seconde réactualise les données (dans l'hypothèse contraire). On affiche le formulaire fArchives, juste en dessous du bouton, en appliquant la méthode décrite par Arkham46 dans cette contribution.

donnerait par exemple ceci :

En effet le texte « 52/13 » est plus grand que « 5/14 » lui-même plus grand que « 49/14 », alors que l'on veut l'ordre chronologique décroissant !

Qu'à cela ne tienne, voici une fonction qui va nous dépanner :

Public Function ArgTriSem(Semaine As String) As Integer
  Dim t() As String
  t = Split(Semaine, "/")
  ArgTriSem = t(1) & Format(t(0), "00")
End Function

L'instruction de la ligne 4 transforme « 52/13 » en « 13/52 » et « 5/14 » en « 14/05 ».

Si nous construisons la source comme ceci :

on obtient l'ordre souhaité :