Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Check if query is there and then open it 1

Status
Not open for further replies.
Triacona

Triacona

Technical User
Joined
Jun 11, 2009
Messages
462
Location
GB
Dear All,

Thanks for a great forum![smile]

My question is as such...
I have a form that has a few buttons...
One of them is Run Query.
The run query button has the following code.
Code: 
Private Sub CpRunQuery_Click()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim SQL As String
    Dim Lbx As ListBox, idx
    Dim Pack As String
    Dim stDocName As String
    stDocName = ListCP ' might need to get removed

    If IsNull(txtStartDate And txtEndDate) Then
             
        If txtEndDate.Visible = True Then
                        
                DoCmd.RunMacro "MsgBoxNoDate"
                    
        Else: Select Case ListCP.ItemData(ListCP.ListIndex)

        Case "CpSotLetter", "CpSotFsaXml", "SotView", "MissingRecordsCheckFsaXmlAndCpListResult", "CpCmbResults", "CpFoodPremTypeSearch"
        DoCmd.OpenQuery stDocName, acNormal, acEdit
    '------------------------------------------------------------------
    Case "CpSotCertificate"
    DoCmd.RunMacro "MsgBxNoQuery"
    
        Case "CpListResults"
 
        Set db = CurrentDb
        Set qdf = db.QueryDefs("CpListResults")
        Set Lbx = ListCpXresult
 
        SQL = "SELECT UNI7LIVE_CPINFO.REFVAL, UNI7LIVE_CPINFO.TRADEAS, UNI7LIVE_CPINFO.ADDRESS AS Add, " & _
            "UNI7LIVE_CPINFO.CPUSE AS MainUse, CpUseCodes.CODETEXT AS MainUseDsc, UNI7LIVE_CPINFO.CONTACT, " & _
            "UNI7LIVE_CPUSES.CPUSE AS AllUses, CpUsesCodes.CODETEXT AS AllUsesDsc " & _
            "FROM ((UNI7LIVE_CPINFO " & _
            "INNER JOIN CpUseCodes ON UNI7LIVE_CPINFO.CPUSE = CpUseCodes.CODEVALUE) " & _
            "INNER JOIN UNI7LIVE_CPUSES ON UNI7LIVE_CPINFO.KEYVAL = UNI7LIVE_CPUSES.PKEYVAL) " & _
            "INNER JOIN CpUseCodes AS CpUsesCodes ON UNI7LIVE_CPUSES.CPUSE = CpUsesCodes.CODEVALUE " & _
            "GROUP BY UNI7LIVE_CPINFO.REFVAL, UNI7LIVE_CPINFO.TRADEAS, UNI7LIVE_CPINFO.ADDRESS, UNI7LIVE_CPINFO.CPUSE, " & _
            "CpUseCodes.CODETEXT , UNI7LIVE_CPINFO.CONTACT, UNI7LIVE_CPUSES.CPUSE, " & _
            "CpUsesCodes.CODETEXT, UNI7LIVE_CPINFO.CLOSEDD "
            
        For Each idx In Lbx.ItemsSelected
            If Pack <> "" Then
        
                Pack = Pack & " AND ((UNI7LIVE_CPINFO.CLOSEDD) Is Null) "
                Pack = Pack & " OR ((UNI7LIVE_CPUSES.CPUSE) LIKE '" & Lbx.Column(0, idx) & "')"
            Else
        
                Pack = " HAVING ((UNI7LIVE_CPUSES.CPUSE) LIKE'" & Lbx.Column(0, idx) & "') "
        

            End If
        Next

        If Pack <> "" Then
            Pack = Pack & " AND ((UNI7LIVE_CPINFO.CLOSEDD) Is Null) " & _
                      " ORDER BY UNI7LIVE_CPINFO.TRADEAS;"
            qdf.SQL = SQL & Pack
        Else
            Pack = Pack & " ORDER BY UNI7LIVE_CPINFO.TRADEAS;" & _
            qdf.SQL = SQL & Pack
        End If
   
        Debug.Print SQL & Pack

        DoCmd.OpenQuery ListCP, acViewNormal
        DoCmd.Maximize
      
        qdf.Close
   
    
   
        Set qdf = Nothing
        Set db = Nothing
        Set Lbx = Nothing
   
'-----------------------------------------------------------------------------------
        Case "CpListResultsInsLiab"
   
   
        Set db = CurrentDb
        Set qdf = db.QueryDefs("CpListResultsInsLiab")
        Set Lbx = CpListInspLiab

        SQL = "SELECT UNI7LIVE_CPINFO.REFVAL, UNI7LIVE_CPINFO.TRADEAS, UNI7LIVE_CPINFO.ADDRESS AS [Add], " & _
        "UNI7LIVE_CPINFO.CPUSE AS MainUse, UNI7LIVE_CPINFO.CONTACT, UNI7LIVE_CPINSPLIAB.INSPTYPE, " & _
        "UNI7LIVE_CPINSPLIAB.INSPECTION_TYPE, UNI7LIVE_CPINSPLIAB.CPFOODMAIN, CmbCpMainUseLiab.CODETEXT " & _
        "FROM CmbCpMainUseLiab INNER JOIN (UNI7LIVE_CPINFO INNER JOIN UNI7LIVE_CPINSPLIAB " & _
        "ON UNI7LIVE_CPINFO.KEYVAL = UNI7LIVE_CPINSPLIAB.PKEYVAL) ON CmbCpMainUseLiab.CODEVALUE = UNI7LIVE_CPINSPLIAB.CPFOODMAIN " & _
        "GROUP BY UNI7LIVE_CPINFO.REFVAL, UNI7LIVE_CPINFO.TRADEAS, UNI7LIVE_CPINFO.ADDRESS, " & _
        "UNI7LIVE_CPINFO.CPUSE, UNI7LIVE_CPINFO.CONTACT, UNI7LIVE_CPINSPLIAB.INSPTYPE, UNI7LIVE_CPINSPLIAB.INSPECTION_TYPE, " & _
        "UNI7LIVE_CPINSPLIAB.CPFOODMAIN, CmbCpMainUseLiab.CODETEXT, UNI7LIVE_CPINFO.CLOSEDD "
 
    
    
        For Each idx In Lbx.ItemsSelected
            If Pack <> "" Then
       
                Pack = Pack & " AND ((UNI7LIVE_CPINFO.CLOSEDD) Is Null) "
                Pack = Pack & " OR ((UNI7LIVE_CPINSPLIAB.CPFOODMAIN) LIKE '" & Lbx.Column(0, idx) & "')" ' UNI7LIVE_CPINSPLIAB.CPFOODMAIN
            Else
       
                Pack = " HAVING ((UNI7LIVE_CPINSPLIAB.CPFOODMAIN) LIKE'" & Lbx.Column(0, idx) & "') "
        

            End If
    
        Next

        If Pack <> "" Then

            Pack = Pack & " AND ((UNI7LIVE_CPINFO.CLOSEDD) Is Null) " & _
                      " ORDER BY UNI7LIVE_CPINFO.TRADEAS;"
            qdf.SQL = SQL & Pack
       
        Else
        
            Pack = Pack & " ORDER BY UNI7LIVE_CPINFO.TRADEAS;" & _
            qdf.SQL = SQL & Pack
        
        End If

        Debug.Print SQL & Pack

        DoCmd.OpenQuery ListCP, acViewNormal
        DoCmd.Maximize
      
        qdf.Close
   
    
   
    Set qdf = Nothing
    Set db = Nothing
    Set Lbx = Nothing
End Select
    
End If
End If


End Sub

The code basically opens certain queries.

My question is is there a way to test if the query exisits.

I then want to display the message box as above (MsgBxNoQuery).

I have done a crude version of this using the case statements.
But would like to, instead of always modifying the list, test if the query exists.

Any help will be greatly appreciated.[smile]

Thanks!

Thank you,

Kind regards

Triacona
 
Sort by date Sort by votes
You may use this function:
Code: 
Function QueryExists(strQueryName As String) As Boolean
On Error Resume Next
QueryExists = (CurrentDb.QueryDefs(strQueryName).Name = strQueryName)
End Function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Dear PV,

Thank you very much for your reply![2thumbsup]

Where would I put the code and how would I use it within my code?

I have been trying to place your code for a while...

Thank you![smile]

Thank you,

Kind regards

Triacona
 
Upvote 0 Downvote
Where would I put the code
In a standard code module

how would I use it within my code
I've just replied to this question:
My question is is there a way to test if the query exisits

A starting point:
If Not QueryExists(ListCP.ItemData(ListCP.ListIndex)) Then
MsgBox ListCP.ItemData(ListCP.ListIndex) & " doesn't exist"
end If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Dear PHV,

Thank you very much for your help again![bigsmile][thumbsup]

It now works!!
Code: 
Private Sub CpRunQuery_Click()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim SQL As String
    Dim Lbx As ListBox, idx
    Dim Pack As String
    Dim stDocName As String
    stDocName = ListCP ' might need to get removed
    
If IsNull(txtStartDate And txtEndDate) Then

             
    If txtEndDate.Visible = True Then
               
        DoCmd.RunMacro "MsgBoxNoDate"

    ElseIf Not QueryExists(ListCP.ItemData(ListCP.ListIndex)) Then
        msgbox ListCP.ItemData(ListCP.ListIndex) & " doesn't exist"

Else: Select Case ListCP.ItemData(ListCP.ListIndex)

        Case "CpSotLetter", "CpSotFsaXml", "SotView", "MissingRecordsCheckFsaXmlAndCpListResult", "CpCmbResults", "CpFoodPremTypeSearch"
        DoCmd.OpenQuery stDocName, acNormal, acEdit
    '------------------------------------------------------------------
        'Case "CpSotCertificate"
        'DoCmd.RunMacro "MsgBxNoQuery"
    
        Case "CpListResults"
 
        Set db = CurrentDb
        Set qdf = db.QueryDefs("CpListResults")
        Set Lbx = ListCpXresult
 
        SQL = "SELECT UNI7LIVE_CPINFO.REFVAL, UNI7LIVE_CPINFO.TRADEAS, UNI7LIVE_CPINFO.ADDRESS AS Add, " & _
            "UNI7LIVE_CPINFO.CPUSE AS MainUse, CpUseCodes.CODETEXT AS MainUseDsc, UNI7LIVE_CPINFO.CONTACT, " & _
            "UNI7LIVE_CPUSES.CPUSE AS AllUses, CpUsesCodes.CODETEXT AS AllUsesDsc " & _
            "FROM ((UNI7LIVE_CPINFO " & _
            "INNER JOIN CpUseCodes ON UNI7LIVE_CPINFO.CPUSE = CpUseCodes.CODEVALUE) " & _
            "INNER JOIN UNI7LIVE_CPUSES ON UNI7LIVE_CPINFO.KEYVAL = UNI7LIVE_CPUSES.PKEYVAL) " & _
            "INNER JOIN CpUseCodes AS CpUsesCodes ON UNI7LIVE_CPUSES.CPUSE = CpUsesCodes.CODEVALUE " & _
            "GROUP BY UNI7LIVE_CPINFO.REFVAL, UNI7LIVE_CPINFO.TRADEAS, UNI7LIVE_CPINFO.ADDRESS, UNI7LIVE_CPINFO.CPUSE, " & _
            "CpUseCodes.CODETEXT , UNI7LIVE_CPINFO.CONTACT, UNI7LIVE_CPUSES.CPUSE, " & _
            "CpUsesCodes.CODETEXT, UNI7LIVE_CPINFO.CLOSEDD "
            
        For Each idx In Lbx.ItemsSelected
            If Pack <> "" Then
        
                Pack = Pack & " AND ((UNI7LIVE_CPINFO.CLOSEDD) Is Null) "
                Pack = Pack & " OR ((UNI7LIVE_CPUSES.CPUSE) LIKE '" & Lbx.Column(0, idx) & "')"
            Else
        
                Pack = " HAVING ((UNI7LIVE_CPUSES.CPUSE) LIKE'" & Lbx.Column(0, idx) & "') "
        

            End If
        Next

        If Pack <> "" Then
            Pack = Pack & " AND ((UNI7LIVE_CPINFO.CLOSEDD) Is Null) " & _
                      " ORDER BY UNI7LIVE_CPINFO.TRADEAS;"
            qdf.SQL = SQL & Pack
        Else
            Pack = Pack & " ORDER BY UNI7LIVE_CPINFO.TRADEAS;" & _
            qdf.SQL = SQL & Pack
        End If
   
        Debug.Print SQL & Pack

        DoCmd.OpenQuery ListCP, acViewNormal
        DoCmd.Maximize
      
        qdf.Close
   
    
   
        Set qdf = Nothing
        Set db = Nothing
        Set Lbx = Nothing
   
'-----------------------------------------------------------------------------------
        Case "CpListResultsInsLiab"
   
   
        Set db = CurrentDb
        Set qdf = db.QueryDefs("CpListResultsInsLiab")
        Set Lbx = CpListInspLiab

        SQL = "SELECT UNI7LIVE_CPINFO.REFVAL, UNI7LIVE_CPINFO.TRADEAS, UNI7LIVE_CPINFO.ADDRESS AS [Add], " & _
        "UNI7LIVE_CPINFO.CPUSE AS MainUse, UNI7LIVE_CPINFO.CONTACT, UNI7LIVE_CPINSPLIAB.INSPTYPE, " & _
        "UNI7LIVE_CPINSPLIAB.INSPECTION_TYPE, UNI7LIVE_CPINSPLIAB.CPFOODMAIN, CmbCpMainUseLiab.CODETEXT " & _
        "FROM CmbCpMainUseLiab INNER JOIN (UNI7LIVE_CPINFO INNER JOIN UNI7LIVE_CPINSPLIAB " & _
        "ON UNI7LIVE_CPINFO.KEYVAL = UNI7LIVE_CPINSPLIAB.PKEYVAL) ON CmbCpMainUseLiab.CODEVALUE = UNI7LIVE_CPINSPLIAB.CPFOODMAIN " & _
        "GROUP BY UNI7LIVE_CPINFO.REFVAL, UNI7LIVE_CPINFO.TRADEAS, UNI7LIVE_CPINFO.ADDRESS, " & _
        "UNI7LIVE_CPINFO.CPUSE, UNI7LIVE_CPINFO.CONTACT, UNI7LIVE_CPINSPLIAB.INSPTYPE, UNI7LIVE_CPINSPLIAB.INSPECTION_TYPE, " & _
        "UNI7LIVE_CPINSPLIAB.CPFOODMAIN, CmbCpMainUseLiab.CODETEXT, UNI7LIVE_CPINFO.CLOSEDD "
 
    
    
        For Each idx In Lbx.ItemsSelected
            If Pack <> "" Then
       
                Pack = Pack & " AND ((UNI7LIVE_CPINFO.CLOSEDD) Is Null) "
                Pack = Pack & " OR ((UNI7LIVE_CPINSPLIAB.CPFOODMAIN) LIKE '" & Lbx.Column(0, idx) & "')" ' UNI7LIVE_CPINSPLIAB.CPFOODMAIN
            Else
       
                Pack = " HAVING ((UNI7LIVE_CPINSPLIAB.CPFOODMAIN) LIKE'" & Lbx.Column(0, idx) & "') "
        

            End If
    
        Next

        If Pack <> "" Then

            Pack = Pack & " AND ((UNI7LIVE_CPINFO.CLOSEDD) Is Null) " & _
                      " ORDER BY UNI7LIVE_CPINFO.TRADEAS;"
            qdf.SQL = SQL & Pack
       
        Else
        
            Pack = Pack & " ORDER BY UNI7LIVE_CPINFO.TRADEAS;" & _
            qdf.SQL = SQL & Pack
        
        End If

        Debug.Print SQL & Pack

        DoCmd.OpenQuery ListCP, acViewNormal
        DoCmd.Maximize
      
        qdf.Close
   
    
   
    Set qdf = Nothing
    Set db = Nothing
    Set Lbx = Nothing
    End Select
    
End If
End If

End Sub

Thanks again!
Have a star!

Thank you,

Kind regards

Triacona
 
Upvote 0 Downvote
Dear PHV,

I have had some more fun! [smile]
I have also copied and modified your function to incorporate a report test...
CODE ON BUTTON
Code: 
[blue]ElseIf Not[/blue] ReportExists(ListCP.ItemData(ListCP.ListIndex)) [blue]Then[/blue]
                            msgbox ListCP.ItemData(ListCP.ListIndex) & " report doesn't exist, RUN the QUERY!"

CODE In FUNCTIONS MODULE
Code: 
[blue]Function [/blue]ReportExists(stDocName [blue]As String[/blue]) [blue]As Boolean[/blue]
[blue]On Error Resume Next[/blue]
ReportExists = (Reports(stDocName).NAME = stDocName)
[blue]End Function[/blue]
[code]

Thanks again PHV! [2thumbsup][star]

Thank you,

Kind regards

Triacona
 
Upvote 0 Downvote
Hi PHV,

A above code seems to run it for every ListCp item! DOH!

Please help [sadeyes]

Thank you![smile]

Thank you,

Kind regards

Triacona
 
Upvote 0 Downvote
Dear PHV,

Here is the code I used for the report button.
Any help you give is much appreciated [smile]
Code: 
Private Sub CpRunReport_Click()
On Error GoTo Err_CpRunReport_Click
    
    Dim stDocName As String
    stDocName = ListCP

        If IsNull(txtStartDate And txtEndDate) Then
        
            If txtEndDate.Visible = True Then
             
                DoCmd.RunMacro "MsgBoxNoDate"
                
            ElseIf Not ReportExists(ListCP.ItemData(ListCP.ListIndex)) Then
                            msgbox ListCP.ItemData(ListCP.ListIndex) & " report doesn't exist, RUN the QUERY!"
           
               
         Else: Select Case ListCP.ItemData(ListCP.ListIndex)
         
            Case "CpSotCertificate", "CpSotFsaXml", "CpSotLetter"
            DoCmd.OpenReport stDocName, acPreview
            
            
            End Select
        End If
        End If
Code: 
Function ReportExists(stDocName As String) As Boolean
On Error Resume Next
ReportExists = (Reports(stDocName).NAME = stDocName)
End Function
Thanks [smile]

Thank you,

Kind regards

Triacona
 
Upvote 0 Downvote
Code: 
Function ReportExists(stDocName As String) As Boolean
On Error Resume Next
ReportExists = (CurrentProject.AllReports(stDocName).Name = stDocName)
End Function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Dear PHV,

Phenomenal![2thumbsup][bigsmile]

Thank you so very much for your help! I really appreciate it!

Thank you,

Kind regards

Triacona
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

TrekBiker
  • Locked
  • Question Question
This is the source query for what
Replies
12
Views
896
dhookom
dhookom
MacroAlan
  • Locked
  • Question Question
Navigation Bar is too short to read
Replies
5
Views
846
dhookom
dhookom
TrekBiker
  • Locked
  • Question Question
Running query with parameter from Inputbox
Replies
8
Views
1K
TrekBiker
TrekBiker
rdpress
  • Locked
  • Article Article
Determine if Close Button is Used to Close Form
Replies
3
Views
430
Andrzejek
Andrzejek
TheresAlwaysAWay
  • Locked
  • Question Question
&quot;There isn't enough memory to perform this operation...&quot; error after upgrading from Access
Replies
2
Views
579
Axworthy
Axworthy

Part and Inventory Search

Sponsor

Back
Top