none
ACCESS2010 vers OUTLOOK2010 : Commment gérer les champs Vide ?? RRS feed

  • Question

  • Bonjour,

    J'ai des problemes pour écrire dans outlook quand un champs est vide

    J'obtiens une erreur d'exécution 94 : Utilisation incorrecte de Null

    Option Explicit
    Const MaDatabase = "E:\bdd\contacts2012.accdb"
    Const NomBAL = "monadresse@mail.test"
    Const NondossContacts = "CONTACTS_test"
    
    Public Sub MettreAJourContact()
    '------------------------------------------------------------------
    '           Procedure pour mettre à jour
    ' les fiches Outlook à partir des éléments plus récents présent
    ' dans la base de données
    '------------------------------------------------------------------
    
    
    Dim nM As NameSpace
    Dim oFold As MAPIFolder
    Dim olApp As Outlook.Application
    Dim oCont As ContactItem
    Dim Oco As ContactItem
    
    
    Dim rs As DAO.Recordset
    Dim db As DAO.Database
    
    
    
    Dim stFilt As String
    
    
    Set db = OpenDatabase(MaDatabase)
    Set rs = db.OpenRecordset("Select * From Tb_Contacts")
    
    
    Set olApp = Outlook.Application
    Set nM = olApp.GetNamespace("MAPI")
    Set oFold = nM.Folders(NomBAL).Folders(NondossContacts)
    
    '--------------------------------------------------------------
    '
    '                   Recherche et filtrage
    'rs.Fields("TTYTDDTelephoneNumber") = index 98
    '--------------------------------------------------------------
    For Each oCont In oFold.Items
        
    
        stFilt = "[TTYTDDTelephoneNumber] = """ & rs.Fields(98) & """"
        
            ' Recherche avec filtre
                Set oCont = oFold.Items.Find(stFilt)
        
                'Filtre pour recherche des données déjà existantes dans les contacts locaux
                                    
                       If rs.Fields("TTYTDDTelephoneNumber") <> "" Then
                                  stFilt = "[TTYTDDTelephoneNumber] = """ & rs.Fields(98) & """"
                               Else
                                    stFilt = "@SQL=" & Chr(34) _
                                    & "urn:schemas:contacts:ttytddphone" & _
                                            Chr(34) & " is null"
                                    End If
        
                                    Dim toto
                                    Set toto = oFold.Items.Restrict(stFilt)
                                    '
                                    stFilt = "[TTYTDDTelephoneNumber] = """ & rs.Fields(98) & """"
        
                                    '' Recherche avec filtre
        
                                    Set Oco = oFold.Items.Find(stFilt)
                                    'Debug.Print Oco
        
                                    Set Oco = toto.Find(stFilt)
                                    Set toto = toto.Restrict(stFilt)
        
        
                                    Debug.Print toto.Count
        
        
                                    If toto.Count > 1 Then MsgBox "Doublon sur : " & rs.Fields(98) & " "
                                    'If toto.Count = 1 Then MsgBox "Pas de doublon"
                                    
     '---------------------------------------------------------------------------------
     '
     ' Ajout des nouvelles fiches si elles n'existent pas dans outlook
     '
     '---------------------------------------------------------------------------------
           
           
     If Oco Is Nothing Then
            
        ' Si pas de données, on les ajoute
          Set Oco = oFold.Items.Add
            Oco.Account = rs.Fields(1)
            
           If Oco.Anniversary = #1/1/4501# Then
            rs.Fields(2) = Oco.Anniversary
           Else
            rs.Fields(2) = Nz(Oco.Anniversary, "")
            End If
            
            
            Oco.AssistantName = rs.Fields(3)
            Oco.AssistantTelephoneNumber = rs.Fields(4)
            Oco.BillingInformation = rs.Fields(5)
            Oco.Birthday = rs.Fields(6)
            Oco.Body = rs.Fields(7)
            Oco.Business2TelephoneNumber = rs.Fields(8)
            Oco.BusinessAddress = rs.Fields(9)
            Oco.BusinessAddressCity = rs.Fields(10)
            Oco.BusinessAddressCountry = rs.Fields(11)
            Oco.BusinessAddressPostalCode = rs.Fields(12)
            Oco.BusinessAddressPostOfficeBox = rs.Fields(13)
            Oco.BusinessAddressState = rs.Fields(14)
            Oco.BusinessAddressStreet = rs.Fields(15)
            Oco.BusinessFaxNumber = rs.Fields(16)
            Oco.BusinessHomePage = rs.Fields(17)
            Oco.BusinessTelephoneNumber = rs.Fields(18)
            Oco.CallbackTelephoneNumber = rs.Fields(19)
            Oco.CarTelephoneNumber = rs.Fields(20)
            Oco.Categories = rs.Fields(21)
            Oco.Children = rs.Fields(22)
            Oco.Companies = rs.Fields(23)
            Oco.CompanyMainTelephoneNumber = rs.Fields("24")
            Oco.CompanyName = rs.Fields(25)
            Oco.CustomerID = rs.Fields(26)
            Oco.Department = rs.Fields(27)
            Oco.Email1Address = rs.Fields(28)
            Oco.Email1AddressType = rs.Fields(29)
            Oco.Email2Address = rs.Fields(31)
            Oco.Email2AddressType = rs.Fields(32)
            Oco.Email2DisplayName = rs.Fields(33)
            Oco.Email3Address = rs.Fields(34)
            Oco.Email3AddressType = rs.Fields(35)
            Oco.Email3DisplayName = rs.Fields(36)
            Oco.FirstName = rs.Fields(38)
            Oco.FTPSite = rs.Fields(39)
            Oco.FullName = rs.Fields(40)
            Oco.Gender = rs.Fields(41)
            Oco.GovernmentIDNumber = rs.Fields(42)
            Oco.Hobby = rs.Fields(43)
            Oco.Home2TelephoneNumber = rs.Fields(44)
            Oco.HomeAddress = rs.Fields(45)
            Oco.HomeAddressCity = rs.Fields(46)
            Oco.HomeAddressCountry = rs.Fields(47)
            Oco.HomeAddressPostalCode = rs.Fields(48)
            Oco.HomeAddressPostOfficeBox = rs.Fields(49)
            Oco.HomeAddressState = rs.Fields(50)
            Oco.HomeAddressStreet = rs.Fields(51)
            Oco.HomeFaxNumber = rs.Fields(52)
            Oco.HomeTelephoneNumber = rs.Fields(53)
            Oco.IMAddress = rs.Fields(54)
            Oco.Importance = rs.Fields(55)
            Oco.Initials = rs.Fields(56)
            Oco.InternetFreeBusyAddress = rs.Fields(57)
            Oco.ISDNNumber = rs.Fields(58)
            Oco.JobTitle = rs.Fields(59)
            Oco.Language = rs.Fields(60)
            
            Oco.LastName = rs.Fields(62)
            Oco.MailingAddress = rs.Fields(63)
            Oco.MailingAddressCity = rs.Fields(64)
            Oco.MailingAddressCountry = rs.Fields(65)
            Oco.MailingAddressPostalCode = rs.Fields(66)
            Oco.MailingAddressPostOfficeBox = rs.Fields(67)
            Oco.MailingAddressState = rs.Fields(68)
            Oco.MailingAddressStreet = rs.Fields(69)
            Oco.ManagerName = rs.Fields(70)
            Oco.MiddleName = rs.Fields(71)
            Oco.Mileage = rs.Fields(72)
            Oco.MobileTelephoneNumber = rs.Fields(73)
            Oco.NetMeetingAlias = rs.Fields(74)
            Oco.NetMeetingServer = rs.Fields(75)
            Oco.NickName = rs.Fields(76)
            Oco.OfficeLocation = rs.Fields(77)
            Oco.OrganizationalIDNumber = rs.Fields(78)
            Oco.OtherAddress = rs.Fields(79)
            Oco.OtherAddressCity = rs.Fields(80)
            Oco.OtherAddressCountry = rs.Fields(81)
            Oco.OtherAddressPostalCode = rs.Fields(82)
            Oco.OtherAddressPostOfficeBox = rs.Fields(83)
            Oco.OtherAddressState = rs.Fields(84)
            Oco.OtherAddressStreet = rs.Fields(85)
            Oco.OtherFaxNumber = rs.Fields(86)
            Oco.OtherTelephoneNumber = rs.Fields(87)
            Oco.PagerNumber = rs.Fields(88)
            Oco.PersonalHomePage = rs.Fields(89)
            Oco.PrimaryTelephoneNumber = rs.Fields(90)
            Oco.Profession = rs.Fields(91)
            Oco.RadioTelephoneNumber = rs.Fields(92)
            Oco.ReferredBy = rs.Fields(93)
            Oco.Spouse = rs.Fields(94)
            Oco.Suffix = rs.Fields(95)
            Oco.TelexNumber = rs.Fields(96)
            Oco.title = rs.Fields(97)
            Oco.TTYTDDTelephoneNumber = rs.Fields(98)
            Oco.User1 = rs.Fields(99)
            Oco.User2 = rs.Fields(100)
            Oco.User3 = rs.Fields(101)
            Oco.User4 = rs.Fields(102)
            Oco.WebPage = rs.Fields(103)
            Oco.YomiCompanyName = rs.Fields(104)
            Oco.YomiFirstName = rs.Fields(105)
            Oco.YomiLastName = rs.Fields(106)
      
            Oco.Save
         
          End If
      
    '----------------------------------------------------------------------------------------
    '
    ' Compare les fiches existantes à partir de la date de modification
    ' Si la fiche de la base est plus récente, elle est mise a jour
    ' dans Outlook
    '----------------------------------------------------------------------------------------
    
    
    If rs.Fields("LastModificationTime") > Oco.LastModificationTime Then
            
            rs.Edit
            Oco.Account = rs.Fields(1)
                     
                    If Oco.Anniversary = #1/1/4501# Then
                    rs.Fields(2) = #1/1/4501#
                Else
                    rs.Fields(2) = Nz(Oco.Anniversary, "")
                    End If
    
            Oco.AssistantName = rs.Fields(3)
            Oco.AssistantTelephoneNumber = rs.Fields(4)
            Oco.BillingInformation = rs.Fields(5)
            
            
                    If Oco.Birthday = #1/1/4501# Then
                    rs.Fields(6) = #1/1/4501#
                Else
                    rs.Fields(6) = Nz(Oco.Birthday, "")
                    End If
            
            
            Oco.Body = rs.Fields(7)
            Oco.Business2TelephoneNumber = rs.Fields(8)
            Oco.BusinessAddress = rs.Fields(9)
            Oco.BusinessAddressCity = rs.Fields(10)
            Oco.BusinessAddressCountry = rs.Fields(11)
            Oco.BusinessAddressPostalCode = rs.Fields(12)
            Oco.BusinessAddressPostOfficeBox = rs.Fields(13)
            Oco.BusinessAddressState = rs.Fields(14)
            Oco.BusinessAddressStreet = rs.Fields(15)
            Oco.BusinessFaxNumber = rs.Fields(16)
            Oco.BusinessHomePage = rs.Fields(17)
            Oco.BusinessTelephoneNumber = rs.Fields(18)
            Oco.CallbackTelephoneNumber = rs.Fields(19)
            Oco.CarTelephoneNumber = rs.Fields(20)
            Oco.Categories = rs.Fields(21)
            Oco.Children = rs.Fields(22)
            Oco.Companies = rs.Fields(23)
            Oco.CompanyMainTelephoneNumber = rs.Fields(24)
            Oco.CompanyName = rs.Fields(25)
            Oco.CustomerID = rs.Fields(26)
            Oco.Department = rs.Fields(27)
            Oco.Email1Address = rs.Fields(28)
            Oco.Email1AddressType = rs.Fields(29)
            Oco.Email2Address = rs.Fields(31)
            Oco.Email2AddressType = rs.Fields(32)
            Oco.Email2DisplayName = rs.Fields(33)
            Oco.Email3Address = rs.Fields(34)
            Oco.Email3AddressType = rs.Fields(35)
            Oco.Email3DisplayName = rs.Fields(36)
            Oco.FirstName = rs.Fields(38)
            Oco.FTPSite = rs.Fields(39)
            Oco.FullName = rs.Fields(40)
            Oco.Gender = rs.Fields(41)
            Oco.GovernmentIDNumber = rs.Fields(42)
            Oco.Hobby = rs.Fields(43)
            Oco.Home2TelephoneNumber = rs.Fields(44)
            Oco.HomeAddress = rs.Fields(45)
            Oco.HomeAddressCity = rs.Fields(46)
            Oco.HomeAddressCountry = rs.Fields(47)
            Oco.HomeAddressPostalCode = rs.Fields(48)
            Oco.HomeAddressPostOfficeBox = rs.Fields(49)
            Oco.HomeAddressState = rs.Fields(50)
            Oco.HomeAddressStreet = rs.Fields(51)
            Oco.HomeFaxNumber = rs.Fields(52)
            Oco.HomeTelephoneNumber = rs.Fields(53)           
            Oco.IMAddress = rs.Fields(54)
            Oco.Importance = rs.Fields(55)
            Oco.Initials = rs.Fields(56)
            Oco.InternetFreeBusyAddress = rs.Fields(57)
            Oco.ISDNNumber = rs.Fields(58)
            Oco.JobTitle = rs.Fields(59)
            Oco.Language = rs.Fields(60)
            
            'oCo.LastModificationTime = rs.Fields(61)
            
            Oco.LastName = rs.Fields(62)
            Oco.MailingAddress = rs.Fields(63)
            Oco.MailingAddressCity = rs.Fields(64)
            Oco.MailingAddressCountry = rs.Fields(65)
            Oco.MailingAddressPostalCode = rs.Fields(66)
            Oco.MailingAddressPostOfficeBox = rs.Fields(67)
            Oco.MailingAddressState = rs.Fields(68)
            Oco.MailingAddressStreet = rs.Fields(69)
            Oco.ManagerName = rs.Fields(70)
            Oco.MiddleName = rs.Fields(71)
            Oco.Mileage = rs.Fields(72)
            Oco.MobileTelephoneNumber = rs.Fields(73)
            Oco.NetMeetingAlias = rs.Fields(74)
            Oco.NetMeetingServer = rs.Fields(75)
            Oco.NickName = rs.Fields(76)
            Oco.OfficeLocation = rs.Fields(77)
            Oco.OrganizationalIDNumber = rs.Fields(78)
            Oco.OtherAddress = rs.Fields(79)
            Oco.OtherAddressCity = rs.Fields(80)
            Oco.OtherAddressCountry = rs.Fields(81)
            Oco.OtherAddressPostalCode = rs.Fields(82)
            Oco.OtherAddressPostOfficeBox = rs.Fields(83)
            Oco.OtherAddressState = rs.Fields(84)
            Oco.OtherAddressStreet = rs.Fields(85)
            Oco.OtherFaxNumber = rs.Fields(86)
            Oco.OtherTelephoneNumber = rs.Fields(87)
            Oco.PagerNumber = rs.Fields(88)
            Oco.PersonalHomePage = rs.Fields(89)
            Oco.PrimaryTelephoneNumber = rs.Fields(90)
            Oco.Profession = rs.Fields(91)
            Oco.RadioTelephoneNumber = rs.Fields(92)
            Oco.ReferredBy = rs.Fields(93)
            Oco.Spouse = rs.Fields(94)
            Oco.Suffix = rs.Fields(95)
            Oco.TelexNumber = rs.Fields(96)
            Oco.title = rs.Fields(97)
            Oco.TTYTDDTelephoneNumber = rs.Fields(98)
            Oco.User1 = rs.Fields(99)
            Oco.User2 = rs.Fields(100)
            Oco.User3 = rs.Fields(101)
            Oco.User4 = rs.Fields(102)
            Oco.WebPage = rs.Fields(103)
            Oco.YomiCompanyName = rs.Fields(104)
            Oco.YomiFirstName = rs.Fields(105)
            Oco.YomiLastName = rs.Fields(106)
    
            Oco.Save
            MsgBox " La Fiche" & Chr(32) & Oco.FirstName & Chr(32) & Oco.LastName & Chr(32) & "a été mise a jour"
            
    
    End If
    ' Déplacement vers l'enregistrement suivant.
    rs.MoveNext
    
    Next
    
    
     
    
     
    
     
    ' Libération des objets
     
    rs.Close
     
    db.Close
     
    Set rs = Nothing
     
    Set db = Nothing
     
    End Sub
    
    

    Je commence a m'arracher les cheveux... les Nz,Cstr,cvar ne change rien

    Comment dire : si il y a rien dans le champ de la bdd, si tu ne veux pas d'une valeur nulle, ne fait rien et passe à la suite

    Egalement je ne comprends pas pourquoi pour certains champ, si je change dans la bdd un prénom par exemple, le champ ne se mets pas a jour dans outlook (et la je n'ai aucun message d'erreur)

    C'est où que cela coince ?

    lundi 18 février 2013 10:27

Réponses

Toutes les réponses