Hi,
I have a form which shows various options with to show report
with details.
The form has report info from 3 specific cases, got from a table.
The table shows simply details including a select statement.
This feeds the filter box for each of the 3 cases.
example: Group BY: Genre, Display: Genre, Title: Albums by Genre,
Filter Row Source: SELECT DISTINCT [Genre] FROM [Genre] ORDER BY [Genre];
A table "Music Analysis" provides the details as required for each case.
In addition there are further filters to show yearly, monthly (by year) and
quarterly (by year) options.
The filters on the dialog etc on the form all work ok.
but I am clearly wrong with my vbs code.
The idea being to show album names for each of the criteria,
eg. Album Name by Genre for the month April 2010
album Name By Artists for the 3rd quarter of 2009.
I have tried many permutations but I clearly have no idea. Help please. Thanks
Preview cmd On Reports Dialog
==============================
Sub PrintReports(ReportView As AcView)
' This procedure used in Preview_Click and Print_Click Sub procedures.
' Preview or print report selected in the ReportToPrint option group.
' Then close the Print Music Reports Dialog form.
Dim strReportName As String
Dim strReportFilter As String
Dim lAlbums As String
' Determine report filtering
If Nz(Me.lstReportFilter) <> "" Then
strReportFilter = "([AlbumGroupingField] = """ & Me.lstReportFilter & """)"
End If
' Determine reporting time frame
Select Case Me.lstMusicPeriod
Case ByYear
strReportName = "Yearly Music Report"
lAlbums = DLookupWrapper("*", "Music Analysis", "[Year]=" & Me.cbYear)
Case ByQuarter
strReportName = "Quarterly Music Report"
lAlbums = DLookupWrapper("*", "Music Analysis", "[Year]=" & Me.cbYear & " AND [Quarter]=" & Me.cbQuarter)
Case ByMonth
strReportName = "Monthly Music Report"
lAlbums = DLookupWrapper("*", "Music Analysis", "[Year]=" & Me.cbYear & " AND [Month]=" & Me.cbMonth)
End Select
If lAlbums > 0 Then
TempVars.Add "Group By", Me.lstMusicReports.Value
TempVars.Add "Display", DLookupStringWrapper("[Display]", "Music Reports", "[Group By]='" & Nz(Me.lstMusicReports) & "'")
TempVars.Add "Year", Me.cbYear.Value
TempVars.Add "Quarter", Me.cbQuarter.Value
TempVars.Add "Month", Me.cbMonth.Value
eh.TryToCloseObject
DoCmd.OpenReport strReportName, ReportView, , strReportFilter, acWindowNormal
Else
MsgBoxOKOnly NoAlbumsAddedInPeriod
End If
End Sub
---------------------------------------------------------------------------------------------------
Yearly Report
==============
Dim strSQL As String
If IsNull(TempVars![Display]) Or IsNull(TempVars![Group By]) Or IsNull(TempVars![Year]) Then
DoCmd.OpenForm "Music Reports Dialog"
Cancel = True
Exit Sub
End If
strSQL = strSQL & " SELECT [" & TempVars![Display] & "] as AlbumGroupingField FROM [Music Analysis] "
strSQL = strSQL & " Where [Year]=" & TempVars![Year]
strSQL = strSQL & " GROUP BY [" & TempVars![Group By] & "], [" & TempVars![Display] & "]"
strSQL = strSQL & " Pivot [Music Analysis].[Quarter] In (1,2,3,4)"
Me.RecordSource = strSQL
Me.AlbumGroupingField_Label.Caption = TempVars![Display]
Done:
Exit Sub
I have a form which shows various options with to show report
with details.
The form has report info from 3 specific cases, got from a table.
The table shows simply details including a select statement.
This feeds the filter box for each of the 3 cases.
example: Group BY: Genre, Display: Genre, Title: Albums by Genre,
Filter Row Source: SELECT DISTINCT [Genre] FROM [Genre] ORDER BY [Genre];
A table "Music Analysis" provides the details as required for each case.
In addition there are further filters to show yearly, monthly (by year) and
quarterly (by year) options.
The filters on the dialog etc on the form all work ok.
but I am clearly wrong with my vbs code.
The idea being to show album names for each of the criteria,
eg. Album Name by Genre for the month April 2010
album Name By Artists for the 3rd quarter of 2009.
I have tried many permutations but I clearly have no idea. Help please. Thanks
Preview cmd On Reports Dialog
==============================
Sub PrintReports(ReportView As AcView)
' This procedure used in Preview_Click and Print_Click Sub procedures.
' Preview or print report selected in the ReportToPrint option group.
' Then close the Print Music Reports Dialog form.
Dim strReportName As String
Dim strReportFilter As String
Dim lAlbums As String
' Determine report filtering
If Nz(Me.lstReportFilter) <> "" Then
strReportFilter = "([AlbumGroupingField] = """ & Me.lstReportFilter & """)"
End If
' Determine reporting time frame
Select Case Me.lstMusicPeriod
Case ByYear
strReportName = "Yearly Music Report"
lAlbums = DLookupWrapper("*", "Music Analysis", "[Year]=" & Me.cbYear)
Case ByQuarter
strReportName = "Quarterly Music Report"
lAlbums = DLookupWrapper("*", "Music Analysis", "[Year]=" & Me.cbYear & " AND [Quarter]=" & Me.cbQuarter)
Case ByMonth
strReportName = "Monthly Music Report"
lAlbums = DLookupWrapper("*", "Music Analysis", "[Year]=" & Me.cbYear & " AND [Month]=" & Me.cbMonth)
End Select
If lAlbums > 0 Then
TempVars.Add "Group By", Me.lstMusicReports.Value
TempVars.Add "Display", DLookupStringWrapper("[Display]", "Music Reports", "[Group By]='" & Nz(Me.lstMusicReports) & "'")
TempVars.Add "Year", Me.cbYear.Value
TempVars.Add "Quarter", Me.cbQuarter.Value
TempVars.Add "Month", Me.cbMonth.Value
eh.TryToCloseObject
DoCmd.OpenReport strReportName, ReportView, , strReportFilter, acWindowNormal
Else
MsgBoxOKOnly NoAlbumsAddedInPeriod
End If
End Sub
---------------------------------------------------------------------------------------------------
Yearly Report
==============
Dim strSQL As String
If IsNull(TempVars![Display]) Or IsNull(TempVars![Group By]) Or IsNull(TempVars![Year]) Then
DoCmd.OpenForm "Music Reports Dialog"
Cancel = True
Exit Sub
End If
strSQL = strSQL & " SELECT [" & TempVars![Display] & "] as AlbumGroupingField FROM [Music Analysis] "
strSQL = strSQL & " Where [Year]=" & TempVars![Year]
strSQL = strSQL & " GROUP BY [" & TempVars![Group By] & "], [" & TempVars![Display] & "]"
strSQL = strSQL & " Pivot [Music Analysis].[Quarter] In (1,2,3,4)"
Me.RecordSource = strSQL
Me.AlbumGroupingField_Label.Caption = TempVars![Display]
Done:
Exit Sub