d'une table à l'autre
Le
fth

Bonjour,
J'ai un petit probleme avec un bout de code qui fonctionne pourtant dans une
autre de mes appli.
Quelqu'un pour me faire une correction?
d'avance merci
ci joint, le code en question:
Private Sub BtValider_Click()
'si le record "idrb" de la table "running_business" n'existe pas dans la
table "BC"
'ajouter le record; si elle existe, mettre à jour le record dans la table
"BC" pour les
'lignes de "running_business" ou ("rbstatus" = "g"). pour les autres lignes,
ne rien faire ( ou peut etre un msgbox)
If IsNull(DLookup("IDRB", "BC", "idrb=" & IDRB)) Then
DoCmd.RunSQL "INSERT INTO BC ( IDRB, IDcompany, IDcontact, RBstart, RBref,
RBstatus," & _
"RBforecastdate, RBcatprod, RbQuArt, RBTotalHw, RBTotalOption,
RBTotalSolution, " & _
"RBclickvolume, RBclickprice, RBLenght, RBestimforecast, RBforecastCA,
RBdonestatus," & _
"RBcomment, RBdetail, RBTotPaHw, RBTotPaOption, RBTotPaSolution,
RBTotPAcopy, RBdone, " & _
"flag1 ) " & _
"SELECT running_business.IDRB, running_business.IDcompany,
running_business.IDcontact, " & _
"running_business.RBstart, running_business.RBref,
running_business.RBstatus," & _
"running_business.RBforecastdate, running_business.RBcatprod,
running_business.RbQuArt, " & _
"running_business.RBTotalHw, running_business.RBTotalOption,
running_business.RBTotalSolution, " & _
"running_business.RBclickvolume, running_business.RBclickprice,
running_business.RBLenght, " & _
"running_business.RBestimforecast, running_business.RBforecastCA,
running_business.RBdonestatus, " & _
"running_business.RBcomment, running_business.RBdetail,
running_business.RBTotPaHw, " & _
"running_business.RBTotPaOption, running_business.RBTotPaSolution,
running_business.RBTotPAcopy, " & _
"running_business.RBdone, running_business.flag1, " & _
"FROM running_business WHERE (running_business.IDRB = IDRB)AND
(running_business.rbstatus = 'G');"
Else
If (DLookup("idrb", "BC", "idrb=" & IDRB)) Then
DoCmd.RunSQL "UPDATE BC INNER JOIN running_business ON BC.IDRB =
running_business.IDRB " & _
"SET BC.IDcompany = [running_business].[IDcompany], " & _
"BC.IDcontact = [running_business].[IDcontact], " & _
"BC.RBstart = [running_business].[RBstart], " & _
"BC.RBref = [running_business].[RBref], " & _
"BC.RBstatus = [running_business].[RBstatus], " & _
"BC.RBforecastdate = [running_business].[RBforecastdate], " & _
"BC.RBcatprod = [running_business].[RBcatprod], " & _
"BC.RbQuArt = [running_business].[RbQuArt], " & _
"BC.RBTotalHw = [running_business].[RBTotalHw], " & _
"BC.RBTotalOption = [running_business].[RBTotalOption], " & _
"BC.RBTotalSolution = [running_business].[RBTotalSolution], " & _
"BC.RBclickvolume = [running_business].[RBclickvolume]," & _
"BC.RBestimforecast = [running_business].[RBestimforecast], " & _
"BC.RBforecastCA = [running_business].[RBforecastCA], " & _
"BC.RBdonestatus = [running_business].[RBdonestatus], " & _
"BC.RBcomment = [running_business].[RBcomment], " & _
"BC.RBdetail = [running_business].[RBdetail], " & _
"BC.RBTotPaHw = [running_business].[RBTotPaHw], " & _
"BC.RBTotPaOption = [running_business].[RBTotPaOption], " & _
"BC.RBTotPaSolution = [running_business].[RBTotPaSolution], " & _
"BC.RBTotPAcopy = [running_business].[RBTotPAcopy], " & _
"BC.flag1 = [running_business].[flag1], " & _
"FROM running_business WHERE (running_business.IDRB = IDRB)AND
(running_business.rbstatus = 'G');"
End If
End If
End Sub
J'ai un petit probleme avec un bout de code qui fonctionne pourtant dans une
autre de mes appli.
Quelqu'un pour me faire une correction?
d'avance merci
ci joint, le code en question:
Private Sub BtValider_Click()
'si le record "idrb" de la table "running_business" n'existe pas dans la
table "BC"
'ajouter le record; si elle existe, mettre à jour le record dans la table
"BC" pour les
'lignes de "running_business" ou ("rbstatus" = "g"). pour les autres lignes,
ne rien faire ( ou peut etre un msgbox)
If IsNull(DLookup("IDRB", "BC", "idrb=" & IDRB)) Then
DoCmd.RunSQL "INSERT INTO BC ( IDRB, IDcompany, IDcontact, RBstart, RBref,
RBstatus," & _
"RBforecastdate, RBcatprod, RbQuArt, RBTotalHw, RBTotalOption,
RBTotalSolution, " & _
"RBclickvolume, RBclickprice, RBLenght, RBestimforecast, RBforecastCA,
RBdonestatus," & _
"RBcomment, RBdetail, RBTotPaHw, RBTotPaOption, RBTotPaSolution,
RBTotPAcopy, RBdone, " & _
"flag1 ) " & _
"SELECT running_business.IDRB, running_business.IDcompany,
running_business.IDcontact, " & _
"running_business.RBstart, running_business.RBref,
running_business.RBstatus," & _
"running_business.RBforecastdate, running_business.RBcatprod,
running_business.RbQuArt, " & _
"running_business.RBTotalHw, running_business.RBTotalOption,
running_business.RBTotalSolution, " & _
"running_business.RBclickvolume, running_business.RBclickprice,
running_business.RBLenght, " & _
"running_business.RBestimforecast, running_business.RBforecastCA,
running_business.RBdonestatus, " & _
"running_business.RBcomment, running_business.RBdetail,
running_business.RBTotPaHw, " & _
"running_business.RBTotPaOption, running_business.RBTotPaSolution,
running_business.RBTotPAcopy, " & _
"running_business.RBdone, running_business.flag1, " & _
"FROM running_business WHERE (running_business.IDRB = IDRB)AND
(running_business.rbstatus = 'G');"
Else
If (DLookup("idrb", "BC", "idrb=" & IDRB)) Then
DoCmd.RunSQL "UPDATE BC INNER JOIN running_business ON BC.IDRB =
running_business.IDRB " & _
"SET BC.IDcompany = [running_business].[IDcompany], " & _
"BC.IDcontact = [running_business].[IDcontact], " & _
"BC.RBstart = [running_business].[RBstart], " & _
"BC.RBref = [running_business].[RBref], " & _
"BC.RBstatus = [running_business].[RBstatus], " & _
"BC.RBforecastdate = [running_business].[RBforecastdate], " & _
"BC.RBcatprod = [running_business].[RBcatprod], " & _
"BC.RbQuArt = [running_business].[RbQuArt], " & _
"BC.RBTotalHw = [running_business].[RBTotalHw], " & _
"BC.RBTotalOption = [running_business].[RBTotalOption], " & _
"BC.RBTotalSolution = [running_business].[RBTotalSolution], " & _
"BC.RBclickvolume = [running_business].[RBclickvolume]," & _
"BC.RBestimforecast = [running_business].[RBestimforecast], " & _
"BC.RBforecastCA = [running_business].[RBforecastCA], " & _
"BC.RBdonestatus = [running_business].[RBdonestatus], " & _
"BC.RBcomment = [running_business].[RBcomment], " & _
"BC.RBdetail = [running_business].[RBdetail], " & _
"BC.RBTotPaHw = [running_business].[RBTotPaHw], " & _
"BC.RBTotPaOption = [running_business].[RBTotPaOption], " & _
"BC.RBTotPaSolution = [running_business].[RBTotPaSolution], " & _
"BC.RBTotPAcopy = [running_business].[RBTotPAcopy], " & _
"BC.flag1 = [running_business].[flag1], " & _
"FROM running_business WHERE (running_business.IDRB = IDRB)AND
(running_business.rbstatus = 'G');"
End If
End If
End Sub
If (DLookup("idrb", "BC", "idrb=" & IDRB)) Then
je n'ai pas tout lu mais sur ton 2eme If il manque le test non ?
De plus Dlookup() va retourner une valeur si la recherche aboutie et Null
dans le cas contraire je crois, donc quel est l'intérêt du 2eme If?
A+
Eric
"fth" news:408e5376$0$21673$:
tu as un problème ici:
If (DLookup("idrb", "BC", "idrb=" & IDRB)) Then
DLookup ne renvoie pas une valeur booléenne mais la valeur du champ si
trouvé ou null.
le plus simple serait de remplacer DLookp par DCount:
If (DCount("idrb", "BC", "idrb=" & IDRB)) > 0 Then
ou si tu tiens à dlookup:
If (DLookup("idrb", "BC", "idrb=" & IDRB)) = IDRB Then
je n'ai pas regardé le reste, je pense qu'il est bon.
--
@+
Raymond Access MVP
http://access.seneque.free.fr/
http://access2003.free.fr/
http://users.skynet.be/mpfa/ pour débuter sur le forum
"fth" news:408e5376$0$21673$
j'ai retiré le deuxieme if mais toujours probleme
en français, ma requeste donnerait aprox:
1.vérifier si la ligne existe deja dans BC, si la réponse est non, ajoute,
si elle existe, met à jour les champs de la ligne
2. le passage d'une table à l'autre ne doit se faire que si une ligne de la
table "running_business" à son champ "rbstatus" à "G".
encore merci!
"Eric" news:
de fait, Dcount me semble plus rationnel...comme quoi, les habitudes...merci
pour le conseil
j'ai toujours qq problemes dans ma requete, à mon avis dans la partie FROM
et WHERE...
En français ,ça devrait donner : si la ligne n'existe pas, l'ajouter dans
"BC", si elle existe, mettre à jour les champs MAIS ne sont à traiter QUE
les lignes de "running_business" dont le champ "rbstatus" est "G"
encore merci
Frederic
"Raymond [mvp]" news:
pour plus de clarté, voici l'explication de ce qui devrait se passer:
3 tables:
running_business-----les affaires en cours
BC------------------une affaire gagnée (running_business.rbstatus="G")
devient une commande
facture--------------la facturation (BC.facture = yes) de la commande passe
dans la table "facture"
en fonction du déroulement des activités, les infos passent d'une table à
l'autre
Seules les affaires en cours gagnées passent en commande
mm opération pour passer de BC à Facture
encore merci
Frederic
Mais pour me faciliter la tache ca plante dans le cas ou dlookup() est null
ou dans l'autre ? Dans les 2 ?
Une remarque : il manque un espace là :
WHERE (running_business.IDRB = IDRB)AND
^ ^
| et peut être là
Eric
"fth" news:408e5c6f$0$21470$:
"FROM running_business WHERE (running_business.IDRB = " & IDRB & ")AND
(running_business.rbstatus = 'G');"
--
@+
Raymond Access MVP
http://access.seneque.free.fr/
http://access2003.free.fr/
http://users.skynet.be/mpfa/ pour débuter sur le forum
"fth" news:408e5a63$0$21678$
Tu auras corrigé de toi même : avant et apres le And
Pour l'instant, ça semble planter dans les deux cas :-/
"Eric" news:
Raymond t'as donné la solution: sortir IDRB des "" dans la clause Where:
"where running_business.IDRB=" & IDRB & " AND..." dans les 2 .
Eric
"fth" @news.skynet.be: