[Access/Excel 2007] Liste déroulante et saisie automatique via BdD [Résolu]

[Syphochaos]

Hello everybody !

Étant nouveau sur le forum, je me présente brièvement :
Cyril, 20 ans, étudiant en deuxième année de DUT.


Après quelques temps de recherche, et n'ayant pas trouvé mon bonheur, je me suis décidé à poser ma question (je n'en ai qu'une pour le moment ^^).

Dans le cadre du stage que j'effectue en ce moment même, j'ai à gérer une base de données via Access et des formulaires à remplir automatiquement sous Excel.

Admettons que dans la base de données, j'ai trois types de données (plus facile pour l'exemple) qui sont : N° produit, Date de livraison et Quantité.

Ma question est la suivante :

Est-il possible, sous Excel, d'avoir une liste déroulante (qui correspondra au N° produit) et deux autres champs (Date de livraison et Quantité) qui se rempliront automatiquement lorsque l'on fera un choix dans la liste ; Tout cela directement via la base de données (en liant le classeur et la BdD par exemple) ?!

Si vous prenez le temps de me lire -et qu'un point n'est pas très clair- j'essaierais de l'éclaircir au maximum.

Merci d'avance braves gens.

[Maxence HUBICHE]

Bonjour, et bienvenue !

Je suis ennuyé pour te répondre car, habituellement, je réponds tout simplement : "OUI" à la question "est-il possible...?"

Mais, tu as pris tellement de temps pour écrire que je me dis que cela ne suffira pas... ^^

La vraie question est surtout : qu'entends-tu par "deux autres champs"

Est-ce que tu compte remplir tout un tableau ?
Est-ce que ta plage est limitée ?
Si oui, combien de lignes ?
...


Mais, en gros, ce que tu pourrais faire (comme ça, là, à br$ule-pourpoing, sans trop trop réfléchir...)

• Créer une feuille tampon (masquée, si tu veux)
• A l'ouverture du classeur, interroger la base de données pour mettre à jour la liste des informations que tu veux, dans le classeur, dans la feuille tampon, et renommer la plage
• Sur la plage de cellule que tu souhaites visualiser avec une zone de liste déroulante, mettre une règle de validation qui va chercher les références
• Sur les cellules adjacentes, mettre une formule avec RechercheV (estNA, Si, ...) pour retrouver les informations de la feuille tampon correspondant à ce que tu as sélectionné dans la première colonne.

Cependant, il est possible que ce que je viens de te dire ne corresponde pas encore à ta demande... dans ce cas, pourrais-tu avoir l'extrême obligeance de condescendre à accepter de bien vouloir (j'en fais pas trop là ????) m'éclairer ?

[Syphochaos]

Envoyé par Maxence HUBICHE

Bonjour, et bienvenue !

Bonjour, et merci.

Envoyé par Maxence HUBICHE

Je suis ennuyé pour te répondre car, habituellement, je réponds tout simplement : "OUI" à la question "est-il possible...?"

Mais, tu as pris tellement de temps pour écrire que je me dis que cela ne suffira pas... ^^

Tu l'aurais fait que je ne t'en aurais pas voulu car tu répondais à la question posée.

Envoyé par Maxence HUBICHE

La vraie question est surtout : qu'entends-tu par "deux autres champs"

Est-ce que tu compte remplir tout un tableau ?
Est-ce que ta plage est limitée ?
Si oui, combien de lignes ?
...

Désolé de ne pas avoir été clair.

Donc en fait, je voudrais avoir trois colonnes (et non champs, au temps pour moi) dont une liste déroulante (N° produit) et et les deux autres seraient des zones de texte (Date de livraison et Quantité).

Ces deux dernières se rempliraient donc automatiquement en fonction du choix de la liste déroulante directement via la BdD.

Pour les lignes, il ne m'en faudrait qu'une seule (de trois colonnes donc) car les formulaires que j'ai à remplir ensuite sont traités au cas par cas.
=> J'insèrerais les données récupérées dans mon formulaire (qui se situera sur une autre feuille Excel), l'imprimerais puis pourrait remplacer ces données par les prochaines à traiter.

Envoyé par Maxence HUBICHE

Mais, en gros, ce que tu pourrais faire (comme ça, là, à br$ule-pourpoing, sans trop trop réfléchir...)

• Créer une feuille tampon (masquée, si tu veux)
• A l'ouverture du classeur, interroger la base de données pour mettre à jour la liste des informations que tu veux, dans le classeur, dans la feuille tampon, et renommer la plage
• Sur la plage de cellule que tu souhaites visualiser avec une zone de liste déroulante, mettre une règle de validation qui va chercher les références
• Sur les cellules adjacentes, mettre une formule avec RechercheV (estNA, Si, ...) pour retrouver les informations de la feuille tampon correspondant à ce que tu as sélectionné dans la première colonne.

Cependant, il est possible que ce que je viens de te dire ne corresponde pas encore à ta demande... dans ce cas, pourrais-tu avoir l'extrême obligeance de condescendre à accepter de bien vouloir (j'en fais pas trop là ????) m'éclairer ?

Cela répondrait à ma demande!

Mais serait-il possible que tout cela se fasse automatiquement dans une seule et unique macro (car je dois automatiser au maximum la récupération des données) ?!

Je souhaiterais que la seule chose à faire manuellement dans la procédure soit le choix des informations à mettre dans la feuille tampon.

En tout cas, merci pour le temps que tu m'accordes.

[Maxence HUBICHE]

Ok, tu l'auras voulu, tant pis pour toi ^^

Alors....

1. Ton classeur, tu l'enregistre en xlsm (classeur prenant en charge les macros) sinon, cela risque de ne pas marcher
2. Tu t'assures que tu as bien une feuille prévue pour ton "Tampon"
3. Tu vas dans le Visual Basic Editor (Alt+F11)
4. Tu repères, dans l'explorateur de projets (la fenêtre représentant une arborescence) ton classeur, puis à l'intérieur de celui-ci,
   • l'objet de ta feuille "Tampon", dont tu modifies le CodeName (Name), dans le fenêtre des propriétés, en fcTampon.
   • l'objet ThisWoorkbook. et tu double-cliques dessus. Ce qui ouvre une fenêtre blanche, à droite : [ThisWorkBook (Code)]
5. Dedans, tu copies-colle le code ci-dessous
6. Tu modifies, éventuellement, les paramètres applicatifs
7. c'est fini pour la partie programmation

Fais un test, en fermant, puis ouvrant ton classeur (attention, tes paramètres doivent permettre l'exéution des macros, sinon c'est mort !)

Voici le code :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
Option Explicit
'Penser à vérifier la référence (outils/références) à la bibliothèque
' Microsoft Office x.xx Access Database Engine Object Library
'################################Paramètres de l'application################################
'# paramètres de connexion à la bdd
Const DB_PATH                 As String = "C:\Users\Maxence\Desktop\Syphochaos\sypho.accdb"
Const USERNAME                As String = "Admin"          'Admin est l'utilisateur par défaut
Const PWD                     As String = ""               'cet utilisateur n'a, par défaut,
'                                                          ' pas de mot de passe
'# paramètres pour la source de données
Const NOM_SOURCE              As String = "qryData"        'nom d'une table, ou requête sélection
'# paramètres pour Excel
Const NOM_PLAGE               As String = "InfosProduits"  'Nom pour le tableau (utilisé avec RechercheV)
Const NOM_COLONNE             As String = "RefProduits"    'Nom pour la première colonne du tableau
'#####################################Code applicatif#######################################
Private Sub Workbook_Open()
    '---------------------------------------------------------------------------------------
    ' Procedure : Workbook_Open
    ' Auteur    : Maxence HUBICHE (<a href="http://www.1formaxion.com" target="_blank">http://www.1formaxion.com</a>)
    ' Date      : 20/04/2011
    ' Objet     : Récupérer les données et les mettre dans la feuille Tampon (Feuil2)
    '               puis, redéfinir la plage, et ce, dès l'ouverture du classeur
    '---------------------------------------------------------------------------------------
    '
    '### Déclaration des variables
    ' le modèle DAO : DBEngine -> WorkSpace -> Database -> Recordset
    Dim oWs                   As Workspace
    Dim oDb                   As DAO.Database
    Dim oRs                   As DAO.Recordset
    '### Affectation des variables
    '# WorkSpace
    '-- on se fout litérallement du nom du workspace
    '-- mais on reprend les paramètres de l'application
    Set oWs = DBEngine.CreateWorkspace("azertyuiop", USERNAME, PWD, dbUseJet)
    '# Database
    '-- ouverture de la base en lecture seule
    Set oDb = oWs.OpenDatabase(DB_PATH)
    '# Recordset
    '-- juste une "photo" (snapshot) des données
    Set oRs = oDb.OpenRecordset(NOM_SOURCE, dbOpenSnapshot)
    '### Copie des données dans la feuille de tampon, en A1
    fcTampon.Cells.Clear
    fcTampon.Range("A1").CopyFromRecordset oRs
    '### Fermeture des objets créés
    oRs.Close: Set oRs = Nothing
    oDb.Close: Set oDb = Nothing
    oWs.Close: Set oWs = Nothing
    '### Redéfinition de la plage nommée
    Names.Add NOM_PLAGE, "=" & fcTampon.Range("A1").CurrentRegion.Address(True, True, xlR1C1, True)
    Names.Add NOM_COLONNE, "=" & fcTampon.Range("A1").CurrentRegion.Columns(1).Address(True, True, xlR1C1, True)
End Sub

[Syphochaos]

Envoyé par Maxence HUBICHE

Ok, tu l'auras voulu, tant pis pour toi ^^

Je ferais juste attention à ne pas demander "Est-il possible..." à nouveau !

Envoyé par Maxence HUBICHE

Fais un test, en fermant, puis ouvrant ton classeur (attention, tes paramètres doivent permettre l'exéution des macros, sinon c'est mort !)

J'ai modifié les paramètres que j'avais à modifier.
J'ai fermé puis rouvert le classeur et :
Test concluant !! J'ai bien les données de ma BdD dans la feuille tampon.

Il ne me reste plus qu'à voir l'histoire de la liste déroulante. Je vais essayer ça et reviendrais te poser des questions si je n'y arrive pas.

Je mets le tag [Résolu] car une grosse partie de mon problème l'est.

Merci beaucoup pour ton aide très précieuse !

[Maxence HUBICHE]

Bon, ben, je continues au cas où... tu reviendrais pour la suite...
Cela m'évitera de m'endormir sur le sujet ^^

Pour la "fabrication" de la liste déroulante

1. Tu cliques dans ta cellule (par exemple A10 dans la feuille du formulaire)
2. Tu vas dans l'onglet Données/Outils de données Validation des données
   • Dans l'onglet Options
     • Dans la propriété Autoriser, tu choisis Liste
     • Dans la propriété Source tu tapes =RefProduits (Correspond à ton NOM_COLONNE dans tes paramètres applicatifs)
   • Dans l'onglet Message de saisie
     • Je te laisse mettre le message que tu souhaites afficher à l'utilisateur lorsqu'il sélectionne A10
   • Si tu veux, dans l'onglet Alerte d'erreur
     • Idem
   • Tu valides

Ta liste est faite

Occupons-nous maintenant des formules que tu auras à côté...
En A11, par exemple, Il faut faire attention ...

• Est-ce qu'il y a une référence de saisie ? Si non, on ne va rien afficher dans la cellule.
• S'il y a une référence, existe-t-elle dans la base ? si oui, on affiche la date (en admettant qu'elle soit dans la 2ème colonne) si non, on affiche "référence inexistante.

Il y a donc 2 conditions et 3 réponses possibles.
Tu peux donc mettre cette formule en A11
=SI(ESTVIDE(A10);"";SI(ESTNA(RECHERCHEV(A10;InfosProduits;2;Faux));"Référence invalide";RECHERCHEV(A10;InfosProduits;2;Faux)))

Je te laisse trouver quoi faire pour la quantité

[Syphochaos]

Envoyé par Maxence HUBICHE

Bon, ben, je continues au cas où... tu reviendrais pour la suite...
Cela m'évitera de m'endormir sur le sujet ^^

Me revoilà !

Envoyé par Maxence HUBICHE

Pour la "fabrication" de la liste déroulante

1. Tu cliques dans ta cellule (par exemple A10 dans la feuille du formulaire)
2. Tu vas dans l'onglet Données/Outils de données Validation des données
   • Dans l'onglet Options
     • Dans la propriété Autoriser, tu choisis Liste
     • Dans la propriété Source tu tapes =RefProduits (Correspond à ton NOM_COLONNE dans tes paramètres applicatifs)
   • Dans l'onglet Message de saisie
     • Je te laisse mettre le message que tu souhaites afficher à l'utilisateur lorsqu'il sélectionne A10
   • Si tu veux, dans l'onglet Alerte d'erreur
     • Idem
   • Tu valides

Ta liste est faite

En fait, j'avais réussi à la faire avant de lire ton post la liste, rien de bien compliqué quand on sait où chercher. Mais ça peut toujours être utile si quelqu'un tombe dessus.

Envoyé par Maxence HUBICHE

Occupons-nous maintenant des formules que tu auras à côté...
En A11, par exemple, Il faut faire attention ...

• Est-ce qu'il y a une référence de saisie ? Si non, on ne va rien afficher dans la cellule.
• S'il y a une référence, existe-t-elle dans la base ? si oui, on affiche la date (en admettant qu'elle soit dans la 2ème colonne) si non, on affiche "référence inexistante.

Il y a donc 2 conditions et 3 réponses possibles.
Tu peux donc mettre cette formule en A11
=SI(ESTVIDE(A10);"";SI(ESTNA(RECHERCHEV(A10;InfosProduits;2;Faux));"Référence invalide";RECHERCHEV(A10;InfosProduits;2;Faux)))

Je te laisse trouver quoi faire pour la quantité

Merci beaucoup pour la formule, parce que je commençais sérieusement à me prendre la tête dessus...

Voilà ce que ça donne pour la quantité :

=SI(ESTVIDE(A10);"";SI(ESTNA(RECHERCHEV(A10;InfosProduits;3;Faux));"Référence invalide";RECHERCHEV(A10;InfosProduits;3;Faux)))

J'ai tout de suite compris que le 2 (remplacé ici par le 3) correspondait au numéro de la colonne de la "zone" InfosProduits !

Sujet entièrement résolu. Encore merci pour ta précieuse aide !