Close Queries if no records (1 Viewer)

hllary

Registered User.
Local time
Today, 16:05
Joined
Sep 23, 2019
Messages
80
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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:05
Joined
Oct 29, 2018
Messages
21,358
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?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 23:05
Joined
Feb 19, 2013
Messages
16,553
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
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:05
Joined
May 7, 2009
Messages
19,169
Code:
    If (Forms![2ndfrm_AddEditView]![CbCATracker1] = True) And _
               Nz(DCount("*", "SearchCaTrackerCLUpdatedOn_Bt_qry"), 0) > 0 Then
    DoCmd.OpenQuery "SearchCaTrackerCLUpdatedOn_Bt_qry"
    End If
…
…
 

hllary

Registered User.
Local time
Today, 16:05
Joined
Sep 23, 2019
Messages
80
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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:05
Joined
Oct 29, 2018
Messages
21,358
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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:05
Joined
Oct 29, 2018
Messages
21,358
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?
 

hllary

Registered User.
Local time
Today, 16:05
Joined
Sep 23, 2019
Messages
80
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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:05
Joined
Oct 29, 2018
Messages
21,358
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:
... 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.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:05
Joined
May 7, 2009
Messages
19,169
put each code to its own checkbox:
Code:
[COLOR="Blue"]Private Sub CbCATracker1_Click()[/COLOR]
    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
 

hllary

Registered User.
Local time
Today, 16:05
Joined
Sep 23, 2019
Messages
80
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:

theDBguy

I’m here to help
Staff member
Local time
Today, 16:05
Joined
Oct 29, 2018
Messages
21,358
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?
 

hllary

Registered User.
Local time
Today, 16:05
Joined
Sep 23, 2019
Messages
80
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
 

Minty

AWF VIP
Local time
Today, 23:05
Joined
Jul 26, 2013
Messages
10,355
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
 

hllary

Registered User.
Local time
Today, 16:05
Joined
Sep 23, 2019
Messages
80
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?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:05
Joined
May 7, 2009
Messages
19,169
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])
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:05
Joined
Oct 29, 2018
Messages
21,358
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?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:05
Joined
Feb 19, 2002
Messages
42,976
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.
 

Users who are viewing this thread

Top Bottom