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
| Private Sub Form_Load()
Dim ctl As Control
For Each ctl In Me.Controls
Select Case Left(ctl.Name, 3)
Case "chk"
ctl.Value = -1
Case "lbl"
ctl.Caption = "- * - * -"
Case "txt"
ctl.Visible = False
ctl.Value = ""
Case "cmb"
ctl.Visible = False
End Select
Next ctl
Me.lstResults.RowSource = "SELECT All FROM Contacts;"
Me.lstResults.Requery
End Sub
Private Sub RefreshQuery()
Dim SQL As String
Dim SQP As String
Dim SQLWhere As String
SQL = "SELECT Nom_de_famille, Prenom, Entreprise, Num_Tel, e_Mail, Groupe, Commentaire FROM Contacts WHERE Contacts.num <> 0 "
'______________
If Me.cNom Then
If Me.chNom = "Tout" Then
SQL = SQL & "And (Contacts.Nom_de_famille like '*" & Me.CmdNom & "*' Or Contacts.Prenom like '*" & Me.CmdNom & "*' Or Contacts.Entreprise like '*" & Me.CmdNom & "*') "
End If
If Me.chNom = "Nom" Then
SQL = SQL & "And Contacts.Nom_de_famille like '*" & Me.CmdNom & "*' "
End If
If Me.chNom = "Prenom" Then
SQL = SQL & "And Contacts.Prenom like '*" & Me.CmdNom & "*' "
End If
If Me.chNom = "Entreprise" Then
SQL = SQL & "And Contacts.Entreprise like '*" & Me.CmdNom & "*' "
End If
' * inseré avant et après le nom permet de trouver des morceaux de mot
End If
'______________
If Me.cCategorie Then
SQL = SQL & "And Contacts.Groupe = '" & Me.chCategorie & "' "
End If
'______________
If Me.cTel Then
SQL = SQL & "And Contacts.Num_Tel= '" & Me.CmdTel & "' "
End If
'______________
If Me.cVille Then
If Me.chVille = "Ville" Then
SQL = SQL & "And Contacts.Ville like '*" & Me.CmdVille & "*' "
End If
If Me.chVille = "Code postal" Then
SQL = SQL & "And Contacts.Code_postal like '*" & Me.CmdVille & "*' "
End If
End If
'______________
If Me.cMail Then
SQL = SQL & "And Contacts.e_Mail = '" & Me.CmdMail & "' "
End If
'______________
' affichage du nombre de contacts
SQLWhere = Trim(Right(SQL, Len(SQL) - InStr(SQL, "Where ") - Len("Where ") + 1))
' rangement dans l'ordre croissant
If Me.rg = "Nom" Then
SQP = "ORDER BY Contacts.Nom_de_famille"
Requery
End If
If Me.rg = "Prenom" Then
SQP = "ORDER BY Contacts.Prenom"
Requery
End If
If Me.rg = "Entreprise" Then
SQP = "ORDER BY Contacts.Entreprise"
Requery
End If
If Me.rg = "Categorie" Then
SQP = "ORDER BY Contacts.Groupe"
Requery
End If
If Me.rg = "Numero de telephone" Then
SQP = "ORDER BY Contacts.Num_Tel"
Requery
End If
If Me.rg = "Mail" Then
SQP = "ORDER BY Contacts.e_Mail"
Requery
End If
SQL = SQL & SQP & ";"
'affichage liste contacts voulus
Me.lblStats.Caption = DCount("*", "Contacts", SQLWhere) & " / " & DCount("*", "Contacts")
Me.lstResults.RowSource = SQL
Me.lstResults.Requery
End Sub |
Partager