Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-08-2019, 07:11 AM   #1
hllary
Newly Registered User
 
Join Date: Sep 2019
Posts: 29
Thanks: 20
Thanked 0 Times in 0 Posts
hllary is on a distinguished road
Close Queries if no records

I've seen multiple post of closing a query if it is blank. But my code checks to see if a check box is clicked then moves to the next box. If a check box has been clicked it opens a query. I'm trying to get it to close the query then return a msgbox if the query is blank.
I get the first msgbox, but when I click other boxes I still get the messages for other boxes.
I've tried playing with ElseIf but that did not help.

Code:
Private Sub BetweenDates_Click()
    If IsNull(Me.DateMin) Then
    MsgBox "Enter starting date"
    End If

    If IsNull(Me.DateMax) Then
    MsgBox "Enter ending date"
    End If

    If (Forms![2ndfrm_AddEditView]![CbCATracker1] = True) Then
    DoCmd.OpenQuery "SearchCaTrackerCLUpdatedOn_Bt_qry"
    End If
    
    If DCount("*", "SearchCaTrackerCLUpdatedOn_Bt_qry") < 1 Then
    DoCmd.Close acQuery, "SearchCaTrackerCLUpdatedOn_Bt_qry"
    MsgBox ("No records for that date range1")
    End If
          
    If (Forms![2ndfrm_AddEditView]![CbDrawing1] = True) Then
    DoCmd.OpenQuery "SearchDrawingUpdatedOn_Bt_qry"
    End If
    
    If DCount("*", "SearchDrawingUpdatedOn_Bt_qry") < 1 Then
    DoCmd.Close acQuery, "SearchDrawingUpdatedOn_Bt_qry"
    MsgBox ("No records for that date range2")
    End If
    
     
    If (Forms![2ndfrm_AddEditView]![CbTool1] = True) Then
    DoCmd.OpenQuery "SearchToolDeliveryDatedOn_Bt_qry"
    End If
    
    If DCount("*", "SearchToolDeliveryDatedOn_Bt_qry") < 1 Then
    DoCmd.Close acQuery, "SearchToolDeliveryDatedOn_Bt_qry"
    MsgBox ("No records for that date range3")
    End If

hllary is offline   Reply With Quote
Old 10-08-2019, 07:14 AM   #2
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,653
Thanks: 58
Thanked 1,461 Times in 1,442 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Close Queries if no records

Hi. You can do a DCount() first, so you don't have to open and close an empty query. Are you opening action queries, by any chance?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
hllary (10-08-2019)
Old 10-08-2019, 07:16 AM   #3
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,350
Thanks: 40
Thanked 3,671 Times in 3,539 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Close Queries if no records

how about something like

Code:
if dcount("*","SearchCaTrackerCLUpdatedOn_Bt_qry")> 0 then
    DoCmd.OpenQuery "SearchCaTrackerCLUpdatedOn_Bt_qry"
else
    msgbox "No results to return in " & "SearchCaTrackerCLUpdatedOn_Bt_qry"
end if

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
hllary (10-08-2019)
Old 10-08-2019, 07:25 AM   #4
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Close Queries if no records

Code:
    If (Forms![2ndfrm_AddEditView]![CbCATracker1] = True) And _
               Nz(DCount("*", "SearchCaTrackerCLUpdatedOn_Bt_qry"), 0) > 0 Then
    DoCmd.OpenQuery "SearchCaTrackerCLUpdatedOn_Bt_qry"
    End If
…
…
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
hllary (10-08-2019)
Old 10-08-2019, 07:28 AM   #5
hllary
Newly Registered User
 
Join Date: Sep 2019
Posts: 29
Thanks: 20
Thanked 0 Times in 0 Posts
hllary is on a distinguished road
Re: Close Queries if no records

I tried but i get a message for each. how do I get it to not return a message if the query was not called?

Code:
If (Forms![2ndfrm_AddEditView]![CbCATracker1] = True) And DCount("*", "SearchCaTrackerCLUpdatedOn_Bt_qry") > 0 Then
        DoCmd.OpenQuery "SearchCaTrackerCLUpdatedOn_Bt_qry"
    Else
        MsgBox "No results to return in table"
    End If
If (Forms![2ndfrm_AddEditView]![CbDrawing1] = True) And DCount("*", "SearchDrawingUpdatedOn_Bt_qry") > 0 Then
        DoCmd.OpenQuery "SearchDrawingUpdatedOn_Bt_qry"
    Else
        MsgBox "No results to return in table"
    End If
hllary is offline   Reply With Quote
Old 10-08-2019, 07:30 AM   #6
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,653
Thanks: 58
Thanked 1,461 Times in 1,442 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Close Queries if no records

Quote:
Originally Posted by hllary View Post
I tried but i get a message for each. how do I get it to not return a message if the query was not called?

Code:
If (Forms![2ndfrm_AddEditView]![CbCATracker1] = True) And DCount("*", "SearchCaTrackerCLUpdatedOn_Bt_qry") > 0 Then
        DoCmd.OpenQuery "SearchCaTrackerCLUpdatedOn_Bt_qry"
    Else
        MsgBox "No results to return in table"
    End If
If (Forms![2ndfrm_AddEditView]![CbDrawing1] = True) And DCount("*", "SearchDrawingUpdatedOn_Bt_qry") > 0 Then
        DoCmd.OpenQuery "SearchDrawingUpdatedOn_Bt_qry"
    Else
        MsgBox "No results to return in table"
    End If
Hi. Try just taking out the Else branches including the MsgBox lines.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 10-08-2019, 07:35 AM   #7
hllary
Newly Registered User
 
Join Date: Sep 2019
Posts: 29
Thanks: 20
Thanked 0 Times in 0 Posts
hllary is on a distinguished road
Re: Close Queries if no records

Quote:
Originally Posted by theDBguy View Post
Hi. Try just taking out the Else branches including the MsgBox lines.
Then how would I get a message when there is no records?

hllary is offline   Reply With Quote
Old 10-08-2019, 07:40 AM   #8
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,653
Thanks: 58
Thanked 1,461 Times in 1,442 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Close Queries if no records

Quote:
Originally Posted by hllary View Post
Then how would I get a message when there is no records?
Hi. I don't understand what you're asking. You were getting a message when there's no records and you asked how to skip the message. Now, you're asking how to get the message back? Please remember we can't see what's happening on your end, so we may not be as clear to what's going on to understand your questions if you don't explain it completely. So, what exactly did you want to happen?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 10-08-2019, 07:46 AM   #9
hllary
Newly Registered User
 
Join Date: Sep 2019
Posts: 29
Thanks: 20
Thanked 0 Times in 0 Posts
hllary is on a distinguished road
Re: Close Queries if no records

Quote:
Originally Posted by theDBguy View Post
Hi. I don't understand what you're asking. You were getting a message when there's no records and you asked how to skip the message. Now, you're asking how to get the message back? Please remember we can't see what's happening on your end, so we may not be as clear to what's going on to understand your questions if you don't explain it completely. So, what exactly did you want to happen?
Sorry for the confusion. If there are records a query will open but if there are none then it will not open. If a query does not open the user might think the button is broken. So i would like to have a message if none of the queries opened.
hllary is offline   Reply With Quote
Old 10-08-2019, 07:53 AM   #10
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,653
Thanks: 58
Thanked 1,461 Times in 1,442 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Close Queries if no records

Quote:
Originally Posted by hllary View Post
Sorry for the confusion. If there are records a query will open but if there are none then it will not open. If a query does not open the user might think the button is broken. So i would like to have a message if none of the queries opened.
Okay, so isn't that what's already happening before? You were getting a message when the query doesn't open. If so, what did you mean when you asked:
Quote:
... how do I get it to not return a message if the query was not called?
That sounds like you were asking not to show a message when there are no records, which is why the query was not called.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
hllary (10-08-2019)
Old 10-08-2019, 08:02 AM   #11
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Close Queries if no records

put each code to its own checkbox:
Code:
Private Sub CbCATracker1_Click()
    If IsNull(Me.DateMin) Then
    MsgBox "Enter starting date"
    End If

    If IsNull(Me.DateMax) Then
    MsgBox "Enter ending date"
    End If

    If (Forms![2ndfrm_AddEditView]![CbCATracker1] = True) And DCount("*", "SearchCaTrackerCLUpdatedOn_Bt_qry") > 0 Then
    DoCmd.OpenQuery "SearchCaTrackerCLUpdatedOn_Bt_qry"

    Else
        DoCmd.Close acQuery, "SearchCaTrackerCLUpdatedOn_Bt_qry"
        MsgBox ("No records for that date range1")

    End If
    
End Sub
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
hllary (10-08-2019)
Old 10-08-2019, 08:06 AM   #12
hllary
Newly Registered User
 
Join Date: Sep 2019
Posts: 29
Thanks: 20
Thanked 0 Times in 0 Posts
hllary is on a distinguished road
Re: Close Queries if no records

Before when i would search for different types of data and I would get a message for every query that had <1 records.

I have five queries. I would like to get a message if the DCount is <1. One message, not a message for each query.

Looking at the problem another way, if the DoCmd.OpenQuery did not happen then i would like a message.

Last edited by hllary; 10-08-2019 at 08:21 AM.
hllary is offline   Reply With Quote
Old 10-08-2019, 08:36 AM   #13
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,653
Thanks: 58
Thanked 1,461 Times in 1,442 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Close Queries if no records

Quote:
Originally Posted by hllary View Post
Before when i would search for different types of data and I would get a message for every query that had <1 records.

I have five queries. I would like to get a message if the DCount is <1. One message, not a message for each query.

Looking at the problem another way, if the DoCmd.OpenQuery did not happen then i would like a message.
Hi. I'm not sure that's clear enough. You say, "if the DoCmd.OpenQuery did not happen then i would like a message," but you have five DoCmd.OpenQuery. So, are you saying if all five did not open, you want five messages, or just one?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 10-08-2019, 08:37 AM   #14
hllary
Newly Registered User
 
Join Date: Sep 2019
Posts: 29
Thanks: 20
Thanked 0 Times in 0 Posts
hllary is on a distinguished road
Re: Close Queries if no records

i ended up using the code below for each checkbox. I hoped there was an little bit simpler code than having a code for each checkbox. But oh well thanks for help.

Code:
If (DCount("*", "SearchToolValidationDate_Bt_qry") < 1) And (Forms![2ndfrm_AddEditView]![CbCATracker1] = True) Then
    MsgBox "No data within range"
    
    End If
hllary is offline   Reply With Quote
Old 10-08-2019, 08:45 AM   #15
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,439
Thanks: 166
Thanked 1,738 Times in 1,707 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Close Queries if no records

In each if>then loop, set a flag to indicate if there no records for that query and add the query name to a string with VbCr after it. Obviously not real code but ;

At the end of all the checks
If your flag is true then
Msg box that "There are no records for " & your message string & " Queries selected "
end if

__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
hllary (10-08-2019)
Reply

Tags
acccess 2016 , access query criteria , vba access 2016

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Close A Form If No Records Learn2010 Forms 2 04-04-2011 10:13 AM
Run Queries on Close of DB without Form??!! isetea General 14 11-05-2006 06:28 PM
Close form with no records tfaiers Forms 8 05-20-2005 03:53 AM
[SOLVED] Queries saving on close even if I don't want to Kevin in NZ Queries 1 10-13-2004 09:57 PM




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


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

Featured Forum post


Sponsored Links


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