Dim county_criteria As String
Dim MyFilter As String
Dim MyPath As String
Dim MyFilename As String
Private Sub btnCustomize_Click()
'MsgBox tradescombo.ListIndex
If cmbMarket.ListIndex = -1 Then
MsgBox "Please select a Market"
Exit Sub
End If
whereclause = ""
tradelisttxt = ""
whereclause = " [Markets] = " & cmbMarket.ItemData(cmbMarket.ListIndex)
If Len(county_criteria) > 0 Then
whereclause = whereclause & " AND ([Project City] in ( " & _
"SELECT c.[City ID] FROM tblCities as c " & _
"WHERE c.[County ID] in (" & county_criteria & ") ) ) "
End If
If tradelist.ItemsSelected.Count > 0 Then
'MsgBox "You must select a least one item of the list"
'Exit Sub
tradelisttxt = ""
For Each it In tradelist.ItemsSelected
tradelisttxt = tradelisttxt & tradelist.ItemData(it) & ","
Next
tradelisttxt = Left(tradelisttxt, Len(tradelisttxt) - 1)
whereclause = whereclause & " AND (tblProjectInfo.[Trade].value in (" & tradelisttxt & ") ) "
End If
Select Case cmbType.ListIndex
Case 0 'Any
' do nothing
Case 1 ' Commercial
whereclause = whereclause & " AND (tblProjectInfo.[Project Type] In (SELECT tblProjectType.ProjecttypeKey FROM tblProjectType WHERE tblProjectType.Category='COM')) "
Case 2 ' Residential
whereclause = whereclause & " AND (tblProjectInfo.[Project Type] In (SELECT tblProjectType.ProjecttypeKey FROM tblProjectType WHERE tblProjectType.Category='RES')) "
End Select
If datecheck.Value = True Then
whereclause = whereclause & " AND ( tblProjectInfo.[Project Date] between #" & date1.Value & "# AND #" & date2.Value & "# )"
End If
Select Case optcombo.ListIndex
Case 0 'all
' do nothing
Case 1 ' Only active projects
whereclause = whereclause & "tblProjectInfo.[newOnHold] = 0"
Case 2 ' Only inactive projects
whereclause = whereclause & "tblProjectInfo.[newOnHold] = 1"
Case 3 ' Only Projects on hold
whereclause = whereclause & "tblProjectInfo.[newOnHold] = 2"
Case 4 'Only active projects and projects on hold
whereclause = whereclause & "tblProjectInfo.[newOnHold] = 0"
whereclause = whereclause & "tblProjectInfo.[newOnHold] = 2"
End Select
'Select Path Output to:
MyPath = "C:\Tempsend\"
'State the filename.
MyFilename = "(cDate())" & "-" & "Customized Report" & ".pdf"
'MsgBox whereclause
DoCmd.OpenReport "Report-custom", acViewPreview, , whereclause
DoCmd.OutputTo acOutputReport, "Report-custom", acFormatPDF, MyPath & MyFilename, True
DoCmd.Close acReport, "Report-custom"
End Sub
Sub LoadMarkets()
If cmbMarket.ListIndex = -1 Then
Exit Sub
End If
strSQL = "SELECT c.CountyName & ' (' & Count(c.[County ID]) & ')' as ProjectCount, c.[County ID] " & _
"FROM tblCounty AS c INNER JOIN (tblCities AS ct INNER JOIN tblProjectInfo AS p " & _
"ON ct.[City ID] = p.City) ON c.[County ID] = ct.[County ID] " & _
"WHERE p.Markets = " & cmbMarket.ItemData(cmbMarket.ListIndex)
Select Case cmbType.ListIndex
Case 0 'Any
Report_Tooter = "Commercial/Residential Projects Requesting Contact"
Case 1 ' Commercial
strSQL = strSQL & " AND (p.[Project Type] In (SELECT tblProjectType.ProjecttypeKey FROM tblProjectType WHERE tblProjectType.Category='COM')) "
Report_Tooter = "Commercial Projects Requesting Contact"
Case 2 ' Residential
strSQL = strSQL & " AND (p.[Project Type] In (SELECT tblProjectType.ProjecttypeKey FROM tblProjectType WHERE tblProjectType.Category='RES')) "
Report_Tooter = "Residential Projects Requesting Contact"
End Select
strSQL = strSQL & " GROUP BY c.CountyName, c.[County ID] ORDER BY c.CountyName"
countylist.RowSource = strSQL
End Sub
Private Sub cmbMarket_Change()
LoadMarkets
Report_Title = cmbMarket.Text & " Report"
End Sub
Private Sub cmbType_Click()
LoadMarkets
End Sub
Private Sub countylist_Click()
county_criteria = ""
If countylist.ItemsSelected.Count = 0 Then
tradelist.RowSource = ""
Exit Sub
End If
txtsql = "SELECT tblTradeList.Trade, count(tblProjectInfo.ProjectKey) as Total, tblTradeList.[Trade Id] " & _
"FROM tblTradeList INNER JOIN tblProjectInfo ON tblTradeList.[Trade Id] = tblProjectInfo.Trade.Value "
countyTxt = ""
For Each it In countylist.ItemsSelected
countyTxt = countyTxt & countylist.ItemData(it) & ","
Next
countyTxt = Left(countyTxt, Len(countyTxt) - 1)
whereclause = "WHERE tblProjectInfo.[City] in ( " & _
"SELECT c.[City ID] FROM tblCities as c " & _
"WHERE c.[County ID] in (" & countyTxt & ") ) "
Select Case optcombo.ListIndex
Case 0 'all
' do nothing
Case 1 ' only active projects
whereclause = whereclause & "tblProjectInfo.[newOnHold] = 0"
Case 2 ' only inactive projects
whereclause = whereclause & "tblProjectInfo.[newOnHold] = 1"
Case 3 ' Only Projects on hold
whereclause = whereclause & "tblProjectInfo.[newOnHold] = 2"
Case 4 'only active projects and projects on hold
whereclause = whereclause & "tblProjectInfo.[newOnHold] = 0"
whereclause = whereclause & "tblProjectInfo.[newOnHold] = 2"
End Select
txtsql = txtsql & whereclause & "GROUP by tblTradeList.Trade,tblTradeList.[Trade ID] ORDER BY tblTradeList.Trade "
county_criteria = countyTxt
'MsgBox txtSQL
tradelist.RowSource = txtsql
End Sub
Private Sub datecheck_Click()
End Sub
Private Sub Form_Load()
'1 month
date1.Value = DateAdd("m", -1, Date)
date2.Value = Date
DoCmd.Maximize
End Sub
Private Sub optcombo_Change()
countylist_Click
End Sub