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
| Public Sub ModificationReq(ReqModele As String, CritereAModife As String, ResultatDocAchat As String, PlagePeriode1 As Date, PlagePeriode2 As Date)
Dim Db As DAO.Database
Dim QryModele As DAO.QueryDef
Dim strSQLModele As String
'Nom de la requete finale
Dim NomReqFinal As String
'MsgBox "NomReqFinal =#" & NomReqFinal & "# Avant modif 1"
NomReqFinal = Replace(ReqModele, "Modele", "Dynamique")
'MsgBox "NomReqFinal =#" & NomReqFinal & " après modif 1"
NomReqFinal = Replace(NomReqFinal, "Modèle", "Dynamique")
'MsgBox "NomReqFinal =#" & NomReqFinal & " est la nouvelle requete modifié de :" & ReqModele
'Initialisation des variables
Set Db = CurrentDb
Set QryModele = Db.QueryDefs(ReqModele)
strSQLModele = QryModele.SQL
'MsgBox PlagePeriode1 & " " & PlagePeriode2
'Modification de la requete source
strSQLModele = Replace(strSQLModele, CritereAModife, ResultatDocAchat)
strSQLModele = Replace(strSQLModele, "[#Periodevente1#]", "#" & PlagePeriode1 & "#")
strSQLModele = Replace(strSQLModele, "[#Periodevente2#]", "#" & PlagePeriode2 & "#")
MsgBox "1 " & strSQLModele
If TesteExistenceReqAchat(NomReqFinal) Then
'alors modifier le code de la requête
Db.QueryDefs(NomReqFinal).SQL = strSQLModele
'contenue de ma requete sql affiché dans la msgbox
MsgBox Db.QueryDefs(NomReqFinal).SQL
'MsgBox ("Requete modifie" & " NomReqFinal:" & NomReqFinal & " Existe")
'Sinon
Else
'Créer la nouvelle requête
Db.CreateQueryDef NomReqFinal, strSQLModele
MsgBox Db.QueryDefs(NomReqFinal).SQL
'MsgBox ("Requete crée") & " NomReqFinal:" & NomReqFinal & " n'existe pas"
End If
Set Db = Nothing
Set QryModele = Nothing
End Sub |
Partager