How to stop an E mail sending if the output query is null

[monplankton]

Hi, wanting to know if anyone has come across any code that stops the vba from running if an 'output to' function is null. I've found some code using the DCount function but I'm struggling to adapt this to multiple excel outputs, any help much appreciated.
I'm sending 5 excel files via E mail to several addresses and I'm wanting to identify if the first file has records, currently using access 2010.:banghead:

 

[namliam]

Dcount function on the query, should not be that hard to implement...

What have you tried that isnt working for you?

 

[monplankton]

Here is my original code

Dim strTargetFolder As String

If Me.Frame5.Value = "3" Then

strTargetFolder = CurrentProject.path
DoCmd.OutputTo acOutputQuery, "HistoryCardNCIC", "ExcelWorkbook(*.xlsx)", strTargetFolder & "\History Card.xlsx", False, "", , acExportQualityPrint
DoCmd.OutputTo acOutputQuery, "PartReject", "ExcelWorkbook(*.xlsx)", strTargetFolder & "\Part Reject Cost.xlsx", False, "", , acExportQualityPrint
DoCmd.OutputTo acOutputQuery, "Qry_GroupedTotalsRAN", "ExcelWorkbook(*.xlsx)", strTargetFolder & "\\RAN List.xlsx", False, "", , acExportQualityPrint
DoCmd.OutputTo acOutputQuery, "Qry_MSD7", "ExcelWorkbook(*.xlsx)", strTargetFolder & "\Maintenance Service Detail.xlsx", False, "", , acExportQualityPrint
Else
strTargetFolder = CurrentProject.path
DoCmd.OutputTo acOutputQuery, "HistoryCard", "ExcelWorkbook(*.xlsx)", strTargetFolder & "\History Card.xlsx", False, "", , acExportQualityPrint
DoCmd.OutputTo acOutputQuery, "PartReject", "ExcelWorkbook(*.xlsx)", strTargetFolder & "\Part Reject Cost.xlsx", False, "", , acExportQualityPrint
DoCmd.OutputTo acOutputQuery, "Qry_GroupedTotalsRAN", "ExcelWorkbook(*.xlsx)", strTargetFolder & "\\RAN List.xlsx", False, "", , acExportQualityPrint
DoCmd.OutputTo acOutputQuery, "Qry_MSD7", "ExcelWorkbook(*.xlsx)", strTargetFolder & "\Maintenance Service Detail.xlsx", False, "", , acExportQualityPrint
End If

Dim objoutlook As Outlook.Application, objMailItem As MailItem
Set objoutlook = New Outlook.Application
Set objMailItem = objoutlook.CreateItem(olMailItem)
With objMailItem

.To = Me![ClaimContacts subform]![EmailAddress]
.CC = Me![ClaimContacts subform]![CCEmailAddress]
.Subject = "Monthly Monetary Claim Reports "
.Body = "Please see the attached documents, detailing the inspection and part reject details from the previous month." & vbCrLf & vbCrLf & "Regards," & vbCrLf & "NMUK Claims Team"
.Attachments.Add strTargetFolder & "\History Card.xlsx"
.Attachments.Add strTargetFolder & "\Part Reject Cost.xlsx"
.Attachments.Add strTargetFolder & "\RAN List.xlsx"
.Attachments.Add strTargetFolder & "\Maintenance Service Detail.xlsx"
.Display
End With

Set objMailItem = Nothing
Set objoutlook = Nothing

Dim path As String

path = strTargetFolder & "\History Card.xlsx"
Kill (path)

path = strTargetFolder & "\Part Reject Cost.xlsx"
Kill (path)

path = strTargetFolder & "\RAN List.xlsx"
Kill (path)

path = strTargetFolder & "\Maintenance Service Detail.xlsx"
Kill (path)


Exit_cmdEmailSupplierClaims_Click:
Set objMailItem = Nothing
If Not objoutlook Is Nothing Then objoutlook.Quit
Set objoutlook = Nothing
Exit Sub

And I was trying to adapt this to stop it from running but kept getting insufficient parameters.

I was trying to adapt

If If DCount("*", "[HistoryCardNCIC]", "[PIR] Is Null") > 0 Then
MsgBox ("No records to send")
Else

But struggled with the if statement and kept getting a message saying insufficient parameters!!!! Bit rusty I think as it's been a while since I've used access and definately new to 2010. Any help much apprciated.

 

[namliam]

Try using CODE wraps instead of Quote wraps to preserve your code when posting.
You have a double IF, the proper statement should be:

If DCount("*", "[HistoryCardNCIC]", "[PIR] Is Null") > 0 Then
    MsgBox ("No records to send")

Though I am expecting that > 0 should instead be = 0 for "Nothing to be send"

 

[monplankton]

Yes sorry for the quotes. I've tried what you have suggested now nothing happens.

Private Sub cmdEmailSupplierClaims_Click()
    Dim strTargetFolder As String
    strTargetFolder = CurrentProject.path
    If DCount("*", "[HistoryCardNCIC]", "[PIR] Is Null") = 0 Then
    MsgBox ("No records to send")
    
    If Me.Frame5.Value = "3" Then
    
    
    DoCmd.OutputTo acOutputQuery, "HistoryCardNCIC", "ExcelWorkbook(*.xlsx)", strTargetFolder & "\History Card.xlsx", False, "", , acExportQualityPrint
    DoCmd.OutputTo acOutputQuery, "PartReject", "ExcelWorkbook(*.xlsx)", strTargetFolder & "\Part Reject Cost.xlsx", False, "", , acExportQualityPrint
    DoCmd.OutputTo acOutputQuery, "Qry_GroupedTotalsRAN", "ExcelWorkbook(*.xlsx)", strTargetFolder & "\\RAN List.xlsx", False, "", , acExportQualityPrint
    DoCmd.OutputTo acOutputQuery, "Qry_MSD7", "ExcelWorkbook(*.xlsx)", strTargetFolder & "\Maintenance Service Detail.xlsx", False, "", , acExportQualityPrint
    Else
    strTargetFolder = CurrentProject.path
    DoCmd.OutputTo acOutputQuery, "HistoryCard", "ExcelWorkbook(*.xlsx)", strTargetFolder & "\History Card.xlsx", False, "", , acExportQualityPrint
    DoCmd.OutputTo acOutputQuery, "PartReject", "ExcelWorkbook(*.xlsx)", strTargetFolder & "\Part Reject Cost.xlsx", False, "", , acExportQualityPrint
    DoCmd.OutputTo acOutputQuery, "Qry_GroupedTotalsRAN", "ExcelWorkbook(*.xlsx)", strTargetFolder & "\\RAN List.xlsx", False, "", , acExportQualityPrint
    DoCmd.OutputTo acOutputQuery, "Qry_MSD7", "ExcelWorkbook(*.xlsx)", strTargetFolder & "\Maintenance Service Detail.xlsx", False, "", , acExportQualityPrint
    End If
    
    Dim objoutlook As Outlook.Application, objMailItem As MailItem

    Set objoutlook = New Outlook.Application
    Set objMailItem = objoutlook.CreateItem(olMailItem)

    With objMailItem
    
        .To = Me![ClaimContacts subform]![EmailAddress]
        .CC = Me![ClaimContacts subform]![CCEmailAddress]
        .Subject = "Monthly Monetary Claim Reports "
        .Body = "Please see the attached documents, detailing the inspection and part reject details from the previous month." & vbCrLf & vbCrLf & "Regards," & vbCrLf & "NMUK Claims Team"
        .Attachments.Add strTargetFolder & "\History Card.xlsx"
        .Attachments.Add strTargetFolder & "\Part Reject Cost.xlsx"
        .Attachments.Add strTargetFolder & "\RAN List.xlsx"
        .Attachments.Add strTargetFolder & "\Maintenance Service Detail.xlsx"
        .Display
    End With
    
    Set objMailItem = Nothing
    Set objoutlook = Nothing
    
     Dim path As String
    
    path = strTargetFolder & "\History Card.xlsx"
    Kill (path)
    
    path = strTargetFolder & "\Part Reject Cost.xlsx"
    Kill (path)
    
    path = strTargetFolder & "\RAN List.xlsx"
    Kill (path)
    
    path = strTargetFolder & "\Maintenance Service Detail.xlsx"
    Kill (path)
    
    
Exit_cmdEmailSupplierClaims_Click:
    Set objMailItem = Nothing
    If Not objoutlook Is Nothing Then objoutlook.Quit
    Set objoutlook = Nothing
    Exit Sub
End If

    
End Sub

 

[Minty]

You haven't used the result of the count correctly - add the bits in RED

Yes sorry for the quotes. I've tried what you have suggested now nothing happens.

Private Sub cmdEmailSupplierClaims_Click()
    Dim strTargetFolder As String
    strTargetFolder = CurrentProject.path
If DCount("*", "[HistoryCardNCIC]", "[PIR] Is Null") = 0 Then
    MsgBox ("No records to send")

[COLOR="Red"]ELSE[/COLOR]

    If Me.Frame5.Value = "3" Then.....
.
.
.
  
    path = strTargetFolder & "\Maintenance Service Detail.xlsx"
    Kill (path)
[COLOR="red"]END IF[/COLOR]