Stop Macro if Report has NoData

[BLFOSTER]

I have a form "Report Picker" with a combo box from which the user selects a report.
I have a command button on form "Report Picker" to export the selected report.
I am using a macro behind the command button.


I just want to stop the macro from running if the selected report has no data. I have tried a lot of expressions and workarounds and cannot figure this out. Any Ideas?

Something like: (in Macro) If [Forms]![Report Picker]![Cbo_Report] .............(has no data, recordcount 0 or something like that) then

cancelevent
else
go on to open report
go on to export report

[ssanfu]

Check out the "On No Data" event in the report event properties.... (this event is what I use)

[BLFOSTER]

I did and that is a really great feature! The macro still runs though.....tried to stop the macro within that event, but cannot figure it out.......thanks though.

[ranman256]

if the query in the report > 0 then print ...

If dCount("*", 'qsRptQry") > 0 then
   docmd.openreport "rMyRpt",,acpreview
endif

[ssanfu]

Sorry ... I only use VBA - no macros.

After re-reading your first post, I don't think you can stop the macro. You would have to somehow determine if the report had data before the macro got to the export part.... easy to do in code, not so much in a macro.

Maybe convert the macro to code and post here???

[BLFOSTER]

Well, the combo box has about 15 different reports and queries. All have their own recordsets and parameters. The macro refers to whichever report is selected on the form, so I cannot see a way to use DCount.

[ssanfu]

I am doing something similar. I have a table that has a field for the displayed name of the report/query (whatever I what to call it), a field for the name in Access, and several check boxes to indicate if the object is a query, form, report, can be exported, etc.

This table is the source (actually a query) for a list box.
In the button click event, I have code using a Select Case statement with 14 cases (so far). I can pick the folder to save the export to, create the file name, check for records, and even call more code to edit the exported data (as Excel workbooks)....
I can export reports as PDF's, as Excel workbooks and as Text or CSV files.

I have one option that exports 3 reports (actually just the query for the report) at the same time (one button click) as Excel WBs and will probably have to create another option to export 5 reports with one button click.

So far approx 225 lines of code including comments for this subroutine.

[BLFOSTER]

Wow! That sounds complicated and very useful. The part I got stuck on when I tried to use code instead of a macro, was the outputTo.....checking for folder/file, creating it if it doesn't exist etc. Care to share?

[ssanfu]

The code I found on the web does not allow the creation of a folder. I've been searching for a "better" select code sample.

I found this code at http://www.mrexcel.com/forum/excel-q...lications.html
posted by Richard Schollar in 2007 (post #2). Seems to be MUCH better than what I am currently using. Time to change....

I did make a small change in the code:

Function GetFolder(strPath As String) As String
    Dim fldr As FileDialog
    Dim sItem As String
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select a Folder"
        .ButtonName = "Select Folder"
        .AllowMultiSelect = False
        .InitialFileName = strPath
        If .Show <> -1 Then GoTo NextCode
         sItem = .SelectedItems(1)
    End With
NextCode:
    GetFolder = sItem
    Set fldr = Nothing
End Function

The line in blue is what I added.

This is how you call it:

Private Sub cmdSelectFolder_Click() '<<= example button
    Dim FolderPath As String
    Dim sDefaultPath as string

    sDefaultPath = CurrentProject.path

    FolderPath = GetFolder(sDefaultPath)
    MsgBox FolderPath
    
End Sub

You must set a reference to "Microsoft Office xx.0 Object Library" where xx is your version. Access 2010 version is 14.

Care to share?

Here is a cut down version:

Private Sub cmdExportData_Click()
    On Error GoTo Err_cmdExportData_Click

    Dim strSaveFileName As String
    Dim sPath As String

    Dim bStatusBar As Variant

    'msgbox variables
    Dim Msg, Style, Title, Response

    'save current status bar state
    bStatusBar = Application.GetOption("Show Status Bar")
    ' show status bar
    Application.SetOption "Show Status Bar", True

    'The call can not have a trailing slash, so
    'strip it from the path if present
    sPath = UnqualifyPath((CurrentProject.Path))

    sPath = GetFolder(sPath )
    strSaveFileName = ""
    Me.Repaint

    If Len(Trim(sPath)) > 0 Then
        Select Case Me.lstForm   'name of report or query from list box
            Case "Statement Monthly Summary-pwk"
                'see "syscmd" in Help
                Application.SysCmd acSysCmdSetStatus, "Exporting: Statement Monthly Summary"

                strSaveFileName = sPath & "\Statement Monthly Summary " & Me.cboMonth & " " & Me.cboYear & " Hours.xls"
                DoCmd.OutputTo acOutputQuery, "EQ_StatementMonthlySummary", acFormatXLS, strSaveFileName, False
                ' Edit the Excel workbook - add rows at top, create column totals
                Call EditVendorWkSht(strSaveFileName, "Statement Monthly Summary", Me.cboMonth & " " & Me.cboYear & " Hours")
            Case xxxx
<snip>
.
.
<snip>
        End Select

        ' clear status bar message
        Application.SysCmd acSysCmdClearStatus

        ' set status bar visible state to original state
        If Not bStatusBar Then
            Application.SetOption "Show Status Bar", False
        End If

        If Len(Trim(strSaveFileName)) > 0 Then
            If Me.lstForm = "AllVendorReports" Then
                MsgBox "Done!!" & vbCrLf & vbCrLf & "The files were saved in " & sPath
            Else
                MsgBox "Done!!" & vbCrLf & vbCrLf & "The file was saved as:   " & strSaveFileName
            End If
        End If
    Else
        MsgBox "Export Canceled"
    End If

Exit_cmdExportData_Click:
    Exit Sub

Err_cmdExportData_Click:
    MsgBox Err.Description
    Resume Exit_cmdExportData_Click

End Sub



'-----------------------------------------------------
Public Function UnqualifyPath(psPath As String) As String

   'Qualifying a path involves assuring that its format
   'is valid, including a trailing slash, ready for a
   'filename. Since SHBrowseForFolder will not pre-select
   'the path if it contains the trailing slash, it must be
   'removed, hence 'unqualifying' the path.
   If Len(psPath) > 0 Then

      If Right$(psPath, 1) = "\" Then

         UnqualifyPath = Left$(psPath, Len(psPath) - 1)
         Exit Function

      End If

   End If

   UnqualifyPath = psPath

End Function

Clear as mud?
Post back with your code if you get stuck .....