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

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.

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 3^e 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.

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 :

 

Sélectionnez

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 :

À 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 !

Private Sub cboClient_AfterUpdate()
  On Error GoTo GestionErreurs
  Dim oQry As DAO.QueryDef
  Dim oRst As DAO.Recordset
  Dim ltDevisPK As Long
  Dim ltLignesDevisPK As Long
  'Créer l'enregistrement du clone dans tDevis
  DoCmd.SetWarnings False
  Set oQry = CurrentDb.QueryDefs("rCloneDevis")
  oQry.Parameters("tClientFK") = Me.cboClient
  oQry.Execute
  'Récupérer le tDevisPK qui vient d'être créé
  ltDevisPK = DMax("tDevisPK", "tDevis")
  'Créer les clones de chaque ligne devis
     'Créer un recordset des lignes à cloner
     Set oRst = CurrentDb.OpenRecordset("SELECT * FROM tLignesDevis WHERE tDevisFK=" & Forms!fDevis!TXTtDevisPK & ";", dbOpenDynaset)
     Do While Not oRst.EOF
       'Sauver chaque ligne dans une table temporaire
       DoCmd.RunSQL "SELECT * INTO temp1 FROM tLignesDevis WHERE tLignesDevisPK=" & oRst("tLignesDevisPK") & ";"
       'Modifier le tDevisFK (=>celui du devis cloné)
       DoCmd.RunSQL "UPDATE temp1 SET tDevisFK =" & ltDevisPK & ";"
       'Cloner cette ligne
       DoCmd.OpenQuery "rAjoutLigneClonee"
       'Récupérer le tLignesDevisPK qui vient d'être créé
       ltLignesDevisPK = DMax("tLignesDevisPK", "tLignesDevis")
       'Copier dans temp les ajouts associés à la ligne du devis original
       DoCmd.RunSQL " SELECT * INTO temp2 FROM tAjouts WHERE tLignesDevisFK=" & oRst("tLignesDevisPK") & ";"
       'Modifier le tLignesDevisFK (=>celui du devis cloné)
       DoCmd.RunSQL "UPDATE temp2 SET tLignesDevisFK = " & ltLignesDevisPK & ";"
       'Cloner les ajouts
       DoCmd.OpenQuery "rAjoutAjoutsClones"
       oRst.MoveNext
     Loop
  'Positionner fDevis sur le devis cloné
  DoCmd.Close acForm, "fDevis"
  DoCmd.OpenForm "fDevis", , , "tDevisPK = " & ltDevisPK
            
Sortie:
  DoCmd.Close acForm, Me.Name
  Set oQry = Nothing
  oRst.Close
  Set oRst = Nothing
  'supprimer la table temp
  DoCmd.DeleteObject acTable, "temp1"
  DoCmd.DeleteObject acTable, "temp2"
  DoCmd.SetWarnings True
            
GestionErreurs:
  Select Case Err.Number
    Case 0 ' pas d'erreur
    Case Else
      MsgBox "Erreur dans Sub cboClient_AfterUpdate N° " & Err.Number & " " & Err.Description
  End Select
End Sub

Commentaires du code

N° de ligne
8-11	On clone l'enregistrement de l'original dans tDevis.   Sélectionnez 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.

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

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.

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.

Private Sub txtAccepteDate_AfterUpdate()
  'Mise à jour des tables tChantiers et tLignesChantiers
  Me.Refresh
  DoCmd.SetWarnings False
  DoCmd.OpenQuery "rAjoutTChantiers"
  DoCmd.OpenQuery "rAjoutTLignesChantiers"
  DoCmd.SetWarnings True
  'Rafraîchir fClients s'il est ouvert
  If CurrentProject.AllForms("fClients").IsLoaded Then
      Forms!fClients!CTNRsfClientDevis.Requery
  End If
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.

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

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

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 :