requete sql vers vba

Version imprimable

bonjour

je réalise un publipostage et pour cela j'utilise une requete avec des critaires et donc je peux avoir une requete vide.

donc j'aimerai savoir comment vérifier si ma requete envoi un enregistrement ou non et si on n'envoi pas d'enregistrement avertir l'utilisateur

qui peux m'aide?? merci

j'ai trouve une fonction avec une requete en sql et j'aimerai l'incorporé en vba mais je ne sais comment faire ( remplace par le "a")

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Dim strNomRequete As String
 
 
' ma requete a mettre en vba :
 
DoCmd.RunSQL "SELECT ENTREPRISE_SOLLICITE.NUM_CORPS_ETATS, ENTREPRISE_SOLLICITE.NUM_ENTREPRISE, ENTREPRISE_SOLLICITE.NUM_AO, ENTREPRISE_SOLLICITE.NUM_OPERATION, OPERATION.NOM_OPERATION, OPERATION.ADRESSE_OPERATION, OPERATION.ADRESSEBIS_OPERATION, OPERATION.VILLE_OPERATION, OPERATION.CP_OPERATION, OPERATION.PROGRAMME, [POUC_TVA]*100 AS POUC_TVA1, SOCIETE.VILLE3D, ESTIM_OPE.NUM_LOT, ENTREPRISE_SOLLICITE.DEVIS_RETENUE, ENTREPRISE.NOM_ENTREPRISE, ENTREPRISE.ADRESSE_ENTREPRISE, ENTREPRISE.ADRESSEBIS_ENTREPRISE, ENTREPRISE.VILLE_ENTREPRISE, ENTREPRISE.CP_ENTREPRISE, ENTREPRISE.RCS, ENTREPRISE.RCS_VILLE, ENTREPRISE_CONTACT.NOM_ENTRE_CONTACT, CORPS_ETATS.NOM_CORPS_ETATS, ENTREPRISE_SOLLICITE.MONTANT_MARCHE, [MONTANT_MARCHE]*[POUC_TVA] AS MONTANT_TVA, [MONTANT_MARCHE]+[MONTANT_TVA] AS MONTANT_MARCHETTC" & _
" FROM SOCIETE INNER JOIN (((INTERVENANT_EXT_CONTACT INNER JOIN (OPERATION INNER JOIN INTERVENANT_EXT_CONTACT_OPE ON (OPERATION.NUM_INTERV_PROMOTEUR = INTERVENANT_EXT_CONTACT_OPE.NUM_INTERVENANT_EXT) AND" & _
"(OPERATION.NUM_OPERATION = INTERVENANT_EXT_CONTACT_OPE.NUM_OPERATION)) ON (INTERVENANT_EXT_CONTACT.NUM_INTERVENANT_EXT = INTERVENANT_EXT_CONTACT_OPE.NUM_INTERVENANT_EXT) AND (INTERVENANT_EXT_CONTACT.NUM_INTERVENANT_CONTACT = INTERVENANT_EXT_CONTACT_OPE.NUM_INTERVENANT_CONTACT)) INNER JOIN (ENTREPRISE_CONTACT INNER JOIN ENTREPRISE_CONTACT_OPE ON (ENTREPRISE_CONTACT.NUM_ENTREPRISE = ENTREPRISE_CONTACT_OPE.NUM_ENTREPRISE) AND (ENTREPRISE_CONTACT.NUM_ENTRE_CONTACT = ENTREPRISE_CONTACT_OPE.NUM_ENTRE_CONTACT)) ON OPERATION.NUM_OPERATION = ENTREPRISE_CONTACT_OPE.NUM_OPERATION) INNER JOIN ((CORPS_ETATS INNER JOIN ESTIM_OPE ON CORPS_ETATS.NUM_CORPS_ETATS = ESTIM_OPE.NUM_CORPS_ETATS) INNER JOIN (ENTREPRISE INNER JOIN ENTREPRISE_SOLLICITE ON ENTREPRISE.NUM_ENTREPRISE = ENTREPRISE_SOLLICITE.NUM_ENTREPRISE)" & _
" ON (ESTIM_OPE.NUM_OPERATION = ENTREPRISE_SOLLICITE.NUM_OPERATION) AND (ESTIM_OPE.NUM_CORPS_ETATS = ENTREPRISE_SOLLICITE.NUM_CORPS_ETATS)) ON OPERATION.NUM_OPERATION = ESTIM_OPE.NUM_OPERATION) ON SOCIETE.NUM_SOCIETE_3D = OPERATION.NUM_SOCIETE_3D WHERE (((ENTREPRISE_SOLLICITE.NUM_CORPS_ETATS)=[Forms]![Fiche Marché]![NUM_CORPS_ETATS]) AND ((ENTREPRISE_SOLLICITE.NUM_ENTREPRISE)=[Forms]![Fiche Marché]![NUM_ENTREPRISE]) AND ((ENTREPRISE_SOLLICITE.NUM_AO)=[Forms]![Fiche Marché]![NUM_AO]) AND ((ENTREPRISE_SOLLICITE.NUM_OPERATION)=[Forms]![Fiche Marché]![NUM_OPERATION]) AND ((ENTREPRISE_SOLLICITE.DEVIS_RETENUE)=Yes) AND ((ENTREPRISE_CONTACT.SIGNATURE_MARCHER)=Yes) AND ((INTERVENANT_EXT_CONTACT.SIGNATURE_MARCHERMO)=Yes));"
 
 
 
 
 
strNomRequete = "a"
 
If DCount("*", strNomRequete) > 0 Then
   DoCmd.OpenQuery strNomRequete, acNormal
Else
   MsgBox "La requête '" & strNomRequete & "' ne renvoie aucun enregistrement"
End If

essaies ça :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
DoCmd.SetWarnings False
Dim sql As String
sql = "SELECT ENTREPRISE_SOLLICITE.NUM_CORPS_ETATS, ENTREPRISE_SOLLICITE.NUM_ENTREPRISE, ENTREPRISE_SOLLICITE.NUM_AO, " & _
      "ENTREPRISE_SOLLICITE.NUM_OPERATION, OPERATION.NOM_OPERATION, OPERATION.ADRESSE_OPERATION, OPERATION.ADRESSEBIS_OPERATION, " & _
      "OPERATION.VILLE_OPERATION, OPERATION.CP_OPERATION, OPERATION.PROGRAMME, [POUC_TVA]*100 AS POUC_TVA1, SOCIETE.VILLE3D," & _
      "ESTIM_OPE.NUM_LOT, ENTREPRISE_SOLLICITE.DEVIS_RETENUE, ENTREPRISE.NOM_ENTREPRISE, ENTREPRISE.ADRESSE_ENTREPRISE, " & _
      "ENTREPRISE.ADRESSEBIS_ENTREPRISE, ENTREPRISE.VILLE_ENTREPRISE, ENTREPRISE.CP_ENTREPRISE, ENTREPRISE.RCS, " & _
      "ENTREPRISE.RCS_VILLE, ENTREPRISE_CONTACT.NOM_ENTRE_CONTACT, CORPS_ETATS.NOM_CORPS_ETATS, ENTREPRISE_SOLLICITE.MONTANT_MARCHE, " & _
      "[MONTANT_MARCHE]*[POUC_TVA] AS MONTANT_TVA, [MONTANT_MARCHE]+[MONTANT_TVA] AS MONTANT_MARCHETTC" & _
      "FROM SOCIETE INNER JOIN (((INTERVENANT_EXT_CONTACT INNER JOIN (OPERATION INNER JOIN INTERVENANT_EXT_CONTACT_OPE ON " & _
      "(OPERATION.NUM_INTERV_PROMOTEUR = INTERVENANT_EXT_CONTACT_OPE.NUM_INTERVENANT_EXT) AND " & _
      "(OPERATION.NUM_OPERATION = INTERVENANT_EXT_CONTACT_OPE.NUM_OPERATION)) ON (INTERVENANT_EXT_CONTACT.NUM_INTERVENANT_EXT = INTERVENANT_EXT_CONTACT_OPE.NUM_INTERVENANT_EXT)" & _
      "AND (INTERVENANT_EXT_CONTACT.NUM_INTERVENANT_CONTACT = INTERVENANT_EXT_CONTACT_OPE.NUM_INTERVENANT_CONTACT)) " & _
      "INNER JOIN (ENTREPRISE_CONTACT NNER JOIN ENTREPRISE_CONTACT_OPE ON (ENTREPRISE_CONTACT.NUM_ENTREPRISE = ENTREPRISE_CONTACT_OPE.NUM_ENTREPRISE) " & _
      "AND (ENTREPRISE_CONTACT.NUM_ENTRE_CONTACT = ENTREPRISE_CONTACT_OPE.NUM_ENTRE_CONTACT)) " & _
      "ON OPERATION.NUM_OPERATION = ENTREPRISE_CONTACT_OPE.NUM_OPERATION) INNER JOIN ((CORPS_ETATS INNER JOIN ESTIM_OPE " & _
      "ON CORPS_ETATS.NUM_CORPS_ETATS = ESTIM_OPE.NUM_CORPS_ETATS) INNER JOIN (ENTREPRISE INNER JOIN ENTREPRISE_SOLLICITE  " & _
      "ON ENTREPRISE.NUM_ENTREPRISE = ENTREPRISE_SOLLICITE.NUM_ENTREPRISE)ON (ESTIM_OPE.NUM_OPERATION = ENTREPRISE_SOLLICITE.NUM_OPERATION) " & _
      "AND (ESTIM_OPE.NUM_CORPS_ETATS = ENTREPRISE_SOLLICITE.NUM_CORPS_ETATS)) " & _
      "ON OPERATION.NUM_OPERATION = ESTIM_OPE.NUM_OPERATION) ON SOCIETE.NUM_SOCIETE_3D = OPERATION.NUM_SOCIETE_3D " & _
      "WHERE (((ENTREPRISE_SOLLICITE.NUM_CORPS_ETATS)=[Forms]![Fiche Marché]![NUM_CORPS_ETATS]) " & _
      "AND ((ENTREPRISE_SOLLICITE.NUM_ENTREPRISE)=[Forms]![Fiche Marché]![NUM_ENTREPRISE]) " & _
      "AND ((ENTREPRISE_SOLLICITE.NUM_AO)=[Forms]![Fiche Marché]![NUM_AO]) " & _
      "AND ((ENTREPRISE_SOLLICITE.NUM_OPERATION)=[Forms]![Fiche Marché]![NUM_OPERATION]) " & _
      "AND ((ENTREPRISE_SOLLICITE.DEVIS_RETENUE)=Yes) AND ((ENTREPRISE_CONTACT.SIGNATURE_MARCHER)=Yes) " & _
      "AND ((INTERVENANT_EXT_CONTACT.SIGNATURE_MARCHERMO)=Yes));"
 DoCmd.RunSQL sql

01/08/2008, 08h53
popofpopof
j'arrive pas a faire ce que tu me dis

je souhaite remplace le "a" ( strNomRequete = "a")

par la requete en vba afin qu'il me réalise le test de sa
Code:

1 2 3 4 5 If DCount("*", strNomRequete) > 0 Then DoCmd.OpenQuery strNomRequete, acNormal Else MsgBox "La requête '" & strNomRequete & "' ne renvoie aucun enregistrement" End If
tu comprend?

:salut:

Simplement :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Dim a as String, strNomRequete

a = "SELECT ENTREPRISE_SOLLICITE.NUM_CORPS_ETATS, ENTREPRISE_SOLLICITE.NUM_ENTREPRISE, ENTREPRISE_SOLLICITE.NUM_AO, ENTREPRISE_SOLLICITE.NUM_OPERATION, OPERATION.NOM_OPERATION, OPERATION.ADRESSE_OPERATION, OPERATION.ADRESSEBIS_OPERATION, OPERATION.VILLE_OPERATION, OPERATION.CP_OPERATION, OPERATION.PROGRAMME, [POUC_TVA]*100 AS POUC_TVA1, SOCIETE.VILLE3D, ESTIM_OPE.NUM_LOT, ENTREPRISE_SOLLICITE.DEVIS_RETENUE, ENTREPRISE.NOM_ENTREPRISE, ENTREPRISE.ADRESSE_ENTREPRISE, ENTREPRISE.ADRESSEBIS_ENTREPRISE, ENTREPRISE.VILLE_ENTREPRISE, ENTREPRISE.CP_ENTREPRISE, ENTREPRISE.RCS, ENTREPRISE.RCS_VILLE, ENTREPRISE_CONTACT.NOM_ENTRE_CONTACT, CORPS_ETATS.NOM_CORPS_ETATS, ENTREPRISE_SOLLICITE.MONTANT_MARCHE, [MONTANT_MARCHE]*[POUC_TVA] AS MONTANT_TVA, [MONTANT_MARCHE]+[MONTANT_TVA] AS MONTANT_MARCHETTC" & _
" FROM SOCIETE INNER JOIN (((INTERVENANT_EXT_CONTACT INNER JOIN (OPERATION INNER JOIN INTERVENANT_EXT_CONTACT_OPE ON (OPERATION.NUM_INTERV_PROMOTEUR = INTERVENANT_EXT_CONTACT_OPE.NUM_INTERVENANT_EXT) AND" & _
"(OPERATION.NUM_OPERATION = INTERVENANT_EXT_CONTACT_OPE.NUM_OPERATION)) ON (INTERVENANT_EXT_CONTACT.NUM_INTERVENANT_EXT = INTERVENANT_EXT_CONTACT_OPE.NUM_INTERVENANT_EXT) AND (INTERVENANT_EXT_CONTACT.NUM_INTERVENANT_CONTACT = INTERVENANT_EXT_CONTACT_OPE.NUM_INTERVENANT_CONTACT)) INNER JOIN (ENTREPRISE_CONTACT INNER JOIN ENTREPRISE_CONTACT_OPE ON (ENTREPRISE_CONTACT.NUM_ENTREPRISE = ENTREPRISE_CONTACT_OPE.NUM_ENTREPRISE) AND (ENTREPRISE_CONTACT.NUM_ENTRE_CONTACT = ENTREPRISE_CONTACT_OPE.NUM_ENTRE_CONTACT)) ON OPERATION.NUM_OPERATION = ENTREPRISE_CONTACT_OPE.NUM_OPERATION) INNER JOIN ((CORPS_ETATS INNER JOIN ESTIM_OPE ON CORPS_ETATS.NUM_CORPS_ETATS = ESTIM_OPE.NUM_CORPS_ETATS) INNER JOIN (ENTREPRISE INNER JOIN ENTREPRISE_SOLLICITE ON ENTREPRISE.NUM_ENTREPRISE = ENTREPRISE_SOLLICITE.NUM_ENTREPRISE)" & _
" ON (ESTIM_OPE.NUM_OPERATION = ENTREPRISE_SOLLICITE.NUM_OPERATION) AND (ESTIM_OPE.NUM_CORPS_ETATS = ENTREPRISE_SOLLICITE.NUM_CORPS_ETATS)) ON OPERATION.NUM_OPERATION = ESTIM_OPE.NUM_OPERATION) ON SOCIETE.NUM_SOCIETE_3D = OPERATION.NUM_SOCIETE_3D WHERE (((ENTREPRISE_SOLLICITE.NUM_CORPS_ETATS)=[Forms]![Fiche Marché]![NUM_CORPS_ETATS]) AND ((ENTREPRISE_SOLLICITE.NUM_ENTREPRISE)=[Forms]![Fiche Marché]![NUM_ENTREPRISE]) AND ((ENTREPRISE_SOLLICITE.NUM_AO)=[Forms]![Fiche Marché]![NUM_AO]) AND ((ENTREPRISE_SOLLICITE.NUM_OPERATION)=[Forms]![Fiche Marché]![NUM_OPERATION]) AND ((ENTREPRISE_SOLLICITE.DEVIS_RETENUE)=Yes) AND ((ENTREPRISE_CONTACT.SIGNATURE_MARCHER)=Yes) AND ((INTERVENANT_EXT_CONTACT.SIGNATURE_MARCHERMO)=Yes));"
 
Docmd.RunSql a

' Ligne inutile pour la suite de ton programme
strNomRequete = a


If DCount("*", a) > 0 Then
   DoCmd.OpenQuery a, acNormal
Else
   MsgBox "La requête '" & a & "' ne renvoie aucun enregistrement"
End If

Bonne journée

Bonjour,

DoCmd.RunSQL ne sert que pour les requêtes actions. Pas pour les requêtes de Sélection.
Si la requête n'existe pas en tant qu'objet Access DCount ne sert à rien.

Ta requête n'existant que dans ton code VBA (si j'ai bien compris), il faut utiliser un recordset.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Dim rs As DAO.Recordset Dim strSQL As String, blnRequeteVide as Boolean strSQL = "SELECT ENTREPRISE_SOLLICITE.NUM_CORPS_ETATS, ...." Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot) blnRequeteVide = False If rs.EOF Then blnRequeteVide = True rs.Close If Not blnRequeteVide Then ' Il y a au moins un enregistrement ' .... Else MsgBox "La requête ne renvoie aucun enregistrement" End If
A+

LEDZEPPII j'ai essai ton code mais sa ne marche pas j'ai copier mon code desous

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
 If listeimpression = 40 Then
 DoCmd.RunCommand acCmdRefresh
Dim rs As DAO.Recordset
Dim strSQL As String, blnRequeteVide As Boolean
 
strSQL = "SELECT ENTREPRISE_SOLLICITE.NUM_CORPS_ETATS, ENTREPRISE_SOLLICITE.NUM_ENTREPRISE, ENTREPRISE_SOLLICITE.NUM_AO, ENTREPRISE_SOLLICITE.NUM_OPERATION, OPERATION.NOM_OPERATION, OPERATION.ADRESSE_OPERATION, OPERATION.ADRESSEBIS_OPERATION, OPERATION.VILLE_OPERATION, OPERATION.CP_OPERATION, OPERATION.PROGRAMME, [POUC_TVA]*100 AS POUC_TVA1, SOCIETE.VILLE3D, ESTIM_OPE.NUM_LOT, ENTREPRISE_SOLLICITE.DEVIS_RETENUE, ENTREPRISE.NOM_ENTREPRISE, ENTREPRISE.ADRESSE_ENTREPRISE, ENTREPRISE.ADRESSEBIS_ENTREPRISE, ENTREPRISE.VILLE_ENTREPRISE, ENTREPRISE.CP_ENTREPRISE, ENTREPRISE.RCS, ENTREPRISE.RCS_VILLE, ENTREPRISE_CONTACT.NOM_ENTRE_CONTACT, CORPS_ETATS.NOM_CORPS_ETATS, ENTREPRISE_SOLLICITE.MONTANT_MARCHE, [MONTANT_MARCHE]*[POUC_TVA] AS MONTANT_TVA, [MONTANT_MARCHE]+[MONTANT_TVA] AS MONTANT_MARCHETTC" & _
" FROM SOCIETE INNER JOIN (((INTERVENANT_EXT_CONTACT INNER JOIN (OPERATION INNER JOIN INTERVENANT_EXT_CONTACT_OPE ON (OPERATION.NUM_INTERV_PROMOTEUR = INTERVENANT_EXT_CONTACT_OPE.NUM_INTERVENANT_EXT) AND" & _
"(OPERATION.NUM_OPERATION = INTERVENANT_EXT_CONTACT_OPE.NUM_OPERATION)) ON (INTERVENANT_EXT_CONTACT.NUM_INTERVENANT_EXT = INTERVENANT_EXT_CONTACT_OPE.NUM_INTERVENANT_EXT) AND (INTERVENANT_EXT_CONTACT.NUM_INTERVENANT_CONTACT = INTERVENANT_EXT_CONTACT_OPE.NUM_INTERVENANT_CONTACT)) INNER JOIN (ENTREPRISE_CONTACT INNER JOIN ENTREPRISE_CONTACT_OPE ON (ENTREPRISE_CONTACT.NUM_ENTREPRISE = ENTREPRISE_CONTACT_OPE.NUM_ENTREPRISE) AND (ENTREPRISE_CONTACT.NUM_ENTRE_CONTACT = ENTREPRISE_CONTACT_OPE.NUM_ENTRE_CONTACT)) ON OPERATION.NUM_OPERATION = ENTREPRISE_CONTACT_OPE.NUM_OPERATION) INNER JOIN ((CORPS_ETATS INNER JOIN ESTIM_OPE ON CORPS_ETATS.NUM_CORPS_ETATS = ESTIM_OPE.NUM_CORPS_ETATS) INNER JOIN (ENTREPRISE INNER JOIN ENTREPRISE_SOLLICITE ON ENTREPRISE.NUM_ENTREPRISE = ENTREPRISE_SOLLICITE.NUM_ENTREPRISE)" & _
" ON (ESTIM_OPE.NUM_OPERATION = ENTREPRISE_SOLLICITE.NUM_OPERATION) AND (ESTIM_OPE.NUM_CORPS_ETATS = ENTREPRISE_SOLLICITE.NUM_CORPS_ETATS)) ON OPERATION.NUM_OPERATION = ESTIM_OPE.NUM_OPERATION) ON SOCIETE.NUM_SOCIETE_3D = OPERATION.NUM_SOCIETE_3D WHERE (((ENTREPRISE_SOLLICITE.NUM_CORPS_ETATS)=[Forms]![Fiche Marché]![NUM_CORPS_ETATS]) AND ((ENTREPRISE_SOLLICITE.NUM_ENTREPRISE)=[Forms]![Fiche Marché]![NUM_ENTREPRISE]) AND ((ENTREPRISE_SOLLICITE.NUM_AO)=[Forms]![Fiche Marché]![NUM_AO]) AND ((ENTREPRISE_SOLLICITE.NUM_OPERATION)=[Forms]![Fiche Marché]![NUM_OPERATION]) AND ((ENTREPRISE_SOLLICITE.DEVIS_RETENUE)=Yes) AND ((ENTREPRISE_CONTACT.SIGNATURE_MARCHER)=Yes) AND ((INTERVENANT_EXT_CONTACT.SIGNATURE_MARCHERMO)=Yes));"
 
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
blnRequeteVide = False
If rs.EOF Then blnRequeteVide = True
rs.Close
 
If Not blnRequeteVide Then
 
 DoCmd.SetWarnings False
 
 DoCmd.RunSQL "DELETE entreprise.* FROM entreprise IN 'C:\access_pc3d\publipostage.mdb';"
DoCmd.RunSQL "INSERT INTO entreprise ( NUM_CORPS_ETATS, NUM_ENTREPRISE, NUM_AO, NUM_OPERATION, NOM_OPERATION, ADRESSE_OPERATION, ADRESSEBIS_OPERATION, VILLE_OPERATION, CP_OPERATION, PROGRAMME, POUC_TVA, VILLE3D, NUM_LOT, DEVIS_RETENUE, NOM_ENTREPRISE, ADRESSE_ENTREPRISE, ADRESSEBIS_ENTREPRISE, VILLE_ENTREPRISE, CP_ENTREPRISE, RCS, RCS_VILLE, NOM_ENTRE_CONTACT, NOM_CORPS_ETATS, MONTANT_MARCHE, MONTANT_TVA, MONTANT_MARCHETTC ) IN 'C:\access_pc3d\publipostage.mdb' SELECT ENTREPRISE_SOLLICITE.NUM_CORPS_ETATS, ENTREPRISE_SOLLICITE.NUM_ENTREPRISE, ENTREPRISE_SOLLICITE.NUM_AO, ENTREPRISE_SOLLICITE.NUM_OPERATION, OPERATION.NOM_OPERATION, OPERATION.ADRESSE_OPERATION, OPERATION.ADRESSEBIS_OPERATION, OPERATION.VILLE_OPERATION, OPERATION.CP_OPERATION, OPERATION.PROGRAMME, [POUC_TVA]*100 AS POUC_TVA1, SOCIETE.VILLE3D, ESTIM_OPE.NUM_LOT, ENTREPRISE_SOLLICITE.DEVIS_RETENUE, ENTREPRISE.NOM_ENTREPRISE, ENTREPRISE.ADRESSE_ENTREPRISE," & _
" ENTREPRISE.ADRESSEBIS_ENTREPRISE, ENTREPRISE.VILLE_ENTREPRISE, ENTREPRISE.CP_ENTREPRISE, ENTREPRISE.RCS, ENTREPRISE.RCS_VILLE, ENTREPRISE_CONTACT.NOM_ENTRE_CONTACT, CORPS_ETATS.NOM_CORPS_ETATS, ENTREPRISE_SOLLICITE.MONTANT_MARCHE, [MONTANT_MARCHE]*[POUC_TVA] AS MONTANT_TVA, [MONTANT_MARCHE]+[MONTANT_TVA] AS MONTANT_MARCHETTC" & _
" FROM SOCIETE INNER JOIN (((INTERVENANT_EXT_CONTACT INNER JOIN (OPERATION INNER JOIN INTERVENANT_EXT_CONTACT_OPE ON (OPERATION.NUM_OPERATION = INTERVENANT_EXT_CONTACT_OPE.NUM_OPERATION) AND (OPERATION.NUM_INTERV_PROMOTEUR = INTERVENANT_EXT_CONTACT_OPE.NUM_INTERVENANT_EXT)) ON (INTERVENANT_EXT_CONTACT.NUM_INTERVENANT_CONTACT = INTERVENANT_EXT_CONTACT_OPE.NUM_INTERVENANT_CONTACT) AND (INTERVENANT_EXT_CONTACT.NUM_INTERVENANT_EXT = INTERVENANT_EXT_CONTACT_OPE.NUM_INTERVENANT_EXT)) INNER JOIN (ENTREPRISE_CONTACT INNER JOIN ENTREPRISE_CONTACT_OPE ON (ENTREPRISE_CONTACT.NUM_ENTRE_CONTACT = ENTREPRISE_CONTACT_OPE.NUM_ENTRE_CONTACT) AND (ENTREPRISE_CONTACT.NUM_ENTREPRISE = ENTREPRISE_CONTACT_OPE.NUM_ENTREPRISE)) ON OPERATION.NUM_OPERATION = ENTREPRISE_CONTACT_OPE.NUM_OPERATION) INNER JOIN ((CORPS_ETATS INNER JOIN ESTIM_OPE ON CORPS_ETATS.NUM_CORPS_ETATS = ESTIM_OPE.NUM_CORPS_ETATS) INNER" & _
" JOIN (ENTREPRISE INNER JOIN ENTREPRISE_SOLLICITE ON ENTREPRISE.NUM_ENTREPRISE = ENTREPRISE_SOLLICITE.NUM_ENTREPRISE) ON (ESTIM_OPE.NUM_CORPS_ETATS = ENTREPRISE_SOLLICITE.NUM_CORPS_ETATS) AND (ESTIM_OPE.NUM_OPERATION = ENTREPRISE_SOLLICITE.NUM_OPERATION)) ON OPERATION.NUM_OPERATION = ESTIM_OPE.NUM_OPERATION) ON SOCIETE.NUM_SOCIETE_3D = OPERATION.NUM_SOCIETE_3D" & _
" WHERE (((ENTREPRISE_SOLLICITE.NUM_CORPS_ETATS)=[Forms]![Fiche Marché]![NUM_CORPS_ETATS]) AND ((ENTREPRISE_SOLLICITE.NUM_ENTREPRISE)=[Forms]![Fiche Marché]![NUM_ENTREPRISE]) AND ((ENTREPRISE_SOLLICITE.NUM_AO)=[Forms]![Fiche Marché]![NUM_AO]) AND ((ENTREPRISE_SOLLICITE.NUM_OPERATION)=[Forms]![Fiche Marché]![NUM_OPERATION]) AND ((ENTREPRISE_SOLLICITE.DEVIS_RETENUE)=Yes) AND ((ENTREPRISE_CONTACT.SIGNATURE_MARCHER)=Yes) AND ((INTERVENANT_EXT_CONTACT.SIGNATURE_MARCHERMO)=Yes));"
DoCmd.SetWarnings True
' Chemin d'accès au document Word de publipostage
Const DOC_WORD = "C:\access_pc3d\Acte d'engagement.doc"
' Variable pour gérer l'objet Word
Dim wdApp As Object    'ici
' Passer à la partie Publipostage
Set wdApp = CreateObject("Word.Application")   'ici
With wdApp
    ' Word est visible pendant les tests
    .Visible = True
    ' Ouvrir la lettre type
    .Documents.Open DOC_WORD
    ' Lier la lettre type à la source de données Access
    .ActiveDocument.MailMerge.OpenDataSource _
        Name:="C:\access_pc3d\publipostage.mdb", _
        SQLStatement:="SELECT * FROM [entreprise]"
     ' La fusion doit se faire dans un nouveau document
    .ActiveDocument.MailMerge.destination = wdSendToNewDocument
    ' Exécuter la fusion
    .ActiveDocument.MailMerge.Execute
    ' Redonner le focus à la lettre type et fermer sans enregistrer
    .Documents.Open DOC_WORD
    .ActiveDocument.Close wdDoNotSaveChanges
    End With
Set wdApp = Nothing
Exit Sub
 Else
   MsgBox "Merci de renseigné pour l'entreprise ou le promoteur une personne signature de AO"
End If

et Je te confirme bien que ma requete n'existe pas réellement que en vba

Citation:

LEDZEPPII j'ai essai ton code mais sa ne marche pas j'ai copier mon code desous

C'est un peu vague comme description ...

Est-ce que tu as testé isolément cette partie dans une Sub ?
Exemple

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Sub TestQry
Dim rs As DAO.Recordset
Dim strSQL As String, blnRequeteVide As Boolean
 
strSQL = "SELECT ENTREPRISE_SOLLICITE.NUM_CORPS_ETATS, ENTREPRISE_SOLLICITE.NUM_ENTREPRISE, ENTREPRISE_SOLLICITE.NUM_AO, ENTREPRISE_SOLLICITE.NUM_OPERATION, OPERATION.NOM_OPERATION, OPERATION.ADRESSE_OPERATION, OPERATION.ADRESSEBIS_OPERATION, OPERATION.VILLE_OPERATION, OPERATION.CP_OPERATION, OPERATION.PROGRAMME, [POUC_TVA]*100 AS POUC_TVA1, SOCIETE.VILLE3D, ESTIM_OPE.NUM_LOT, ENTREPRISE_SOLLICITE.DEVIS_RETENUE, ENTREPRISE.NOM_ENTREPRISE, ENTREPRISE.ADRESSE_ENTREPRISE, ENTREPRISE.ADRESSEBIS_ENTREPRISE, ENTREPRISE.VILLE_ENTREPRISE, ENTREPRISE.CP_ENTREPRISE, ENTREPRISE.RCS, ENTREPRISE.RCS_VILLE, ENTREPRISE_CONTACT.NOM_ENTRE_CONTACT, CORPS_ETATS.NOM_CORPS_ETATS, ENTREPRISE_SOLLICITE.MONTANT_MARCHE, [MONTANT_MARCHE]*[POUC_TVA] AS MONTANT_TVA, [MONTANT_MARCHE]+[MONTANT_TVA] AS MONTANT_MARCHETTC" & _
" FROM SOCIETE INNER JOIN (((INTERVENANT_EXT_CONTACT INNER JOIN (OPERATION INNER JOIN INTERVENANT_EXT_CONTACT_OPE ON (OPERATION.NUM_INTERV_PROMOTEUR = INTERVENANT_EXT_CONTACT_OPE.NUM_INTERVENANT_EXT) AND" & _
"(OPERATION.NUM_OPERATION = INTERVENANT_EXT_CONTACT_OPE.NUM_OPERATION)) ON (INTERVENANT_EXT_CONTACT.NUM_INTERVENANT_EXT = INTERVENANT_EXT_CONTACT_OPE.NUM_INTERVENANT_EXT) AND (INTERVENANT_EXT_CONTACT.NUM_INTERVENANT_CONTACT = INTERVENANT_EXT_CONTACT_OPE.NUM_INTERVENANT_CONTACT)) INNER JOIN (ENTREPRISE_CONTACT INNER JOIN ENTREPRISE_CONTACT_OPE ON (ENTREPRISE_CONTACT.NUM_ENTREPRISE = ENTREPRISE_CONTACT_OPE.NUM_ENTREPRISE) AND (ENTREPRISE_CONTACT.NUM_ENTRE_CONTACT = ENTREPRISE_CONTACT_OPE.NUM_ENTRE_CONTACT)) ON OPERATION.NUM_OPERATION = ENTREPRISE_CONTACT_OPE.NUM_OPERATION) INNER JOIN ((CORPS_ETATS INNER JOIN ESTIM_OPE ON CORPS_ETATS.NUM_CORPS_ETATS = ESTIM_OPE.NUM_CORPS_ETATS) INNER JOIN (ENTREPRISE INNER JOIN ENTREPRISE_SOLLICITE ON ENTREPRISE.NUM_ENTREPRISE = ENTREPRISE_SOLLICITE.NUM_ENTREPRISE)" & _
" ON (ESTIM_OPE.NUM_OPERATION = ENTREPRISE_SOLLICITE.NUM_OPERATION) AND (ESTIM_OPE.NUM_CORPS_ETATS = ENTREPRISE_SOLLICITE.NUM_CORPS_ETATS)) ON OPERATION.NUM_OPERATION = ESTIM_OPE.NUM_OPERATION) ON SOCIETE.NUM_SOCIETE_3D = OPERATION.NUM_SOCIETE_3D WHERE (((ENTREPRISE_SOLLICITE.NUM_CORPS_ETATS)=[Forms]![Fiche Marché]![NUM_CORPS_ETATS]) AND ((ENTREPRISE_SOLLICITE.NUM_ENTREPRISE)=[Forms]![Fiche Marché]![NUM_ENTREPRISE]) AND ((ENTREPRISE_SOLLICITE.NUM_AO)=[Forms]![Fiche Marché]![NUM_AO]) AND ((ENTREPRISE_SOLLICITE.NUM_OPERATION)=[Forms]![Fiche Marché]![NUM_OPERATION]) AND ((ENTREPRISE_SOLLICITE.DEVIS_RETENUE)=Yes) AND ((ENTREPRISE_CONTACT.SIGNATURE_MARCHER)=Yes) AND ((INTERVENANT_EXT_CONTACT.SIGNATURE_MARCHERMO)=Yes));"
 
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
blnRequeteVide = False
If rs.EOF Then blnRequeteVide = True
rs.Close
 
MsgBox blnRequeteVide, , "blnRequeteVide"
End Sub

01/08/2008, 16h23
popofpopof

alors je vien de le teste comme tu vien de me dire et il erreur 3061
trop peu de parametre 4 attendu

et il me souligne en jaune

Code:

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

Ça ne te rappelle rien ?

1: [Forms]![Fiche Marché]![NUM_CORPS_ETATS]
2: [Forms]![Fiche Marché]![NUM_ENTREPRISE]
3: [Forms]![Fiche Marché]![NUM_AO]
et 4 : [Forms]![Fiche Marché]![NUM_OPERATION]

il faut opérer les quatre substitutions :

Code:

1
2
3
4
5
6
7
8
9
strSQL = "SELECT ENTREPRISE_SOLLICITE.NUM_CORPS_ETATS, ENTREPRISE_SOLLICITE.NUM_ENTREPRISE, ENTREPRISE_SOLLICITE.NUM_AO, ENTREPRISE_SOLLICITE.NUM_OPERATION, OPERATION.NOM_OPERATION, OPERATION.ADRESSE_OPERATION, OPERATION.ADRESSEBIS_OPERATION, OPERATION.VILLE_OPERATION, OPERATION.CP_OPERATION, OPERATION.PROGRAMME, [POUC_TVA]*100 AS POUC_TVA1, SOCIETE.VILLE3D, ESTIM_OPE.NUM_LOT, ENTREPRISE_SOLLICITE.DEVIS_RETENUE, ENTREPRISE.NOM_ENTREPRISE, ENTREPRISE.ADRESSE_ENTREPRISE, ENTREPRISE.ADRESSEBIS_ENTREPRISE, ENTREPRISE.VILLE_ENTREPRISE, ENTREPRISE.CP_ENTREPRISE, ENTREPRISE.RCS, ENTREPRISE.RCS_VILLE, ENTREPRISE_CONTACT.NOM_ENTRE_CONTACT, CORPS_ETATS.NOM_CORPS_ETATS, ENTREPRISE_SOLLICITE.MONTANT_MARCHE, [MONTANT_MARCHE]*[POUC_TVA] AS MONTANT_TVA, [MONTANT_MARCHE]+[MONTANT_TVA] AS MONTANT_MARCHETTC" & _
" FROM SOCIETE INNER JOIN (((INTERVENANT_EXT_CONTACT INNER JOIN (OPERATION INNER JOIN INTERVENANT_EXT_CONTACT_OPE ON (OPERATION.NUM_INTERV_PROMOTEUR = INTERVENANT_EXT_CONTACT_OPE.NUM_INTERVENANT_EXT) AND " & _
"(OPERATION.NUM_OPERATION = INTERVENANT_EXT_CONTACT_OPE.NUM_OPERATION)) ON (INTERVENANT_EXT_CONTACT.NUM_INTERVENANT_EXT = INTERVENANT_EXT_CONTACT_OPE.NUM_INTERVENANT_EXT) AND (INTERVENANT_EXT_CONTACT.NUM_INTERVENANT_CONTACT = INTERVENANT_EXT_CONTACT_OPE.NUM_INTERVENANT_CONTACT)) INNER JOIN (ENTREPRISE_CONTACT INNER JOIN ENTREPRISE_CONTACT_OPE ON (ENTREPRISE_CONTACT.NUM_ENTREPRISE = ENTREPRISE_CONTACT_OPE.NUM_ENTREPRISE) AND (ENTREPRISE_CONTACT.NUM_ENTRE_CONTACT = ENTREPRISE_CONTACT_OPE.NUM_ENTRE_CONTACT)) ON OPERATION.NUM_OPERATION = ENTREPRISE_CONTACT_OPE.NUM_OPERATION) INNER JOIN ((CORPS_ETATS INNER JOIN ESTIM_OPE ON CORPS_ETATS.NUM_CORPS_ETATS = ESTIM_OPE.NUM_CORPS_ETATS) INNER JOIN (ENTREPRISE INNER JOIN ENTREPRISE_SOLLICITE ON ENTREPRISE.NUM_ENTREPRISE = ENTREPRISE_SOLLICITE.NUM_ENTREPRISE)" & _
" ON (ESTIM_OPE.NUM_OPERATION = ENTREPRISE_SOLLICITE.NUM_OPERATION) AND (ESTIM_OPE.NUM_CORPS_ETATS = ENTREPRISE_SOLLICITE.NUM_CORPS_ETATS)) ON OPERATION.NUM_OPERATION = ESTIM_OPE.NUM_OPERATION) ON SOCIETE.NUM_SOCIETE_3D = OPERATION.NUM_SOCIETE_3D " & _
"WHERE (((ENTREPRISE_SOLLICITE.NUM_CORPS_ETATS)=" & [Forms]![Fiche Marché]![NUM_CORPS_ETATS] & ")" & _
" AND ((ENTREPRISE_SOLLICITE.NUM_ENTREPRISE)=" & [Forms]![Fiche Marché]![NUM_ENTREPRISE] & ")" & _
" AND ((ENTREPRISE_SOLLICITE.NUM_AO)=" & [Forms]![Fiche Marché]![NUM_AO] & ")" & _
" AND ((ENTREPRISE_SOLLICITE.NUM_OPERATION)=" & [Forms]![Fiche Marché]![NUM_OPERATION] & ")" & _
" AND ((ENTREPRISE_SOLLICITE.DEVIS_RETENUE)=Yes) AND ((ENTREPRISE_CONTACT.SIGNATURE_MARCHER)=Yes) AND ((INTERVENANT_EXT_CONTACT.SIGNATURE_MARCHERMO)=Yes));"

J'ai supposé que les quatre champs étaient numériques de type Entier.

A+

01/08/2008, 21h51
popofpopof

merci bcp sa marche super bien comme je le voulai :D