Solved Subform based on a querydefs would't refresh

[Etxezarreta]

Hello everyone,
I have a form called "sf_re_ResultatRecherche" based on a querydefs called "re_ResultatRecherche".
This form is included in a form called "DSP_RDV_PREPA_f_CreationListeAppels": the name of the subform in the form is sff_re_ResultatRecherche.
When the VBA code runs, the querydefs is updated, and so is the form sf_re_ResultatRecherche.
But the subform sff_re_ResultatRecherche wouldn't refresh in the form:
here is my code:

            str_SQL_finale = "SELECT ID_Contact, " & _
             "Nom_contact, Prenom_contact, Nom_structure, Fonction, FK_Specialite, FK_Profession, Departement, " & _
             "Secteur, Case_du_plan, Adresse_1, Ville, Secretariat_telephonique, Tel1, Courriel, Integrer_liste_appels " & _
            "FROM Contacts_t_ListeContacts " & _
            "WHERE " & strAllCriteria & " ORDER BY Nom_contact ASC ;"
            
            Debug.Print (str_SQL_finale)
            
            CurrentDb.QueryDefs("re_ResultatRecherche").SQL = str_SQL_finale
            
            'Forms!sf_re_ResultatRecherche.Requery
            Forms!DSP_RDV_PREPA_f_CreationListeAppels!sff_re_ResultatRecherche.Form.Requery
            Forms!DSP_RDV_PREPA_f_CreationListeAppels!sff_re_ResultatRecherche.Form.Refresh
            'Me.sff_re_ResultatRecherche.Form.Requery
            Me.Requery
            'Me.sff_re_ResultatRecherche.Form.Refresh
            Me.Refresh
            'Me.sff_re_ResultatRecherche.Form.Repaint
            Me.Repaint
'21/06: Ezinbertzekoa
    DoCmd.Hourglass False
    Application.Echo True
    
objConnection.Close
Set objConnection = Nothing

Exit Sub

GestionnaireErreur:
    MsgBox "Une erreur s'est produite: " & Err.Number & "-" & Err.Description, vbOKOnly + vbInformation
Exit Sub

EnregistrementVide:
MsgBox "Pas d'enregistrement correspondantà votre demande", vbOKOnly + vbInformation

End Sub

Many thanks for your suggestions.
Etxe

 

[theDBguy]

Maybe try just assigning the new SQL in the Record Source of the subform.

 

[Etxezarreta]

Maybe try just assigning the new SQL in the Record Source of the subform.

Hi,
I just tried: it seems to clear the subform, even though the querydefs shows the correct data, and so does the original form that became my subform

 

[theDBguy]

Hi,
I just tried: it seems to clear the subform, even though the querydefs shows the correct data, and so does the original form that became my subform

My guess would be it has to do with the linked fields. Try removing them for now just to troubleshoot.

 

[Etxezarreta]

I had removed them already, the data dont appear (still appeal correctly in the query and the form used for the subform)

 

[Etxezarreta]

May I send you a sample database?

 

[theDBguy]

May I send you a sample database?

Are you able to post it here?

 

[Etxezarreta]

Hi

Are you able to post it here?

I just changed the whole system, it was not necessary to have a subform, it works fine now.
Many thanks.
Etxe.

 

[theDBguy]

Hi

I just changed the whole system, it was not necessary to have a subform, it works fine now.
Many thanks.
Etxe.

Excellent! Good luck with your project.