Apprendre comment composer un devis, analyser la rentabilité du chantier et présenter des statistiques avec Access

Claude Leloup

Les points techniques suivants seront abordés dans ce tutoriel :

- nombreux exemples de formulaires « pères/fils » ;

- comment exploiter le contenu des colonnes cachées dans une zone de liste ;

- comment construire une requête « Union » ;

- un formulaire indépendant (c'est-à-dire sans source) dans lequel le contenu des différents champs est créé à la volée ;

- les précautions à prendre lorsqu'on crée ou qu'on supprime des enregistrements dans un jeu de tables mises en relation avec intégrité référentielle.

Pour réagir, un espace de discussion est ouvert : Commentez

- Titre : Apprendre comment composer un devis, analyser la rentabilité du chantier et présenter des statistiques avec Access

- Auteur : Claude Leloup

- Parution : 19 mars 2017

- Mise à jour : 19 mars 2017

- 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 ® 2017 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. Contexte

- II. Prérequis

- III. Un formulaire pour construire un devis

- III-A. Ce qu'on veut faire

- III-B. Comment on le fait

- III-B-1. Les tables en jeu

- III-B-2. Le formulaire père

- III-B-3. Le formulaire fils

- III-B-4. Créer un clone au départ d'un ancien devis

- III-B-5. Imprimer le document

- IV. Un formulaire pour enregistrer les écarts sur les chantiers réalisés

- IV-A. Ce qu'on voudrait faire

- IV-B. Comment on le fait

- IV-B-1. Les tables en jeu

- IV-B-2. Un père, deux fils

- V. Un formulaire pour enregistrer les données d'un client et retrouver les devis qui le concernent

- V-A. Ce qu'on voudrait faire

- V-B. Comment on le fait

- V-B-1. fClient

- V-B-2. sfClientDevis

- VI. Un formulaire pour afficher quelques statistiques

- VI-A. Ce qu'on voudrait faire

- VI-B. Comment on le fait

- VI-B-1. Des noms particuliers pour les champs mensuels

- VI-B-2. Alimenter les champs du formulaire

- VII. Téléchargement

- VIII. Remerciements

I - Contexte

Cette discussion sur le forum.

II - Prérequis

Ce tutoriel s'adresse à des utilisateurs qui maitrisent déjà les bases du logiciel Access ou qui veulent faire l'effort pour progresser : l'utilisation du VBA décuple le potentiel de solutions !

Pour vérifier votre niveau, parcourez ces tutoriels : si vous les comprenez facilement, vous êtes OK et si ce n'est pas le cas, insistez :

- pour commencer : Maxence Hubiche Access - Les Bases ;

- pour construire des requêtes : Jean Ballat  Créer des requêtes simples ;

- pour construire un formulaire :Jean-Philippe Ambrosino le chapitre 2-1-2 de Mise en surbrillance d'un enregistrement dans un formulaire ;

- pour le VBA : Olivier Lebeau Initiation au VBA Office.

D'une manière générale, pour vous documenter sur les propriétés d'un formulaire ou d'un état, ou de leurs contrôles :
- affichez l'objet en mode création ;
- cliquez sur la propriété, elle se met alors en surbrillance ;
- enfoncez la touche <F1>.
Pour un problème de code dans un module, placez le curseur n'importe où dans l'instruction et pressez <F1>.

L'aide Access s'ouvre alors à la bonne page.

On peut aussi :
- ouvrir l'aide <F1>, choisir l'onglet « Aide intuitive » et suivre les instructions ;
- ouvrir la fenêtre d'exécution (<Ctrl> + G), saisir un mot-clé, y placer le point d'insertion et presser <F1>.

III - Un formulaire pour construire un devis

III-A - Ce qu'on veut faire

On voudrait pouvoir compléter dans un formulaire les éléments (standards, mais modifiables) qui vont déterminer le prix que nous voulons proposer au client.

Cliquez sur l'image pour l'agrandir

La date à laquelle le devis est établi.

La date à laquelle le client a marqué son accord.

Référence libre qui permet de distinguer un type de devis pour un client régulier (plusieurs demandes successives).

Une liste déroulante qui permet de choisir un des clients.

Permet d'intégrer un texte « standard » (préencodé dans une table) qui sert d'amorce pour la description de l'objet du devis. L'utilisateur le complétera ensuite selon les circonstances.

Certains fournisseurs communiquent un tarif qui mentionne pour leurs articles :
- le prix public ;
- la ristourne qu'ils accordent aux professionnels.
Un clic sur le bouton permet de choisir l'article dans la table. Par exemple ici :

le fournisseur nous octroie 10 % de remise, nous souhaitons 40 % de marge sur la livraison et nous estimons qu'il faudra quatre heures et demie à deux hommes pour monter le portail.

Les cases « Prix public », « K achat », « Prix achat », « K vente », « Heures pose » et « Nbre poseurs » sont complétées automatiquement dans le devis, mais l'utilisateur peut les modifier si nécessaire.

Ces deux contrôles permettent de compléter (éventuellement) le libellé de l'article pour y ajouter les dimensions.

Ce taux horaire est une estimation du prix à facturer au client pour couvrir le coût salarial complet d'une heure passée sur un chantier (salaire de l'ouvrier + charges sociales + temps de trajet…). Par défaut, c'est la valeur inscrite dans une table de paramètres,

mais elle peut être adaptée manuellement dans le devis, si nécessaire.

Il s'agit du coût d'accessoires éventuels : un moteur, une télécommande…

Une liste déroulante permet de choisir le taux de TVA. En février 2017 (pour la France) :
- 5,50 % pour les travaux d'amélioration de la qualité énergétique des logements ;
- 10,00 % pour les travaux d'amélioration, de transformation, d'aménagement et d'entretien portant sur des locaux à usage d'habitation achevés depuis plus de 2 ans ;
- 20,00 % dans les autres cas.

Un devis ancien peut servir d'exemple pour construire une nouvelle demande pour un autre client quitte à adapter quelques éléments du « clone ».

Un clic et le document destiné au client s'affiche :

La durée du chantier, c'est la somme des « Heures de pose » multipliées par leur « Quantité » (incluses celles de l'ajout). Ici (4,50 X 1) + (2,50 X 1) + (0,50 X 1) pour l'ajout que l'on peut voir en double-cliquant sur  :

Un clic sur ce bouton supprimera la ligne du produit. (Surtout utile dans le cas où il s'agit d'un devis « cloné » au départ d'un devis plus étoffé).

III-B - Comment on le fait

III-B-1 - Les tables en jeu

Cliquez sur l'image pour l'agrandir

Le formulaire est construit sur la base d'un père (fDevis, le formulaire principal) et un fils (sfLignesDevis, le sous-formulaire inclus).

Si la technique père/fils ne vous est pas familière, voyez ce tutoriel

Comment classer les données dans des tables liées et construire un formulaire père/fils

En bref, la source du formulaire principal (le père) et la source du sous-formulaire (le fils) ont une clé commune, le fils n'affiche que les enregistrements qui ont la même clé que celle affichée par le père.

III-B-2 - Le formulaire père

Les données du client

La zone de liste aura donc six colonnes, mais seule la deuxième sera affichée, dans l'ordre alphabétique.

Le champ cboClient contiendra la clé du client (tClientsFK).

Dans la deuxième colonne, la concaténation du nom et du prénom séparés par une virgule et un espace.

Dans cette colonne, on a concaténé les éléments de l'adresse. Car(13) & Car(10) va provoquer un saut de ligne à l'affichage.

Remarquez la syntaxe employée pour faire référence à la 3e colonne de la zone de liste : (2) représente l'indice et Access commence à zéro !
Par analogie, on aura :

Durée du chantier en heures

La durée du chantier, c'est la somme des « Heures de pose » multipliées par leur « Quantité » (incluses celles de l'ajout).

Voici comment nous avons procédé.

1° D'abord une requête pour ramener Heures de pose X Quantité des produits :

soit le SQL :

SELECT [HeuresPose]*[Quantite] AS HrsChantier, ([PrixAchat]+[TotalMO])*[Quantite] AS PR
FROM tDevis INNER JOIN tLignesDevis ON tDevis.tDevisPK = tLignesDevis.tDevisFK
WHERE (((tDevis.tDevisPK)=[Formulaires]![fDevis]![TXTtDevisPK]));

2° Une seconde requête pour les ajouts éventuels

soit le SQL :

SELECT [tAjouts].[HeuresPose]*[tAjouts].[Quantite] AS HrsChantier, ([tAjouts].[PrixAchat]+[tAjouts].[TotalMO])*[tAjouts].[Quantite] AS PR
FROM tLignesDevis INNER JOIN tAjouts ON tLignesDevis.tLignesDevisPK = tAjouts.tLignesDevisFK
WHERE (((tLignesDevis.tDevisFK)=[Formulaires]![fDevis]![TXTtDevisPK]));

3° Puisque ces deux requêtes ont le même nombre de colonnes et dans le même ordre, on peut les fusionner pour obtenir une « requête Union ».

Il suffit de récupérer les deux SQL, supprimer le point-virgule final du premier et les joindre avec le mot-clé UNION ALL :

SELECT [HeuresPose]*[Quantite] AS HrsChantier, ([PrixAchat]+[TotalMO])*[Quantite] AS PR
FROM tDevis INNER JOIN tLignesDevis ON tDevis.tDevisPK = tLignesDevis.tDevisFK
WHERE (((tDevis.tDevisPK)=[Formulaires]![fDevis]![TXTtDevisPK]))
UNION ALL
SELECT [tAjouts].[HeuresPose]*[tAjouts].[Quantite] AS HrsChantier, ([tAjouts].[PrixAchat]+[tAjouts].[TotalMO])*[tAjouts].[Quantite] AS PR
FROM tLignesDevis INNER JOIN tAjouts ON tLignesDevis.tLignesDevisPK = tAjouts.tLignesDevisFK
WHERE (((tLignesDevis.tDevisFK)=[Formulaires]![fDevis]![TXTtDevisPK]));

4° Finalement, on recourt à la fonction de domaine SomDom() pour récupérer le total dans le formulaire

=SomDom("HrsChantier";"rHrsChantierEtMargeDevisDetail").

Marge

On suit la même démarche que ci-dessus.

Ces deux boutons déclenchent respectivement la procédure pour créer un « clone » du devis et pour imprimer le document destiné au client. Ces processus seront décrits plus loin dans ce tutoriel.

III-B-3 - Le formulaire fils

Un clic sur ce bouton déclenche ce code :

Private Sub btImporterTexte_Click()
If CurrentProject.AllForms("fTextes").IsLoaded Then DoCmd.Close acForm, "fTextes"
DoCmd.OpenForm "fTextes", , , , , acHidden
PositionForm Forms("fTextes"), Me.btImporterTexte
End Sub

C'est la syntaxe pour ouvrir un formulaire (ici fTextes) et faire en sorte qu'il s'affiche immédiatement en dessous d'un contrôle (ici le bouton btImporterTexte) du formulaire d'où part l'appel.

La routine PositionForm() est proposée à cette adresse par Arkham46.
Même si vous ne comprenez pas à quoi servent toutes les API auxquelles il fait appel dans son code, faites confiance : ça marche au poil !

Il suffit de copier le code proposé dans un module (dans notre base exemple, c'est le module mArkham46).

Et pour l'utiliser, deux lignes de code :

DoCmd.OpenForm "FormulaireAPositionner", , , , , acHidden
PositionForm Forms("FormulaireAPositionner"), Me.UnControle

Deux précautions :

- le formulaire à ouvrir doit être en fenêtre indépendante :

- en cas d'appels successifs, refermez d'abord le formulaire précédemment ouvert avant de l'ouvrir une nouvelle fois.

Cela provoque l'ouverture du formulaire fTextes :

Code associé au double-clic sur le texte :

Private Sub txtTexte_DblClick(Cancel As Integer)
'Vérifer que le formulaire fDevis est actuellement ouvert
If CurrentProject.AllForms("fDevis").IsLoaded = True Then
'si oui, exporter le contenu
Forms!fDevis!CTNRsfLignesDevis.Form!txtTexte = Me.txtTexte
'et refermer fTextes
DoCmd.Close acForm, Me.Name
End If
End Sub

Le formulaire fTextes est construit comme un formulaire de recherche multicritère. Vous trouverez la description détaillée de la technique dans ce tutoriel : Langue Formulaire de recherche polyvalent sur la base d'une requête enregistrée.

Un clic sur ce bouton déclenche un processus analogue, si ce n'est que le formulaire ouvert concerne cette fois les produits :

Dans le formulaire fProduits, il est convenu qu'on communique soit le « Prix public » (et le K achat), soit le « Prix d'achat », mais pas les deux en même temps pour éviter les incohérences. En cas de non-respect, les deux champs s'affichent en rouge.
Pour ce faire, on utilise la mise en forme conditionnelle :

Si après l'import des données dans la ligne du devis, l'utilisateur décide de modifier l'une d'elles, le processus suivant se déclenche :

Public Sub txtPrixPublic_AfterUpdate()
Me.txtPrixAchat = Me.txtPrixPublic * Me.txtKachat
Call CalculTotalLigne
End Sub

Private Sub txtKachat_AfterUpdate()
Call txtPrixPublic_AfterUpdate
Call CalculTotalLigne
End Sub

Private Sub txtPrixAchat_AfterUpdate()
Me.txtPrixPublic = Null
Me.txtKachat = Null
Call CalculTotalLigne
End Sub

Private Sub txtKvente_AfterUpdate()
Call CalculTotalLigne
End Sub

Private Sub txtHeuresPose_AfterUpdate()
Call CalculTotalLigne
End Sub

Private Sub txtNbrePoseurs_AfterUpdate()
Call CalculTotalLigne
End Sub

Private Sub txtCoutHoraire_AfterUpdate()
Call CalculTotalLigne
End Sub

Tous ces évènements appellent la même routine CalculTotalLigne que voici :

Public Sub CalculTotalLigne()
If Me.txtPrixAchat = 0 Then Me.txtPrixAchat = Me.txtPrixPublic * Me.txtKachat
Me.txtMO = Me.txtHeuresPose * Me.txtNbrePoseurs * Me.CoutHoraire
Me.txtPrixUnitaire = Me.txtPrixAchat * Me.txtKvente _
+ Me.txtMO _
+ Me.txtTotalAjout
'Arrondi du prix unitaire
Me.txtPrixUnitaire = Round(Me.txtPrixUnitaire, 1)
Me.txtTotalLigne = Me.txtPrixUnitaire * Me.txtQuantite
Me.Refresh
Me.Parent!txtTotalDevis.Requery
End Sub

Encoder un ajout

Un double-clic sur le champ Total ajouts provoque l'ouverture du formulaire fAjouts :

L'utilisateur complète les données et lorsqu'il referme le formulaire, le champ Total ajouts est mis à jour :

Private Sub Form_Close()
'Mise à jour de TotalAjouts dans le devis
Forms!fDevis!CTNRsfLignesDevis.Form!txtTotalAjout = _
Nz(DSum("TotalAjout", "tAjouts", "tLignesDevisFK=" & Me.TXTtLignesDevisFK), 0)
'Mise à jour des sous-totaux dans le devis
Call Forms.fDevis.CTNRsfLignesDevis.Form.CalculTotalLigne
'Sauvegarde des modif dans le devis
Forms.fDevis.CTNRsfLignesDevis.Form.Refresh
End Sub

Remarquez l'instruction 6, où l'on appelle une routine logée dans un autre formulaire !

Raison pour laquelle cette dernière avait été déclarée Public :

Un clic sur ce bouton et la ligne disparaît.

Private Sub btSupprimer_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "rsfLignesDevisSupprAjout"
DoCmd.OpenQuery "rsfLignesDevisSupprLigne"
DoCmd.SetWarnings True
Me.Parent.Refresh
End Sub

Ce qui revient à exécuter successivement :

- une requête pour supprimer les ajouts (éventuels) de la ligne concernée :

- une seconde requête pour supprimer la ligne elle-même :

L'ordre dans lequel on supprime est important !

Comme il s'agit de tables liées avec intégrité référentielle, il faut d'abord supprimer celle du côté de la relation :

III-B-4 - Créer un clone au départ d'un ancien devis

Cliquez sur l'image pour l'agrandir

À première vue, il « suffit » de copier :

- dans chaque table (tDevis, tLignesDevis et tAjouts) ;

- les enregistrements relatifs à l'original.

Le hic, c'est que ces enregistrements sont chaque fois liés par une relation avec intégrité référentielle.

Il faudra donc procéder dans l'ordre :

- commencer par la table qui est le plus du côté

- quand l'enregistrement d'une table a été créé, il faut récupérer la clé primaire qui a été attribuée par Access, car elle sert de clé externe dans la table située du côté .

Voici le code qui se déclenche lorsque le client du clone a été choisi :

On s'accroche !

1. Private Sub cboClient_AfterUpdate()

2. On Error GoTo GestionErreurs

3. Dim oQry As DAO.QueryDef

4. Dim oRst As DAO.Recordset

5. Dim ltDevisPK As Long

6. Dim ltLignesDevisPK As Long

7. 'Créer l'enregistrement du clone dans tDevis

8. DoCmd.SetWarnings False

9. Set oQry = CurrentDb.QueryDefs("rCloneDevis")

10. oQry.Parameters("tClientFK") = Me.cboClient

11. oQry.Execute

12. 'Récupérer le tDevisPK qui vient d'être créé

13. ltDevisPK = DMax("tDevisPK", "tDevis")

14. 'Créer les clones de chaque ligne devis

15. 'Créer un recordset des lignes à cloner

16. Set oRst = CurrentDb.OpenRecordset("SELECT * FROM tLignesDevis WHERE tDevisFK=" & Forms!fDevis!TXTtDevisPK & ";", dbOpenDynaset)

17. Do While Not oRst.EOF

18. 'Sauver chaque ligne dans une table temporaire

19. DoCmd.RunSQL "SELECT * INTO temp1 FROM tLignesDevis WHERE tLignesDevisPK=" & oRst("tLignesDevisPK") & ";"

20. 'Modifier le tDevisFK (=>celui du devis cloné)

21. DoCmd.RunSQL "UPDATE temp1 SET tDevisFK =" & ltDevisPK & ";"

22. 'Cloner cette ligne

23. DoCmd.OpenQuery "rAjoutLigneClonee"

24. 'Récupérer le tLignesDevisPK qui vient d'être créé

25. ltLignesDevisPK = DMax("tLignesDevisPK", "tLignesDevis")

26. 'Copier dans temp les ajouts associés à la ligne du devis original

27. DoCmd.RunSQL " SELECT * INTO temp2 FROM tAjouts WHERE tLignesDevisFK=" & oRst("tLignesDevisPK") & ";"

28. 'Modifier le tLignesDevisFK (=>celui du devis cloné)

29. DoCmd.RunSQL "UPDATE temp2 SET tLignesDevisFK = " & ltLignesDevisPK & ";"

30. 'Cloner les ajouts

31. DoCmd.OpenQuery "rAjoutAjoutsClones"

32. oRst.MoveNext

33. Loop

34. 'Positionner fDevis sur le devis cloné

35. DoCmd.Close acForm, "fDevis"

36. DoCmd.OpenForm "fDevis", , , "tDevisPK = " & ltDevisPK

38. Sortie:

39. DoCmd.Close acForm, Me.Name

40. Set oQry = Nothing

41. oRst.Close

42. Set oRst = Nothing

43. 'supprimer la table temp

44. DoCmd.DeleteObject acTable, "temp1"

45. DoCmd.DeleteObject acTable, "temp2"

46. DoCmd.SetWarnings True

48. GestionErreurs:

49. Select Case Err.Number

50. Case 0 ' pas d'erreur

51. Case Else

52. MsgBox "Erreur dans Sub cboClient_AfterUpdate N° " & Err.Number & " " & Err.Description

53. End Select

54. End Sub

Commentaires du code

N° de ligne

8-11

On clone l'enregistrement de l'original dans tDevis.

PARAMETERS [tClientFK] Long;
INSERT INTO tDevis ( tClientsFK, DevisDate )
SELECT [tclientFK] AS Expr1, Date() AS Expr2;

13

On récupère la clé (tDevisPK). En fait, c'est le N° auto le plus élevé actuellement contenu dans la table tDevis.

16-33

Puisqu'on connait maintenant leur clé externe, on peut cloner les enregistrements de tLignesDevis de l'original. Mais, ça se corse : il peut y en avoir plusieurs et chacun d'eux peut avoir plusieurs enregistrements liés dans dans tAjouts.

On va procéder comme suit :

- d'abord créer un jeu avec chaque enregistrement à dupliquer dans tLignesDevis (un recordset) (instruction 16) pour pouvoir les traiter l'un après l'autre dans une boucle (instructions 17 à 33) ;

- tour à tour, on loge chaque enregistrement du recordset dans une table temp1 (instruction 19), le SQL exécuté correspond à une requête comme celle-ci :

- dans l'enregistrement de temp1, on remplace le tDevisFK de l'original par la clé du devis du clone (instruction 20) ;

- instruction 23, on ajoute cet enregistrement dans la tLignesDevis :

Chaque colonne de temp1 est copiée dans un nouvel enregistrement de tLignesDevis ;

- pour cette ligne que nous venons d'ajouter dans tLignesDevis, il faut créer dans tAjouts les clones des enregistrements (éventuels) correspondants.
En 25, nous récupérons la clé tLignes DevisPK qui vient d'être créée.
En 27, nous copions dans une autre table temp2 les enregistrements tAjouts de l'original.
En 29, nous remplaçons la valeur de la clé externe tLignesDevisFK par celle du clone.
En 31, nous ajoutons tout le contenu de temp2 dans tAjouts.
En 33, on reboucle pour cloner la ligne suivante.

35-36

On ferme le formulaire fDevis et on le rouvre immédiatement à la page du devis cloné.

39

On ferme le formulaire fClientClone (celui qui accueille ce code).

40-46

On termine proprement : libérer les variables en mémoire, supprimer temp1 et temp2, et rétablir les avertissements.

III-B-5 - Imprimer le document

Le clic sur le bouton provoque l'affichage de eDevis dont voici la requête source :

Cliquez sur l'image pour l'agrandir

Cliquez sur l'image pour l'agrandir

Tous les éléments de l'adresse sont concaténés dans une seule colonne. Des & Car(13) & Car(10) pour provoquer des retours à la ligne lors de l'impression :

Pour le traitement de l'image, voyez ce tutoriel : Stockez les images statiques de vos formulaires et états Access hors de la base de données.

IV - Un formulaire pour enregistrer les écarts sur les chantiers réalisés

IV-A - Ce qu'on voudrait faire

Ce formulaire reprend, un à un, tous les devis pour lesquels la date d'acceptation a été mentionnée dans fDevis.
une zone de liste permet d'afficher un devis particulier.

Les données du devis sont reprises telles qu'elles figurent dans fDevis.

Ces quatre valeurs sont souvent égales à celles du devis, le programme les propose par défaut. L'utilisateur les modifie si nécessaire.

L'utilisateur introduit ici le détail des prestations réelles. Le total des heures et le coût de la M.-O. (paramètre « CoutHoraire ») sont automatiquement reportés dans la colonne .

Les écarts sont calculés : Réel - Devis. Quand ils ont un effet positif sur la marge bénéficiaire, ils s'affichent en vert, sinon en rouge.

IV-B - Comment on le fait

IV-B-1 - Les tables en jeu

IV-B-2 - Un père, deux fils

fChantiers est le père de sfChantiers (pour afficher les lignes du devis) et sfHeuresChantiers (pour permettre d'encoder les prestations réelles).

Notez que la hauteur de sfHeuresChantiers s'adapte à son nombre d'enregistrements.

On applique ici la technique décrite dans ce tutoriel : Comment ajuster la taille d'un sous-formulaire en fonction du nombre de ses enregistrements.

La source de fChantiers

C'est la table tChantiers. Celle-ci est complétée automatiquement, chaque fois que l'on introduit une date d'acceptation dans tDevis.

1. Private Sub txtAccepteDate_AfterUpdate()

2. 'Mise à jour des tables tChantiers et tLignesChantiers

3. Me.Refresh

4. DoCmd.SetWarnings False

5. DoCmd.OpenQuery "rAjoutTChantiers"

6. DoCmd.OpenQuery "rAjoutTLignesChantiers"

7. DoCmd.SetWarnings True

8. 'Rafraîchir fClients s'il est ouvert

9. If CurrentProject.AllForms("fClients").IsLoaded Then

10. Forms!fClients!CTNRsfClientDevis.Requery

11. End If

12. End Sub

Chaque fois qu'une date d'acceptation est modifiée, ces deux requêtes sont actionnées :

Notez qu'elles ne risquent pas de créer de doublons étant donné la définition des deux tables :

Les données relatives au devis dans fChantiers

On utilise la fonction de domaine RechDom() pour alimenter les champs :

Syntaxe pour les heures prestées et coût de la main-d'œuvre

Les écarts

Ils sont construits « à la Excel » :

Mise en forme conditionnelle des champs « écart »

Par exemple pour les écarts sur le prix facturé au client :

Si l'écart (Réel - Devis) est négatif, cela détériore la marge => en rouge.

A contrario, si positif => en vert.

Remarquez que l'on compare à 0,0099 et non zéro pour se protéger des différences d'arrondis éventuelles.

V - Un formulaire pour enregistrer les données d'un client et retrouver les devis qui le concernent

V-A - Ce qu'on voudrait faire

Une zone de liste permet d'accéder aux données d'un client choisi.

Les devis de ce client sont listés.

Si on double-clique sur une case dans la colonne , ce devis s'affiche.

Si on double-clique sur une case qui mentionne une date d'acceptation, c'est la fiche chantier qui apparaît à l'écran.

V-B - Comment on le fait

Encore une fois, un père (fClients) et un fils (sfClientDevis).

V-B-1 - fClient

Il s'agit d'un banal formulaire d'encodage, qui ne nécessite aucun commentaire.

V-B-2 - sfClientDevis

Source

Pour afficher un devis

Private Sub txtDevisDate_DblClick(Cancel As Integer)
DoCmd.OpenForm "fDevis", acNormal, , "[tDevisPK]=" & Me.tDevisPK
End Sub

Pour afficher une fiche Chantier

Private Sub txtAccepteDate_DblClick(Cancel As Integer)
If Not IsNull(Me.txtAccepteDate) _
Then DoCmd.OpenForm "fChantiers", acNormal, , "[tChantiersPK]=" & Me.tChantiersPK
End Sub

VI - Un formulaire pour afficher quelques statistiques

VI-A - Ce qu'on voudrait faire

Dans cet exemple, l'exercice comptable court de juillet à juin de l'année civile suivante. Par défaut, c'est l'année comptable en cours qui s'affiche, mais l'utilisateur peut en choisir une précédente.

On veut présenter, au mois le mois, l'évolution de ces quatre données de gestion.

C'est le chiffre d'affaires cumulé depuis le début de la période jusqu'à aujourd'hui.

C'est le chiffre d'affaires qui reste à réaliser sur les devis qui ont été acceptés, mais qui ne sont pas encore terminés aujourd'hui.

VI-B - Comment on le fait

Ce formulaire est indépendant : il n'a pas de source.
Le contenu des différents champs est créé à la volée, soit à l'ouverture ou lorsque l'utilisateur modifie l'année de l'exercice.

VI-B-1 - Des noms particuliers pour les champs mensuels

VI-B-2 - Alimenter les champs du formulaire

La période

À l'ouverture, par défaut l'année est celle en cours :

'Aménager la période
Me.txtDu = DateSerial(txtExerciceFiscal, 7, 1)
Me.txtAU = DateSerial(txtExerciceFiscal + 1, 6, 30)
Me.etPeriode.Caption = "(période du " & Me.txtDu & " au " & Me.txtAU & ")."

Les données mensuelles

On va explorer chaque contrôle du formulaire et si on constate que son nom commence par « Stat » , on choisit l'algorithme qui correspond à l'une des quatre lignes .

Exemple de construction des paramètres de la fonction de domaine Dcount() utilisée :

C.A. cumulé

À ce stade, les C.A. mensuels ont été construits. Il suffit de lancer une boucle pour les cumuler :

'Aménager C.A. cumulé
Me.txtCACumul = 0
For i = 1 To 12
Me.txtCACumul = Me.txtCACumul + Me("StatCAMois" & Format(i, "00"))
Next i

C.A. devis acceptés non encore terminés aujourd'hui

On utilise la fonction de domaine RechDom sur cette requête qui ne ramène qu'une ligne :

VII - Téléchargement

La base exemple en version Access2000 est disponible ici.

L'archive complète doit être décompressée dans un répertoire quelconque.

VIII - Remerciements

Merci à  Guillaume_b03 pour l'aspect métier.

Merci à Chrtophe, tee_grandbois, gaby277, Winjerome pour leurs remarques et à f-leb pour la correction orthographique.