Probleme en ecriture avec una table ODBC

Question

Salut &#224; tous



Bon je vous expose mon probl&#232;me. J'utilise plusieurs tables attach&#233;es
ODBC en lecture et j'&#233;cris les informations utiles dans 2 tables
&#233;galement attach&#233;es ODBC. Le soucis c'est que pour la 1&#232;re table j'ai 3
occurences de valides mais au lieu de voir les 3 occurrences, je vois 3
fois la premi&#232;re. Et pour la seconde table idem sauf qu'il ya 15
occurences diff&#233;rentes donc 15 fois la 1&#232;re.



Je vous mets la base de mon programme:

    Set Rs1 = Db1.OpenRecordset(Name:="GZCRPDTA_F47121", Type:=dbOpenDynaset)

    Set Rs2 = Db1.OpenRecordset(Name:="GZCRPDTA_F47122", Type:=dbOpenDynaset)



    Selection = "SELECT CMV_CODE, CMV_GENCODE, EMP_CODE, MAG_CODE, PAL_CODE, LOT_CODE, "

    Selection = Selection & "MOUVT.ART_CODE, MVT_DATE, MVT_QTE, MVT_SENS, ART_USER4 "

    Selection Selection & "FROM MOUVT INNER JOIN ARTICLES ON MOUVT.ART_CODE ARTICLES.ART_CODE "

    Selection = Selection & "ORDER BY MOUVT.CMV_CODE, MOUVT.MVT_DATE"



    Set Rs3 = Db1.OpenRecordset(Selection)



    NbRecord = Rs3.RecordCount

    NumeroLigne = 1

    Rs3.MoveFirst



    Do Until Rs3.EOF

    NoLot = Rs3("CMV_CODE") & Str(Rs3("MVT_DATE"))

       

    If AncienNoLot <> NoLot Then

    With Rs1

        .AddNew

        .....

        .Update

    End With

    With Rs2

        .AddNew

        .....

        .Update

    End With

    Rs3.MoveNext

Loop



Je ferme tous et voil&#224; le programme est termin&#233;e. J'ai
pens&#233; que l'erreur pouvait venir d'une mauvaise d&#233;claration de mes 2
tables avec les OpenRecordSet donc si quelqu'un a une id&#233;e je l'en
remercie



A plus

acecel · Answer

et en mettant ca :

Set Rs3 = Db1.OpenRecordset(Selection,dbOpenDynaset)

rieuxg · Answer

Et bien non ca change rien, toujours la m&#234;me erreur.

yuri1961 · Answer

Salut!

Regarde s'il y a qchose sous AncienNoLot et NoLot

Do Until Rs3.EOF
     NoLot = Rs3("CMV_CODE") & Str(Rs3("MVT_DATE"))
      Msgbox   NoLot & vbCrLF AncienNoLot
     If AncienNoLot <> NoLot Then
     With Rs1
         .AddNew
         .....
         .Update
     End With
     With Rs2
         .AddNew
         .....
         .Update
     End With
     Rs3.MoveNext
 Loop

rieuxg · Answer

Il y a bien quelque chose dans ces valeurs. Apr&#233;s le if il y a 3 msgbox
diff&#233;rent qui s'affiche avec les valeurs des 3 occurences qui devraient
s'ecrire dans ma table. J'ai fait afficher toutes les valeurs qui
doivent &#234;tre &#233;crite dans la table et elles changent bien mais elles ne
s'&#233;crivent pas dans la table execpt&#233; la 1&#233;re qui s'&#233;crit plusieurs fois.

aieeeuuuuu · Answer

salut

tu ne mets pas a jour ta variable AncienNoLot

avant ton movenext; il faudrait que tu fasse une AncienNoLot = NoLot

rieuxg · Answer

Si je le fait apr&#233;s le 1er .update et avant le end with. D&#233;sol&#233; d'avoir oubli&#233; de copier cette ligne.

aieeeuuuuu · Answer

et est ce que tu es sur de ce que te retourne ta requete.

je pense que tu dois avoir un probleme de jointure. es tu sur qu'il n'y a pas un deuxieme champs dans ta clef primaire ou qq chose comme ca. 

Je te conseille de lancer ta requete sous access direcement pour voir si le resultat est bien celui auquel tu t'attend, ou de faire un point d'arret.

yuri1961 · Answer

Salut!

Tu as fait le bleme, ou est end if

et ou tu obtiens la valeur d'  
AncienNoLot

AncienNoLot=?

 Do Until Rs3.EOF
     NoLot = Rs3("CMV_CODE") & Str(Rs3("MVT_DATE"))

Msgbox   NoLot & vbCrLF & AncienNoLot

         If AncienNoLot <> NoLot Then
     With Rs1
         .AddNew
         .....
         .Update
     End With
     With Rs2
         .AddNew
         .....
         .Update
     End With

End If

     Rs3.MoveNext
 Loop

Et Regarde les valeurs 
des NoLot et AncienNoLot

rieuxg · Answer

Salut bon je vais vous mettre le code au grand complet ca va être un peu lourd mais bon. Option Compare Database Dim Db1 As Database Dim Rs2 As Recordset Public Function Traitement_MOUVT() Dim fso, FicMouvt, Selection Dim Rs1 As Recordset Dim error$, TypeEmplacement As String ' On Error GoTo Traitement_MOUVT_Error: Set fso = CreateObject("Scripting.FileSystemObject") OldName = "\10.34.1.6\d$\GAZECHIM\IER\INTERFACE\Fichiers\MOUVT.DAT" NewName = "\10.34.1.6\d$\GAZECHIM\IER\INTERFACE\Fichiers\MOUVT.TXT" NoLot = "" AncienNoLot = "" NoDocumentEDI = Format(Date, "yymmdd") & Format(Time, "hhmm") 'If fso.FileExists(OldName) = True Then ' Name OldName As NewName ' DoCmd.TransferText acImportFixed, "MOUVT", "MOUVT", NewName ' Set FicMouvt = fso.getfile(NewName) ' FicMouvt.Delete Set Db1 = CurrentDb() 'Set Rs1 = Db1.OpenRecordset(Name:="F47121", Type:=dbOpenDynaset) 'Set Rs2 = Db1.OpenRecordset(Name:="F47122", Type:=dbOpenDynaset) Set Rs1 = Db1.OpenRecordset("GZCRPDTA_F47121", dbOpenDynaset) Set Rs2 = Db1.OpenRecordset("GZCRPDTA_F47122", dbOpenDynaset) Selection = "SELECT CMV_CODE, CMV_GENCODE, EMP_CODE, MAG_CODE, PAL_CODE, LOT_CODE, " Selection = Selection & "MOUVT.ART_CODE, MVT_DATE, MVT_QTE, MVT_SENS, ART_USER4 " Selection Selection & "FROM MOUVT INNER JOIN ARTICLES ON MOUVT.ART_CODE ARTICLES.ART_CODE " Selection = Selection & "ORDER BY MOUVT.CMV_CODE, MOUVT.MVT_DATE" ' Selection = "SELECT * FROM MOUVT ORDER BY MOUVT.CMV_CODE, MOUVT.MVT_DATE" Set Rs3 = Db1.OpenRecordset(Selection, dbOpenDynaset) NbRecord = Rs3.RecordCount NumeroLigne = 1 Rs3.MoveFirst Do Until Rs3.EOF NoLot = Rs3("CMV_CODE") & Str(Rs3("MVT_DATE")) Set Rs1 = Db1.OpenRecordset("GZCRPDTA_F47121", dbOpenDynaset) Set Rs2 = Db1.OpenRecordset("GZCRPDTA_F47122", dbOpenDynaset) If AncienNoLot <> NoLot Then ' Alimentation du F47121 entete mouvement With Rs1 .AddNew .Fields("M1EDTY").Value = "" .Fields("M1EDSQ").Value = 0 .Fields("M1EKCO").Value = "00009" .Fields("M1EDOC").Value = NoDocumentEDI .Fields("M1EDCT").Value = "VS" .Fields("M1EDLN").Value = 0 .Fields("M1EDST").Value = "852" .Fields("M1EDFT").Value = "" .Fields("M1EDDT").Value = 0 .Fields("M1EDER").Value = "R" .Fields("M1EDDL").Value = 0 .Fields("M1EDSP").Value = "" .Fields("M1EDBT").Value = NoLot .Fields("M1PNID").Value = "" .Fields("M1THCD").Value = "" .Fields("M1AN8").Value = 99999999 .Fields("M1DTFR").Value = 0 .Fields("M1DTTO").Value = 0 .Fields("M1VR01").Value = "" .Fields("M1URCD").Value = "" .Fields("M1URDT").Value = 0 .Fields("M1URAT").Value = 0 .Fields("M1URAB").Value = 0 .Fields("M1URRF").Value = "" .Fields("M1TORG").Value = "" .Fields("M1USER").Value = "IER" .Fields("M1PID").Value = "MOUVT" .Fields("M1JOBN").Value = "ACCESS" .Fields("M1UPMJ").Value = JulianDate(Date) .Fields("M1TDAY").Value = Format(Time, "hhmmss") .Update End With AncienNoLot = NoLot End If '----------------------------------------- ' Traitement des nomenclatures If Rs3("CMV_GENCODE") = "IR" Then Emplacement = Rs3("EMP_CODE") Select Case Emplacement Case MSSA TypeEmplacement = "GT1" Case ASSIDECA TypeEmplacement = "GT2" Case Else TypeEmplacement = "GTC" End Select CoutUT = Traitement_Nomenclature(Rs3("ART_CODE"), Rs3("MAG_CODE"), Rs3("MVT_QTE"), TypeEmplacement, Rs3("LOT_CODE"), NoDocumentEDI, Rs3("EMP_CODE"), NoLot) End If '----------------------------------------- With Rs2 .AddNew .Fields("MJEDTY").Value = "" .Fields("MJEDSQ").Value = 0 .Fields("MJEKCO").Value = "00009" .Fields("MJEDOC").Value = NoDocumentEDI .Fields("MJEDCT").Value = "VS" .Fields("MJEDLN").Value = 0 .Fields("MJEDST").Value = "852" .Fields("MJEDFT").Value = "" .Fields("MJEDDT").Value = 0 .Fields("MJEDER").Value = "R" .Fields("MJEDDL").Value = 0 .Fields("MJEDSP").Value = "" .Fields("MJEDBT").Value = NoLot .Fields("MJPNID").Value = "" .Fields("MJPACD").Value = "QS" 'QR qte recue + ; QS qte vendue - .Fields("MJKSEQ").Value = 0 .Fields("MJCITM").Value = "" .Fields("MJXRT").Value = "" .Fields("MJAN8").Value = 99999999 .Fields("MJVR01").Value = "Cond test de ACCESS" .Fields("MJMCU").Value = " " & Rs3("MAG_CODE") .Fields("MJITM").Value = 0 ' Test code Article Plein ou vide NoSerie = Rs3("LOT_CODE") If NoSerie <> "" And Rs3("MVT_QTE") = 1 Then .Fields("MJLITM").Value = Rs3("ART_USER4") Else .Fields("MJLITM").Value = Rs3("ART_CODE") End If .Fields("MJAITM").Value = "" If IsNull(Rs3("EMP_CODE")) Then .Fields("MJLOCN").Value = " " Else .Fields("MJLOCN").Value = Rs3("EMP_CODE") End If If IsNull(Rs3("LOT_CODE")) Then .Fields("MJLOTN").Value = " " Else .Fields("MJLOTN").Value = Rs3("LOT_CODE") End If .Fields("MJPLOT").Value = "" .Fields("MJSTUN").Value = 0 .Fields("MJLDSQ").Value = 0 .Fields("MJTRNO").Value = 0 .Fields("MJFRTO").Value = "" .Fields("MJLMCX").Value = "" .Fields("MJLOTS").Value = "" .Fields("MJLOTP").Value = 0 .Fields("MJLOTG").Value = "" .Fields("MJLDSC").Value = "" .Fields("MJMMEJ").Value = 0 .Fields("MJDSC1").Value = "test description" .Fields("MJDSC2").Value = "" .Fields("MJTRDJ").Value = 0 .Fields("MJTRUM").Value = "UN" ' Traitement de la quantité ----------------------- If NoSerie <> "" Then 'Bt Gazechim If Rs3("MVT_QTE") = 1 Then 'Bt Vide qte = Rs3("MVT_QTE") Else qte = Rs3("MVT_QTE") / 100 'Bt Pleine End If Else 'Bt Client If Rs3("MVT_QTE") = 1 Then 'Bt Vide qte = Rs3("MVT_QTE") Else qte = Rs3("MVT_QTE") / 100 'Bt Pleine End If End If If Rs3("MVT_SENS") = "S" Then qte = qte * -1000 Else qte = qte * 1000 End If .Fields("MJTRQT").Value = qte ' ------------------------------------------------- If CoutUT <> 0 Then .Fields("MJUNCS").Value = CoutUT ' Coût Unitaire * 10000 Else .Fields("MJUNCS").Value = 0 End If .Fields("MJPAID").Value = 0 .Fields("MJMMCU").Value = "" .Fields("MJDMCT").Value = "" .Fields("MJDMCS").Value = 0 .Fields("MJBALU").Value = "" .Fields("MJKCO").Value = "00009" .Fields("MJDOC").Value = 0 .Fields("MJDCT").Value = Rs3("CMV_GENCODE") .Fields("MJSFX").Value = "" .Fields("MJJELN").Value = 1000 + NumeroLigne .Fields("MJICU").Value = 0 .Fields("MJDGL").Value = 0 .Fields("MJGLPT").Value = "" .Fields("MJDCTO").Value = "" .Fields("MJDOCO").Value = 0 .Fields("MJKCOO").Value = "" .Fields("MJLNID").Value = 0 .Fields("MJNLIN").Value = 0 .Fields("MJTREX").Value = "Test cond ref" ' 30 Carcatètes .Fields("MJTREF").Value = "" .Fields("MJRCD").Value = "" .Fields("MJCRCD").Value = "" .Fields("MJCRR").Value = 0 .Fields("MJCDEC").Value = "" .Fields("MJANI").Value = "" .Fields("MJASII").Value = "" .Fields("MJSBL").Value = "" .Fields("MJSBLT").Value = "" .Fields("MJWR01").Value = "" .Fields("MJURCD").Value = "" .Fields("MJURDT").Value = 0 .Fields("MJURAT").Value = 0 .Fields("MJURAB").Value = 0 .Fields("MJURRF").Value = "" .Fields("MJTORG").Value = "" .Fields("MJUSER").Value = "IER" .Fields("MJPID").Value = "MOUVT" .Fields("MJJOBN").Value = "ACCESS" .Fields("MJUPMJ").Value = JulianDate(Date) .Fields("MJTDAY").Value = Format(Time, "hhmmss") .Update End With Rs3.MoveNext NumeroLigne = NumeroLigne + 1 Rs1.Close Rs2.Close Loop 'Rs1.Close 'Rs2.Close Rs3.Close Traitement_MOUVT_Exit: Exit Function Traitement_MOUVT_Error: MsgBox Error$ Resume Traitement_MOUVT_Exit: End Function Public Function Traitement_Nomenclature(Article, Magasin, Quantite, TypeNomenclature, NumSerie, NoDocumentEDI, Emplacement, NoLot) As Double CoutUT = 0 Selection2 = "SELECT IXKITL, IXTBM, IXMMCU, IXLITM, IXCMCU, IXQNTY, IXUM, COUNCS, COCSIN, IMUOM1 " Selection2 Selection2 & "FROM (GZCRPDTA_F3002 INNER JOIN GZCRPDTA_F4105 ON (GZCRPDTA_F3002.IXLITM " Selection2 Selection2 & "GZCRPDTA_F4105.COLITM) AND (GZCRPDTA_F3002.IXCMCU GZCRPDTA_F4105.COMCU)) " Selection2 Selection2 & "INNER JOIN DTAPROD_F4101 ON GZCRPDTA_F3002.IXLITM DTAPROD_F4101.IMLITM " Selection2 Selection2 & "WHERE IXKITL """ & Article & """ AND IXTBM = """ & TypeNomenclature & """ AND " Selection2 Selection2 & "IXMMCU """ & " " & Magasin & """ AND COCSIN = ""I"" ORDER BY IXCPNT" Set Rs4 = Db1.OpenRecordset(Selection2) NbRecord = Rs4.RecordCount If NbRecord <> 0 Then Rs4.MoveFirst Do Until Rs4.EOF With Rs2 .AddNew .Fields("MJEDTY").Value = "" .Fields("MJEDSQ").Value = 0 .Fields("MJEKCO").Value = "00009" .Fields("MJEDOC").Value = NoDocumentEDI .Fields("MJEDCT").Value = "VS" .Fields("MJEDLN").Value = 0 .Fields("MJEDST").Value = "852" .Fields("MJEDFT").Value = "" .Fields("MJEDDT").Value = 0 .Fields("MJEDER").Value = "R" .Fields("MJEDDL").Value = 0 .Fields("MJEDSP").Value = "" .Fields("MJEDBT").Value = NoLot .Fields("MJPNID").Value = "" .Fields("MJPACD").Value = "QS" 'QR qte recue + ; QS qte vendue - .Fields("MJKSEQ").Value = 0 .Fields("MJCITM").Value = "" .Fields("MJXRT").Value = "" .Fields("MJAN8").Value = 99999999 .Fields("MJVR01").Value = "Cond test de ACCESS" '.Fields("MJMCU").Value = " " & Magasin .Fields("MJMCU") = Rs4("IXMMCU") .Fields("MJITM").Value = 0 .Fields("MJLITM") = Rs4("IXLITM") .Fields("MJAITM").Value = "" If IsNull(Emplacement) Then .Fields("MJLOCN").Value = " " Else .Fields("MJLOCN").Value = Emplacement End If If IsNull(NumSerie) Then .Fields("MJLOTN").Value = " " Else .Fields("MJLOTN").Value = NumSerie End If .Fields("MJPLOT").Value = "" .Fields("MJSTUN").Value = 0 .Fields("MJLDSQ").Value = 0 .Fields("MJTRNO").Value = 0 .Fields("MJFRTO").Value = "" .Fields("MJLMCX").Value = "" .Fields("MJLOTS").Value = "" .Fields("MJLOTP").Value = 0 .Fields("MJLOTG").Value = "" .Fields("MJLDSC").Value = "" .Fields("MJMMEJ").Value = 0 .Fields("MJDSC1").Value = "test description" .Fields("MJDSC2").Value = "" .Fields("MJTRDJ").Value = 0 '.Fields("MJTRUM").Value = "UN" .Fields("MJTRUM") = Rs4("IXUM") .Fields("MJTRQT") = Rs4("IXQNTY") * Quantite * -1 .Fields("MJUNCS").Value = ((Rs4("COUNCS") / 1000) * (Rs2("MJTRQT") / 1000)) * -1 .Fields("MJPAID").Value = 0 .Fields("MJMMCU").Value = "" .Fields("MJDMCT").Value = "" .Fields("MJDMCS").Value = 0 .Fields("MJBALU").Value = "" .Fields("MJKCO").Value = "00009" .Fields("MJDOC").Value = 0 .Fields("MJDCT").Value = "IR" .Fields("MJSFX").Value = "" '.Fields("MJJELN").Value = 1000 + NumeroLigne .Fields("MJICU").Value = 0 .Fields("MJDGL").Value = 0 .Fields("MJGLPT").Value = "" .Fields("MJDCTO").Value = "" .Fields("MJDOCO").Value = 0 .Fields("MJKCOO").Value = "" .Fields("MJLNID").Value = 0 .Fields("MJNLIN").Value = 0 .Fields("MJTREX").Value = "Test cond ref" ' 30 Carcatètes .Fields("MJTREF").Value = "" .Fields("MJRCD").Value = "" .Fields("MJCRCD").Value = "" .Fields("MJCRR").Value = 0 .Fields("MJCDEC").Value = "" .Fields("MJANI").Value = "" .Fields("MJASII").Value = "" .Fields("MJSBL").Value = "" .Fields("MJSBLT").Value = "" .Fields("MJWR01").Value = "" .Fields("MJURCD").Value = "" .Fields("MJURDT").Value = 0 .Fields("MJURAT").Value = 0 .Fields("MJURAB").Value = 0 .Fields("MJURRF").Value = "" .Fields("MJTORG").Value = "" .Fields("MJUSER").Value = "IER" .Fields("MJPID").Value = "MOUVT" .Fields("MJJOBN").Value = "ACCESS" .Fields("MJUPMJ").Value = JulianDate(Date) .Fields("MJTDAY").Value = Format(Time, "hhmmss") .Update End With CoutU = ((Rs4("COUNCS") / 1000) * (Rs4("IXQNTY") / 1000)) * Quantite CoutUT = CoutU + CoutUT Rs4.MoveNext Loop End If Rs4.Close Let Traitement_Nomenclature = CoutUT End Function

Probleme en ecriture avec una table ODBC

9 réponses

Discussions similaires