Blank Report

A

Alan

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 Sales Reports Dialog form.
Dim strReportName As String
Dim strReportFilter As String
Dim lOrderCount As Long

' Determine report filtering
If Nz(Me.lstReportFilter) <> "" Then
strReportFilter = "([SalesGroupingField] = """ & Me.lstReportFilter
& """)"
End If

' Determine reporting time frame
Select Case Me.lstSalesPeriod
Case ByYear
strReportName = "Yearly Sales Report"
lOrderCount = DCountWrapper("*", "Sales Analysis", "[Year]=" &
Me.cbYear)
Case ByQuarter
strReportName = "Quarterly Sales Report"
lOrderCount = DCountWrapper("*", "Sales Analysis", "[Year]=" &
Me.cbYear & " AND [Quarter]=" & Me.cbQuarter)
Case ByMonth
strReportName = "Monthly Sales Report"
lOrderCount = DCountWrapper("*", "Sales Analysis", "[Year]=" &
Me.cbYear & " AND [Month]=" & Me.cbMonth)
End Select

If lOrderCount > 0 Then
TempVars.Add "Group By", Me.lstSalesReports.value
TempVars.Add "Display", DLookupStringWrapper("[Display]", "Sales
Reports", "[Group By]='" & Nz(Me.lstSalesReports) & "'")
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 NoSalesInPeriod
End If
End Sub

This code is for my print perview button, which link to yearly report which
is this code.

Option Compare Database

Option Explicit


Private Sub Report_Open(Cancel As Integer)
On Error GoTo ErrorHandler

Dim strSQL As String

If IsNull(TempVars![Display]) Or IsNull(TempVars![Group By]) Or
IsNull(TempVars![Year]) Then
DoCmd.OpenForm "Actual Report"
Cancel = True
Exit Sub
End If

strSQL = "TRANSFORM CCur(Nz(Sum([Amount]),0)) AS X"
strSQL = strSQL & " SELECT [" & TempVars![Display] & "] as
SalesGroupingField FROM [Sales Analysis] "
strSQL = strSQL & " Where [Year]=" & TempVars![Year]
strSQL = strSQL & " GROUP BY [" & TempVars![Group By] & "], [" &
TempVars![Display] & "]"
strSQL = strSQL & " Pivot [Sales Analysis].[Quarter] In (1,2,3,4)"

Me.RecordSource = strSQL
Me.SalesGroupingField_Label.Caption = TempVars![Display]

Done:
Exit Sub
ErrorHandler:
' Resume statement will be hit when debugging
If eh.LogError("Yearly Sales Report_Open", "strSQL = " & strSQL) Then
Resume
Else
Cancel = True
End If
End Sub

But everytime i run they report it does pick up any result the report is blank

can anyone tell me why?
 
A

Arvin Meyer [MVP]

What happens if you run the queries without the report? Do they return
records?
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


Alan said:
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 Sales Reports Dialog form.
Dim strReportName As String
Dim strReportFilter As String
Dim lOrderCount As Long

' Determine report filtering
If Nz(Me.lstReportFilter) <> "" Then
strReportFilter = "([SalesGroupingField] = """ & Me.lstReportFilter
& """)"
End If

' Determine reporting time frame
Select Case Me.lstSalesPeriod
Case ByYear
strReportName = "Yearly Sales Report"
lOrderCount = DCountWrapper("*", "Sales Analysis", "[Year]=" &
Me.cbYear)
Case ByQuarter
strReportName = "Quarterly Sales Report"
lOrderCount = DCountWrapper("*", "Sales Analysis", "[Year]=" &
Me.cbYear & " AND [Quarter]=" & Me.cbQuarter)
Case ByMonth
strReportName = "Monthly Sales Report"
lOrderCount = DCountWrapper("*", "Sales Analysis", "[Year]=" &
Me.cbYear & " AND [Month]=" & Me.cbMonth)
End Select

If lOrderCount > 0 Then
TempVars.Add "Group By", Me.lstSalesReports.value
TempVars.Add "Display", DLookupStringWrapper("[Display]", "Sales
Reports", "[Group By]='" & Nz(Me.lstSalesReports) & "'")
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 NoSalesInPeriod
End If
End Sub

This code is for my print perview button, which link to yearly report
which
is this code.

Option Compare Database

Option Explicit


Private Sub Report_Open(Cancel As Integer)
On Error GoTo ErrorHandler

Dim strSQL As String

If IsNull(TempVars![Display]) Or IsNull(TempVars![Group By]) Or
IsNull(TempVars![Year]) Then
DoCmd.OpenForm "Actual Report"
Cancel = True
Exit Sub
End If

strSQL = "TRANSFORM CCur(Nz(Sum([Amount]),0)) AS X"
strSQL = strSQL & " SELECT [" & TempVars![Display] & "] as
SalesGroupingField FROM [Sales Analysis] "
strSQL = strSQL & " Where [Year]=" & TempVars![Year]
strSQL = strSQL & " GROUP BY [" & TempVars![Group By] & "], [" &
TempVars![Display] & "]"
strSQL = strSQL & " Pivot [Sales Analysis].[Quarter] In (1,2,3,4)"

Me.RecordSource = strSQL
Me.SalesGroupingField_Label.Caption = TempVars![Display]

Done:
Exit Sub
ErrorHandler:
' Resume statement will be hit when debugging
If eh.LogError("Yearly Sales Report_Open", "strSQL = " & strSQL) Then
Resume
Else
Cancel = True
End If
End Sub

But everytime i run they report it does pick up any result the report is
blank

can anyone tell me why?
Click to expand...
 
A

Alan

Yer i solved this issue now, was bound to wrong column

You would know how i can select multi customer from my list box and then
place them into report using same code as below

Arvin Meyer said:
What happens if you run the queries without the report? Do they return
records?
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


Alan said:
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 Sales Reports Dialog form.
Dim strReportName As String
Dim strReportFilter As String
Dim lOrderCount As Long

' Determine report filtering
If Nz(Me.lstReportFilter) <> "" Then
strReportFilter = "([SalesGroupingField] = """ & Me.lstReportFilter
& """)"
End If

' Determine reporting time frame
Select Case Me.lstSalesPeriod
Case ByYear
strReportName = "Yearly Sales Report"
lOrderCount = DCountWrapper("*", "Sales Analysis", "[Year]=" &
Me.cbYear)
Case ByQuarter
strReportName = "Quarterly Sales Report"
lOrderCount = DCountWrapper("*", "Sales Analysis", "[Year]=" &
Me.cbYear & " AND [Quarter]=" & Me.cbQuarter)
Case ByMonth
strReportName = "Monthly Sales Report"
lOrderCount = DCountWrapper("*", "Sales Analysis", "[Year]=" &
Me.cbYear & " AND [Month]=" & Me.cbMonth)
End Select

If lOrderCount > 0 Then
TempVars.Add "Group By", Me.lstSalesReports.value
TempVars.Add "Display", DLookupStringWrapper("[Display]", "Sales
Reports", "[Group By]='" & Nz(Me.lstSalesReports) & "'")
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 NoSalesInPeriod
End If
End Sub

This code is for my print perview button, which link to yearly report
which
is this code.

Option Compare Database

Option Explicit


Private Sub Report_Open(Cancel As Integer)
On Error GoTo ErrorHandler

Dim strSQL As String

If IsNull(TempVars![Display]) Or IsNull(TempVars![Group By]) Or
IsNull(TempVars![Year]) Then
DoCmd.OpenForm "Actual Report"
Cancel = True
Exit Sub
End If

strSQL = "TRANSFORM CCur(Nz(Sum([Amount]),0)) AS X"
strSQL = strSQL & " SELECT [" & TempVars![Display] & "] as
SalesGroupingField FROM [Sales Analysis] "
strSQL = strSQL & " Where [Year]=" & TempVars![Year]
strSQL = strSQL & " GROUP BY [" & TempVars![Group By] & "], [" &
TempVars![Display] & "]"
strSQL = strSQL & " Pivot [Sales Analysis].[Quarter] In (1,2,3,4)"

Me.RecordSource = strSQL
Me.SalesGroupingField_Label.Caption = TempVars![Display]

Done:
Exit Sub
ErrorHandler:
' Resume statement will be hit when debugging
If eh.LogError("Yearly Sales Report_Open", "strSQL = " & strSQL) Then
Resume
Else
Cancel = True
End If
End Sub

But everytime i run they report it does pick up any result the report is
blank

can anyone tell me why?
Click to expand...


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Paramater box pop up even if customer selected in combo box 1
HELP NEEDED WITH REPORT 1
Adding report with another 0
combo box method or data member not found?? 2
Change a report to a query 1
Select case statement questions 1
VBA code for my report 5
VBA code for my report 0

Top