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 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166
| Private Sub cmd_recherche_Click()
Dim strTable As String, strField As String, strCriteria As String, strSQL As String
Dim Criter As Variant
Dim intTypChamp As Integer
Dim intOpeChamp As Integer
strTable = "[" & Me.cbo_table & "]" ' recupère le nom de la table
strField = "[" & Me.cbo_champ & "]" ' recupère le nom du champ
intTypChamp = lf_GetTypeField(strTable, strField) ' pour trouver le type du champs ...
intOpeChamp = Me.opt_Recherche
Select Case intTypChamp
Case dbBoolean ' booleen
Select Case intOpeChamp
Case 1 ' oui
strCriteria = strTable & "." & strField & "=-1"
Case 2 ' non
strCriteria = strTable & "." & strField & "=0"
Case 3
strCriteria = "ISNULL(" & strTable & "." & strField & ")"
Case 4
strCriteria = "NOT ISNULL(" & strTable & "." & strField & ")"
End Select
Case dbByte To dbBinary, dbLongBinary, dbBigInt To dbVarBinary, dbNumeric To dbTimeStamp
' traite les champs de type numerique
If Not IsNull(Me.txt_critere) Then ' si le null n'est pas la valeur à traiter
strCriteria = Me.txt_critere
' traite la virgule si elle existe
If InStr(1, Me.txt_critere, ",") > 0 Then strCriteria = Replace(Me.txt_critere, ",", ".", 1)
' type champ = date ; rajoute les dièses
If intTypChamp = dbDate And IsDate(Me.txt_critere) Then strCriteria = "#" & Me.txt_critere & "#"
End If
Select Case intOpeChamp ' numerique, date
Case 1 ' égal
If IsNull(Me.txt_critere) Then
strCriteria = "ISNULL(" & strTable & "." & strField & ")"
Else
strCriteria = strTable & "." & strField & "=" & strCriteria
End If
Case 2 ' supérieur ou égal
strCriteria = strTable & "." & strField & ">=" & strCriteria
Case 3 ' strictement supérieur
strCriteria = strTable & "." & strField & ">" & strCriteria
Case 4 ' inférieur ou égal
strCriteria = strTable & "." & strField & "<=" & strCriteria
Case 5 ' strictement inférieur
strCriteria = strTable & "." & strField & "<" & strCriteria
Case 6 ' différent
If IsNull(Me.txt_critere) Then
strCriteria = "NOT ISNULL(" & strTable & "." & strField & ")"
Else
strCriteria = strTable & "." & strField & "<>" & strCriteria
End If
End Select
Case dbText, dbMemo, dbChar ' texte
Select Case intOpeChamp
Case 1 ' strictement egal
If IsNull(Me.txt_critere) Then
strCriteria = "ISNULL(" & strTable & "." & strField & ")"
Else
strCriteria = strTable & "." & strField & " Like """ & Me.txt_critere & """"
End If
Case 2 ' commence par
strCriteria = strTable & "." & strField & " Like """ & Me.txt_critere & "*"""
Case 3 ' contient
strCriteria = strTable & "." & strField & " Like ""*" & Me.txt_critere & "*"""
Case 4 ' fini par
strCriteria = strTable & "." & strField & " Like ""*" & Me.txt_critere & """"
Case 5 ' ne contient pas
If IsNull(Me.txt_critere) Then
strCriteria = "NOT ISNULL(" & strTable & "." & strField & ")"
Else
strCriteria = "NOT " & strTable & "." & strField & " Like """ & Me.txt_critere & """"
End If
End Select
Case Else
MsgBox "Type de données non pris en charge."
Exit Sub
End Select
' debut de selection des champs
Dim strChamps As String
Dim entCurrLigne As Integer
Dim strLenCol As String 'taille de la colonne
Dim lenVal 'Taille de la valeur
Dim lenNam 'Taille du nom du champ
For entCurrLigne = 0 To Me.lst_champs.ListCount - 1
If Me.lst_champs.Selected(entCurrLigne) Then
strChamps = strChamps & "[" & Me.lst_champs.Column(0, entCurrLigne) & "], "
'Longueur valeur
lenVal = Nz(DMax(Eval("""len([" & Me.lst_champs.Column(0, entCurrLigne) & "])"""), strTable, strCriteria), 0)
'Longueur nom
lenNam = Len(Me.lst_champs.Column(0, entCurrLigne))
If lenVal < lenNam Then lenVal = lenNam 'Nom plus long que valeur
' Largeur de colonne dynamique
If Not strLenCol = "" Then strLenCol = strLenCol & "; "
'strLenCol = strLenCol & Round(((Nz(DMax(Eval("""len([" & _
Me.lst_champs.Column(0, entCurrLigne) & "])"""), _
strTable, strCriteria), 0) * 110) / 571), 2) & " cm"
' méthode WizHook.TwipsFromFont Voir tuto de Cafeine
strLenCol = strLenCol & Round(GetTextLength(Me.lst_resultat, String(lenVal, "u"), False) / 571, 2) & " cm"
' fin Largeur de colonne dynamique
End If
Next entCurrLigne
Me.lst_resultat.ColumnWidths = strLenCol ' Affecte Largeur de colonne dynamique
If Len(strChamps) = 0 Then
strChamps = strTable & ".*"
Else
strChamps = Left(strChamps, Len(strChamps) - 2)
End If
' fin de selection des champs
' construit la requête sql
Dim ctrl_table As String
If Me.Opt_RechCourante And Not Len(Me.lst_resultat.RowSource) = 0 Then
ctrl_table = Left(strTable, Len(strTable) - 1)
ctrl_table = Right(ctrl_table, Len(ctrl_table) - 1)
If Not Me.lst_resultat.RowSource Like "*FROM [[]" & ctrl_table & "*" Then
MsgBox "La recherche précédente ne porte pas sur la même table que la recherche actuelle.", _
vbExclamation + vbOKOnly, "Erreur"
Exit Sub
End If
strSQL = Left(Me.lst_resultat.RowSource, Len(Me.lst_resultat.RowSource) - 3)
strSQL = strSQL & " " & Me.cbo_operateur & " " & strCriteria & "));"
Else
' construit la rq sql
strSQL = "SELECT DISTINCTROW " & strChamps
strSQL = strSQL + " FROM " & strTable
strSQL = strSQL + " WHERE ((" & strCriteria & "));"
End If
Me.lst_resultat.RowSource = strSQL ' affecte sql a lst_Resultat
Me.lst_resultat.Requery ' recalcule la liste
Me.txt_ChaineSQL.Value = strSQL ' affiche le code
Me.lbl_nbRecord.Caption = IIf(Me.lst_resultat.ListCount _
<= 1, 0, Me.lst_resultat.ListCount - 1) & "/" & _
DCount(Me.cbo_champ, Me.cbo_table)
End Sub |
Partager