Etxezarreta
Member
- Local time
- Today, 23:31
- Joined
- Apr 13, 2020
- Messages
- 175
Hello,
I need to populate the table "PROJETS_t_AffectationProjetA_Contacts_temp" from the table "Contacts_t_ListeContacts" , in two steps:
- firstly, I use the values of listboxes from the form "PROJETS_f_AffectationContacts _Projets" to select only the rows that respect those criteria
- then I need to select rows on other two "unmatch" criteria: the field "ID_contacts" from the table "Contacts_t_ListeContacts" must not be present in the field "FK_ID_contact" from the table "PROJETS_f_AffectationContacts _Projet" and the field "FK_ID_projet" value in the table "PROJETS_f_AffectationContacts _Projet" must be different from the value of the combobox "combo_ListeProjetsAaffecterAuxContacts" in the form "PROJETS_f_AffectationContacts _Projets".
I tried to combine the INSERT INTO, the unmatched query in one signle string, but I haven't managed so far.
You will find the code in the module form_PROJETS_f_AffectationContacts_Projets.
You will find below the problematic part.
str_NomDuProjet = Me.combo_ListeProjetsAaffecterAuxContacts.Column(1)
str_SQL_finale = "" 'DBG str_SQl
str_SQL_finale = "INSERT INTO PROJETS_t_AffectationProjetA_Contacts_temp(ID_contact)" & _
" SELECT Contacts_t_ListeContacts.ID_contact" & _
" FROM Contacts_t_ListeContacts LEFT JOIN PROJETS_t_AffectationProjetA_Contacts" & _
" ON Contacts_t_ListeContacts.ID_contact = PROJETS_t_AffectationProjetA_Contacts.FK_ID_contact" & _
" WHERE Contacts_t_ListeContacts.Departement In (" & str_CritereDepartement & ")" & _
" AND Contacts_t_ListeContacts.Ville In (" & str_CritereVille & ")" & _
" AND Contacts_t_IntitulesProfession.ID_Profession IN (" & str_CritereProfession & ") " & _
" AND Contacts_t_StatutJuridiqueSoignants.ID_StatutJuridiqueSoignant IN (" & str_CritereStatutJuridique & ")" & _
" AND Contacts_t_Specialite.ID_Specialite IN (" & str_CritereSpecialite & ")" & _
" AND PROJETS_t_AffectationProjetA_Contacts.FK_ID_contact Is Null" & _
" AND PROJETS_t_AffectationProjetA_Contacts <> " & str_Projet & ""
'Here: I would like to add one thing to this string: keep only the Contacts_t_ListeContacts.ID_contact values that are both not contained in the table PROJETS_t_AffectationProjetA_Contacts and for which the
'FK_ID_projet value is different from the variable int_Projet
Debug.Print str_SQL_finale
'On vide la table intermediaire
str_Sql = "DELETE FROM PROJETS_t_AffectationProjetA_Contacts_temp"
objConnection.Execute (str_Sql)
Many thanks in advance!
Etxe.
I need to populate the table "PROJETS_t_AffectationProjetA_Contacts_temp" from the table "Contacts_t_ListeContacts" , in two steps:
- firstly, I use the values of listboxes from the form "PROJETS_f_AffectationContacts _Projets" to select only the rows that respect those criteria
- then I need to select rows on other two "unmatch" criteria: the field "ID_contacts" from the table "Contacts_t_ListeContacts" must not be present in the field "FK_ID_contact" from the table "PROJETS_f_AffectationContacts _Projet" and the field "FK_ID_projet" value in the table "PROJETS_f_AffectationContacts _Projet" must be different from the value of the combobox "combo_ListeProjetsAaffecterAuxContacts" in the form "PROJETS_f_AffectationContacts _Projets".
I tried to combine the INSERT INTO, the unmatched query in one signle string, but I haven't managed so far.
You will find the code in the module form_PROJETS_f_AffectationContacts_Projets.
You will find below the problematic part.
str_NomDuProjet = Me.combo_ListeProjetsAaffecterAuxContacts.Column(1)
str_SQL_finale = "" 'DBG str_SQl
str_SQL_finale = "INSERT INTO PROJETS_t_AffectationProjetA_Contacts_temp(ID_contact)" & _
" SELECT Contacts_t_ListeContacts.ID_contact" & _
" FROM Contacts_t_ListeContacts LEFT JOIN PROJETS_t_AffectationProjetA_Contacts" & _
" ON Contacts_t_ListeContacts.ID_contact = PROJETS_t_AffectationProjetA_Contacts.FK_ID_contact" & _
" WHERE Contacts_t_ListeContacts.Departement In (" & str_CritereDepartement & ")" & _
" AND Contacts_t_ListeContacts.Ville In (" & str_CritereVille & ")" & _
" AND Contacts_t_IntitulesProfession.ID_Profession IN (" & str_CritereProfession & ") " & _
" AND Contacts_t_StatutJuridiqueSoignants.ID_StatutJuridiqueSoignant IN (" & str_CritereStatutJuridique & ")" & _
" AND Contacts_t_Specialite.ID_Specialite IN (" & str_CritereSpecialite & ")" & _
" AND PROJETS_t_AffectationProjetA_Contacts.FK_ID_contact Is Null" & _
" AND PROJETS_t_AffectationProjetA_Contacts <> " & str_Projet & ""
'Here: I would like to add one thing to this string: keep only the Contacts_t_ListeContacts.ID_contact values that are both not contained in the table PROJETS_t_AffectationProjetA_Contacts and for which the
'FK_ID_projet value is different from the variable int_Projet
Debug.Print str_SQL_finale
'On vide la table intermediaire
str_Sql = "DELETE FROM PROJETS_t_AffectationProjetA_Contacts_temp"
objConnection.Execute (str_Sql)
Many thanks in advance!
Etxe.