Go Back   Access World Forums > Microsoft Access Discussion > Macros

 
Reply
 
Thread Tools Rating: Thread Rating: 8 votes, 5.00 average. Display Modes
Old 01-15-2013, 08:11 AM   #1
AlexSalvadori
Newly Registered User
 
Join Date: Oct 2012
Posts: 145
Thanks: 40
Thanked 9 Times in 9 Posts
AlexSalvadori is on a distinguished road
Macro: if 'query returns no records'...

Hi all,

I have created a macro which emails the results of a query to myself when I open the access database.

- However, I only want it to do this if the query finds any records, otherwise it sends me a blank document half the time.

- I have tried using the 'If' Program Flow so it either sends the email, or just makes a popup to say no results found. But I don't know how to instruct Access to know whether the Query found any records or not, so you see the missing piece of the puzzle.

So the issue is in the If function, so far all I have is:
If [TheQuery]![QuerysUniqueID]="" then...

- but this doesn't work. I need it to say 'if the query finds no records...'

Please note I'm an access noobie using the macro creator not VBA (I have a vague understanding of it...), so use simple words please

Thanks in advance,

AlexSalvadori is offline   Reply With Quote
Old 01-16-2013, 03:23 AM   #2
pr2-eugin
Super Moderator
 
pr2-eugin's Avatar
 
Join Date: Nov 2011
Location: Bournemouth, UK
Posts: 8,498
Thanks: 68
Thanked 2,062 Times in 2,012 Posts
pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about
Re: Macro: if 'query returns no records'...

Have you considered using a DCount as a checking condition before calling the procedure? I am not great with Macros.. but should be simple like..
Code:
If DCount("*","yourQueryName") > 0 Then
    Call the function
Else
    Msgbox("No details to send")
End If


Could you delete the other thread, if this is what you wanted and if this has solved your problem?
Attached Images
File Type: png macro.png (8.8 KB, 12465 views)
__________________
Regards,
Paul Eugin

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
!
!
Windows 7 Professional, MS Access 2010
---------------------------------------------------------------------------------------------------------------------
If the above post has helped you, please click the scales
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
or click the 'Thumbs up'. Cheers.

---------------------------------------------------------------------------------------------------------------------




Last edited by pr2-eugin; 01-16-2013 at 03:33 AM. Reason: Added more info.
pr2-eugin is offline   Reply With Quote
The Following 2 Users Say Thank You to pr2-eugin For This Useful Post:
AlexSalvadori (01-16-2013), blacross (05-13-2013)
Old 01-16-2013, 07:22 AM   #3
AlexSalvadori
Newly Registered User
 
Join Date: Oct 2012
Posts: 145
Thanks: 40
Thanked 9 Times in 9 Posts
AlexSalvadori is on a distinguished road
Re: Macro: if 'query returns no records'...

You are fast becoming my favorite Tecchy this works perfectly thank you very much.
I have deleted the other thread.

AlexSalvadori is offline   Reply With Quote
Old 01-16-2013, 07:30 AM   #4
pr2-eugin
Super Moderator
 
pr2-eugin's Avatar
 
Join Date: Nov 2011
Location: Bournemouth, UK
Posts: 8,498
Thanks: 68
Thanked 2,062 Times in 2,012 Posts
pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about
Re: Macro: if 'query returns no records'...

Thanks for the comment.. Appreciate it.. But I am just like you trying to learn and helping others with what I could.. Glad to do it..
__________________
Regards,
Paul Eugin

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
!
!
Windows 7 Professional, MS Access 2010
---------------------------------------------------------------------------------------------------------------------
If the above post has helped you, please click the scales
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
or click the 'Thumbs up'. Cheers.

---------------------------------------------------------------------------------------------------------------------



pr2-eugin is offline   Reply With Quote
Old 06-22-2015, 12:39 PM   #5
dsm787
Newly Registered User
 
Join Date: Jun 2015
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
dsm787 is on a distinguished road
Re: Macro: if 'query returns no records'...

I have used this solution, but am getting the error "cannot find the name 'queryname' you entered in the expression. You may have specified a control that wasn't on the current object without specifying the correct form or report context."
dsm787 is offline   Reply With Quote
Old 06-30-2018, 10:48 PM   #6
SPARGEL
Newly Registered User
 
Join Date: Jun 2018
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
SPARGEL is on a distinguished road
Re: Macro: if 'query returns no records'...

Finally, this works. Thank you

SPARGEL is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Query Returns Too Many Records funderburgh Queries 5 09-18-2008 12:01 PM
When query returns no records in set bonesy Queries 2 07-29-2007 12:44 AM
SQL in VBA recordset returns 0 records, in query returns 2 SteveClarkson Modules & VBA 11 11-13-2006 05:07 AM
Query Returns No Records davidbodhi Queries 1 08-30-2005 10:25 AM
If query returns no records... gfcaim Queries 11 07-09-2004 07:52 AM




All times are GMT -8. The time now is 03:05 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World