Récolter et consolider les données en VBA Access
Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :
Récolter et consolider les données en VBA Access
Dans cette formation, nous reprenons le développement de l'application sur l'évaluation des candidats sous forme de QCM. Dans les formations précédentes, nous avions :
- Créé une zone d'authentification et d'inscription à l'accueil,
- Charger une liste déroulante des questionnaires correspondant aux tables Access,
- Généré 20 questions aléatoires sur le thème choisi grâce à une requête SQL,
- Transmis les informations comme l'identifiant, entre formulaires,
- Verrouillé et protégé l'application pour empêcher la modification des sources,
Désormais, comme l'illustre la capture ci-dessus, nous souhaitons développer la partie évaluation. L'utilisateur doit pouvoir cliquer sur l'une des réponses qui lui est proposée. Un code VBA doit se déclencher pour récolter cette information et la comparer avec la valeur correcte et comptabiliser les points. A la fin du test, les résultats doivent être consolidés et archivés dans la table resultats, sous l'identifiant du candidat.
Base de données et formulaire du QCM
Nous récupérons les travaux que nous avons réalisés précédemment afin de ne pas repartir de zéro.
- Télécharger la base de données consolider-donnees.accdb en cliquant sur son lien,
- L'ouvrir dans Access en maintenant la touche MAJ (Shift) enfoncée,
- Cliquer avec le bouton droit sur le formulaire questions dans le volet des objets Access,
- Dans la liste, choisir Mode création,
- Dans le ruban Création, cliquer sur le bouton Feuille de propriétés si elle n'est pas visible,
- Activer l'onglet Format de la feuille de propriétés du formulaire,
- Régler la propriété Afficher le sélecteur sur Non,
- Régler la propriété Boutons de déplacement sur Non,
- Régler la propriété Barre défilement sur Aucune,
- Régler la propriété Bouton Fermer sur Non,
- Régler la propriété Boutons MinMax sur Aucun,
Ainsi, une fois le QCM lancé, le candidat n'aura d'autre choix que de le terminer pour finir l'application.
- Double cliquer sur le formulaire Listes depuis le volet des objets Access, pour l'exécuter,
- Taper un identifiant inscrit dans la base de données, par exemple : 123456,
- Valider par Ok et choisir un questionnaire dans la liste déroulante,
- Cliquer sur la flèche du bouton Affichage dans le ruban Accueil,
- Dans la liste, choisir Mode création,
- Cliquer avec le bouton droit sur le formulaire Listes depuis le volet des objets Access,
- Dans le menu contextuel, choisir Mode création,
- Activer l'onglet Evénement de la feuille de propriétés,
- Cliquer sur le bouton de son événement Sur chargement,
- Dans la boucle For Each, après le dernier critère : And chaque_table.Name <> "ListeTables" et avant le mot clé Then, ajouter l'exception sur la table resultats comme suit :
Nous excluons ainsi la table resultats reconnue par son nom, du traitement de la boucle For Each, destinée à charger la liste déroulante.
- Enregistrer les modifications (CTRL + S) et basculer sur l'application Access (ALT + F11),
- Exécuter le formulaire Listes,
- Saisir un identifiant reconnu et le valider,
- Déployer la liste déroulante des questionnaires,
Grâce à l'exception ajoutée dans l'instruction conditionnelle If, sur le nom de la table resultats, cette dernière n'apparaît plus dans la liste des propositions.
- Fermer le formulaire Listes par le raccourci clavier CTRL + W par exemple,
- Afficher de nouveau le formulaire questions en mode création,
Pour chaque question, quatre propositions de réponses sont suggérées au candidat. Au survol de chacune d'entre elles, nous souhaitons que le contrôle zone de texte, change de couleur. Mais lorsque la souris quitte la zone, le contrôle doit reprendre son apparence d'origine. Il s'agit d'une ergonomie intéressante pour indiquer à l'utilisateur que ces zones sont directement cliquables. De plus, au survol d'un TextBox, le curseur de la souris change naturellement d'aspect pour indiquer qu'il s'agit d'une zone de saisie. Nous souhaitons que le curseur conserve l'icône d'une flèche. Les zones de texte tout comme le formulaire propose un événement Sur souris déplacée qui permet de déclencher un code au survol.
- Tout d'abord, sélectionner les 5 zones de texte (Question et quatre réponses),
- Activer l'onglet Données de leur feuille de propriétés,
- Régler leur propriété Verrouillé sur Oui,
- Sélectionner maintenant, seulement la zone de texte choix1,
- Activer l'onglet Evénement de sa feuille de propriétés,
- Cliquer sur le petit bouton de son événement Sur souris déplacée,
- Dans la boîte de dialogue qui suit, choisir Générateur de code et valider,
- Entre les bornes de la procédure, ajouter les lignes de code suivantes :
choix1.BackColor = RGB(231, 222, 218)
Screen.MousePointer = 1
End If
La propriété MousePointer de l'objet VBA Screen permet, par le biais d'une valeur numérique, de définir l'apparence du pointeur de la souris, comme l'indique l'aide en ligne. 0 est la valeur par défaut. C'est pourquoi nous vérifions d'abord cette condition à l'aide d'une instruction If, afin de n'opérer les changements d'apparence que s'ils n'ont pas encore eu lieu. Le déplacement de la souris étant perpétuel, cet événement déclencherait le code de façon récurrente. Le critère permet donc de ne déclencher le code qu'une seule fois, afin de modifier l'aspect. Si l'apparence n'a pas encore changé, alors la propriété BackColor d'un TextBox permet de définir sa couleur de remplissage. Et c'est la fonction VBA RGB à l'aide des trois composantes de couleurs rouge, vert et bleu qui réalise cette affectation. Dans la foulée nous affectons la propriété MousePointer de l'objet Screen à 1 de manière à conserver la flèche, au-dessus de la zone de saisie. Testons les effets de ce code :
- Enregistrer les modifications (CTRL + S) et basculer sur l'application Access (ALT + F11),
- Exécuter le formulaire Listes, taper un identifiant et choisir un questionnaire,
- Sur le formulaire questions qui suit, déplacer la souris sur la zone choix1,
Comme vous le constatez, la zone de texte change en effet de couleur et la souris conserve son pointeur. Mais lorsque vous sortez de la zone d'influence, la zone de texte ne recouvre pas ses attributs d'origine. Access ne propose pas d'événement OnMouseOut pour traduire le fait que la souris quitte la zone définie. Donc, nous développerons un code sur l'événement de la souris déplacée sur le formulaire, ce qui indiquera que la souris ne survole plus une zone de texte. Ce code VBA sera chargé de réinitialiser toutes les zones de texte, ne sachant pas de quelle zone la souris est sortie. Mais avant cela, nous allons gérer les événements de la souris sur les autres zones de texte.
- Afficher le formulaire questions en mode création,
- Sélectionner le contrôle choix2 et activer l'onglet Evénement de sa feuille de propriété,
- Cliquer sur le bouton de son événement Sur souris déplacée,
- Puis, choisir Générateur de code pour basculer dans l'éditeur VBA,
- Copier le code précédemment conçu pour le contrôle choix1,
- Puis, le coller entre les bornes de la procédure événementielle choix2_MouseMove,
- Modifier le nom d'objet choix1 par choix2 dans le If du code collé,
- Dans la liste déroulante de gauche, en haut de l'éditeur de code, choisir l'objet choix3,
- A l'aide de la liste déroulante de droite, lui associer l'événement MouseMove,
- Coller le code du contrôle choix1 en remplaçant le nom d'objet par choix3,
- Réitérer ces opération pour le contrôle choix4,
Cette méthode est une autre façon d'associer des procédures événementielles aux contrôles de formulaire. La liste de gauche en haut de l'éditeur propose tous les objets recensés sur le formulaire. La liste de droite quant à elle, propose tous les gestionnaires d'événements disponibles pour un objet sélectionné dans la liste de gauche.
- Enregistrer les modifications et basculer sur l'application Access,
- Exécuter le formulaire Listes, taper un identifiant et choisir un questionnaire,
- Sur le questionnaire qui suit, survoler les zones de texte,
- Afficher le formulaire questions en mode création,
- Cliquer sur la barre de la section Détail en haut du formulaire,
- Activer l'onglet Evénement de sa feuille de propriétés,
- Cliquer sur le bouton de son événement Sur souris déplacée,
- Choisir Générateur de code pour basculer de nouveau dans l'éditeur VBA,
- Entre les bornes de la procédure Détail_MouseMove, saisir le code suivant :
choix1.BackColor = RGB(249, 207, 195)
choix2.BackColor = RGB(249, 207, 195)
choix3.BackColor = RGB(249, 207, 195)
choix4.BackColor = RGB(249, 207, 195)
Screen.MousePointer = 0
End If
Cette fois nous vérifions le critère inverse. Si la propriété MousePointer est affectée à la valeur 1, cela signifie que la souris sort d'une zone de saisie. Dans ces conditions, nous réinitialisons toutes les couleurs d'origine de chacun des TextBox en affectant leur propriété BackColor à l'aide de la fonction RGB. Nous avons retrouvé les composantes précises des couleurs d'origine grâce à l'outil pipette de Photoshop sur l'arrière-plan d'une zone de saisie en capture d'écran.
- Enregistrer les modifications et retourner sur l'application Access,
- Exécuter le formulaire Listes, taper un identifiant et choisir un questionnaire,
- Sur le formulaire questions qui suit, survoler tour à tour les zone de saisie,
Cette fois tout fonctionne parfaitement. Comme les valeurs sont réinitialisées au survol du formulaire, chaque zone de texte change tour à tour d'aspect, dès que la souris entre dans sa zone d'influence. Encore une fois, comme la condition est vérifiée par une instruction If, une fois les modifications réalisées, le code n'est pas réexécuté sans fin.
Actions au clic de la souris
Désormais, c'est au clic sur ces zones de texte qu'un code VBA Access doit être déclenché. Il a pour mission de comparer la valeur correspondant à la zone cliquée, avec le bon numéro de réponse. Ainsi il pourra comptabiliser les points et dérouler le QCM en proposant la question suivante, soit l'enregistrement suivant.
- Afficher le formulaire questions en mode création,
- Sélectionner le contrôle choix1,
- Dans l'onglet Evénement de sa feuille de propriétés, cliquer sur le bouton de son événement Au clic,
- Dans la boîte de dialogue qui suit, choisir Générateur de code et valider,
- En haut de l'éditeur de code, déployer la liste déroulante de gauche,
- Dans la liste, choisir le contrôle choix2,
- Dans la liste de droite, choisir l'événement Click,
- De la même manière, créer les procédures choix3_Click et choix4_Click,
Chacune de ces procédures, en transmettant le numéro de la réponse cliquée, doit comparer cette valeur à celle de la bonne réponse, inscrite dans le contrôle REPONSES qui est masqué. Si les chiffres sont équivalents (Instruction VBA If), la note doit être incrémentée d'un point. Chacune doit également incrémenter un compteur de questions pour mettre à jour l'information du formulaire, sur le nombre de questions restant à passer. Lorsque la valeur maximale de ce compteur est atteinte, signifiant que le questionnaire est terminé, un code doit déclencher une requête SQL d'insertion de ces résultats, dans la table resultats, pour l'identifiant inscrit dans le contrôle id_candidat.
Comme chacune de ces procédures doit réaliser un traitement similaire, nous proposons de créer une procédure externe attendant comme paramètre, le numéro de la réponse cliquée. Ainsi, chaque code associé au clic sur une zone de texte, se contentera d'appeler cette procédure, en lui passant le numéro correspondant. Comme les valeurs de la note, du compteur de questions ainsi que de la durée écoulée, doivent être consolidées à chaque nouveau clic, les durées de vie des variables ne peuvent pas se limiter aux bornes de la procédure. Nous devons donc commencer par déclarer des variables publiques, en dehors de toute procédure, pour qu'elles portent l'information en mémoire, tout au long du test.
- Tout en haut de l'éditeur de code, en dehors de tout procédure, taper l'instruction suivante :
Ainsi VBA Access nous forcera à déclarer toutes les variables que nous utiliserons.
- Juste en dessous de l'expression précédente, ajouter les déclarations suivantes :
Nous déclarons trois variables publiques. Les deux premières permettront de porter la note et la numérotation. La troisième, non typée, sera initialisée sur un chronomètre, grâce à la fonction VBA Timer, afin de commencer le décompte du temps qui passe.
Fort naturellement, ces variables doivent être initialisées avant d'être exploitées. Leur affectation doit donc intervenir au chargement du formulaire.
- Dans la procédure Form_Load, sous la ligne de code de la requête SQL aléatoire, ajouter les affectations suivantes :
debut = Timer
nb_reste.Caption = "20"
note.Caption = "0"
Au chargement du thème du questionnaire, aucune question n'a encore été passée, donc nous initialisons la variable numero à 0. Nous enclenchons le chronomètre au démarrage, grâce à la fonction VBA Timer qui affecte la variable debut du temps qu'il est. Nous initialisons les zones de renseignements en bas du formulaire pour le candidat, soit les label nb_reste et note, respectivement pour le nombre de questions restantes, et la valeur de la note cumulée.
- Sous la procédure Form_Load, créer la procédure indépendante comme suit :
End Sub
C'est donc par ce nom, consolidation, que chaque code associé au clic sur l'une des zones de texte, devra lancer la suite du traitement, en passant le numéro de la réponse choisie (1, 2, 3 ou 4).
- En conséquence, entre les bornes de la procédure choix1_Click, ajouter l'instruction suivante :
Il s'agit simplement de l'appel de la procédure consolidation, avec le chiffre 1 en paramètre, correspondant au numéro de réponse cliqué.
- De la même façon, réaliser l'appel de la procédure consolidation, depuis chaque clic sur chaque zone de texte, en adaptant la valeur du paramètre,
A ce stade, si nous exécutons le code, rien ne se produit encore, au clic sur les zones de texte. La procédure consolidation est en effet encore vide. Commençons par les affectations :
- Entre les bornes de la procédure consolidation, ajouter les initialisationssuivantes :
nb_reste.Caption = (20 - numero)
If (le_choix = REPONSES.Value) Then la_note = la_note + 1
note.Caption = la_note
Si l'utilisateur a cliqué, cela signifie qu'il a validé une réponse. Dans ce cas, avant d'afficher la question suivante, nous incrémentons le compteur de questions (numero = numero + 1). Ce résultat retranché au nombre 20, pour les 20 questions de départ, est affecté au contenu de l'étiquette nb_reste, grâce à sa propriété Caption, pour en informer le candidat en temps réel. Si le chiffre de la réponse fournie, transmis par la variable le_choix est équivalent au chiffre de la bonne réponse, mémorisée dans la zone de texte REPONSES (If (le_choix = REPONSES.Value) Then), alors nous incrémentons la valeur de la note pour compter les points obtenus (la_note = la_note + 1). Puis, nous inscrivons ce résultat en temps réel dans le label dédié (note.Caption), pour en informer le candidat.
Avant d'afficher la question suivante, soit le prochain enregistrement, il faut réaliser un test consistant à savoir si la fin du questionnaire n'est pas atteinte. En effet, lorsque tous les enregistrements ont été lus, le code qui consisterait à afficher le suivant, génèrerait une erreur.
- A la suite du code, grâce à l'instruction If, ajouter le test sur le nombre de questions passées :
End If
Si le critère est vérifié, les résultats consolidés doivent être inscrits dans la table resultats, attachés à l'identifiant du candidat. Nous pouvons donc déclarer les variables nécessaires, dont la variable de base de données. La durée de vie de ces dernières sera limitée non seulement à la durée de vie de la procédure, mais plus précisément aux bornes de l'instruction conditionnelle If.
- Entre les bornes de l'instruction If, ajouter les déclarations suivantes :
La variable requete permettra de mémoriser l'instruction SQL d'ajout de données à exécuter. C'est la raison pour laquelle nous la déclarons comme une chaîne de caractères (String). La variable la_base permettra de pointer sur la base de données en cours, afin de pouvoir exécuter la requête action, sur la table resultats de cette base de données. La variable la_duree permettra de stocker le temps de parcours, en réalisant le calcul de l'intervalle de temps, depuis le démarrage du test. Comme toujours, les affectations doivent suivre la déclaration des variables.
- A la suite du code, ajouter les affectations suivantes :
la_duree = Timer - debut
requete = "INSERT INTO resultats (res_id, res_nom, res_note, res_duree) VALUES ('" & id_candidat.Caption & "','" & transf.Caption & "'," & la_note & "," & Int(la_duree) & ")"
Comme nous l'avions appris au travers de la formation pour établir la connexion aux données d'une base, la méthode CurrentDb de l'objet Application permet d'instancier une classe. Cette instanciation produit un objet (la_base) capable de piloter la base de données en cours, en lui fournissant des propriétés et méthodes. Nous calculons ensuite l'intervalle de temps écoulé dans la variable la_duree. Puis, nous construisons la requête d'ajout de données, comme nous l'avions appris dans la formation d'authentification et d'inscription en VBA Access. Les mots clés INSERT INTO permettent d'initialiser une requête Ajout. Ils sont nécessairement suivis du nom de la table (resultats) dans laquelle doit se produire l'insertion. Nous énumérons ensuite tous les champs qui doivent être affectés pour ce nouvel enregistrement. Le champ res_date n'est pas inclus car lors de la création de la table, nous avions défini une valeur par défaut, calée sur la date du jour. Le mot clé VALUES permet ensuite d'annoncer l'énumération des valeurs variables pour affecter chacun de ces champs, dans le même ordre. Notez la présence des simples cotes pour encadrer les champs de type texte, et leur absence dans le cas de valeurs numériques.
Il s'agit ensuite d'exécuter la requête pour insérer les nouvelles données, d'en informer le candidat puis de vider les variables objets utilisées, sans oublier de fermer la connexion à la base de données.
- Pour ce faire, ajouter les lignes suivantes après le code précédent :
MsgBox "L'évaluation est terminée, vos résultats ont été archivés"
la_base.Close
Set la_base = Nothing
La méthode Execute d'un objet de base de données permet d'exécuter la requête passée en paramètre. La syntaxe SQL avec le moteur de base de données, font le reste. Nous en informons le candidat à l'aide d'une boîte de dialogue MsgBox. Puis nous fermons la connexion aux données à l'aide de la méthode Close de l'objet de base de données. Et enfin, nous déchargeons la variable objet afin de libérer les ressources.
Nous venons d'écrire le code qui permet d'agir lorsque le QCM est terminé. Mais pour l'instant, rien n'a été fait pour le dérouler, si ce n'est l'incrémentation des variables. Notre application n'est donc pas encore fonctionnelle. L'objet DoCmd en VBA Access permet d'exécuter des commandes, comme celle qui consiste à afficher l'enregistrement suivant, pour changer de question donc. Ce code doit être déclenché lorsque les 20 questions n'ont pas été atteintes. Il faut donc ajouter une branche Else (Sinon) dans l'instruction conditionnelle If.
- Avant le End If, ajouter le code suivant :
DoCmd.GoToRecord acDataForm, "questions", acNext
La méthode GoToRecord de l'objet VBA DoCmd permet de déplacer le pointeur sur les enregistrements. Le premier paramètre à lui fournir est la nature de l'objet concerné, soit le formulaire (acDataForm). Puis nous indiquons le nom de ce formulaire en deuxième paramètre. Et enfin la constante acNext du dernier paramètre permet d'indiquer le sens du déplacement, vers l'avant.
- Enregistrer les modifications et basculer sur l'application Access,
- Exécuter le formulaire Listes, taper un identifiant reconnu et valider,
- Choisir un questionnaire dans la liste déroulante,
- Lorsque le QCM apparaît, répondre à quelques questions pour tester l'application,
Comme vous le remarquez, l'application d'évaluation est parfaitement fonctionnelle. A chaque clic sur une réponse, les résultats sont consolidés et affichés. Puis la question suivante est déroulée.
Si vous poursuivez jusqu'à la fin de l'évaluation, le message destiné au candidat se déclenche. En ouvrant la table resultats dans la foulée, vous constatez la présence des données consolidées, insérées par la requête ajout SQL, déclenchée par le code VBA Access.
Un petit phénomène visuel reste cependant à corriger. A chaque fois qu'une zone de texte est cliquée, en guise de réponse, le focus lui est donné. Et ce focus est conservé au passage à la question suivante. Ce focus se traduit par la mise en surbrillance du texte dans sa zone. Nous proposons donc d'ajouter un contrôle TextBox sur le formulaire avec des dimensions définies à zéro pour la largeur et la hauteur. Puis nous proposons de lui donner le focus par le code VBA, à chaque passage de question. Tout en restant visible et donc disponible par le code, ce contrôle ne serait pas visible par le candidat, grâce à ses dimensions. Ainsi l'illusion sera parfaite.
- Afficher le formulaire questions en mode création,
- A l'aide du ruban Création, ajouter un contrôle Zone de texte en bas du formulaire,
- Le renommer p_focus avec sa propriété Nom dans l'onglet Autres de sa feuille de propriétés,
- Dans l'onglet Format, régler ses propriétés Largeur et Hauteur à 0,
- Supprimer la légende de son étiquette associée,
- Enregistrer les modifications (CTRL + S) et basculer dans l'éditeur de code (ALT + F11),
- Dans la branche Else du code précédent, sous l'instruction DoCmd, ajouter la ligne suivante :
A chaque nouvelle question, nous activons la zone de texte p_focus, pour que la surbrillance ne soit plus visible sur les TextBox des réponses proposées. Il serait d'ailleurs judicieux de reproduire cette instruction à l'identique au Form_Load afin d'empêcher l'activation par défaut du premier contrôle, soit celui de la question posée. Si vous déroulez un nouveau test d'évaluation, vous constatez désormais, que le focus n'est plus rendu à aucun contrôle, en apparence.
Dans les étapes suivantes concernant le développement de cette application de QCM, nous proposerons notamment au candidat, de visualiser ses résultats, au travers de classements, dans un formulaire. Il s'agira donc de récupérer les données par requêtes SQL et de les trier sur plusieurs champs, la note et la durée. Au final, nous obtiendrons une solide application d'évaluation, en comparaison du QCM interactif et automatisé que nous avions développé avec Excel, mais qui offrait l'avantage d'être exclusivement développé avec des fonctions de calcul, sans code VBA.
Le code complet pour le déroulement du QCM est le suivant :
Private Sub consolidation(le_choix As String)
numero = numero + 1
nb_reste.Caption = (20 - numero)
If (le_choix = REPONSES.Value) Then la_note = la_note + 1
note.Caption = la_note
If (numero = 20) Then
Dim requete As String: Dim la_base As Database: Dim la_duree
Set la_base = Application.CurrentDb
la_duree = Timer - debut
requete = "INSERT INTO resultats (res_id, res_nom, res_note, res_duree) VALUES ('" & id_candidat.Caption & "','" & transf.Caption & "'," & la_note & "," & Int(la_duree) & ")"
la_base.Execute requete
MsgBox "L'évaluation est terminée, vos résultats ont été archivés"
la_base.Close
Set la_base = Nothing
Else
DoCmd.GoToRecord acDataForm, "questions", acNext
p_focus.SetFocus
End If
End Sub
Sur Facebook
Sur Youtube
Les livres
Contact
Mentions légales
