Tutoriel

Appliquer le CRUD (Create - Read - Update - Delete) en vba

Que faire des données ?

Quand on gère des données, il n'y a pas à chercher loin pour savoir quoi faire.

Il n'y a que quatre actions possibles :

  • créer (Create) ;
  • consulter (Read) :
    Ce qui inclut la navigation dans les données ;
  • modifier (Update) ;
  • supprimer (Delete).

Problématiques lors de la gestion de données

Selon le contexte, certaines actions du CRUD sont possibles et d'autres pas, les données doivent être en lecture seule ou éditables, les boutons "Annuler" et "Valider" doivent être activés ou pas.

L'autre aspect contraignant est la navigation au sein du formulaire.

Le positionnement des enregistrements (sur le premier, sur le dernier) a aussi un impact sur l'état du formulaire, notamment lors de la suppression d'enregistrements.

L'état des enregistrements a aussi un impact surtout quand il n'y en a pas ou plus.

Il n'est pas toujours simple de synchroniser ces contraintes, c'est pourquoi je vous propose une démarche qui j'espère vous aidera dans vos développements.

Bien sûr ce projet est orienté VBA, mais la démarche s'applique à tous les langages.

Capture écran formulaire crud en mode création

Télécharger le projet

Chaque étape de réalisation est accompagnée d'un classeur exemple que vous pouvez télécharger.

Version 1 : crud-01.

Version 2 : crud-02.

Version 3 : crud-03.

Version 4 : crud-04.

Version 5 : crud-05.

Etat des lieux

Définir le CRUD

Je recense les actions dans une énumération.

Si le sujet vous est peu familier, je vous renvoie vers mon article "Comprendre les énumérations".

Enum ECrud

  Create = 1

  Read = 2

  Update = 4

  Delete = 8

End Enum

J'ai donc un ensemble de constantes représentant les 4 actions possibles.

Recenser les boutons

Il y aura six boutons.

Les quatre boutons liés au CRUD et les boutons "Valider" et "Annuler".

Capture écran formulaire crud read

Là aussi, je vais créer une énumération.

Public Enum EButton

  btnCreate = 1

  btnRead = 2

  btnUpdate = 4

  btnDelete = 8

  btnOk = 16

  btnCancel = 32

End Enum

Remarques sur la lecture

Pour la lecture, j'aurais pu utiliser des boutons de navigation.

Pour l'heure, j'utiliserais une liste (ComboBox) pour choisir l'enregistrement à atteindre.

La gestion sera plus simple et si un jour je veux l'implémenter, on verra que cette façon de faire va largement faciliter son développement.

En fait, sur les boutons de la barre de navigation, il suffira de changer le numéro de l'index à lire dans la liste de choix (enfin à quelques détails près).

Statut des boutons

Pas de données disponibles

Dans ce cas, la liste de choix doit être désactivée et le formulaire doit être en mode création (ECRUD.Create).

Seuls les boutons "Valider" et "Annuler" sont actifs.

Capture écran formulaire crud create

En mode création et modification

Les boutons "Annuler" et "Valider" doivent être allumés.

Les boutons "Supprimer" et la combobox sont désactivés.

S'il existe des données, la liste de choix pourrait restée active, ce qui a pour conséquence de revenir en mode lecture et abandonnée l'action en cours.
Je n'envisagerai pas ce cas.

Bien sûr si nous sommes en mode nouveau, le bouton modifier est désactivé et si nous sommes en modification, le bouton créer est désactivé.

Le bouton de suppression est spécial en ce sens, que son action est directe et n'oblige pas à modifier l'état des autres boutons.

Toutefois si suite à la suppression, il ne reste plus de données, le formulaire passe en mode création.

Lecture des données

Les boutons "Nouveau", "Modifier", "Supprimer" sont activés.

Les boutons "Annuler" et "Valider" sont désactivés.

Valider ou annuler une action

Dans les deux cas, le formulaire repasse en mode consultation.

Mais si on annule la création de données et qu'il n'en existait pas avant, le formulaire reste en mode ajout.

Boutons allumés selon le statut actuel

Les boutons associés à une action seront conservés dans des constantes.

Action "Create"

Sur le bouton de création "Nouveau", il ne peut y avoir que les boutons "Valider" et "Annuler" qui soient disponibles.

J'ai donc la constante suivante :

Private Const cs_WhatButtonCreate As Long = EButton.btnOk Or EButton.btnCancel.

Si vous ne comprenez pas l'usage du "Or", là encore, retournez-vous vers mon article "Comprendre les énumérations".

Action "Read"

C'est donc une liste de choix qui assure la lecture (navigation).

Dans ce mode, trois boutons doivent être actifs

  • Nouveau ;
  • Modifier ;
  • Supprimer.

Cela me donne la constante :

Private Const cs_WhatButtonRead As Long = ECRUD.Create Or ECRUD.Read Or ECRUD.Update Or ECRUD.Delete.

ECrud.Update

Le mode "Modifier", est identique au mode "Nouveau".

La constante est donc la même.

Private Const cs_WhatButtonUpdate As Long = cs_WhatButtonCreate.

ECrud.Delete

Le bouton "Supprimer" est un peu particulier, il ne demande pas de désactiver et/ou activer d'autres boutons.

Quand on clique dessus, l’action est immédiatement appliquée.

Comme en mode consultation, les autres boutons peuvent rester actifs.

C'est la même chose qu'en lecture :

Private Const cs_WhatButtonDelete As Long = cs_WhatButtonRead.

Éléments nécessaires

J'aurais un module "pkgEnum" pour gérer les énumérations et la synchronisation entre les boutons et le mode CRUD.

Capture écran gestionnaire de projets vba

Pour faciliter la compréhension du projet, je n'utiliserais pas un module de classe, mais je développerais quand même dans cette optique.

Ainsi, bien que travaillant avec des modules standard, j'utiliserais principalement des propriétés.

J'ai besoin de deux propriétés.

Mode actuel du CRUD

Private m_ModeCRUD As ECRUD

J'aurais une propriété CurrentCRUD qui est en lecture et écriture, pour accéder à la variable m_ModeCRUD.

Public Property Get CurrentCRUD() As ECRUD

  CurrentCRUD = m_ModeCRUD

End Property

Public Property Let CurrentCRUD(ByVal argCRUD As ECRUD)

  m_ModeCRUD = argCRUD

  WhatButton

End Property

Les boutons à allumer selon le mode choisi

Private m_Button As EButton

Pour la variable m_Button la propriété s'appelera CurrentButton. Celle-ci sera en lecture seule.

Public Property Get CurrentButton() As EButton

  CurrentButton = m_Button

End Property

Manipuler les énumérations

Un module « pkgEnum » contient les fonctions nécessaires à la gestion des énumérations.

Décoder un nombre

La fonction Decode(), permet de prendre un nombre et connaitre les valeurs de l’énumération qui le compose.

Compter les constantes

La fonction CountConstant() permet de connaître combien de valeurs constitués l’énumération.

Elle n’est utilisée que par la fonction Decode().

Pour comprendre leur rôle là encore vous pouvez aller voir l'article "Comprendre les énumérations".

Association bouton - action

Enfin une procédure WhatButton() associe les actions aux boutons à allumer.

Private Sub WhatButton()

  Select Case m_ModeCRUD

    Case Create

      m_Button = cs_WhatButtonCreate

    Case Read

      m_Button = cs_WhatButtonRead

    Case Update

      m_Button = cs_WhatButtonUpdate

    Case Delete

      m_Button = cs_WhatButtonDelete

  End Select

End Sub

Module d'entré

Dans un autre module "modSample", j'ai une procédure OpenCrud() qui se contente d'afficher le formulaire.

Public Sub OpenCrud()

  usfCRUD.Show

End Sub

Le formulaire exemple

Il se compose des boutons nécessaires et d'une liste de choix.

Nom des contrôles

Tous les boutons et la combobox, ont pour nom la même racine, avec un index accolé (cmdCRUD_00, cmdCRUD_01, cmdCRUD_02, etc.).

Le bouton "Nouveau" porte l'index 0, la liste de choix l'index 1, le bouton "Modifier" 2, le bouton "Supprimer" 3, le bouton "Valider" 4, enfin le bouton "Annuler" l'indice 5.

Cette racine est dans une constante :

Private Const cs_RacineNomCtrl As String = "cmdCRUD_0"

L’index a aussi son importance puisqu’il est utilisé dans la gestion des énumérations.

La liste de choix est remplie à l'initialisation du formulaire avec des données quelconques.

Private Sub UserForm_Initialize()

  ' Valeurs exemple dans la liste pour pouvoir tester l'évènement Change().

  cmdCRUD_01.AddItem "A"

  cmdCRUD_01.AddItem "B"

End Sub

Il existe une variable globale au formulaire pour reprendre l'état du CRUD courant : Private m_StatutCRUD As Long

Cette variable est modifiée sur l'évènement clic des boutons, et change de la liste.

Sur le bouton de création, nous avons donc le code :

Private Sub cmdCRUD_00_Click()

  CurrentCRUD = ECRUD.Create

End Sub

Liste de choix

Private Sub cmdCRUD_01_Change()

  CurrentCRUD = ECRUD.Read

End Sub

Bouton "Modifier"

Private Sub cmdCRUD_02_Click()

  CurrentCRUD = ECRUD.Update

End Sub

Pour la suppression

Private Sub cmdCRUD_03_Click()

  CurrentCRUD = ECRUD.Delete

End Sub

Liste des boutons à afficher

Enfin nous avons une variable qui reprend la liste des boutons à afficher et elle est du type de l'énumération EButton.

Private m_myButton As EButton

Cette variable est affectée dans une procédure qui appelle la propriété vue plus haut CurrentButton().

Private Sub CreateView()

  m_myButton = CurrentButton()

  SwitchButton

End Sub

Allumer ou éteindre les boutons

Enfin la procédure SwitchButton() allume ou éteint les boutons selon la valeur de m_Button.

Private Sub SwitchButton()

  Dim i As Long

  Dim myButton() As Boolean

  myButton = Decode(EButton.[_Last], m_myButton) ' m_myButton = le nombre à décoder.

  For i = LBound(myButton) To UBound(myButton) - 1

    Me.Controls(cs_RacineNomCtrl & i).Enabled = myButton(i)

  Next i

End Sub

Pour terminer, la procédure CreateView() est appelée sur chaque bouton, ici le code pour le bouton "Nouveau".

Private Sub cmdCRUD_00_Click()

  CurrentCRUD = ECRUD.Create

  CreateView

End Sub

Vous pouvez télécharger le classeur exemple : crud-01.

Gestion des données

Dans cette version, je vais gérer l'affichage des boutons "Valider" et "Annuler", ainsi qu'un squelette pour la gestion des données.

Procédures liées au CRUD

Tout d'abord, un nouveau module standard fait son apparition (pkgData).

Il possède une propriété pour compter le nombre d'enregistrements existant.

Nombre d'enregistrements

Cette propriété est de type long, je la nomme :

Private m_CountRecord As Long

Public Property Get CountRecord() As Long

  CountRecord = m_CountRecord

End Property

Simuler des enregistrements

Dans cette version, il n'est pas encore temps de s'embêter avec des données réelles.

Ici, je vais générer un nombre aléatoire d'enregistrements. C'est la procédure SetCountRecord() qui s'en charge.

Public Sub SetCountRecord()

  Randomize

  m_CountRecord = Int((2 - 0 + 1) * Rnd + 0) ' Je prends une valeur aléatoire entre 0 et 2.

End Sub

Cette procédure est appelée dans la procédure OpenCrud() chargée de l'ouverture du formulaire.

Public Sub OpenCrud()

  SetCountRecord ' Détermine un nombre aléatoire d'enregistrements (entre 0 et 2).

  usfCRUD.Show

End Sub

Méthodes liées au crud

Il existe dans le module "pkgData" 3 procédures :

Ajouter des données

Dans ce cas, le nombre d'enregistrements est incrémenté.

Message crud create

Public Sub AddData()

  m_CountRecord = m_CountRecord + 1

  MsgBox "Ajout des données !", vbInformation

End Sub

Mettre à jour les données

Le nombre d'enregistrements n'est pas impacté.

Mais on peut déjà se dire que nous aurons besoin du numéro de l'enregistrement à modifier.

Message crud update

Public Sub UpdateData()

  MsgBox "Mise à jour des données !", vbInformation

End Sub

Supprimer des données

Dans ce cas, il existe un enregistrement de moins.

Message crud delete

Public Sub DeleteData()

  m_CountRecord = m_CountRecord - 1

  MsgBox "Suppression des données !", vbInformation

End Sub

Récupérer le nombre d'enregistrements

Dans le code du formulaire, il existe une variable :

Private m_myCountRecord As Long

Elle est initialisée dans la procédure CreateView() existante.

Private Sub CreateView()

  m_myCountRecord = CountRecord()

  '...

Le reste du code est inchangé.

Peupler la combobox

À l'initialisation du formulaire, la liste est remplie avec des données quelconques.

Private Sub UserForm_Initialize()

  CurrentCRUD = ECRUD.Read

  CreateView

  If (m_myCountRecord > 0) Then

    cmdCRUD_01.AddItem "A"

    If (m_myCountRecord = 2) Then

      cmdCRUD_01.AddItem "B"

    End If

  End If

End Sub

Synchroniser les boutons "Valider" et "Annuler"

Bouton "Annuler"

Je me contente de modifier l'étât du crud et refaire l'affichage.

Private Sub cmdCRUD_05_Click()

  CurrentCRUD = ECRUD.Read

  CreateView

End Sub

Bouton "Valider"

Selon l'état actuel du crud, on appelle la fonction AddData() ou UpdateData(). Ensuite le statut du crud repasse en mode read.

Private Sub cmdCRUD_04_Click()

  If (CurrentCRUD = ECRUD.Create) Then

    AddData

  ElseIf (CurrentCRUD = ECRUD.Update) Then

    UpdateData

  End If

  CurrentCRUD = ECRUD.Read

  CreateView

End Sub

Supprimer les données

C'est la procédure DeleteData() qui est appelée.

Private Sub cmdCRUD_03_Click()

  CurrentCRUD = ECRUD.Delete

  DeleteData

  CreateView

End Sub

Vous pouvez télécharger cette seconde version : crud-02.

Enregistrement courant et formulaire en lecture ou écriture

Index d'enregistrement

Pour modifier ou supprimer un enregistrement, j'ai besoin de connaître son indice.

C'est le contenu d'une nouvelle variable déclarée dans le module "pkgData" :

Private m_CurrentRecord As Long

Cette propriété est en lecture et écriture, j'ai donc écrit un ascesseur et mutateur.

Public Property Get CurrentRecord() As Long

  CurrentRecord = m_CurrentRecord

End Property

Public Property Let CurrentRecord(ByVal argIndex As Long)

  m_CurrentRecord = argIndex

End Property

S'il n'y a pas de données, sa valeur est fixée à -1.

Pourquoi pas zéro ?

L'indice des combobox commence à 0, donc l'indice 0 correspond au premier élément de la liste.

La propriété ListIndex des combobox et listbox prends la valeur -1 s'il n'y a pas de données.

Modifier l'index

L'index courant, c'est donc la valeur de la propriété ListIndex de la liste de choix.

Mais cet index peut changer selon l'action à entreprendre.

Impact sur l'index lors de l'ajout

Après l'ajout, nous souhaitons que le formulaire reste positionné sur ce nouvel enregistrement.

Mais avant de cliquer sur le bouton "Nouveau", il peut être positionné sur n'importe quel enregistrement.

Il ne faut pas que le formulaire revienne sur cet enregistrement.

Il faut donc recalculer l'indice courant.

Sur la méthode AddData(), nous avons incrémenté le nombre d'enregistrements de 1.

Le nouvel indice, c'est ce nombre - 1.

Public Sub AddData()

  m_CurrentRecord = m_CountRecord ' Atteindre l'enregistrement.

  m_CountRecord = m_CountRecord + 1

  MsgBox "Ajout des données !", vbInformation

End Sub

Impact sur l'index lors d'une modification

Nous étions déjà sur le bon indice.

Il n'y a rien de plus à faire et la procédure UpdateData() reste inchangée.

Impact sur l'index lors d'une suppression

C'est un peu plus compliqué.

Trois cas se présentent

Il n'y a plus d'enregistrements

L'index passe donc à -1.

Nous supprimons le dernier enregistrement

J'ai arbitrairement décidé que l'on se rendait au premier enregistrement.
Ce n'est pas le plus correct, et nous verrons dans une prochaine version qu'il est mieux d'atteindre le précédent (s'il existe).

Un enregistrement autre est supprimé

J'affiche le suivant et du coup, il n'est pas nécessaire de changer l'index.

Je supprime l'enregistrement d'index 8, la donnée avec l'indice 8 est bien la suivante

Il n'y a donc rien à faire.

Public Sub DeleteData()

  m_CountRecord = m_CountRecord - 1

  ' Il ne reste plus d'enregistrements

  If (m_CountRecord = 0) Then

    m_CurrentRecord = -1

  ' On a supprimé le dernier => Atteindre le premier.

  Else

    If (m_CurrentRecord = m_CountRecord) Then

      m_CurrentRecord = 0

    ' On a supprimé un autre => Atteindre le suivant.

    ' Else

      ' Le numéro ne change pas, mais à cet index, se trouve l'enregistrement suivant, il n'y a rien à faire.

    End If

  End If ' m_CountRecord = 0

End Sub

Affecter l'index courant

Lors du choix d'une donnée :

  • L'index est modifié lors du choix d'un élément dans la iste de sélection.
  • La propriété CurrentRecord() prend la valeur de la propriété ListIndex.

Private Sub cmdCRUD_01_Change()

  CurrentCRUD = ECRUD.Read

  CreateView

  CurrentRecord = Me.cmdCRUD_01.ListIndex ' Récupérer l'enregistrement en cours de consultation.

End Sub

À l'initialisation du formulaire

En général, on se positionne sur le premier lors de l'affichage du formulaire.

Private Sub UserForm_Initialize()

  Dim myCountRecord As Long ' Nombre d'enregistrements existant.

  Dim myCurrentRecord As Long

  ' Mode lecture activé.

  CurrentCRUD = ECRUD.Read

  CreateView

  myCountRecord = CountRecord()

  ' Il existe des données, les afficher.

  If (myCountRecord > 0) Then

    cmdCRUD_01.AddItem "A"

    If (myCountRecord = 2) Then

      cmdCRUD_01.AddItem "B"

    End If

    myCurrentRecord = CurrentRecord() ' Définir l'enregistrement à consulter (typiquement ici le premier).

    cmdCRUD_01.ListIndex = myCurrentRecord ' Sélectionner l'enregistrement.

  End If

End Sub

état du formulaire à l'ouverture

Déporter code pour peupler la liste

Le code qui renseigne la liste ECRUD.Read est écrit dans une procédure PopulateRead() plutôt que de rester dans UserForm_Initialize().

Private Sub PopulateRead()

  cmdCRUD_01.AddItem "A"

  If (myCountRecord = 2) Then

    cmdCRUD_01.AddItem "B"

  End If

  cmdCRUD_01.ListIndex = myCurrentRecord ' Sélectionner l'enregistrement

End Sub

Cette procédure est appelée à l’ouverture du formulaire.

Private Sub UserForm_Initialize()

  ' Mode lecture activé.

  CurrentCRUD = ECRUD.Read

  CreateView

  ' Il existe des données, les afficher.

  If (myCountRecord > 0) Then

    PopulateRead

  End If

End Sub

Afficher les données

Le formulaire reçoit une TextBox nommée "txtData", qui prendra comme valeur celle qui est sélectionnée dans la liste de choix.

textbox dans une userform

Pour l'heure dans cette version, je me contente de lui donner le focus lors de la création de l'affichage si bien sûr, il existe es données.

Private Sub CreateView()

  Dim myCountRecord As Long

  Dim bActiveCtrl As Boolean

  myCountRecord = CountRecord()

  If (myCountRecord = 0) Then

    CurrentCRUD = ECRUD.Create

  End If

  m_myButton = CurrentButton()

  SwitchButton

  If ((CurrentCRUD = ECRUD.Create) Or (CurrentCRUD = ECRUD.Update)) Then

    bActiveCtrl = True

  Else

    bActiveCtrl = False

  End If

  ReadOnlyControl bActiveCtrl

  If (bActiveCtrl) Then

    Me.txtData.SetFocus ' Le premier champ de données reçoit le focus.

  End If ' bActiveCtrl

End Sub

Un frame parent des données

Le contrôle exemple txtData est englobé par un composant de type Frame car dans la réalité vous n'aurez pas qu'un seul composant.

J'ai nommé ce frame frmData.

Il est plus simple d'activer ou désactiver un frame, plutôt que de boucler sur tous les contrôles de données pour switcher entre l'activation ou la désactivation.

frame dans une userform

Une nouvelle procédure se charge d'activer ou pas le frame.

Private Sub ReadOnlyControl(ByVal argReadOnly As Boolean)

Me.frmData.Enabled = argReadOnly

End Sub

Elle est appelée dans la procédure CreateView().

Private Sub CreateView()

  Dim bActiveCtrl As Boolean

  myCountRecord = CountRecord()

  myCurrentRecord = CurrentRecord()

  ' Pas d'enregistrements, mode création.

  If (myCountRecord = 0) Then

    CurrentCRUD = ECRUD.Create

  End If

  m_myButton = CurrentButton()

  SwitchButton

  If ((CurrentCRUD = ECRUD.Create) Or (CurrentCRUD = ECRUD.Update)) Then

    bActiveCtrl = True

  Else

    bActiveCtrl = False

  End If

  ReadOnlyControl bActiveCtrl

  If (bActiveCtrl) Then

    Me.txtNom.SetFocus

  End If ' bActiveCtrl

End Sub

Confirmation suppression

La suppression est une action dangereuse et irréversible.

Il est de bon ton de demander confirmation avant de l'accomplir.

Je vais faire cette demande sur le bouton de suppression et non pas dans la procédure qui supprime physiquement les données.

Confirmation crud delete

L'interaction utilisateur doit se faire au plus haut niveau du code.

En effet la suppression pourrait être réalisée automatiquement sur un bout de code quelconque et dans ce cas, notre message pourrait être gênant.

On peut vouloir supprimer sans forcément demander son avis à l'utilisateur (dans ce projet ou un autre).

Private Sub cmdCRUD_03_Click()

  Dim Reponse As Long

  Reponse = MsgBox("Supprimer ?", vbYesNo Or vbQuestion)

  If (Reponse = vbYes) Then ' Suppression confirmée.

    DeleteData

    CurrentCRUD = ECRUD.Delete

    CreateView

  End If ' Reponse = vbYes

End Sub

Troisième version : crud-03.

Synchroniser les données

Dans cette quatrième version, nous allons voir l'affichage des données selon la valeur du CRUD courant.

Mode lecture

Quand on choisit une valeur dans la liste, on affiche dans les contrôles les valeurs de l'enregistrement choisi.

Mode création

Quand on clique sur le bouton "Nouveau", il faut effacer les données de l'enregistrement éventuellement affiché

Bouton "Annuler"

Quand on annule la création, il faut remettre à blanc les contrôles de données.

S'il y a des enregistrements, il faut revenir sur l'enregistrement affiché précédemment.

Bouton "Valider"

Dans ce cas, il faut relire la liste de choix pour se positionner sur l'enregistrement qui vient d'être créé.

La procédure AddData() est modifiée en conséquence.

Public Sub AddData()

  ' Your code to add data here...

  m_CountRecord = m_CountRecord + 1

  m_CurrentRecord = m_CountRecord - 1

  MsgBox "Ajout des données !", vbInformation

End Sub

Mode modification

Bouton "Annuler"

Il faut relire l'enregistrement courant pour réafficher les données existantes avant modification.

Bouton "Valider"

Il n'y a rien à faire de particulier.

Les données sont affichées et nous sommes sur le bon enregistrement.

Le formulaire repasse en mode lecture.

Mode suppression

Selon la réponse à la boite de confirmation de suppression, nous n'avons pas la même action à faire.

Bouton "Non"

Il n'y a rien à faire, nous sommes déjà sur le bon enregistrement et il est déjà en lecture seule.

Bouton "Valider"

En premier lieu, on décrémente le nombre d'enregistrements.

On efface les données du formulaire.

Ensuite, plusieurs cas se présentent.

Il n'y a plus d'enregistrement

On passe en mode création.

Il faut relire la liste.

On supprime le dernier

On se positionne sur l'enregistrement précédent celui qui est supprimé.

Dans les autres cas

On va lire l'enregistrement suivant celui qui est supprimé.

Ce qui donne dans la module pkgData cette nouvelle version pour la procédure de suppression.

Public Sub DeleteData(ByVal argIndex As Long)

  m_CountRecord = m_CountRecord - 1

  If (m_CountRecord = 0) Then ' Il ne reste plus d'enregistrements

    m_CurrentRecord = -1

  Else

    ' On a supprimé le dernier :

    ' Atteindre le précédent.

    If (m_CurrentRecord = m_CountRecord) Then

      m_CurrentRecord = m_CurrentRecord - 1

      ' On a supprimé un autre :

      ' Atteindre le suivant.

    ' Else

      ' Le numéro ne change pas, mais à cet index, se trouve l'enregistrement suivant.

    End If

  End If ' m_CountRecord = 0

  MsgBox "Suppression des données !", vbInformation

End Sub

Adapter le code du formulaire

Remettre les contrôles à blanc

J'ai besoin d'une procédure pour remettre à blanc le contenu des contrôles.

Ici je n'ai qu'un contrôle, s'il y en a d'autre, il me reste simplement à les rajouter dans cette fonction.

Private Sub EraseData()

  Me.txtValue.Text = ""

End Sub

Renseigner les contrôles

J'ai ensuite une fonction qui renseigne les contrôles avec les bonnes données.

Private Sub ViewData()

  Me.txtValue.Text = Me.cmdCRUD_01.Value

End Sub

Note : J'aurais pu avoir une seule procédure pour effacer et renseigner les contrôles.

Le code des boutons sera ajusté dans la version suivante.

Vous trouverez la quatrième version ici : crud-04.

Utiliser une collection

Afficher les données

Lors de la création je rappelle que l'on efface les données affichées pour les relire.

Private Sub cmdCRUD_00_Click()

  CurrentCRUD = ECRUD.Create

  CreateView

  EraseData ' Effacer les données existantes.

End Sub

Sur la suppression et le bouton "Valider", je rappelle la procédure PopulateRead() pour relire les données.

Sur le bouton "Annuler" j'efface les données affichées à l'aide la fonction EraseData() vue plus haut.

Private Sub cmdCRUD_05_Click()

  EraseData ' Effacer les données existantes.

  CurrentCRUD = ECRUD.Read

  CreateView

  If (myCountRecord > 0) Then

    cmdCRUD_01_Change

  End If

End Sub

Les conteneurs disponibles

Maintenant que l'architecture est prête, il est temps de s'occuper des données.

Il existe plusieurs méthodes d'accès aux données.

Tout d'abord, la méthode utilisée, ne change pas le code existant. C'est du code qui va se trouver au-dessus de celui-ci.

Feuilles de calcul

C'est sûrement le cas le plus fréquent.

Je n'utiliserais pas dans cet article une feuille de calcul pour ne pas alourdir inutilement le programme (ce n'est qu'un tutoriel).

Le seul petit piège se produit à la suppression du dernier enregistrement restant.

Il ne faut pas supprimer cette ligne.

Souvent elle contient des cellules calculées.

Des formules peuvent faire référence à cette ligne. Le risque est de se trouver avec des valeurs #REF partout.

Tableau au sens programmation

On pourrait imaginer la création d'un type (structure) ou d’une classe et d'un tableau dynamique.

La gestion est lourde, peu commode surtout lors des suppressions.

Soit on reconstruit le tableau à chaque fois, soit on rajoute dans la structure une variable qui indique que l'enregistrement est supprimé, ce que l'on nomme un flag ou drapeau.

Chaque fois que le tableau est lu, il faut tester si c'est un enregistrement en suppression ou pas.

Il faudra aussi identifier les enregistrements qui ont été modifiés.

Dictionnaire

Les dictionnaires sont intéressants, mais ils nécessitent d'inclure la référence "Scripting.Dictionary".

Collection

C'est le support choisi dans cet exemple.

Il possède deux inconvénients.

Clé de type String

La clé d'un dictionnaire est de type String.

Ce n'est pas gênant et rien ne m'oblige à utiliser cette clé.

Modifier les données du dictionnaire

Les données ne sont pas modifiables.

Cela pourrait paraître un problème épineux.

Mais si la collection est renseignée avec un objet, cette limitation tombe.

De toute façon, quel que soit le conteneur choisi, j'aurais utilisé une classe.

Par contre les collections sont simples à utiliser.

Connection à une base de données

Il est tout à fait possible de récupérer des données issues d'un logiciel de base de données comme Microsoft Access, SQL Server ou autre.

Création de la classe métier

Quel que soit le conteneur choisi, je transfèrerais les données dans un objet pour faciliter les opérations.

Mon code sera plus simple et propre.

J'ai donc une classe cData qui contient une seule propriété m_Value avec son get et son let.

Private m_Value As String

Public Property Get Value() As String

  Value = m_Value

End Property

Public Property Let Value(ByVal argValue As String)

  m_Value = argValue

End Property

Création de la collection

Dans mon module de gestion des données pkgData, j'ai une variable de type collection.

Private m_Data As Collection

J'ai besoin d'une procédure d'initialisation de la collection.

Jusqu'à présent, j'avais une procédure setCountRecord() qui donnait un nombre aléatoire d'enregistrements.

Elle est remplacée par une procédure InitData().

Public Sub InitData()

  m_CountRecord = AleatRecord() ' Déterminer un nombre aléatoire d'enregistrements.

  If (m_CountRecord = 0) Then ' Pas d'enregistrements.

    m_CurrentRecord = -1

  Else

    m_CurrentRecord = 0

  End If ' m_CountRecord = 0

  Set m_Data = New Collection ' Le stockage est créé qu'il existe ou non des données.

  PopulateData

End Sub

Du coup, j'ai créé une fonction qui calcule un nombre aléatoire et qui se nomme AleatRecord().

Bien sûr elle n'a de sens que pour illustrer mon exemple, dans la réalité, vous compterez le nombre réel de données et cette fonction sera à supprimer.

Private Function AleatRecord() As Long

  Const cs_MaxItem As Long = 3

  Randomize

  AleatRecord = Int((cs_MaxItem - 0 + 1) * Rnd + 0) ' Je prend une valeur aléatoire entre 0 et cs_MaxItem.

End Function

Vous remarquez aussi l'appel à une fonction PopulateData().

C'est elle qui se charge de renseigner la collection avec nos objets.

Elle boucle sur le nombre d'enregistrements aléatoire pour étendre la collection.

La propriété m_value de l'objet est renseignée avec juste une lettre. C'est la fonction Chr() de VBA qui s'en charge, avec Chr(65) égal à la lettre "A".

Ensuite une nouvelle "cellule" de la collection est remplie avec un objet avec la méthode Add() de l'objet collection.

Private Sub PopulateData()

  Dim myRecord As cData

  Dim i As Long

  If (m_CountRecord > 0) Then

    For i = 0 To m_CountRecord - 1 Step 1

      Set myRecord = New cData

      myRecord.Value = Chr(65 + i)

      m_Data.Add myRecord

    Next i

  End If ' m_CountRecord > 0

  Set myRecord = Nothing

End Sub

Compter les enregistrements

Dans un projet concret, mon nombre d'enregistrements ne dépends pas d'une valeur aléatoire.

Par contre les collections possèdent la propriété Count.

Ainsi après remplissage de la collection, il faudrait affecter à la variable globale m_CountRecord du module pkgData avec l'instruction m_CountRecord = m_Data.Count.

Initialiser les données

Sur mon point d'entrée OpenCrud() du module module Sample, j'utilise la procédure InitData en lieu et place de setCountRecord(), ce qui donne le code modifié suivant.

Public Sub OpenCrud()

  InitData ' Remplir la collection.

  usfCRUD.Show

End Sub

Détruire la collection

Comme toujours, il est de bon ton de supprimer les objets qui ne servent plus. C'est le rôle dévolu à la procédure DestroyData().

Je boucle sur tous les enregsitrements de la collection avec sa méthode Remove(), et à la fin je la détruit.

Public Sub DestroyData()

  Dim i As Long

  If (m_CountRecord > 0) Then

    For i = m_Data.Count To 1 Step -1

      m_Data.Remove (i)

    Next i

  End If ' m_CountRecord > 0

  Set m_Data = Nothing

End Sub

Cette destruction s'opère à la fermeture du formulaire, soit sur la fonction OpenCrud().

Public Sub OpenCrud()

  InitData

  usfCRUD.Show

  DestroyData ' Détruire la collection.

End Sub

Manipuler la collection

Ajouter des données

Ma procédure AddData() est modifiée en conséquence. Elle prend en paramètre un objet de type cData.

Public Sub AddData(ByRef argData As cData)

  m_Data.Add argData ' Ajouter l'enregistrement.

  m_CountRecord = m_CountRecord + 1

  m_CurrentRecord = m_CountRecord - 1

  MsgBox "Ajout des données !", vbInformation

End Sub

Modifier des données

Là aussi, je passe un objet de type cData, mais j'ai aussi besoin de l'index à modifier dans la collection.

C'est la valeur de la liste de choix du formulaire + 1.

Les index des ComboBox ou ListBox commencent à 0, mais celui des collection à 1.

Public Sub UpdateData(ByVal argIndex As Long, ByRef argData As cData)

  m_Data(argIndex).Value = argData.Value()

  MsgBox "Mise à jour des données !", vbInformation

End Sub

Supprimer des données

C'est la méthode Remove() de l'objet Collection qui est invoquée.

Là aussi, il est nécessaire de posséder l'index à supprimer.

Public Sub DeleteData(ByVal argIndex As Long)

  m_Data.Remove (argIndex) ' Supprimer l'enregistrement.

  m_CountRecord = m_Data.Count

  If (m_CountRecord = 0) Then

    m_CurrentRecord = -1

  Else

    If (m_CurrentRecord = m_CountRecord) Then

      m_CurrentRecord = m_CurrentRecord - 1

    End If

  End If ' m_CountRecord = 0

  MsgBox "Suppression des données !", vbInformation

End Sub

Retourner la collection

Cette fonction retourne la collection pour l'utiliser dans le formulaire.

Public Property Get Data() As Collection

  Set Data = m_Data

End Property

Adapter le formulaire pour gérer une collection

Tout d'abord, une nouvelle procédure PrepareData() est créée.

L'appel aux fonctions AddData() et UpdateData() est modifié pour passer les arguments nécessaires.

Private Sub PrepareData()

  Dim myRecord As cData

  Set myRecord = New cData

  myRecord.Value = Me.txtValue.Text

  If (CurrentCRUD = ECRUD.Create) Then

    AddData myRecord

  ElseIf (CurrentCRUD = ECRUD.Update) Then

    UpdateData CurrentRecord + 1, myRecord ' + 1 car les index de ComboBox commencent à 0 et ceux d'une collection à 1.

  End If

  Set myRecord = Nothing

End Sub

Le code du bouton "Valider" est déporté dans cette procédure.

Private Sub cmdCRUD_04_Click()

  PrepareData

  CurrentCRUD = ECRUD.Read

  CreateView

  PopulateRead

End Sub

La fonction qui peuple la liste de choix est modifiée en conséquence.

Private Sub PopulateRead()

  Dim myData As Collection ' Usage de la collection.

  Dim i As Long

  Me.cmdCRUD_01.Clear

  Set myData = Data()

  For i = 1 To myData.Count Step 1

    Me.cmdCRUD_01.AddItem myData(i).Value

  Next i

  Set myData = Nothing

  Me.cmdCRUD_01.ListIndex = myCurrentRecord ' Sélectionner l'enregistrement.

End Sub

Celle qui renseigne les composants de données.

Private Sub ViewData()

  Dim myData As Collection

  Dim Index As Long

  Set myData = Data()

  Index = Me.cmdCRUD_01.ListIndex + 1

  Me.txtValue.Text = myData(Index).Value

  Set myData = Nothing

End Sub

Voilà pour cette nouvelle version que vous pouvez télécharger : crud-05.

Pistes

Maintenant que le code de base fonctionne, il est possible d'envisager des évolutions.

Optimiser le code

On peut toujours optimiser un code, même s'il est court comme celui-ci.

Il ne s'agit pas que le projet s'exécute plus vite, mais de réduire le nombre de lignes de codes pour faciliter la maintenance.

Gestion d'erreurs

Pour compléter ce code, il faudrait gérer quelques erreurs inévitables dans un projet.

Il y a plusieurs types d'erreurs.

Les erreurs métier

Ce sont les erreurs liées aux données.

  • Les données obligatoires sont-elles renseignées ?
  • Est-ce qu'une nouvelle donnée existe déjà ?
  • Est-ce qu'une valeur est dans des bornes admissibles pour le programme ?

Les erreurs internes

Ce sont les erreurs qui se produisent sur les autres éléments du projet.

Par exemple on essaye de supprimer des données dans la collection alors qu'il n'en existe plus.

Adapter le code à un classeur

La charge de travail est peu importante. Il faut faire des méthodes pour envoyer les données de la collection vers son tableau Excel et de remplir la collection avec les données du tableau.

L'objet cData devra être modifié selon les données du tableau Excel.

Le plus compliqué réside dans la suppression de la première ligne du tableau Excel.
Pour se faire, il est préférable de créer un nouveau module (surcouche) pour ne pas polluer l'existant qui fonctionne et pouvoir l'utiliser dans d'autres projets.

Barre de navigation

Ce n'est pas aussi simple qu'il n'y parait.

L'état de la barre et ses boutons dépendent de plusieurs conditions.

  • L'état du CRUD.
  • L'élément sélectionné.
  • Etc.
Barre de navigation sur une userform

Logiquement, cette partie devrait être traitée avant celle sur les données, mais l’article est déjà long.

Je pense faire prochainement un article sur le sujet.

Conclusions

Une démarche

Prendre les problèmes les uns après les autres

J'aurais pu commencer par la gestion des données, peu importe.

L'essentiel c'est de se concentrer que sur un problème à la fois.

Quand on n’est pas un développeur habituel, on veut tout faire en même temps. C'est le meilleur moyen d'avoir des bouts de codes partout et n'arriver à rien.

Découpage du code

Utiliser des modules est un gage de stabilité du code.

Le module est réutilisable dans un autre projet et ne contient aucuns éléments parasites.

Nous n'avons que le code utile.

Code bas niveau auquel on ne touche plus

Avec le temps, on se trouve avec toute une bibliothèque de modules que l'on peut assembler dans des projets.

Ainsi le module qui gère les énumérations est utile dans une multitude de programmes sans modification.

D'ailleurs, pour gérer des boutons de navigation, j'utiliserais ce principe.

Généraliser

Ce formulaire est suffisamment généraliste pour s'inclure dans tous les projets vba.

Seules les parties qui gère les données sont à modifiées.

J'espère que cet article vous sera utile.

Ressources

Chaque étape de réalisation est accompagnée d'un classeur exemple que vous pouvez télécharger.

Version 1 : crud-01.

Version 2 : crud-02.

Version 3 : crud-03.

Version 4 : crud-04.

Version 5 : crud-05.

>