Formater une adresse postale

Voici un petit code qui permet de formater une adresse postal. Il est à insérer en dessous des lignes d’options (Option Compare…) d’un module standard (pas dans le formulaire) :

Valeur saisie : 115, impasse de la petite reine
Valeur formatée : 115, Impasse de la Petite Reine

Const particules = " de ; du ; d' ; des ; l'; la ; le ; les ; en "  
'rajouter les mots en minuscule à ne pas mettre en majuscule

Function formatAdresse(strAdresse As Variant) As String
 Dim strA As String
 Dim arrParticules() As String
 Dim i As Long

 If IsNull(strAdresse) Then Exit Function    
   'si c'est null on ne traite pas
 If strAdresse = "" Then Exit Function       
   'si c'est vide on ne traite pas

arrParticules = Split(particules, ";")
'toutes les premières lettres en majuscule
strA = StrConv(strAdresse, vbProperCase) 

For i = 0 To UBound(arrParticules)
    strA = Replace(strA, arrParticules(i), LCase(arrParticules(i))) 
           'supprime la majuscule
Next

formatAdresse = strA

End Function
Function

On renseigne la constante avec les mots que l’on ne veut pas mettre en majuscule.
1) le strconv() met toutes les 1ère lettre de chaque mot en majuscule.
2) la boucle For remplace les mots trouvés qui correspondent à ceux de la constante.

Et l’appel :
Sélectionner la zone de texte de l’adresse que j’ai nommé Adresse, l’évènement Après MAJ et mettre ceci :

 Private Sub Adresse_AfterUpdate()
     Me.Adresse.Value = formatAdresse(Me.Adresse.Value)
 End Sub
AfterUpdate

« Adresse » est le nom de la zone de texte. Elle doit être remplacée par le nom de celle que vous souhaitez traiter.

Tri d’enregistrements sur le contenu d’une zone de liste déroulante

Il est fréquent et même commun d’utiliser des listes déroulantes dans des formulaires.

Dans le cas où le contenu affiché n’est pas présent dans la source de données, il est difficile de faire un tri sur cette donnée.

Je vous livre ici une astuce qui va vous permettre de faire un tri de la source du formulaire à partir des données affichées dans la liste.

Admettons une liste nommée LmAdherent dont le champ affiché est nommé Nom_Adherent et provient d’une source externe au formulaire.

Dans ce cas sur l’évènement Sur Ouverture indiqué cette syntaxe :

Me.OrderBy = "[Lookup_LmAdherent].[Nom_Adherent]"
Me.OrderByOn = True

Chaque fois que vous ouvrirez le formulaire, il apparaitra trié sur le contenu Nom_Adherent alors que cette données n’est pas contenu dans la source du formulaire.

Vous pouvez découvrir d’autre syntaxe en observant ce qui se passe dans la propriété OrderBy du formulaire lorsque vous utilisez le tri du menu contextuel.

VBA : syntaxe avec ! ou .

Lorsque vous parcourez du code VBA, vous pouvez constater que suivant les développeurs, les uns emploieront le point d’exclamation et le point, tandis que d’autres ne feront usage que du point.

Origine

Le ! est l’héritage de Access Basic (Access 1.1). Dans VBA ont peut encore utiliser quelques instructions Access Basic, uniquement avec les objets survivants de cette époque. Les formulaires et états en sont le plus bel exemple.

L’exemple le plus courant étant celui-ci :

forms!monformulaire

Comportement du !

Ce que l’on sait moins c’est le que le point d’exclamation remplace le membre par défaut.

On peut facilement le vérifier au travers de quelques instructions habituelles.

forms!monformulaire!prenom

dont l’équivalent et la signification n’est pas :

forms.monformulaire.controls("prenom")

mais plutôt :

forms.monformulaire.recordset.fields("prenom")

Il faut donc être extrêmement vigilant avec l’utilisation de point d’exclamation. Ms ACCESS a suffisamment de faux amis pour ne pas en provoquer.

Une dernière syntaxe :

dim rst as dao.recordset
set rst ...
rst!monchamp

Que l’on traduira par :

rst.fields("monchamp")

Le point et ses avantages

Même si utiliser le point implique un code plus verbeux, ce qui n’est pas forcément une mauvaise chose, le point a l’avantage d’être précis et surtout de mettre l’auto complétion à disposition du développeur.

Rien que cet avantage devrait encourager les néophytes à l’employer.

Si vous êtes un mordu du « ! » ou que votre touche « point » n’est pas disponible, vous pouvez toujours regarder dans l’explorateur d’objets de VBE (touche F2) pour connaître le membre par défaut d’un objet ou d’une classe. Il est symbolisé par une pastille bleu et sa description est suffisamment claire.

2017-03-15_00h13_45

 

MS ACCESS et persistance (2)

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.

Ecrire 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 http://blogaccess.free.fr/?p=127). 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 http://blogaccess.free.fr/?p=122) pour gérer de la persistance n’est pas critique, manipuler la base de registre l’est sans aucun doute. Prenez des précautions avant la manipuler.

Manipuler des groupes de contrôles

Quoi de plus énervant que la réorganisation de gros formulaires ?

Avec sa capacité de plus de 700 contrôles, heureusement chiffre rarement atteint, les formulaires peuvent devenir de gros machin difficile à modifier et très chronophage lorsqu’il s’agit de réorganiser leurs contrôles.

Depuis la version 2000, et oui ce n’est plus tout jeune, Microsoft a doté le mode création d’ACCESS d’un petit outil sympathique qui permet de grouper/dégrouper des objets.

Attention, il ne s’agit pas d’exploiter cette fonctionnalité dans vos applications, mais de simplement apporter un peu de souplesse et de facilité dans cette tâche ingrate.

Comment ça marche ?

Tout d’abord prenez un gros formulaire, passez en mode Création, puis sélectionnez un groupe de contrôles.

Cliquez sur le ruban Organiser.

grouper0

Puis cliquer sur Taille/Espace pour dérouler le menu.

grouper1

Enfin sélectionnez Grouper.

Maintenant vos contrôles sont groupés, ils ne forment plus qu’un seul ensemble que vous pouvez manipuler. Déplacement, réduction, agrandissement…

Par contre si vous souhaitez régler un contrôle vous devez faire l’opération inverse et sélectionner Dissocier dans le menu.

Notez que ces regroupements sont conservés à la sauvegarde.

A vos souris !

MS ACCESS : Classe et gain de temps

Le concept de classe en VBA a certes des lacunes mais il ne doit pas être négligés pour autant.

En effet par leur utilisation ont peut gagner un temps précieux. Voici un petit exemple fonctionnel d’une classe qui gère les évènements DblClick et NotInList d’une zone de liste déroulante. Le DblClick permet d’ouvrir un formulaire de saisie/édition avec l’item de la liste, tandis que NotInList permet de saisir l’item.

Créer le module de classe

Dans VBE faites Insertion/Module de classe. En bas à gauche dans la fenêtre des propriétés vous devez renseigner le nom de l’instance. cComboBox

classe1

Instancing permet de définir si la classe n’est visible que dans le fichier courant ou si elle est visible depuis l’extérieur. Laissez 1 – Private.

Déclaration

Sur la ligne qui suit les options, nous allons déclarer les variables nécessaires. Tout d’abord la plus importante, celle qui contiendra l’objet Combobox.

Private WithEvents LmCombo          As Access.ComboBox

WithEvents indique que cet objet pourra lever des évènements.

Access.ComboBox est le type Liste déroulante.

Ensuite nous aurons besoin de variables de type String pour stocker différents éléments nécessaires à l’ouverture du formulaire.

'nom du formulaire qui est appelé lors du notinlist
Private strFormNameLie     As String       
   'nom du champ id de la table recevant la nouvelle valeur
Private strIdFieldName     As String       
   'nom du champ recevant la valeur saisie dans la combo 
Private strControlName     As String       
   'nom de la table recevant la nouvelle valeur
Private strTableFormLie    As String

En suivant nous écrivons deux méthodes habituelles dans les classes.

Private Sub Class_Initialize()
' Initialise

End Sub

La Class_Initialize est exécutée au démarrage de la classe. Elle ne contient rien dans cet exemple.

Private Sub Class_Terminate()
' libère les variables
    On Error Resume Next
    Set LmCombo = Nothing
End Sub

Tandis que Class_Terminate est exécuté lorsqu’on libère la classe, qu’on la décharge. Celle-ci est importante car c’est là qu’on décharge également toutes nos variables.

Pour charger la combo depuis le formulaire il faut utiliser un Setteur. Le voici !

Public Property Set objComboBox(objCombo As Access.ComboBox)
    Set LmCombo = objCombo
    LmCombo.OnDblClick = "[Event Procedure]"
    LmCombo.OnNotInList = "[Event Procedure]"
End Property

On voit qu’on lui passe un objet combobox et qu’il est chargé dans la variable définie précédemment. Les 2 lignes suivantes permettent d’activer l’écoute des évènements dont nous avons besoin : DblClick et NotInList.

Les autres variables ou propriétés de la classe doivent également être valorisées toujours à l’aide de Setteurs.

Public Property Let frmNameLie(strNomFormulaireLie As String)
    strFormNameLie = strNomFormulaireLie
End Property
Public Property Let idFieldName(strNomIdFieldFormulaireLie As String)
    strIdFieldName = strNomIdFieldFormulaireLie
End Property
Public Property Let controlNameLie(strNomControleFormulaireLie As String)
    strControlName = strNomControleFormulaireLie
End Property
Public Property Let tableNameLie(strNomTableFormulaireLie As String)
    strTableFormLie = strNomTableFormulaireLie
End Property

Rien de bien particulier à part qu’on utilise un Let au lieu du Set réservé aux objets.

Nos variables sont devenus des propriétés de la classe.

Méthode privée

Une méthode privée permettra d’attendre la fermeture du formulaire de saisie avant de poursuivre l’exécution du code. Nous d’obtiendrons un déroulement synchrone du processus.

Private Sub pAttendreFermeture(vlFrmRprt As Object)
      '-----------------------------------------------------------
      ' Procedure   : pAttendreFermeture
      ' Author      : Fabrice CONSTANS
      ' Date        : 20/10/2011
      ' Description : Attend la fermeture de l'objet pour rendre la main
      ' Paramètres  : vlFr est l'objet à controler
      '-----------------------------------------------------------
        On Error GoTo pgAttenteFermeture_Error
        Do
            DoEvents
        Loop While vlFrmRprt.Visible

pgAttenteFermeture_Error:
        On Error GoTo 0
        Exit Sub
End Sub

Les évènements

Les évènements vont être gérés à l’aide de procédure qui deviendront de fait des méthodes de la classe. La différence c’est qu’à aucun moment nous ne devront les appeler pour qu’elles fonctionnent. Ceci grâce au WithEvents de la déclaration et à l’activation des écouteurs.

Le Notinlist

On remarque que la signature de la procédure est strictement identique à celle qui pourrait être générée dans un formulaire. Je vous conseille d’ailleurs d’utiliser des signature générée et de les copier dans la classe.

Public Sub LmCombo_NotInList(NewData As String, Response As Integer)
'--------------------------------------------------------------------
' Procedure : LmContact_NotInList
' Author    : Fabrice CONSTANS (MVP)
' Date      : 21/01/2016
' Purpose   : Nouveau contact, on appelle le formulaire de saisie.
'------------------------------------------------------------------
'
Dim strNouveauNumero As String

On Error GoTo Errsub

    If vbYes = MsgBox("Cette valeur n'existe pas. " & _
"Souhaitez-vous la créer ?", vbInformation + vbYesNo, _
                                      "classe ccombo") Then
      'ouverture du formulaire en mode Ajout  
      DoCmd.OpenForm strFormNameLie, acNormal, , , acFormAdd
      'on gèle le contrôle reception de la valeur saisie
      Forms(strFormNameLie).Controls(strControlName).Enabled = False
      'on ajoute la valeur saisie dans la liste
      Forms(strFormNameLie).Controls(strControlName).Value = NewData
      'traitement synchrone
      pAttendreFermeture Forms(strFormNameLie)

'Vérification que le valeur existe (peut-être que l'utilisateur a
' changé d'avis et a annulé la création
   If dLookUp("nz(" & strIdFieldName & ",-1)" _
, strTableFormLie, strControlName & "=""" & NewData & """") > 0 Then
         'la valeur a été saisi
         Response = acDataErrAdded
      Else
        'la valeur n'a pas été saisi (annulation)  
        Response = acDataErrContinue
        LmCombo.Parent.Undo
     End If
   Else
     'il ne souhaite pas la création
     Response = acDataErrContinue
     LmCombo.Parent.Undo
   End If

Exitsub:
      On Error GoTo 0
      Exit Sub
Errsub:
       msgbox "cComboBox.LmCombo_NotInList", Err, Erl, Err.Description
End Sub

Vous pouvez constater que nous utilisons bien les constantes habituelles pour ce type d’évènement : NewData, acDataErrContinue, acDataErrAdded

Le DblClick

Cet évènement est plus simple que le précédent puisqu’il ne fait qu’ouvrir le formulaire en mode consultation/modification.
A la sortie on pense à rafraichir le contenu de la combo au cas ou l’utilisateur a modifié une valeur.
Il va de soit que pour cette dernière le traitement doit également être synchrone.

Public Sub LmCombo_DblClick(Cancel As Integer)
'--------------------------------------------------------------------
' Procedure : btnEditContact_Click
' Author    : Fabrice CONSTANS (MVP)
' Date      : 16/02/2016
' Purpose   : edit et raffraichit la liste au retour
'--------------------------------------------------------------------
'
Dim Id As Long

On Error GoTo Errsub

    If IsNull(LmCombo.Column(0)) Then
        MsgBox "Pour créer un item vous devez entrer sa valeur.", _
               vbInformation + vbOKOnly, "classe ccombo"
        Exit Sub
    End If

    Id = LmCombo.Column(0)

    DoCmd.OpenForm strFormNameLie, acNormal, , _
                strIdFieldName & "=" & Id, acFormEdit

    pAttendreFermeture Forms(strFormNameLie)
    LmCombo.Undo
    LmCombo.Requery
    LmCombo.value = Id

 Exitsub:
   On Error GoTo 0
   Exit Sub 

Errsub:
    msgbox ("cComboBox.LmCombo_DblClick", Err, Erl, Err.Description)
End Sub

Voilà la Classe cCombo est prête à être utilisée. Voyons cela dans les faits.

Utiliser la classe dans un formulaire

En premier lieu il faut indiquer au formulaire que nous allons utiliser cette classe. Pour cela nous devons utiliser autant de variables qu’il y a de zone de liste à implémenter. Ces variables doivent être globale, donc déclarer immédiatement après les 2 lignes d’Options du module de classe du formulaire.

Dim cComboClient         As cComboBox

Dans l’événement Sur Ouverture du formulaire on poursuit la déclaration.

Set cComboClient = New cComboBox

cComboClient.controlNameLie = "RaisonSociale"
cComboClient.frmNameLie = "fClient"
cComboClient.idFieldName = "id_Client"
Set cComboClient.objComboBox = Me.Controls("lmClient")
cComboClient.tableNameLie = "tClient"

New permet de créer l’instance de la classe et ainsi pouvoir valoriser les propriétés.

Dans cet exemple la liste déroulante se nomme lmClient. Vous pouvez adapter ce code déclaratif à votre propre cas. Vous ne devez jamais modifier la classe pour y intégrer des noms propres à l’application. Une classe doit rester générique sous peine de ne plus être portable.

Il va de soit qu’à chaque nouvelle liste déroulante à instancier, un code similaire devra être ajouté.

Une fois sauvegardé, vous pouvez utiliser le formulaire.

Les tests de comportement à faire :

  • Entrer une nouvelle valeur.
  • Double cliquer sur la zone de liste.

Classe en VBA, quels avantages ?

Le poids de l’application : On remplace des centaine de lignes par une partie déclarative.

L’uniformité du comportement : Comme on utilise le même code pour tous les contrôles nous sommes sûr que le comportement sera la même.

Maintenance et évolution : Si un bug est constaté ou que l’on souhaite faire évoluer le comportement, une seule intervention est nécessaire. Le recopie de code ne sont plus nécessaires avec leur lots d’erreurs potentielles.

Rapidité de conception : Une simple déclaration permet d’exploiter la classe.

Le revers de la médaille : En cas d’erreur non traitée, il peut se produire un déchargement de classes. Il faudra alors relancer les déclarations.

La mise au point peut s’avérer plus complexe qu’avec un code standard, c’est pour cela qu’il est conseillé lorsqu’on débute, de concevoir les traitements dans un formulaire pour ensuite le transformer en classe.

Conclusion

J’espère que ce tuto vous aura plu et qu’il vous donnera des idées d’implémentations pour vos développements.

 

 

 

La recherche et les accents en SQL et VBA.

Rechercher dans une base de données avec l’opérateur Like est assez trivial. Avec quelques jokers on arrive à retrouver ce que l’on souhaite. Cependant lorsque on a affaire à des contenus ayant des caractères accentués il est difficile de récupérer à la fois ceux qui en comporte et ceux qui n’en ont pas.

L’objet de ce billet est d’utiliser VBA pour contourner ce problème.

La première chose à faire est de créer une fonction qui va traiter le mot recherché.

Function ConvertAccForLike(strValue As String) As String
' Fabrice Constans (MVP ACCESS) mars 2016
If InStr(1, strValue, "a") > 0 Then
    strValue = Replace(strValue, "a", "[aàâä]")
End If
If InStr(1, strValue, "e") > 0 Then
    strValue = Replace(strValue, "e", "[eéèêë]")
End If
If InStr(1, strValue, "i") > 0 Then
    strValue = Replace(strValue, "i", "[iîï]")
End If
If InStr(1, strValue, "o") > 0 Then
    strValue = Replace(strValue, "o", "[oôö]")
End If
If InStr(1, strValue, "u") > 0 Then
    strValue = Replace(strValue, "u", "[uùûü]")
End If
ConvertAccForLike = strValue
End Function

Dans cette fonction, on recherche la présence des voyelles, une à une, en commençant par le a, ensuite le e, puis le i, etc. Chaque fois que la voyelle est détectée on la remplace par la syntaxe Contient de l’opérateur Like, soit […]

Pour le mot « eleve », la valeur renvoyée sera :

"[eéèêë]l[eéèêë]v[eéèêë]"

Les mots trouvés seront :

eleve, éleve, élève, élevé…

Vous pouvez l’utiliser directement dans objet requête, une source de formulaire, une clause Where d’un OpenForm ou OpenReport comme dans une requête en VBA.

Voici son utilisation :

SELECT * FROM table WHERE champ1 Like convertAccForLike("eleve");

Evidemment vous pouvez compléter avec les autres jokers.

SELECT * FROM table WHERE champ1 Like convertAccForLike("eleve?");

Bonne utilisation !

 

 

 

 

 

Champ mémo et Export Excel, la galère !?

Si vous avez un jour tenté de lier un source de données MS ACCESS, requête ou table, à Excel, vous avez sûrement été confronté à l’interprétation aléatoire des champs mémo.

Aléatoire ? Pas tant que ça. Il faut savoir que pour réaliser un export à partir du menu DONNEES EXTERNES / Exporter Excel, MS ACCESS ne se base non pas sur la structure du champ mais sur le contenu des premières lignes. Si dans ces premières lignes il rencontre un contenu de plus de 255 caractères il considère ce champ comme mémo (texte long)… et là tout va bien.

Dans le cas contraire, il tronquera tout le reste à 255 caractères qui est la limite du format Texte Court, anciennement Texte. Adieu vos 32 000 caractère suivant !

Info : Un champ Texte Long, anciennement  Mémo, de la base de données 
Jet (Ms ACCESS) et d'une capacité de 65 535 caractères exactement. 
Une cellule MS EXCEL en contient tout au plus 32 767. 
Il en manquera toujours un peu.

On peut donc oublier l’export CSV ou depuis la commande MS ACCESS, car à part faire un tri sur le nombre de caractère de votre champ mémo, si techniquement cela ne pose aucun problème fonctionnellement cela peut ne pas convenir, on ne sera jamais sûr d’avoir la totalité du contenu.

Heureusement, il existe un méthode, certes un peu plus complexe mais également plus efficace. Il s’agit de l’automation. Autrement dit, piloter Excel depuis ACCESS et plus particulièrement une méthode de copie de recordset.

CopyFromRecordset

Cette méthode est disponible dans Excel sans ajout de bibliothèque (Références) car il faut le savoir, EXCEL est un consommateur de Recordset à ces heures.

Voici le code commenté :

Function fInsertInSheet(ByVal strPath As String, 
                        ByVal strFeuille As String, 
                        rst As Recordset2) As Boolean
      '-----------------------------------------------------------------
      ' Procedure : fInsertInSheet
      ' Author    : Fabrice CONSTANS (MVP)
      ' Date      : 21/01/2014
      ' Purpose   : Insère un ou plusieurs enregistrements issu de RST
      '             dans la feuille excel strFeuille du fichier strPath
      '             utilise fFieldFormated() et ADODB
      ' Parameters: strPath = chemin+nom du fichier Xls au format 12.0
      '             strFeuille = la feuille dans laquelle insérer l'enrg
      '             rst = le recordset contenant les données à insérer
      ' Return    : Boolean renvoi vrai si insertion réussie
      '-----------------------------------------------------------------
      '
      'Ecrit le recordset transmis dans la feuille indiquée
          Dim strSql As String
          Dim i As Long
          Dim l As Long
          ' Late Binding (cf mon article)
          Dim oExcel As Object         ' Excel application
          Dim oFeuille As Object       ' la feuille
          Dim oWork As Object          ' le workbook
          Dim boolStateDisplayAlerts As Boolean
          Dim boolStateAskToUpdateLinks As Boolean
10        On Error GoTo Errsub
          
20        Set oExcel = CreateObject("Excel.Application")
30        oExcel.Visible = False
          'enregistre l'état
40        boolStateDisplayAlerts = oExcel.DisplayAlerts
50        boolStateAskToUpdateLinks = oExcel.AskToUpdateLinks
          'met en mode silentieux
60        oExcel.AskToUpdateLinks = False
70        oExcel.DisplayAlerts = False
          
80        Set oWork = oExcel.Workbooks.Open(strPath) ' ouvre le classeur
          
90        Set oFeuille = oWork.Sheets(strFeuille)    ' active la feuille
          
          'xlByRows, xlPrevious
100       l = 1                     'insertion en ligne 1
110       oFeuille.Cells(l, 1).CopyFromRecordset rst 'copie recordset
          
120       oExcel.Windows(1).Visible = True      
130       oWork.Save                            'on le sauve
          'remet à l'état d'origine
140       oExcel.DisplayAlerts = boolStateDisplayAlerts
150       oExcel.AskToUpdateLinks = boolStateAskToUpdateLinks
          
160       oExcel.Visible = True
170       oExcel.Quit
          
180       Set oFeuille = Nothing ' ferme les objet xls
190       Set oWork = Nothing
200       Set oExcel = Nothing
210       fInsertInSheet = True  'ça c'est bien passé !
Exitsub:
220      On Error GoTo 0
230      Exit Function
              
Errsub:
240       fInsertInSheet = False  'il y a un problème

250       'ici une gestion d'erreur ou un msg

End Function

Donc voilà un code pas si mystérieux. On ouvre un recrodset coté ACCESS et on le copie coté EXCEL.

Vous pouvez utiliser ce code dans le cadre personnel ou professionnel. Toute vente directe ou indirecte est bien entendu prohibée sans mon accord.

ACCESS – Répéter des données dans un état

La répétition de données dans un état peut être une nécessité, c’est souvent le cas pour l’édition d’étiquettes, de formulaires en plusieurs exemplaires ou tout simplement pour des codes barres.

Plusieurs solutions

La première, qui fait plus office de bricolage consiste à dupliquer les enregistrements à la source. Cependant cela nécessite d’avoir une table source dévouée à l’impression, de préparer l’impression en amont et bien sûr de consacrer du temps SGBD et serveur pour cette tâche.

La seconde, celle que nous allons mettre en pratique consiste à utiliser le moteur d’impression des états d’ACCESS pour simuler cette duplication.

Comment procéder ?

Admettons une table composé des 2 colonnes suivantes :

  • ValeurAImprimer qui contient les données à imprimer. (Texte 255)
  • FréquenceImpression un code qui détermine la fréquence d’impression. (Texte 50)

On commence par créer un état simplement basé sur cette table, on cache la zone de texte FréquenceImpression en mettant sa propriété Visible à Non.

Voilà le décors est planté, il n’y a plus qu’à insérer le code VBA correspondant.

Le code

Le code exploite les évènements Sur Ouverture et Sur Formatage, une variable globale, une petite fonction qui peut être facultative comme nous le verrons plus tard et utilise la propriété NextRecord propre aux états ACCESS.

La variable globale va permettre de compter les occurrences imprimées.

Option Compare Database
Option Explicit
Dim cpt As Long

Lors de l’ouverture de l’état la variable est initialisée à 1.

Private Sub Report_Open(Cancel As Integer)
    cpt = 1
End Sub

A chaque préparation de l’impression de la zone détail on analyse ce qui doit être effectué.

Private Sub Détail_Format(Cancel As Integer, FormatCount As Integer)
cpt = cpt + 1 
If cpt <= nbrRepeat(Me.FréquenceImpression) Then 
    Me.NextRecord = False 
Else
    cpt = 1 End If
End Sub

Si le compteur (cpt) n’a pas atteint le nombre de répétition souhaité on réimprime le même enregistrement.
Ce tour de force est effectué grâce à la propriété NextRecord qui, si elle est False, ne charge pas l’enregistrement suivant.

Le nombre de répétition est déterminé par la valeur contenue dans FréquenceImpression.
Dans cet exemple on veut imprimer suivant une périodicité une quantité déterminée d’informations.

Function nbrRepeat(period As String)
Select Case period
    Case "trimestriel"
       nbrRepeat = 3
    Case "annuel"
       nbrRepeat = 1
    Case "semestriel"
       nbrRepeat = 2
End Select
End Function

Par exemple si FréquenceImpression contient « trimestriel » l’enregistrement sera imprimé 3 fois.

Notez que cette dernière fonction peut être paramétrée à votre guise, ne pas exister du tout si vous indiquez directement le nombre dans la colonne FréquenceImpression ou si le nombre d’impression est fixe.

Bonne impression !