Snapshot file created from file even when there is no data

[Cosmos75]

I have this code to save a snapshot file from a report in Access 97.

Private Sub cmdSnapshotDateRange_Click()
On Error GoTo Err_cmdSnapshotDateRange_Click
    
    'Check if date fields are empty
    Call CheckDateRange
    If DateRangeProblem = True Then
        Exit Sub
    End If
    
    'Check is a report is seleted from combobox
    If IsNull(Me.cboRPTDateRange.Column(1)) Then
        MsgBox "Please select a report...", vbCritical, "No report selected!"
        Me.cboRPTDateRange.SetFocus
        Exit Sub
    End If
    
    Dim db As Database
    Set db = CurrentDb()

    Dim stDocName As String
    Dim StartDate As Date
    Dim EndDate As Date
    Dim ReportDesc As String
    Dim FileName As String
    Dim SavePath As String
        
    StartDate = Format(Me.txtStartDate, "mm-dd-yyyy")
    EndDate = Format(Me.txtEndDate, "mm-dd-yyyy")

    'Name of report in Access
    stDocName = Me.cboRPTDateRange.Column(1)

    'Description of report
    ReportDesc = Me.cboRPTDateRange.Column(2)
    
    'Name of saved file
    FileName = ReportDesc & " for " & Format(StartDate, "mm-dd-yyyy") &_
    " to " & Format(EndDate, "mm-dd-yyyy")

    'Path to save file to
    SavePath = Left(db.Name, Len(db.Name) - Len(Dir(db.Name)))_
    & "Reports\"
    MsgBox "File will be saved as " & FileName & ".snp in " & SavePath

    'Save Snapshot file
    DoCmd.OutputTo acReport, stDocName, "SnapshotFormat(*.snp)",_
    SavePath & "" & FileName & ".snp", True

Exit_cmdSnapshotDateRange_Click:
    Exit Sub

Err_cmdSnapshotDateRange_Click:
'Err 2501 returned when report has no data and is closed with [b]Cancel = True[/b]
If Err.Number = 2501 Then
    Resume Exit_cmdSnapshotDateRange_Click
Else
    MsgBox Err.Number
    MsgBox Err.Description
    Resume Exit_cmdSnapshotDateRange_Click
End If
 
End Sub

The problem I am having is that a snapshot file is created when there is no data in the report!

On all my reports I have this code to close report if there is no data to be shown.

Private Sub Report_NoData(Cancel As Integer)
On Error GoTo ProblemMsg:

    MsgBox "Report has no data.", vbInformation, "No Data!"
    Cancel = True

ExitReportNow:
Exit Sub

ProblemMsg:
    MsgBox Err.Number
    MsgBox Err.Description
    Resume ExitReportNow

End Sub

 

[sparx]

An easy way to get around this is to simply create a recordset based on the record source of the report. then use an if statement like the following:

Dim rst as DAO.recordset
Dim db as Database

set db = CurrentDB()
set rst = db.OpenRecordset("The reports record source",dbOpenDynaset)

if rst.EOF and rst.BOF then
'The report has no data
else
'Create the snapshot file
end if


HTH,

Erik

 

[Cosmos75]

Thanks for the reply.

but I got a error
3601 - Too Few Parameters. Expected 2

With a search found a thread that led me to this
ACC2000: "Too Few Parameters. Expected 1" Error Message
http://support.microsoft.com/default.aspx?scid=kb;en-us;209203
- I am using Access 97

qbfRunTestMonth is based on another query qbfTestRolling1

qbfRunTestMonth:
SELECT qbfRunTestRolling1.RunID, qbfRunTestRolling1.BurnDate, qbfRunTestRolling1.RollingDate, qbfRunTestRolling1.MonthDiff
FROM qbfRunTestRolling1
WHERE (((qbfRunTestRolling1.MonthDiff)=0));

qbfTestRolling1:
SELECT tblRunTime.RunID, tblRunTime.BurnDate, DateAdd("m",1,DateSerial([Forms]![frmReports]![txtYear],[Forms]![frmReports]![cboMonth],1)) AS RollingDate, DateDiff("m",[RollingDate],[BurnDate]) AS MonthDiff
FROM tblRunTime;

I now have this code

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs("qbfRunTestMonth")
qdf![Forms!frmReports!txtYear] = Forms![frmReports]![txtYear]
qdf![Forms!frmReports!cboMonth] = Forms![frmReports]![cboMonth]

Set rst = qdf.OpenRecordset()

MsgBox "Count = " & rst.RecordCount

rst.Close
qdf.Close
db.Close

But it doesn't return the correct number or records returned by qbfRunTestMonth? What am I doing wrong?


Edit: In case it matters, rst.RecordCount has only been returning either 1 or 0?
More

 

[Cosmos75]

Found a thread that explained it to me.
FAQ: How can I get the record count of a recordset?
http://www.accessvba.com/showthread.php?s=&threadid=5085

Changed code to

        rst.MoveLast
        MsgBox "Count = " & rst.RecordCount