Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-08-2019, 08:54 AM   #16
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 Minty View Post
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
That's a little bit above my ability, can you give me some sample code? Or point me in the direction where i can figure it out?

hllary is offline   Reply With Quote
Old 10-08-2019, 08:56 AM   #17
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 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:
Public Sub fncBetweenDates(Byval ctl As Control)
    If IsNull(Me.DateMin) Then
        MsgBox "Enter starting date"
        Exit Function
    End If

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

    SELECT CASE ctl.Name
    CASE "cbCATracker1"
        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
 
    CASE "CbDrawing1"     
        If (Forms![2ndfrm_AddEditView]![CbDrawing1] = True) And DCount("*", "SearchDrawingUpdatedOn_Bt_qry") > 0 Then
            DoCmd.OpenQuery "SearchDrawingUpdatedOn_Bt_qry"
        Else
            DoCmd.Close acQuery, "SearchDrawingUpdatedOn_Bt_qry"
            MsgBox ("No records for that date range2")

        End If
    
    CASE "CbTool1"
        If (Forms![2ndfrm_AddEditView]![CbTool1] = True) And DCount("*", "SearchToolDeliveryDatedOn_Bt_qry") > 0 Then
            DoCmd.OpenQuery "SearchToolDeliveryDatedOn_Bt_qry"
        Else
            DoCmd.Close acQuery, "SearchToolDeliveryDatedOn_Bt_qry"
            MsgBox ("No records for that date range3")

        End If

    END SELECT    
End Function
On each checkbox Click Event, (type next to Event):
Code:
for cbCATracker1 checkbox
=fncBetweenDates([cbCATracker1])

for CbDrawing1checkbox
=fncBetweenDates([CbDrawing1])

for CbTool1 checkbox
=fncBetweenDates([CbTool1])
__________________
"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:57 AM   #18
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,880
Thanks: 57
Thanked 1,286 Times in 1,267 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 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
Hi. Glad to hear you got it working. So, this is doing exactly what you wanted, correct?

__________________
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 online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
hllary (10-08-2019)
Old 10-08-2019, 09:15 AM   #19
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. Glad to hear you got it working. So, this is doing exactly what you wanted, correct?
It's not very clean but doing a loop is above my head right now.
hllary is offline   Reply With Quote
Old 10-08-2019, 09:32 AM   #20
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,253
Thanks: 15
Thanked 1,592 Times in 1,512 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Close Queries if no records

Allowing users to interact with naked queries is scary dangerous. Users should only ever interact with forms and reports where you have control over what they do. There is nothing you can do to stop users from modifying or deleting data when you just open a query for them to work with.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
The Following User Says Thank You to Pat Hartman For This Useful Post:
hllary (10-08-2019)
Old 10-08-2019, 09:34 AM   #21
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 Pat Hartman View Post
Allowing users to interact with naked queries is scary dangerous. Users should only ever interact with forms and reports where you have control over what they do. There is nothing you can do to stop users from modifying or deleting data when you just open a query for them to work with.
The queries are not editable.
hllary is offline   Reply With Quote
Old 10-08-2019, 11:05 AM   #22
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,333
Thanks: 40
Thanked 3,670 Times in 3,538 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

Quote:
The queries are not editable.
what happens if you right click on the top bar of the query window? Do you not see?
Attached Images
File Type: png Capture.PNG (8.0 KB, 19 views)


__________________
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
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 09:35 AM.


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