By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,462 Members | 2,394 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,462 IT Pros & Developers. It's quick & easy.

Check report for data

P: 54
I have the following code to save a report as a pdf, email it as an attachment and then delete the pdf. It works fine.
Expand|Select|Wrap|Line Numbers
  1. Dim iCfg As Object
  2. Dim iMsg As Object
  3. Set iCfg = CreateObject("CDO.Configuration")
  4. Set iMsg = CreateObject("CDO.Message")
  5. With iCfg.Fields
  6. .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "exc2007.futuraind.com"
  7. .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
  8. .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
  9. .Update
  10. End With
  11.  
  12. 'Stop
  13. With iMsg
  14. .Configuration = iCfg
  15. .Subject = "Late Orders"
  16. stremail = DLookup("email", "reminder_date")
  17. .to = stremail
  18. .TextBody = "Attached is a list of items that have not been ordered yet.  Open the TR Card database for more information."
  19. .AddAttachment "G:\DATA\FUTURA\FINISHG\Titrations_TRcards\TR Cards\TR Master Database\unpurchased orders.pdf"
  20. .from = ""
  21. .sender = ""
  22. .send
  23. End With
  24. Set iMsg = Nothing
  25. Set iCfg = Nothing
  26. Kill "G:\DATA\FUTURA\FINISHG\Titrations_TRcards\TR Cards\TR Master Database\unpurchased orders.pdf"
  27. DoCmd.SetWarnings False
  28. DoCmd.OpenQuery "reminder_date query"
  29. DoCmd.SetWarnings True
I don't want the email to send if the report does not contain data though, so I included this in the code.
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport "unpurchased orders"
  2. If Reports![unpurchased orders].HasData = True Then
  3. 'run the email code from above
  4. else exit sub
  5. end if
Checking the report for data however does not work because it says the report is not open. I think because the code runs too fast for the report to finish loading. Any ideas on how the accomplish this?
Sep 3 '12 #1

✓ answered by Seth Schrock

You could use the DCount function to check if there are greater than 0 records in the data source for the report. I have done this before and it works great.

Expand|Select|Wrap|Line Numbers
  1. If DCount(*, [QueryName]) > 0 Then
  2.     'run your code
  3. Else
  4.     MsgBox("There are no records")
  5. End If

Share this Question
Share on Google+
3 Replies


Seth Schrock
Expert 2.5K+
P: 2,930
You could use the DCount function to check if there are greater than 0 records in the data source for the report. I have done this before and it works great.

Expand|Select|Wrap|Line Numbers
  1. If DCount(*, [QueryName]) > 0 Then
  2.     'run your code
  3. Else
  4.     MsgBox("There are no records")
  5. End If
Sep 3 '12 #2

zmbd
Expert Mod 5K+
P: 5,285
Seth's method is one way - and may be the best depending on when your code is ran... i.e. from a command button.

Taking a poke at the empty light socket:
It looks like on line 19 that you already have created a report and then in line 26 you delete the report.

In the code that creates the report in Line19, check for the records there... either use Seth's suggestion or use the NoDataEvent http://msdn.microsoft.com/en-us/libr.../ff837041.aspx to prevent the document from being created.

Then In the code you posted above, or before what ever point you call this code ... use the following:
If Len(Dir(strFile))>0 Then "code to run" End If. where strFile is the full name and path to the document to see if the file exsists and if so, then run the code, if not...

-z
Sep 3 '12 #3

P: 54
Thanks Seth! That's exactly what I needed.
Sep 3 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.