Case by case using DLookup

[kevin24]

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

 

[Khalid_Afridi]

look like a full project code .
can you make it simple? nobody will have time to read all this and give reply.
where is the main cause of problem?

 

[kevin24]

Sorry I thought as much info would help.

The report shows correct period and list of
Genre, artists etc but does not show albums
Associated with each.
Thanks

 

[Khalid_Afridi]

Instead of using Dlookup in Case statment, Report based on query is more faster, better and prefrable..