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
|
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
If IsNull(Me.cbo_table) Or IsNull(Me.cbo_champ) Then
MsgBox "Vous devez renseigner la table et le champ pour effectuer une recherche !", vbExclamation + vbOKOnly, "Recherche"
Exit Sub
End If
strTable = "[" & Me.cbo_table & "]" ' recupère le nom de la table
strField = "[" & Me.cbo_champ & "]" ' recupère le nom du champ
' compose le critere de recherche
intTypChamp = lf_GetTypeField(strTable, strField) ' pour trouver le type du champs ...
intOpeChamp = Me.opt_recherche
Select Case intTypChamp
Case dbBoolean ' bool
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 numeriques
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)
' pour les versions antérieure à la 2000
'If InStr(1, Me.txt_critere, ",") > 0 Then strCriteria = Left(Me.txt_critere, InStr(1, Me.txt_critere, ",") - 1) & "." & Right(Me.txt_critere, InStr(1, Me.txt_critere, ","))
If intTypChamp = dbDate And IsDate(Me.txt_critere) Then strCriteria = "#" & Me.txt_critere & "#" ' type champ = date
' rajoute les dièses
End If
Select Case intOpeChamp ' numerique, date
Case 1 ' =
If IsNull(Me.txt_critere) Then
strCriteria = "ISNULL(" & strTable & "." & strField & ")"
Else
strCriteria = strTable & "." & strField & "=" & strCriteria
End If
Case 2 ' >=
strCriteria = strTable & "." & strField & ">=" & strCriteria
Case 3 ' <=
strCriteria = strTable & "." & strField & "<=" & strCriteria
Case 4 '<>
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 "Cas non prévu."
Exit Sub
End Select
' debut de selection des champs
Dim strChamps As String
Dim entCurrLigne As Integer
Dim strLenCol As String
For entCurrLigne = 0 To Me.lst_champs.ListCount - 1
If Me.lst_champs.Selected(entCurrLigne) Then
strChamps = strChamps & "[" & Me.lst_champs.Column(0, entCurrLigne) & "], "
' Largeur de colonne dynamique
If Not strLenCol = "" Then strLenCol = strLenCol & "; "
strLenCol = strLenCol & Round((DMax(Eval("""len([" & Me.lst_champs.Column(0, entCurrLigne) & "])"""), strTable, strCriteria) * 160) / 571, 2) & " cm"
' 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
If Me.Opt_rechcourante And Not Len(Me.lst_resultat.RowSource) = 0 Then
' ancienne ligne
'If Not Me.lst_resultat.RowSource Like "*FROM " & strTable & "*" Then
' remplacé par
Dim ctrl_table As String
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