Dernièrement, un internaute me demandait s’il était possible de modifier le fonctionnement de l’auto complétion dans les listes déroulante. S’agissant d’une fonctionnalité interne d’ACCESS, il est évident que ce n’est pas possible nativement.

Par contre on peut toujours contourner le problème à l’aide du code VBA. Voici cette astuce basée sur l’utilisation de l’évènement KeyPress (touche pressée) de la liste.

On commence par créer une variable globale dans le formulaire pour stocker les touches pressées.

Option Compare Database Option Explicit Dim strList As String 'stocke les keycode

A chaque touche tapée par l’utilisateur, on parcourt la liste à la recherche du premier item correspondant aux caractères saisis.

Private Sub Modifiable1_KeyPress(KeyAscii As Integer) 
   Dim i As Long 'contrôle les touches 
   If KeyAscii = 27 Then strList = ""  'Sur Echap vide le contenu 
   If Not (KeyAscii > 64 And KeyAscii < 123) Then 
      Exit Sub 'ce n'est pas un caractère A-Z a-z (à affiner) 
   End if
   strList = strList & Chr(KeyAscii) 'ajoute la touche pressée 
   For i = 0 To Me.Modifiable1.ListCount - 1 'parcours les items 
      If Me.Modifiable1.Column(1, i) Like "*" & strList & "*" Then 
         'l'item correspond 
         'pour un liste  Me.Modifiable1.ListIndex = i 'on s'y positionne 
         'pour une liste déroulante 
         Me.Modifiable1 = Me.Modifiable1.Column(0, i) 
         Exit For 'et on sort 
      End If 
   Next 
End Sub

La méthode suivante donne la valeur de la colonne 2 de la ligne i.

Column(1, i)

Like est un opérateur logique commun à SQL et VBA.

Like "*" & strList & "*"

Par sécurité on vide les caractères saisis sur la prise et la perte du focus.

Private Sub Modifiable1_GotFocus() 
   strList = "" 'perte du focus on vide la liste de touche 
End Sub 

Private Sub Modifiable1_LostFocus() 
   strList = "" 'perte du focus on vide les keycode 
End Sub

Comme vous le voyez, rien de complexe, on utilise juste les nombreuses possibilités d’Access pour contourner le problème. Il va sans dire que l’auto complétion classique de la liste fonctionne toujours.

N’hésitez pas à affiner le contrôle des touches tapées, en effet, dans cet exemple seul l’alphabet classique est pris en compte, il manque les caractères accentués, les chiffres …

Avertissement

L’utilisation de like successif, 1 like par touche pressée, est très consommatrice pour la base de données. Il peut y avoir de très fort ralentissement sur de gros volumes de données. A vous de juger si il est vraiment opportun d’utiliser cette méthode.

Bonne utilisation !

Ce message est assez commun lorsque vous manipulez des requêtes dans VBA. Nous allons voir pourquoi cela s’affiche et quels sont les solutions à y apporter. Dans l’exemple suivant tout est correct , aucune erreur n’est déclenchée.

Sub getName() 
Dim db As dao.Database 
Dim rst As dao.Recordset 

Set db = CurrentDb 

Set rst = db.OpenRecordset("SELECT [Nom] FROM " & _ "tCONTACT WHERE Nom='Martin';", dbOpenSnapshot) 
MsgBox "Il s’appelle " & rst.Fields("Nom").Value 

rst.Close
db.close
Set rst = Nothing 
Set db = Nothing 
End Sub

Lorsqu’on utilise cette requête avec un paramètre issue d’un formulaire comme ci-dessous :

Sub getName()

 ... 

Set rst = db.OpenRecordset("SELECT [Nom] FROM " & _ "tCONTACT WHERE Nom=forms.fChercher.txt_nom;", dbOpenSnapshot) 
MsgBox "Il s’appelle " & rst.Fields("Nom").Value 

... 
End Sub

Voici le message Erreur 3061 Trop peu de paramètres… qui apparaît :

La seule solution a ce problème est que les paramètres de l’interface soit interprétés non plus par le moteur de base de données comme avec ce code mais directement par VBA.

1ère Solution :

La partie IHM est directement interprété par VBA qui n’envoie que la valeur au moteur de base de données.

Set rst = db.OpenRecordset("SELECT [Nom] FROM tCONTACT " & _ 
       "WHERE Nom=""" & Forms.fChercher.txt_nom & """;", dbOpenSnapshot)

C’est ceci qui est envoyé au moteur de base de données :

SELECT [Nom] FROM tCONTACT WHERE Nom="Martin";

2ème Solution :

Set rst = db.OpenRecordset("SELECT [Nom] FROM tCONTACT " & _ 
          "WHERE Nom=Eval('Forms.fChercher.txt_nom');", dbOpenSnapshot)

Ici c’est un peu différent, c’est la fonction VBA Eval() qui fait la liaison entre le moteur de base de données et l’interface (formulaire). Eval() résout l’expression Forms. et renvoi sa valeur.

3ème Solution :

Celle-ci est un peu plus complexe car elle nécessite l’écriture d’une fonction utilisateur.

Set rst = db.OpenRecordset("SELECT [Nom] FROM tCONTACT " & _ 
         "WHERE Nom=fDonneNom();", dbOpenSnapshot)

Dans un module standard écrivez cette fonction :

Public Function fDonneNom() As Variant
    fDonneNom = Forms.fChercher.txt_nom 
End Function

Ce cas est plutôt à envisager lorsque la valeur renvoyée est le résultat d’une opération complexe comme un calcul, une concaténation…

Public Function fDonneNom() As String 
    fDonneNom = Forms.fChercher.txt_nom 
End Function

4ème Solution

Cette solution est à envisager surtout si vous avez de nombreux paramètres à passer issue de l’IHM ou qu’ils sont hétérogènes ; issue de plusieurs sources (VBA, résultat de requête, IHM…).

Commencer par modifier la requête en utilisant la clause PARAMETERS. Vous pouvez utiliser le QBE (Query By Exemple), autrement dit, l’éditeur de requêtes de Microsoft Access, en Mode Création pour créer cette clause et l’alimenter.

Remplissez le tableau qui apparaît avec un paramètre par ligne et réglez son type (texte, date, entier…). Une fois ceci effectué placez vos paramètres sur la ligne de critères. Si vous basculer en mode SQL, voici ce que vous pourrez observer :

PARAMETERS [txt_nom] Text ( 255 ), [NumSociete] Long; 

SELECT tContact.Nom, tContact.Prenom, tContact.IdSociete FROM tContact WHERE (((tContact.Nom) Like [txt_nom]) AND ((tContact.IdSociete)=[NumSociete]));

 La clause PARAMETERS est ses arguments sont terminés par un point-virgule. Coté VBA voici comment utiliser les PARAMETERS de cette requête.

Sub getName() 
Dim db As DAO.Database 
Dim qry As DAO.QueryDef 
Dim rst As DAO.Recordset 
Dim sql As String 

Set db = CurrentDb 'composition de la requete SQL 
sql = "PARAMETERS [txt_nom] Text ( 255 ), [NumSociete] Long; " 
sql = sql & " SELECT tContact.Nom, tContact.Prenom, " 
sql = sql & " tContact.IdSociete" 
sql = sql & " FROM tContact" 
sql = sql & " WHERE tContact.Nom Like [txt_nom] " 
sql = sql & " AND tContact.IdSociete=[NumSociete];" 

'creation de la requete 
Set qry = db.CreateQueryDef("rqtemporaire", sql) 
'affectation des valeurs aux parametres 
qry.Parameters("txt_nom") = Forms.FChercher.txt_nom 
qry.Parameters("NumSociete") = Forms.FChercher.numSociete 

'composition du recordset 
Set rst = qry.OpenRecordset(dbOpenSnapshot) 
'Resultat 
MsgBox "Il s’appelle " & rst.Fields("Nom").Value 
rst.Close qry.Close 
Set qry = Nothing 
Set rst = Nothing 
Set db = Nothing 

End Sub

Conclusion

Comme vous le voyez il faut passer par un objet Query pour accéder aux Parameters, puis faire un recordset à partir de ce dernier. C’est plus complexe mais lorsque vous avez de nombreuses valeurs à passer à la requête cela fait gagner du temps.

Après avoir parlé dans un billet précédent de la persistance dans un fichier ini, nous allons poursuivre sur la technique de la base de registre.

Écrire dans la base de registre apporte, contrairement au fichier ini, un semblant de sécurité. En effet il est moins aisé de retrouver une valeur parmi les milliers existantes dans la base de registre que de consulter un fichier ini. La destination n’est donc pas la même.

Quels sont les moyens à notre disposition ?

Grâce à la bibliothèque Windows Script Host que l’on retrouve dans les références sous le nom de Microsoft Scripting Runtime on peut invoquer différentes commandes de gestion de la base de registre.

Déclaration

La déclaration de cette bibliothèque peut se faire en early ou late binding (cf Early ou LateBinding). C’est la deuxième méthode que nous allons utiliser ici.

Le modèle est toujours le même, déclaration, instanciation, utilisation, libération.

Dim wsh As Object 
Set wsh = CreateObject("WScript.Shell") 
... instructions 
Set wsh = Nothing

Lecture

Pour la lecture on utilise l’instruction RegRead en précisant le chemin de la clef à lire.

Chemin = wsh.RegRead("HKEY_CURRENT_USER\Software\Microsoft\Office\" & _ 
      Val(SysCmd(acSysCmdAccessVer)) & ".0\Access\Security\" & _ 
      "Trusted Locations\mon application\Path"

En retour la fonction renvoi le contenu de la clef. Dans cet exemple on interroge la base de registre pour connaître l’emplacement approuvé pour « mon application ».

Ecriture

De la même manière on peut écrire une valeur ou créer une clef à l’aide de l’instruction RegWrite.

wsh.RegWrite "HKEY_CURRENT_USER\Software\Microsoft\Office\" & _
       Val(SysCmd(acSysCmdAccessVer)) & ".0\Access\Security\" & _ 
       "Trusted Locations\" & vNameProduit & "\Path", _ 
       "c:\application access\mon application"

Le premier paramètre est le chemin de la clef de registre et le second la valeur à écrire.

Si la clef (le chemin) n’existe pas, elle est créée.

Conclusion

Comme nous l’avons vu rien de sorcier dans l’utilisation de ces instructions. Par contre il est fortement conseillé d’avoir les bases de connaissances minimales de ce qu’est la base de registre. En effet, si la manipulation d’un fichier ini (cf Access et la persistance (1)) pour gérer de la persistance n’est pas critique, manipuler la base de registre l’est sans aucun doute. Prenez les précautions d’usage avant la manipuler.

Nous sommes conscient que la séparation des données et de l’ihm est primordiale pour une application MS Access. L’attachement automatique est depuis de nombreuses années rentré dans les mœurs, chaque développeur utilise le sien.

Si au moment de l’installation, il est obligatoire de spécifier le chemin du fichier de données, lors des mises à jour de l’application cela peut agacer l’utilisateur. Surtout si elles sont régulières. On se doit alors d’utiliser la persistance pour sauvegarder ces paramètres.

Comment créer de la persistance pour les paramètres d’une application ?

En fait il n’y a pas une, mais plusieurs méthodes pour stocker ses valeurs de configuration. La première utilise les fichiers ini. Ce sont des fichiers texte qui ont une structure particulière dont voici un exemple :

[Etiquette1] 
parametre1=Valeur 
parametre2=Valeur 

[Etiquette2] 
parametre1=Valeur

Lire et écrire dans un fichier ini

Lire et écrire dans ce type de fichier ne constitue pas une grosse difficulté puisque Windows possède ces 2 fonctions dans l’API Kernel32.

Je vous livre ici ces 2 fonctions implémentées en VBA qui permettent de gérer un fichier ini dans le répertoire de l’application.

Private Declare Function GetPrivateProfileString Lib "kernel32" _ 
                Alias "GetPrivateProfileStringA" _ 
               (ByVal lpApplicationName As String, _ 
                ByVal lpKeyName As Any, ByVal lpDefault As String, _ 
                ByVal lpReturnedString As String, _ 
                ByVal nSize As Long, _ 
                ByVal lpFileName As String) As Long 

Private Declare Function WritePrivateProfileString Lib _ 
                "kernel32" Alias "WritePrivateProfileStringA" _ 
               (ByVal lpApplicationName As String, _ 
                ByVal lpKeyName As Any, ByVal lpString As Any, _ 
                ByVal lpFileName As String) As Long 

Const vgInifile = "monapplication.ini" 

Function pIniLireParametre(vlEntete As String, _ 
         vlNomParametre As String) As String '---------------------------------------------------------------- 
' Procedure : pIniLireParametre 
' Description : renvoi un paramètre depuis un fichier ini 
' spécifié par vgIniFile 
' Parameters : vlNomParametre est le nom du paramètre à renvoyer 
' Return : le paramètre correspondant à vlNomParametre '---------------------------------------------------------------- 

On Error GoTo Errsub 

Dim vParam As String, vlLong As Long 

vParam = String(255, 0) 
vlLong = GetPrivateProfileString(vlEntete, vlNomParametre, _ 
         "", vParam, 255, CurrentProject.Path & "\" & vgInifile) 

If vlLong <> 0 Then vParam = Left$(vParam, vlLong) 

pIniLireParametre = RTrim(vParam) 
Exit Function 

Errsub: pIniLireParametre = "" 

End Function 

Function pIniEcrireParametre(vlEntete As String, _ 
         vlNomParametre As String, _ 
         vlValeurParametre As String) As Boolean '-------------------------------------------------------------- 
' Procedure : pIniEcrireParametre 
' Description : remplace le paramètre dans un fichier ini spécifié 
' Parameters : vlEntete est l'entête de la rubrique [] 
' vlNomParametre est le nom du paramètre 
' vlValeurParametre est la valeur du paramètre ' Return : N/A '-------------------------------------------------------------- ' 
On Error GoTo Errsub 
    pIniEcrireParametre = True 
    WritePrivateProfileString vlEntete, vlNomParametre, vlValeurParametre, _ 
          CurrentProject.Path & "\" & vgInifile 

Exit Function 

Errsub: 
pIniEcrireParametre = False 

End Function

Mise en œuvre de la persistance

Admettons un fichier Ini comportant plusieurs paramètres dont celui indiquant le répertoire du fichier de données.

[Donnees] 
CheminData=C:\Donnees\ApplicationData\data.accdb

La lecture se fera via l’instruction suivante :

vFichierData = pIniLireParametre("Donnees", "CheminData")

Il sera alors très simple de vérifier le chemin fourni et celui des tables attachées.

Pour écrire le paramètre on utilise cette instruction lors de la première installation :

pIniEcrireParametre "Donnees", "CheminData", _ 
         "c:\Donnees\ApplicationData\data.accdb"

Conclusion

En quelques commandes vous avez donné de la persistance à votre application et apporter un sérieux gain de confort à l’utilisateur.

Autant la sélection automatique d’un item dans une liste modifiable est facile grâce à une simple affectation, autant avec une liste classique c’est plus compliqué.

Je vous livre l’astuce consistant à mettre un peu de code dans une liste déroulante reprenant les valeurs de la liste.

Dans l’événement Après MAJ de la liste déroulante ou d’une zone de texte mettre le code VBA ci-dessous :

Dim i As Long 
Dim i_save As Long  
Me.lstMultiple.Selected(0) = True    'désélection de la liste multiple 

For i = 1 To Me.lstMultiple.ListCount - 1  'parcours la liste multiple    Me.lstMultiple.Selected(i) = False      'déselection de l'élement courant   
   If (Me.lstMultiple.ItemData(i) = CStr(Nz(Me.lstRecherche, ""))) Then   
      's'il est identique à ce que je cherche        
      i_save = i  'je repère la ligne     
   End If 
Next 
Me.lstMultiple.Selected(i_save) = True  'en sortant je sélectionne la liste

lstMultiple est la liste classique et lstRecherche est la liste modifiable ou la zone de texte ou vous sélectionnez la valeur à trouver.

L’astuce consiste à parcourir la liste à la recherche de la valeur, d’en capter l’indice de la ligne pour l’affecter à cette même liste.

Bonne utilisation !

Si vous utilisez des bibliothèques externes à MS Access, comprenez qui ne sont pas distribuées avec MS Access, cet article vous intéresse.

Earlybinding, vous l’utilisez systématiquement sans le savoir.

Pour utiliser une bibliothèque avec MS Access, habituellement on ouvre VBE, l’éditeur de VBA, et on utilise Outils/Références. La liste qui s’affiche dans cette fenêtre sont les bibliothèques disponibles sur le poste. Il suffit de cocher la bibliothèque que l’on souhaite utiliser.

Dans ce cas on utilise le Earlybinding qu’on peut traduire approximativement par liaison en amont. MS Access se comporte alors de la manière suivante. Au moment de l’ouverture de l’application et avant que la première ligne de code de l’application ne  s’exécute, MS Access va vérifier que les bibliothèques requises existent. Il va sans dire qu’aucun contrôle n’est possible durant cette phase.

Cela occasionne des problèmes lorsque la bibliothèque n’est pas installée sur le poste cible ou qu’elle a une version différente. Dans ce cas, un message d’erreur s’affiche et l’application ne démarre pas.

Voici l’exemple d’un code en Earlybinding en relation avec la bibliothèque Microsoft Word x.xx Object Library :

Dim wApp As Word.Application 
Dim oDoc As Word.Document 'crée l'objet Word 
Set wApp = CreateObject("Word.Application") 

wApp.Visible = False 
Set oDoc = wApp.Documents.Open(Chemin) 

With oDoc 
   .MailMerge.OpenDataSource Name:="c:\temp\export.csv" ...

On remarque que les objets sont fortement typés lors de leur déclaration.

Latebinding ou comment contrôler la présence des bibliothèques.

Heureusement, le langage VBA dispose de moyen de prévenir l’absence de la bibliothèque au moyen du Latebinding ou liaison tardive.

Avec le LateBinding la bibliothèque n’est plus déclarée avant, c’est à dire dans les références, mais bel et bien dans le code au moment de son utilisation. Les objets ne sont plus fortement typés mais reçoivent le type générique Object. De même éventuelles constantes ne sont plus disponibles, il faudra donc les recréer.

Voici un cas concret d’une transformation Early vers Late pour palier un problème de version d’Office lors d’une automation avec Word.

On recrée les constantes correspondantes à la bibliothèque Microsoft Office x.xx Object Library.

Option Compare Database 
Option Explicit 

Const wdDoNotSaveChanges = 0 
Const wdExportFormatPDF = 17

Les objets sont typés de manière générique.

'Debug 'Dim wApp As Word.Application 
'Dim oDoc As Word.Document 'Exploitation 
Dim wApp As Object 
Dim oDoc As Object 

Set wApp = CreateObject("Word.Application") 

wApp.Visible = False 
Set oDoc = wApp.Documents.Open(Chemin) 
With oDoc .MailMerge.OpenDataSource Name:="C:\export.csv" ... 
.Close (wdDoNotSaveChanges)

Notez la mise en commentaires des lignes Earlybinding pour permettre une maintenance/évolution facilité.

Cette technique permet, dans la limite de la compatibilité des bibliothèques, de déployer l’application sur n’importe quel Office puisque le code n’est plus attaché à une version unique de Word.

Dans les références on peut se passer de la déclaration comme on peut le voir ci-dessous.

Voici un cas concret d’une vérification de présence de Word lors de l’ouverture d’un formulaire.

Private Sub Form_Open(Cancel As Integer) 

On Error GoTo ErrSub 
Dim wApp As Object 
Set wApp = CreateObject("Word.Application") 
Set wApp = Nothing 
Exit Sub 

ErrSub: 
If Err.Number = 429 Then 
   MsgBox "Vous devez disposer de Word pour utiliser cette fonctionnalité." 
   vComposant = False 
End If 

End Sub

L’erreur 429 est remontée lorsqu’une bibliothèque est absente.

Conclusion

Si le LateBinding est préférable pour une application en exploitation, il n’en va pas de même lors du développement. En effet le Earlybinding donne accès à l’autocomplétion, ce qui est un confort non négligeable dans cette phase du projet.