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
| Option Compare Database
Option Explicit
Private Sub RefreshQuery()
Dim sSQL As String 'évite de nommer avec des mots-clés ! SQL => sSQL
Dim sSQLWhere As String
Dim ctl As Control
Dim q As QueryDef
'Constructionde la partie SELECT
sSQL = "SELECT CodComposants, Composant, Reference, Marque, " _
& "Fournisseur, Qte, PUHT, PTOTAL, Secteur, Machine, OI, " _
& "Date, Commande, Devis FROM Composants"
'Construction de la clause Where
For Each ctl In Me.Controls
If Left(ctl.Name, 7) = "cmbRech" And Not IsNull(ctl) = True Then
Select Case ctl.Tag 'choisir le délimiteur en fonction du type inscrit dans la propriété Remarque
Case "Texte"
sSQLWhere = sSQLWhere & Mid(ctl.Name, 8) & "=""" & ctl & """ and "
Case "Date"
sSQLWhere = sSQLWhere & Mid(ctl.Name, 8) & "=#" & Format(ctl, "mm/dd/yy") & "# and "
Case "Numérique"
sSQLWhere = sSQLWhere & Mid(ctl.Name, 8) & "=" & ctl & " and "
End Select
End If
Next ctl
'Entête de la clause Where et suppression du dernier " and "
If Len(sSQLWhere) <> 0 Then sSQLWhere = " Where " & Left(sSQLWhere, Len(sSQLWhere) - 5)
'Ajouter la clause Where et poser le point-virgule final
sSQL = sSQL & sSQLWhere & ";"
'Mise à jour de rRowSource
Set q = CurrentDb.QueryDefs("rRowSource")
q.SQL = sSQL
Set q = Nothing
Me.lstResults.RowSource = "rRowSource"
Me.lblStats.Caption = DCount("*", "rRowSource") & " / " & DCount("*", "Composants")
End Sub
Public Sub ModifCHK()
Me("cmbRech" & Mid(Me.ActiveControl.Name, 4)).Visible = Not Me("cmbRech" & Mid(Me.ActiveControl.Name, 4)).Visible
If Me("cmbRech" & Mid(Me.ActiveControl.Name, 4)).Visible = False Then
Me("cmbRech" & Mid(Me.ActiveControl.Name, 4)) = Null
Call RefreshQuery
Else
DoCmd.GoToControl Me("cmbRech" & Mid(Me.ActiveControl.Name, 4)).Name
Me.ActiveControl.Dropdown
End If
End Sub
Private Sub chkMachine_Click()
Call ModifCHK
End Sub
Private Sub chkMarque_Click()
Call ModifCHK
End Sub
Private Sub chkQte_AfterUpdate()
Call ModifCHK
End Sub
Private Sub chkSect_Click()
Call ModifCHK
End Sub
Private Sub chkSecteur_Click()
Call ModifCHK
End Sub
Private Sub chkDate_Click()
Call ModifCHK
End Sub
Private Sub chkCommande_click()
Call ModifCHK
End Sub
Private Sub chkFournisseur_click()
Call ModifCHK
End Sub
Private Sub chkReference_click()
Call ModifCHK
End Sub
Private Sub chkComposant_click()
Call ModifCHK
End Sub
Private Sub cmbRechCommande_AfterUpdate()
Call RefreshQuery
End Sub
Private Sub cmbRechComposant_AfterUpdate()
Call RefreshQuery
End Sub
Private Sub cmbRechDate_AfterUpdate()
Call RefreshQuery
End Sub
Private Sub cmbRechFournisseur_AfterUpdate()
Call RefreshQuery
End Sub
Private Sub cmbRechMachine_AfterUpdate()
Call RefreshQuery
End Sub
Private Sub cmbRechMarque_AfterUpdate()
Call RefreshQuery
End Sub
Private Sub cmbRechQte_AfterUpdate()
Call RefreshQuery
End Sub
Private Sub cmbRechReference_AfterUpdate()
Call RefreshQuery
End Sub
Private Sub cmbRechSect_AfterUpdate()
Call RefreshQuery
End Sub
Private Sub cmbRechSecteur_AfterUpdate()
Call RefreshQuery
End Sub
Private Sub Form_Open(Cancel As Integer)
Call RefreshQuery
End Sub
Private Sub lstResults_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmAutoComposants", acNormal, , "[CodComposants] = " & Me.lstResults
End Sub |
Partager