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
| Private Sub btnRechercher_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.cboTable) Or IsNull(Me.cboChamp) Then
If Me.cboChamp = "" Or IsNull(Me.cboChamp) Then
MsgBox "Vous devez renseigner le champ pour effectuer une recherche !", vbExclamation + vbOKOnly, "Recherche"
Exit Sub
End If
strTable = "T_Bouton" ' recupère le nom de la table
strField = "[" & Me.cboChamp & "]" ' 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.txtCritere) Then ' si le null n'est pas la valeur à traiter
strCriteria = Me.txtCritere
' traite la virgule si elle existe
If InStr(1, Me.txtCritere, ",") > 0 Then strCriteria = Replace(Me.txtCritere, ",", ".", 1)
' pour les versions antérieure à la 2000
'If InStr(1, Me.txtcritere, ",") > 0 Then strCriteria = Left(Me.txtcritere, InStr(1, Me.txtcritere, ",") - 1) & "." & Right(Me.txtcritere, InStr(1, Me.txtcritere, ","))
If intTypChamp = dbDate And IsDate(Me.txtCritere) Then strCriteria = "#" & Me.txtCritere & "#" ' type champ = date
' rajoute les dièses
End If
Select Case intOpeChamp ' numerique, date
Case 1 ' =
If IsNull(Me.txtCritere) 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.txtCritere) 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.txtCritere) Then
strCriteria = "ISNULL(" & strTable & "." & strField & ")"
Else
strCriteria = strTable & "." & strField & " Like """ & Me.txtCritere & """"
End If
Case 2 ' commence par
strCriteria = strTable & "." & strField & " Like """ & Me.txtCritere & "*"""
Case 3 ' contient
strCriteria = strTable & "." & strField & " Like ""*" & Me.txtCritere & "*"""
Case 4 ' fini par
strCriteria = strTable & "." & strField & " Like ""*" & Me.txtCritere & """"
Case 5 ' ne contient pas
If IsNull(Me.txtCritere) Then
strCriteria = "NOT ISNULL(" & strTable & "." & strField & ")"
Else
strCriteria = "NOT " & strTable & "." & strField & " Like ""*" & Me.txtCritere & "*"""
End If
End Select
Case Else
MsgBox "Cas non prévu."
Exit Sub
End Select
If Me.Opt_RechCourante And Not Len(Me.lstResultat.RowSource) = 0 Then
Dim ctrl_table As String
ctrl_table = Left(strTable, Len(strTable) - 1)
ctrl_table = Right(ctrl_table, Len(ctrl_table) - 1)
'If Not Me.lstResultat.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
SQL = Left(Me.lstResultat.RowSource, Len(Me.lstResultat.RowSource) - 3)
SQL = SQL & " AND " & strCriteria & "));"
Else
' construit la rq sql
SQL = "SELECT DISTINCTROW " & strTable & ".*"
SQL = SQL + " FROM " & strTable
SQL = SQL + " WHERE ((" & strCriteria & "));"
End If
Me.lstResultat.RowSource = SQL ' affecte sql a lst_result
Me.lstResultat.Requery ' recalcule la liste
Me.txt_ChaineSQL.Value = SQL
'Me.lblNbreEnr.Caption = IIf(Me.lstResultat.ListCount <= 1, 0, Me.lstResultat.ListCount - 1) & "/" & DCount(Me.cboChamp, Me.cboTable)
'Me.lblNbreEnr.Caption = ""
If Me.lstResultat.ListCount = 0 Or Me.lstResultat.ListCount = 1 Then
Me.lblNbreEnr.Caption = "Aucun enregistrement trouvé"
ElseIf Me.lstResultat.ListCount = 2 Then
Me.lblNbreEnr.Caption = (Me.lstResultat.ListCount - 1) & " " & "enregistrement correspond à votre critère"
ElseIf Me.lstResultat.ListCount > 2 Then
Me.lblNbreEnr.Caption = (Me.lstResultat.ListCount - 1) & " " & "enregistrements correspondent à votre critère"
End If
End Sub |
Partager