Bug with and "INSERT INTO" +"no match" query sent from a VBA procedure (1 Viewer)

Etxezarreta

Member
Local time
Today, 22:49
Joined
Apr 13, 2020
Messages
175
Hello,
I am stuck with a bad result of a query sent form a VBA procedure:
here is the file: https://we.tl/t-MmO8fV1Stq
it is in the "DSP_RDV_f_ConsultGestion_HistorRdv" form : you need to select items in the 5 list boxes, then click in "Jamais visités" and send the procedure by clicking on "lancer la recherche par critères": I do not understand at all what is going on there.
It is meant to select fields from the Contacts_t_ListeContacts table according the values of the list boxes, then remove the records that have the "ID_contact" field value that matches with the field "FK_contact" in the table " DSP_RDV_CR_t_CompteRendus". and to populate a table that feeds the subform.
I have been searching for ages, thanks for your help.

Etxe.
 

isladogs

MVP / VIP
Local time
Today, 21:49
Joined
Jan 14, 2017
Messages
18,186
Many forum members aren't willing to download files from an external site.
Please upload your file here instead.
 

Etxezarreta

Member
Local time
Today, 22:49
Joined
Apr 13, 2020
Messages
175
Ok, here is the file
 

Attachments

  • Essai.zip
    2 MB · Views: 138

Etxezarreta

Member
Local time
Today, 22:49
Joined
Apr 13, 2020
Messages
175
Hello arnelgep,
Thank you very much. Very interesting how you create the strAllCriteria, will work this way from now on.
It works fine for the insert into part, but I think I forgot one part: I would like to remove the rows in which the ID_contact value equals the DSP_RDV_t_CompteRenduRdv.FK_contact value: I need to add a left join like Contacts_t_ListeContacts LEFT JOIN t_DSP_CompteRendu_RDV ON Contacts_t_ListeContacts.[ID_Contact] = t_DSP_CompteRendu_RDV.[FK_contact])
and AND (DSP_RDV_t_CompteRenduRdv.FK_contact) Is Null)"
But I havent managed so far.
Could you help again?
Etxe.
 

Etxezarreta

Member
Local time
Today, 22:49
Joined
Apr 13, 2020
Messages
175
Ok, I just found the code:
str_SQL_finale = "INSERT INTO DSP_RDV_t_ListeContactsJamaisVisites_Temp ( ID_Contact, " & _
"Nom_contact, Prenom_contact, Nom_structure, Fonction, Specialite, Profession, Departement, " & _
"Secteur, Case_du_plan, Adresse_1, Ville, Tel1, Courriel ) " & _
"SELECT Contacts_t_ListeContacts.ID_Contact, NZ(Contacts_t_ListeContacts.Nom_contact,''), " & _
"NZ(Contacts_t_ListeContacts.Prenom_contact,''), NZ(Contacts_t_ListeContacts.Nom_structure,''), " & _
"NZ(Contacts_t_ListeContacts.Fonction,''), NZ(Contacts_t_ListeContacts.Specialite,''), " & _
"NZ(Contacts_t_ListeContacts.Profession,''), NZ(Contacts_t_ListeContacts.Departement,''), " & _
"NZ(Contacts_t_ListeContacts.Secteur,''), NZ(Contacts_t_ListeContacts.Case_du_plan,''), " & _
"NZ(Contacts_t_ListeContacts.Adresse_1,''), NZ(Contacts_t_ListeContacts.Ville,''), " & _
"NZ(Contacts_t_ListeContacts.Tel1,''), NZ(Contacts_t_ListeContacts.Courriel,'') FROM Contacts_t_ListeContacts " & _
"LEFT JOIN DSP_RDV_CR_t_CompteRendus ON Contacts_t_ListeContacts.[ID_Contact] = DSP_RDV_CR_t_CompteRendus.[FK_contact]" & _
"WHERE " & strAllCriteria & "" & _
" AND DSP_RDV_CR_t_CompteRendus.FK_contact Is Null;"
Many thanks again arnelgp, I learnt a lot with few code rows.
Etxe.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:49
Joined
May 7, 2009
Messages
19,169
oh i see.
can you just delete it after Adding the records to DSP_RDV_t_ListeContactsJamaisVisites_Temp:
Code:
...
...
'add to table
objConnection.Execute (str_SQL_finale)

'create new query to delete
str_SQL_finale = "Delete * From DSP_RDV_t_ListeContactsJamaisVisites_Temp " & _
        "Where ID_Contact In (SELECT FK_Contact FROM DSP_RDV_t_CompteRenduRdv);"

'delete it/them
objConnection.Execute (str_SQL_finale)

i browse the db and i cannot find DSP_RDV_t_CompteRenduRdv
only DSP_RDV_t_CompteRenduRdv_Temp is available.


EDIT: you need to always use Nz(), because you're fields are Text and
therefore will result an error if you assign Null to it.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 08:49
Joined
Jan 20, 2009
Messages
12,849
you need to always use Nz(), because you're fields are Text and therefore will result an error if you assign Null to it.

Not so. A text field in a table can be assigned Null, provided its Required Property is not Yes or some other constraint prevents it.

Perhaps you are confusing with a string variable in VBA which cannot be assigned Null.
(In VBA only a Variant can be Null).
 

Etxezarreta

Member
Local time
Today, 22:49
Joined
Apr 13, 2020
Messages
175
Perfect. A question about methodology: when you have to create a multiple step query, like insert into+no-match one, do you always split the code like you did in my example, or do you try a one step sql code?
I guess it depends on the complexity, but I would be interested to know about your general approach.
Thanks for your answer.
Etxe.
 

Users who are viewing this thread

Top Bottom