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 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162
|
Private Sub chkConventionne_Click()
RefreshQuery
End Sub
Private Sub chkarelance_Click()
RefreshQuery
End Sub
Private Sub chkNOMOPERATION_Click()
If Me.chkNOMOPERATION Then
Me.cmbRechNOMOPERATION.Visible = False
Else
Me.cmbRechNOMOPERATION.Visible = True
End If
RefreshQuery
End Sub
Private Sub chkNOTEPRESC_Click()
If Me.chkNOTEPRESC Then
Me.txtRechNOTEPRESC.Visible = False
Else
Me.txtRechNOTEPRESC.Visible = True
End If
RefreshQuery
End Sub
Private Sub chknonConventionne_Click()
RefreshQuery
End Sub
Private Sub chkGRANDPETITCOMPTEPRESC_Click()
If Me.chkGRANDPETITCOMPTEPRESC Then
Me.cmbRechGRANDPETITCOMPTEPRESC.Visible = False
Else
Me.cmbRechGRANDPETITCOMPTEPRESC.Visible = True
End If
RefreshQuery
End Sub
Private Sub cmbRechNOMOPERATION_BeforeUpdate(Cancel As Integer)
RefreshQuery
End Sub
Private Sub cmbRechGRANDPETITCOMPTEPRESC_BeforeUpdate(Cancel As Integer)
RefreshQuery
End Sub
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 recherprescri.NUM_PRESC, recherprescri.NOM_PRESC, recherprescri.GRANDPETITCOMPTE_PRESC, recherprescri.NOTE_PRESC, recherprescri.DATE_RECU, recherprescri.COMM_IMMO, recherprescri.COMM_PACKAGE, recherprescri.NOM_OPERATION, recherprescri.DATECOMMEN FROM recherprescri where recherprescri!NUM_PRESC <> 1 and recherprescri.OPE_FINI <> Yes ;"
Me.lstResults.Requery
End Sub
Private Sub RefreshQuery()
Dim SQL As String
Dim SQLWhere As String
SQL = "SELECT recherprescri.NUM_PRESC, recherprescri.NOM_PRESC, recherprescri.GRANDPETITCOMPTE_PRESC, recherprescri.NOTE_PRESC, recherprescri.DATE_RECU, recherprescri.COMM_IMMO, recherprescri.COMM_PACKAGE, recherprescri.NOM_OPERATION, recherprescri.DATECOMMEN FROM recherprescri Where recherprescri!NUM_PRESC <> 1 and recherprescri.OPE_FINI <> Yes "
If Not Me.chkConventionne Then
SQL = SQL & "And recherprescri!DATE_RECU between " & CLng(Date) - 1 & " and " & CLng(Date - 540) - 1
End If
If Not Me.chkNOMOPERATION Then
SQL = SQL & "And recherprescri!NOM_OPERATION = '" & Me.cmbRechNOMOPERATION & "' "
End If
If Not Me.chkNOTEPRESC Then
SQL = SQL & "And recherprescri!NOTE_PRESC like '*" & Me.txtRechNOTEPRESC & "*' "
End If
If Not Me.chknonConventionne Then
SQL = SQL & "And (recherprescri!DATE_RECU between " & CLng(Date - 540) - 1 & " and " & CLng(Date - 5400000) - 1 & _
" Or recherprescri!DATE_RECU Is Null) "
End If
If Not Me.chkGRANDPETITCOMPTEPRESC Then
SQL = SQL & "And recherprescri!GRANDPETITCOMPTE_PRESC = '" & Me.cmbRechGRANDPETITCOMPTEPRESC & "' "
End If
If Not Me.chkarelance Then
SQL = SQL & "And (recherprescri!DATECOMMEN between " & CLng(Date - 30) & " and " & CLng(Date - 5400000) & _
" Or recherprescri!DATECOMMEN Is Null) "
End If
SQLWhere = Trim(Right(SQL, Len(SQL) - InStr(SQL, "Where ") - Len("Where ") + 1))
' ligne avant modif
'SQL = SQL & ";"
' ligne après modif
SQL = SQL & vbCrLf & _
"ORDER BY recherprescri.NOTE_PRESC DESC, PRESCRIPTEUR.NOM_PRESC ;"
Me.lblStats.Caption = DCount("*", "recherprescri", SQLWhere) & " / " & DCount("*", "recherprescri")
Me.lstResults.RowSource = SQL
Me.lstResults.Requery
End Sub
Private Sub lstResults_DblClick(Cancel As Integer)
DoCmd.OpenForm "saisie d'une fiche Prescripteur", acNormal, , "[NUM_PRESC] = " & Me.lstResults
End Sub
Private Sub txtRechNOTEPRESC_BeforeUpdate(Cancel As Integer)
RefreshQuery
End Sub
Private Sub chkarelance_BeforeUpdate(Cancel As Integer)
RefreshQuery
End Sub |
Partager