Outils pour la gestion d'un restaurant

| macgyver44 | Claude Leloup |

Voici quelques outils développés avec Access2000 pour aider un restaurateur à analyser la marge qu'il réalise sur la vente de ses plats. En quelque sorte, faire un nœud entre le montant de ses achats de matières premières et la partie « nourriture » de son chiffre d'affaires.

Vous pouvez réagir en participant à la discussion sur le forum :

Commentez

- Titre : Outils pour la gestion d'un restaurant

- Auteurs : macgyver44, Claude Leloup

- Parution : 9 août 2013

- Mise à jour : 4 juin 2016

- 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. Avant-propos

- II. Le formulaire F_Recettes

- II-A. La section Détail du formulaire

- II-A-1. Fonctionnalités

- II-A-2. Quelques explications « Métier »

- II-A-2-a. Statut

- II-A-2-b. Coefficient

- II-A-2-c. PV Normal

- II-B. La section En-tête du formulaire

- II-C. Comment fonctionne le système

- II-C-1. La requête rFiltrePlat

- II-C-2. Conclusion

- II-D. Nous avons une fiche de P.R. par plat, mais encore…

- III. L'encodage des achats

- III-A. Présentation du formulaire F_Achats

- III-B. L'événement « Sur fermeture » de F_Achats

- III-C. Cas particulier : l'utilisateur modifie ou supprime l'enregistrement unique d'un ingrédient

- IV. L'encodage des ventes

- V. Où en sommes-nous à ce stade de la réflexion ?

- VI. Le contrôle de l'inventaire

- VI-A. La source du formulaire F_Inventaire

- VI-B. Le fonctionnement du formulaire F_Inventaire

- VI-B-1. À l'ouverture le formulaire…

- VI-B-2. L'utilisateur complète la date du nouvel inventaire…

- VI-B-3. L'utilisateur complète alors la colonne « Stock »…

- VI-B-4. Quand l'examen est terminé

- VII. Conclusion

- VIII. Le modèle de données

- IX. Remarques

- X. Téléchargement

I - Avant-propos

Cet article s'adresse à un lecteur ayant quelques notions de Visual Basic for Access (VBA).

Toutefois, les commentaires inclus dans le code devraient permettre au débutant de comprendre le fonctionnement. À plusieurs endroits, nous donnons la référence de tutoriels qui expliquent en détail les techniques utilisées.

Cet ensemble est aussi l'occasion de mettre en application des solutions proposées sur le forum Access de Développer.com, entre autres :

- les formulaires « père/fils » ;

- les formulaires qui combinent l'usage traditionnel (ajout, modification et suppression des enregistrements) avec la recherche multicritère ;

- l'utilisation d'images logées en dehors de la base de données (DB) ;

- l'ajustement de la taille d'un sous-formulaire en fonction du nombre d'enregistrements à afficher ;

- positionner un formulaire indépendant à proximité d'un contrôle d'un autre formulaire ;

- …

II - Le formulaire F_Recettes

Ce formulaire est polyvalent :

- il permet d'ajouter, de modifier et de supprimer une recette (la section Détail en gris clair et foncé) ;

- il permet de rechercher les recettes qui répondent à certains critères (la section En-tête en bleu).

II-A - La section Détail du formulaire

Les champs sur fond blanc sont complétés par l'utilisateur, les autres sont calculés par le programme.

La partie en gris foncé (la liste des ingrédients) est un sous-formulaire qui fonctionne suivant la technique père-fils. (Pour plus de détails sur la technique, référerez-vous à ce tutoriel.)

L'image (dynamique) du plat n'est pas incluse dans la base de données. Les fichiers idPlat.jpg sont logés dans un sous-répertoire (« Images ») de celui qui contient la base de données. (La technique est décrite dans le tutoriel de cafeine : Gestion de photos par formulaire.)

II-A-1 - Fonctionnalités

La taille du sous-formulaire s'ajuste en fonction du nombre d'ingrédients. (Pour la technique voir le § IV de ce tutoriel de comptabilité.)

Les ingrédients mentionnés en doublon sont allumés en rouge.

Voici comment.

1° D'abord une fonction

Public Function Doublon(Plat As Long, Ingredient As Long) As Boolean
Doublon = DCount("*", "tblPlatsIngredients", "PlatFK=" & Plat & " and IngredientFK =" & Ingredient) > 1
End Function

On donne en paramètres les identificateurs du plat et de l'ingrédient.
On compte dans tblPlatsIngredients le nombre d'enregistrements qui répondent à ces critères, s'il y en a plus qu'un, la comparaison >1 donne True et la fonction renvoie cette valeur : il y a doublon.

2° Dans SF_RecettesIng, nous avons ajouté un contrôle non visible :

Ce contrôle aura donc la valeur False ou True selon que txtPlatFK de l'enregistrement actif est unique ou en doublon.

3° Et enfin, on exploite ce critère dans la mise en forme conditionnelle de cboIngredientFK

Lors de la confection d'une nouvelle recette, l'utilisateur peut importer, en un clic, les ingrédients d'une recette existante prise comme modèle. Voici le code :

1. Private Sub btnAjoutIng_Click()

3. Dim strSQL As String

4. If IsNull(Me.cboRecette) Then

5. MsgBox "Vous devez d'abord choisir une recette !"

6. DoCmd.GoToControl "cboRecette"

7. Me.cboRecette.Dropdown

8. Exit Sub

9. End If

11. Me.Refresh 'pour enregistrer la recette en cours

13. 'Ajouter les ingrédients du modèle

14. DoCmd.SetWarnings False

15. strSQL = "INSERT INTO tblPlatsIngredients ( PlatFK, IngredientFK, Quantite ) " _

16. & "SELECT  " & Forms!F_Recettes!txtPlatPK _

17. & ", tblPlatsIngredients.IngredientFK, tblPlatsIngredients.Quantite " _

18. & "FROM tblPlatsIngredients " _

19. & "WHERE tblPlatsIngredients.PlatFK=" & Me.cboRecette & ";"

20. CurrentDb.Execute (strSQL)

21. DoCmd.SetWarnings True

23. CtnrSF_RecettesIng.Requery

25. 'Remoduler la taille du sous-formulaire

26. Call AmenagerTailleSF(Me.Name, "CtnrSF_RecettesIng")

28. End Sub

Commentaire du code

4-9 : on vérifie qu'un modèle de recette a été choisi. Sinon, on invite à le faire.

14-21 : on construit à la volée le SQL d'une requête qui va ajouter
- dans tblPlatsIngredients,
- pour la recette en cours,
- les ingrédients du modèle choisi.

Voici l'image de la requête qui serait construite, si on demandait d'ajouter les ingrédients de « Soupe à l'oignon » dans « Filets de rougets » (beurk !)

23-26 : on actualise le sous-formulaire fils et on ajuste sa taille.

Un double-clic sur la photo miniature du plat provoque l'affichage de celle-ci dans sa dimension originale en utilisant le logiciel que l'utilisateur a associé à l'extension « jpg » (pour autant que son navigateur soit Explorer).

Private Sub iPhotoPlat_DblClick(Cancel As Integer)
Shell "C:\WINDOWS\EXPLORER.EXE " & CurrentProject.Path & "\Images\" & Me.txtPlatPK & ".jpg"
End Sub

N.B. Les images sont toutes logées dans le sous-répertoire « Images » du dossier qui contient la base de données. Leur nom est composé de l'identificateur du plat, suivi de l'extension « .jpg ».

Si un prix unitaire n'est pas récent, il est affiché sur un fond orange, vert sinon.

Voici comment on y arrive.

La source du contrôle qui fait office de fond (txtPrixActu) :

=(Date()-nz(RechDom("DateDernAchat";"tblIngredients";"IngredientPK =" & [cboIngredientFK]);DelaiPrixActu()+1))<DelaiPrixActu()

La valeur du contrôle est donc -1 si le prix est toujours d'actualité, 0 sinon. Cette valeur sert de base au formatage conditionnel.

II-A-2 - Quelques explications « Métier »

II-A-2-a - Statut

- En force : ce plat est actuellement proposé à la carte.

- En veille : actuellement hors-saison (on reproposera le plat lors de la saison prochaine).

- Caduque : gardée temporairement pour ne pas risquer d'altérer la cohérence technique (antichambre de la poubelle).

II-A-2-b - Coefficient

C'est le rapport entre le prix de vente hors-taxe et le prix de revient des matières premières.

Ce rapport est souvent utilisé lors d'un contrôle indiciaire du chiffre d'affaires déclaré. (À bon entendeur, salut !)

II-A-2-c - PV Normal

« Prix de Vente Normal », celui qui respecterait une politique de prix, par exemple (au hasard) :

pour une entrée : un coefficient 2.5 et un prix minimum de 6.25 € ;

pour un plat : coefficient 3.0, minimum 8.75 € ;

pour un dessert : coefficient 4.00, minimum 3.75 €.

Le PVN est le prix qu'on voudrait pouvoir imposer, le PV TTC est celui qui est réaliste, en fonction des prix de la concurrence et de ce que la clientèle est d'accord de payer !

Une fonction pour calculer le PVN

1. Public Function PVN(PR As Single, idCategorie As Long) As Single

2. Dim PCoeff As Single

3. Dim PMin As Single

4. Select Case idCategorie

5. Case 1 'Entrées

6. PCoeff = PR * CoeffEntr

7. PMin = MinEntr

8. Case 2 'Plats

9. PCoeff = PR * CoeffPlat

10. PMin = MinPlat

11. Case 3 'Desserts

12. PCoeff = PR * CoeffDess

13. PMin = MinDess

14. End Select

15. 'choix du plus avantageux

16. If PCoeff > PMin Then

17. PVN = PCoeff * (1 + TVANour)

18. Else

19. PVN = PMin

20. End If

21. End Function

Commentaire du code

1 : les paramètres sont : le prix de revient et l'identifieur de la catégorie.

4-14 : selon l'identifieur de catégorie, on loge dans PCoeff et PMin respectivement le prix calculé suivant le coefficient et le prix minimum pour cette catégorie.
N.B. CoeffEntr, CoeffPlat, CoeffDess, MinEntr, MinPlat et MinDess sont des fonctions décrites en fin de ce document.

16-20 : la fonction ramène le tarif le plus avantageux : soit le prix selon le coefficient, augmenté de la TVA ; soit le prix plancher pour cette catégorie.

II-B - La section En-tête du formulaire

À l'instar des images dynamiques des plats, les images fixes du formulaire sont aussi à l'extérieur de la DB. (Pour la technique, voir : Stockez les images statiques de vos formulaires et états Access hors de la base de données.)

Les contrôles affichés en vert représentent des critères à « recherche stricte » : le choix du critère est limité aux items de la liste déroulante.

Les contrôles affichés en orange représentent des critères à « recherche élargie » : l'utilisateur peut faire une recherche en utilisant les items proposés par la liste ou sur la base d'une chaine de caractères de son choix.

L'utilisateur peut évidemment combiner les critères.

Par exemple : rechercher les recettes

- qui ont « asperge » dans la dénomination ;

- en Entrée ;

- actuellement à la carte ;

- qui contiennent « saumon » dans le nom d'un ingrédient.

II-C - Comment fonctionne le système

La source du formulaire correspond à cette requête :

C'est-à-dire que nous prenons :

toutes les colonnes de la table « tblPlats » ;

dans l'ordre alphabétique croissant des dénominations,

mais les lignes de la table seront limitées à celles contenues dans la requête « rFiltresPlat ».

Cette requête source reste modifiable, c'est dire que dans le formulaire, on pourra ajouter, supprimer ou modifier le contenu de la table tblPlats.

Cette astuce permet d'avoir un formulaire qui combine les fonctionnalités traditionnelles (Ajouter, Supprimer, Modifier) avec celles d'un formulaire de recherche multicritère.

II-C-1 - La requête rFiltrePlat

N.B. Pour pouvoir être incluse dans une fonction « In() », il est important que cette requête ne ramène qu'une seule colonne : les IdPlat.

Principe : on prend comme source la table que l'on aurait choisie « naturellement » comme source du formulaire (ici tblPlats) et on y joint toutes les tables du modèle qui sont nécessaires pour afficher les données sur lesquelles on veut filtrer.

Dans le cas présent : la dénomination, la catégorie (en clair), le statut (en clair) et l'ingrédient.

Comme une occurrence de IdPlat suffit, on regroupe sur idPlat.

Examinons les valeurs de la ligne « Critères » de la requête : elles ont toutes la même structure :

C'est-à-dire qu'on sélectionne les enregistrements qui contiennent :

- soit le texte exact (cas d'une sélection stricte) ;

- soit la chaine de caractères (cas d'une sélection élargie) ;

- soit tous les enregistrements, si le filtre est Null (non-choix).

II-C-2 - Conclusion

Une telle organisation facilite la maintenance au cas où un nouveau critère devrait être ajouté : il suffit d'ajouter un contrôle dans le formulaire (en respectant la convention de nommage) et d'adapter la requête rFiltresPlat.

II-D - Nous avons une fiche de P.R. par plat, mais encore…

Il y a deux points névralgiques : le prix des ingrédients et les quantités.
Nous devons réfléchir sur deux axes :
- comment organiser la mise à jour des prix unitaires ;
- comment contrôler la pertinence des quantités renseignées dans les fiches des recettes.

Tout n'a pas la même importance. Si dans la fiche de la recette on s'est trompé dans la quantité de sel ou qu'on a valorisé la farine au prix qu'elle coûtait il y a six mois, ça n'a guère de conséquences. Par contre, renseigner 125 g de filets de sole alors qu'en réalité on en met 150 sur l'assiette et si en plus le P.R. est basé sur les prix d'il y a un mois… il vaut mieux ne plus se servir de l'outil !

Voici le schéma de réflexion pour la suite :

- Sur la base des factures d'achats de matières premières, on encode les prix et les quantités des ingrédients « à suivre ». On peut ainsi :
mettre à jour les prix dans tblIngredients ;
enregistrer une entrée dans le stock de cet ingrédient (tblInventaire).

- Sur la base des plats vendus, on peut calculer la consommation théorique de chaque ingrédient « à suivre ».

Anciens stocks + Entrées réelles - consommations théoriques = la quantité qui devrait rester.

- On compare alors l'inventaire physique avec l'inventaire théorique et on se pose les bonnes questions en cas d'écart significatif :
- « cela vient-il d'une erreur dans la quantité renseignée à la fiche Recette ? » On corrige celle-ci s'il échet ;
- « commet-on des erreurs en cuisine, plats ratés à jeter ? » ;
- « cela vient-il de matières périssables que l'on a dû jeter ? » Si oui, c'est intéressant d'en garder une preuve pour justifier une perte vis-à-vis d'un contrôle fiscal éventuel ;
- « cela vient-il de matières qui disparaissent ? » Auquel cas, il faut resserrer des boulons !

III - L'encodage des achats

III-A - Présentation du formulaire F_Achats

Ce formulaire est aussi conçu sous le mode polyvalent : il permet non seulement l'ajout, la suppression et la modification d'enregistrements, mais aussi la recherche sur la base du fournisseur (recherche stricte) et de l'ingrédient (recherche élargie).

La mise en place est ici plus simple que celle expliquée pour le formulaire F_Recettes : pas besoin de recourir à une requête filtre : on peut filtrer directement dans la source et celle-ci restera modifiable.

Voici la source :

SELECT tblAchats.*
FROM tblIngredients INNER JOIN tblAchats ON tblIngredients.IngredientPK = tblAchats.IngredientFK
WHERE (((tblAchats.FournisseurFK) Like "*" & [forms]![F_Achats]![FiltreFournisseur] & "*") AND ((tblIngredients.Ingredient) Like "*" & [forms]![F_Achats]![FiltreIngredient] & "*"));

Pour faciliter l'encodage en série des postes d'une facture, les données susceptibles d'être identiques sont mises par défaut pour l'éventuel ajout suivant.

Private Sub cboFournisseurFK_AfterUpdate()
If Me.NewRecord Then Me.cboFournisseurFK.DefaultValue = Me.cboFournisseurFK
DoCmd.GoToControl "txtDateAchat"
End Sub

Private Sub txtDateAchat_AfterUpdate()
If Me.NewRecord Then Me.txtDateAchat.DefaultValue = "#" & Format(txtDateAchat, "mm/dd/yy") & "#"
DoCmd.GoToControl "txtRefAchat"
End Sub

Private Sub txtRefAchat_AfterUpdate()
If Me.NewRecord Then Me.txtRefAchat.DefaultValue = txtRefAchat
DoCmd.GoToControl "cboIngredient"
Me.cboIngredient.Dropdown
End Sub

Cette donnée fait référence au document qui sert de base à l'encodage (sans doute une facture). L'utilisateur saisit n'importe quelle valeur de son choix qui lui permettra de retrouver facilement la pièce. Par exemple, le N° d'inscription au facturier des achats.

Cette donnée servira pour le processus d'inventaire. Éventuellement négative pour une note de crédit (allumée en rouge si égale à 0).

Cette donnée servira à la mise à jour du prix dans tblIngredient. Nécessairement supérieure à 0 (allumée en rouge sinon).

Calculé et affiché pour permettre un contrôle visuel du montant facturé.

III-B - L'événement « Sur fermeture » de F_Achats

C'est cet événement qui va provoquer la mise à jour du prix dans la table tblIngredients.

Le principe : à la fermeture du formulaire, on reporte dans tblIngredients, systématiquement le prix de l'achat le plus récent pour tous les ingrédients mentionnés dans la table tblAchats.

Cela signifie que non seulement les achats que l'on vient d'ajouter sont pris en compte, mais aussi toutes les modifications que l'utilisateur aurait éventuellement apportées dans des enregistrements anciens (pour corriger une erreur par exemple). Voir cependant la remarque au point II.C.

Voici le code :

Private Sub Form_Close()

Dim strIngredients As String, strAchats As String
Dim rsAchats As DAO.Recordset

'Mettre à jour le prix des ingrédients
DoCmd.SetWarnings False
'Mettre les DateDernAchat à Null
DoCmd.RunSQL "UPDATE tblIngredients INNER JOIN tblAchats ON tblIngredients.IngredientPK = tblAchats.IngredientFK " _
& "SET tblIngredients.DateDernAchat = Null;"

'Ouvrir un Recordset sur la table tblAchats
'qui extrait la dernière date et le prix correspondant de chaque ingrédient

strAchats = "SELECT tblAchats.IngredientFK, tblAchats.PrixAchat, tblAchats.DateAchat " _
& "FROM tblAchats,(SELECT tblAchats.IngredientFK, " _
& "MAX(tblAchats.DateAchat) As DernDate FROM tblAchats GROUP BY tblAchats.IngredientFK) AS DatesRecentes " _
& "WHERE tblAchats.IngredientFK = DatesRecentes.IngredientFK And tblAchats.DateAchat = DatesRecentes.DernDate "

Set rsAchats = CurrentDb.OpenRecordset(strAchats, dbOpenDynaset)

'Vérifie si le Recordset contient des enregistrements
'et positionne le curseur sur le premier enregistrement du Recordset
If Not rsAchats.EOF Then
rsAchats.MoveFirst
End If

'Parcourt tous les enregistrements du Recordset
'tant qu'on n'a pas atteint la fin
Do While Not rsAchats.EOF

'Requête MAJ qui permet la mise des colonnes Prix et DateDernAchat de la table T_Ingredients
strIngredients = "UPDATE tblIngredients SET " _
& "tblIngredients.Prix = " & Replace(rsAchats!PrixAchat, ",", ".") _
& ", tblIngredients.DateDernAchat=#" & Format(rsAchats!DateAchat, "mm/dd/yy") & "# " _
& "WHERE tblIngredients.IngredientPK= " & rsAchats!IngredientFK _
& " And ((tblIngredients.DateDernAchat Is Null) Or " _
& "(tblIngredients.DateDernAchat<#" & Format(rsAchats!DateAchat, "mm/dd/yy") & "#));"

CurrentDb.Execute strIngredients
rsAchats.MoveNext

Loop

'Fermeture du Recordset
rsAchats.Close
'Libération de la variable Recordset rsAchats
Set rsAchats = Nothing

DoCmd.SetWarnings True

End Sub

Commentaires au sujet du code :

Dans tblIngredients, on remet à Null DateDernAchat de tous les ingrédients présents dans tblAchats. Ceci pour pouvoir recommencer la mise à jour du prix depuis l'origine.

Le code correspond à cette requête :

On crée une requête qui va ramener, pour chaque ingrédient, le prix de l'achat le plus récent :

On crée un recordset qui contient les enregistrements ramenés par cette requête et pour chaque item, on opère la mise à jour du Prix et DernDate dans tblIngredients en exécutant la requête décrite au point suivant.

Voici la représentation graphique de la requête exécutée pour chaque dernier prix de chaque ingrédient (ici pour l'ingrédient 21, au 22 juin 2013) :

III-C - Cas particulier : l'utilisateur modifie ou supprime l'enregistrement unique d'un ingrédient

Si à la suite d'une modification, il n'y a plus d'enregistrement dans tblAchats pour cet ingrédient, ni son prix, ni sa date ne seront modifiés dans tblIngrédients (la mise à Null de DateDernAchat ne concerne que les ingrédients présents dans tblAchats).

Pour éviter cet écueil, on va remettre DateDernAchat à Null et Prix à zéro dès que l'on détecte une modification de l'ingrédient ou la suppression d'un enregistrement. Voici le code :

Option Compare Database
Option Explicit

Dim iIngredientIni As Integer
Private Sub Form_Current()
'Mémoriser l'ingrédient initial
If Not Me.NewRecord Then iIngredientIni = Me.cboIngredient
End Sub
Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
Dim sSql As String
'Modifier la DateDernAchat de l'ingrédient concerné dans tblIngredients
sSql = "UPDATE tblIngredients SET DateDernAchat = Null, tblIngredients.Prix = 0 WHERE tblIngredients.IngredientPK=" & iIngredientIni & ";"
DoCmd.SetWarnings False
DoCmd.RunSQL sSql
DoCmd.SetWarnings True
End Sub

Private Sub cboIngredient_BeforeUpdate(Cancel As Integer)
Dim sSql As String
If Me.NewRecord Then Exit Sub
'Modifier la DateDernAchat de l'ancien ingrédient concerné dans tblIngredients
sSql = "UPDATE tblIngredients SET DateDernAchat = Null, tblIngredients.Prix = 0 WHERE tblIngredients.IngredientPK=" & Me.cboIngredient.OldValue & ";"
DoCmd.SetWarnings False
DoCmd.RunSQL sSql
DoCmd.SetWarnings True
End Sub

IV - L'encodage des ventes

Un formulaire « père » et trois « fils ».

Le père accède à l'unique colonne « VentesDatesPK » de la table tblVentesDates.

La coordination avec les fils s'opère sur cette base :

Chacun des fils accède à la table tblVentes pour la partie qui concerne sa catégorie

Source du « fils »Entrées

Le formulaire nous permet donc de comptabiliser les ventes date après date. Ces données nous serviront à calculer la consommation théorique de chaque ingrédient composant le plat.

V - Où en sommes-nous à ce stade de la réflexion ?

- Nous connaissons le nombre de plats vendus :

- Nous connaissons les quantités d'ingrédients pour chaque plat :

- Nous pouvons donc calculer les quantités de chaque ingrédient qui ont - en théorie - été mises en œuvre pendant une période.

- Nous avons d'autre part comptabilisé les achats d'ingrédients.

- Nous pouvons donc calculer les quantités d'ingrédients achetées pendant une période

« La » question : le stock effectivement constaté correspond-il à la théorie ?
Concrètement, si en début de période

- nous avions x kg de dos de cabillaud,

- que nous en avons acheté y kg

- et que, théoriquement, nous en avons utilisé z kg,

est-ce que dans le frigo, il reste bien (x + y - z) kg ?
Au chapitre suivant, on crée l'outil pour répondre à cette question.

VI - Le contrôle de l'inventaire

VI-A - La source du formulaire F_Inventaire

VI-B - Le fonctionnement du formulaire F_Inventaire

VI-B-1 - À l'ouverture le formulaire…

… affiche les données du dernier inventaire qui a été enregistré, c'est-à-dire les enregistrements qui ont la date la plus récente dans tblInventaire.

Les éventuels nouveaux ingrédients sont ajoutés :

Private Sub Form_Open(Cancel As Integer)
Dim sSql As String
Dim dDernInven As Date
dDernInven = DMax("InvDate", "tblInventaire")
Me.TxtDateDepart = dDernInven + 1
Me.etDernier.Caption = "Dernier inventaire " & dDernInven
'Ajouter les nouveaux ingrédients éventuels
DoCmd.SetWarnings False
sSql = "INSERT INTO tblInventaire ( IngredientFK, InvDate ) " _
& "SELECT IngredientPK, #" & Format(dDernInven, "mm/dd/yy") & "# AS Expr1 " _
& "FROM tblIngredients GROUP BY IngredientPK;"
DoCmd.RunSQL sSql
DoCmd.SetWarnings True
'Afficher les stocks du dernier inventaire
Me.Filter = "InvDate = #" & Format(dDernInven, "mm/dd/yy") & "#"
Me.FilterOn = True
End Sub

VI-B-2 - L'utilisateur complète la date du nouvel inventaire…

D'autres données s'affichent alors :

Le bouton a changé de légende et devient « Activé ».

On affiche la somme des achats de chaque ingrédient pendant la période qui nous sépare du précédent inventaire. On exploite pour cela la requête rInventaireDetailAchats que nous avons évoquée plus haut.

On affiche la somme des consommations théoriques de chaque ingrédient pendant la période qui nous sépare du précédent inventaire. On exploite pour cela la requête rInventaireDetailConsom que nous avons évoquée plus haut.

VI-B-3 - L'utilisateur complète alors la colonne « Stock »…

avec les données qu'il constate sur place actuellement :

Il examine et essaie d'expliquer les écarts.

Un double-clic sur le montant d'un achat en affiche le détail :

Private Sub TxtEntrees_DblClick(Cancel As Integer)
'Formater le formulaire F_InventaireDetail
If CurrentProject.AllForms("F_InventaireDetail").IsLoaded Then DoCmd.Close acForm, "F_InventaireDetail"
DoCmd.OpenForm "F_InventaireDetail", , , , , acHidden
Forms!F_InventaireDetail.RecordSource = "SELECT * FROM rInventaireDetailAchats " _
& "WHERE IngredientFK=" & Me.txtIngredientFK & ";"
Forms!F_InventaireDetail!Titre.Caption = "Détail des achats de " & Me.TxtEntrees _
& " " & Forms!F_InventaireDetail!txtLibelleUV & Chr(13) & Chr(10) _
& Me.txtIngredient & Chr(13) & Chr(10) _
& "entre le " & Me.TxtDateDepart _
& " et le " & Me.TxtDateFin
'Positionner le formulaire F_InventaireDetail
Call PositionFormBis("F_InventaireDetail", Me.ActiveControl)
End Sub

De la même manière, un double-clic sur le total des consommations en affiche le détail :

Private Sub txtConsom_DblClick(Cancel As Integer)
'Formater le formulaire F_InventaireDetail
If CurrentProject.AllForms("F_InventaireDetail").IsLoaded Then DoCmd.Close acForm, "F_InventaireDetail"
DoCmd.OpenForm "F_InventaireDetail", , , , , acHidden
Forms!F_InventaireDetail.RecordSource = "SELECT * FROM rInventaireDetailConsom " _
& "WHERE IngredientPK=" & Me.txtIngredientFK & ";"
Forms!F_InventaireDetail!Titre.Caption = "Détail des consommations de " & Format(Me.txtConsom, "#,#0.00") _
& " " & Forms!F_InventaireDetail!txtLibelleUV & Chr(13) & Chr(10) _
& Me.txtIngredient & Chr(13) & Chr(10) _
& "entre le " & Me.TxtDateDepart _
& " et le " & Me.TxtDateFin
'Positionner le formulaire F_InventaireDetail
Call PositionFormBis("F_InventaireDetail", Me.ActiveControl)
End Sub

Dans un cas comme dans l'autre, le formulaire qui affiche le détail se positionne immédiatement en dessous du contrôle que l'utilisateur a double-cliqué (au-dessus à défaut de place suffisante à l'écran).

Pour ce faire, on utilise une procédure mise au point par Arkham46, dans cette contribution.

VI-B-4 - Quand l'examen est terminé

Le clic sur ce bouton provoque la création des enregistrements dans tblInventaire. Et on est reparti pour une nouvelle période de travail avant le prochain inventaire…

Private Sub btCreerInvSuivant_Click()
Dim sSql As String
Me.Refresh
DoCmd.SetWarnings False
'Purge d'une création antérieure éventuelle
DoCmd.RunSQL "DELETE * FROM tblInventaire  WHERE InvDate>#" & Format(Me.TxtDateDepart, "mm/dd/yy") & "#;"
'MàJ tblInventaire pour enregistrer le nouvel inventaire
sSql = "INSERT INTO tblInventaire ( Stock, IngredientFK, InvDate ) " _
& "SELECT InvSuivant, IngredientFK, #" & Format(Me.TxtDateFin, "mm/dd/yy") & "# AS Expr1 " _
& "FROM tblInventaire " _
& "WHERE InvDate=#" & Format(Me.TxtDateDepart - 1, "mm/dd/yy") & "#;"
DoCmd.RunSQL sSql
DoCmd.SetWarnings True
End Sub

VII - Conclusion

L'ensemble de ces quatre outils :

- les fiches recettes ;

- l'enregistrement des achats d'ingrédients « à surveiller » ;

- l'enregistrement des plats vendus ;

- l'analyse de l'inventaire

permet de maitriser la composition du coefficient, l'une des clés de la rentabilité d'un restaurant.

Access convient très bien pour la réalisation d'une telle application.

Et l'utilisateur ?

La confection des fiches recettes va lui demander un effort considérable au démarrage, beaucoup moins à l'usage. Par contre, il faudra qu'il s'astreigne à un travail administratif récurrent : encoder achats et ventes (idéalement tous les jours) et analyser les écarts d'inventaire le plus souvent possible (idéalement toutes les semaines). Sinon, cela ne vaut pas la peine de développer l'outil !

VIII - Le modèle de données

IX - Remarques

Une table tblParametres et la série de fonctions qui utilisent son contenu faciliteront la maintenance en cas de modification des taux de TVA, de politique de prix, etc.

Option Compare Database
Option Explicit

Public Function TVANour() As Single
TVANour = DLookup("ValeurNum", "tblParametres", "Sigle = ""TVANour""")
End Function
Public Function TVABois() As Single
TVABois = DLookup("ValeurNum", "tblParametres", "Sigle = ""TVABois""")
End Function
Public Function TVAAlco() As Single
TVAAlco = DLookup("ValeurNum", "tblParametres", "Sigle = ""TVAAlco""")
End Function

Public Function CoeffEntr() As Single
CoeffEntr = DLookup("ValeurNum", "tblParametres", "Sigle = ""CoeffEntr""")
End Function

Public Function CoeffPlat() As Single
CoeffPlat = DLookup("ValeurNum", "tblParametres", "Sigle = ""CoeffPlat""")
End Function

Public Function CoeffDess() As Single
CoeffDess = DLookup("ValeurNum", "tblParametres", "Sigle = ""CoeffDess""")
End Function
Public Function MinEntr() As Single
MinEntr = DLookup("ValeurNum", "tblParametres", "Sigle = ""MinEntr""")
End Function

Public Function MinPlat() As Single
MinPlat = DLookup("ValeurNum", "tblParametres", "Sigle = ""MinPlat""")
End Function

Public Function MinDess() As Single
MinDess = DLookup("ValeurNum", "tblParametres", "Sigle = ""MinDess""")
End Function
Public Function DelaiPrixActu() As Single
DelaiPrixActu = DLookup("ValeurNum", "tblParametres", "Sigle = ""DelaiPrixActu""")
End Function

X - Téléchargement

La base de données et les images peuvent être téléchargées ici.

Le dossier « Images » doit être logé dans le répertoire de la base de données.