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
| Public Sub FiltreFeuillesDeRoute()
Dim i As Integer
Dim rs As DAO.Recordset
Dim ctl As Control
Dim varItem As Variant
Dim strEtatProspection As String
Dim EtatProspection As String
Dim nbResultat As Integer
Set ctl = Me.LstEtatsProspection
For Each varItem In ctl.ItemsSelected
strEtatProspection = strEtatProspection & "'" & ctl.Column(1, varItem) & "'" & ","
Next varItem
'Constitution de la chaine de filtre suivant sélection dans la listbox
'on retire la virgule
strEtatProspection = Left(strEtatProspection, Len(strEtatProspection) - 1)
'Debug.Print strEtatProspection
sqlFDR = "select * from rqmairies " & _
" where idcommercial='" & Me.cboEVTSCommercial & "'" & _
" and dateecheanceevt between #" & Format(Me.EVTSCommercialDU, "mm/dd/yyyy 00:00:00") & "# and #" & Format(Me.EVTSCommercialAU, "mm/dd/yyyy 23:59:00") & "#" & _
" and etatprospection in(" & strEtatProspection & ")"
Me.ListeMairies.RowSource = "select idcommune,idmairie,Ouverte,[Communeetdep] as Mairie,Etatprospection as [Etat prospection] from rqmairies" & _
" where idcommercial='" & Me.cboEVTSCommercial & "'" & _
" and dateecheanceevt between #" & Format(Me.EVTSCommercialDU, "mm/dd/yyyy 00:00:00") & "# and #" & Format(Me.EVTSCommercialAU, "mm/dd/yyyy 23:59:00") & "#" & _
" and etatprospection in(" & strEtatProspection & ")"
Set rs = CurrentDb.OpenRecordset(sqlFDR)
If Not rs.EOF Then
rs.MoveLast
nbResultat = rs.RecordCount
End If
Me.CompteurRésultats = nbResultat & " fiches trouvée(s)."
'DoCmd.OpenReport "rptfeuillesderoute", acViewPreview, , , acHidden
'Forms!menu!SFNav.Form!CadrePrincipal.Form.SourceObject = "rptfeuillesderoute"
'Reports("rptFeuillesDeRoute").RecordSource = "sqlfdr"
Set ctl = Nothing
rs.Close: Set rs = Nothing
End Sub |
Partager