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
| Private Sub cmbPays_AfterUpdate()
sfmRefresh
End Sub
Private Sub cmbVille_AfterUpdate()
sfmRefresh
End Sub
Private Sub sfmRefresh()
Dim strCrit1 As String, strCrit2 As String
Dim strSQL As String, p As Long, strWHERE As String
strCrit1 = Nz(Me.cmbPays, "")
strCrit2 = Nz(Me.cmbVille, "")
' Récupérer source du sous-formulaire
strSQL = Me.fmDataSheet.Form.RecordSource
' Si pas de SELECT alors Table ou Requête que l'on change
' en SELECT * FROM Table/Requête
If InStr(1, strSQL, "SELECT", vbTextCompare) = 0 Then
strSQL = "SELECT * FROM " & strSQL
End If
' Si WHERE, l'enlever
p = InStr(1, strSQL, "WHERE", vbTextCompare)
If p > 1 Then
strSQL = Left(strSQL, p - 1)
End If
' Si ; l'enlever
p = InStr(1, strSQL, ";")
If p > 1 Then
strSQL = Left(strSQL, p - 1)
End If
' Construire clause WHERE
If strCrit1 <> "" Then
If strWHERE <> "" Then strWHERE = strWHERE & " AND "
strWHERE = strWHERE & "Pays='" & strCrit1 & "'"
End If
If strCrit2 <> "" Then
If strWHERE <> "" Then strWHERE = strWHERE & " AND "
strWHERE = strWHERE & "Ville='" & strCrit2 & "'"
End If
' Si Critères, ajouter une clause WHERE
If strWHERE <> "" Then strSQL = strSQL & " WHERE " & strWHERE
' Modifier source du sous-formulaire
Me.fmDataSheet.Form.RecordSource = strSQL
End Sub |
Partager