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
|
Private Sub cmd_recherche_Click()
Dim strTable As String, strField As String, strCriteria As String, strSql As String
Dim Criter As Variant
If Me.listbox_search = "Numéro de contrat" Then
Debug.Print Me.listbox_search
strTable = "AFN_LOT0"
strField = "NUMERO"
strCriteria = strTable & "." & strField & " Like """ & Me.txt_critere & """"
' construction de la requête sql
strSql = "SELECT DISTINCTROW " & strTable & "." & strField & "," & strTable & ".FORMULE," & strTable & ".DATE_EFFET," & strTable & ".DATE_ECHEANCE," & strTable & ".ETAT," & strTable & ".CODE_RESILIATION," & strTable & ".DATE_RESIL," & strTable & ".DATE_OPERATION"
strSql = strSql & " FROM " & strTable
strSql = strSql & " WHERE " & strCriteria & ";"
Me.lst_resultat.RowSource = strSql
Me.lst_resultat.Requery
ElseIf Me.listbox_search = "Nom Prénom souscripteur" Then
Debug.Print Me.listbox_search
strSql = " SELECT DISTINCTROW AFN_LOT0.ID_SOR, TITRE_LOT0.LIBELLE_TITRE, PERSONNE_LOT0.NOM, PERSONNE_LOT0.PRENOM, AFN_LOT0.NUMERO, ENTITE_PRODUIT_LOT0.CODE_PRODUIT, AFN_LOT0.FORMULE, AFN_LOT0.DATE_EFFET, AFN_LOT0.DATE_ECHEANCE, AFN_LOT0.ETAT, ETAT_POLICE_LOT0.LIBELLE_ETAT, AFN_LOT0.CODE_RESILIATION, MOTIF_RESIL_LOT0.RESILIATION_LIB, AFN_LOT0.DATE_RESIL, AFN_LOT0.DATE_OPERATION"
strSql = strSql & " FROM ENTITE_PRODUIT_LOT0"
strSql = strSql & " INNER JOIN (TITRE_LOT0 INNER JOIN (PERSONNE_LOT0 INNER JOIN (MOTIF_RESIL_LOT0 INNER JOIN (ETAT_POLICE_LOT0 INNER JOIN AFN_LOT0 ON ETAT_POLICE_LOT0.ETAT=AFN_LOT0.ETAT) ON MOTIF_RESIL_LOT0.CODE_RESILIATION=AFN_LOT0.CODE_RESILIATION) ON PERSONNE_LOT0.ID_CLIENT=AFN_LOT0.ID_CLIENT) ON TITRE_LOT0.ID_TITRE=PERSONNE_LOT0.ID_TITRE) ON ENTITE_PRODUIT_LOT0.ID_PRODUIT=AFN_LOT0.ID_PRODUIT"
strSql = strSql & " WHERE PERSONNE_LOT0.NOM LIKE """ & Me.txt_critere & """;"
Me.lst_resultat.RowSource = strSql
Me.lst_resultat.Requery
End If
End Sub |
Partager