Executing Query in VBA

itsmedd

Registered User.
Local time
Yesterday, 23:02
Joined
Sep 19, 2007
Messages
30
Hi, I am a beginner in access. I am trying to execute the below query through VBA on a click event on a Command Button in a Form. I am taking input parameter from Form, based on which I want the query result( dynamic Query Parameter).
I getting the attached error message

Please help me out in executing this query in VBA. An example would be of great help. Thanks in advance..
 

Attachments

  • VBA Queryerr.JPG
    VBA Queryerr.JPG
    19.6 KB · Views: 154
Hey Ken ,, Thanks for your quick response.. PFB my small select case code..

Private Sub View_Click()
Dim StrMon, StrQry As String
StrMon = [Forms]![Month]![MonthCMB].[Value]
MsgBox StrMon

Select Case StrMon
Case "Jan"
StrQry = "SELECT Software.BXG_Desc, Software.Application, Software.Vendor_Name_Normalized, Software.[Product/Expense Desc], Software.[Project Manager], Sum(Software.Jan) AS SumOfJan FROM Software GROUP BY Software.BXG_Desc, Software.Application, Software.Vendor_Name_Normalized, Software.[Product/Expense Desc], Software.[Project Manager];"

Case "Feb"
StrQry = "SELECT Software.BXG_Desc, Software.Application, Software.Vendor_Name_Normalized, Software.[Product/Expense Desc], Software.[Project Manager], Sum(Software.Feb) AS SumOfFeb FROM Software GROUP BY Software.BXG_Desc, Software.Application, Software.Vendor_Name_Normalized, Software.[Product/Expense Desc], Software.[Project Manager];"

Case "Mar"
DoCmd.OpenQuery "SELECT Software.BXG_Desc, Software.Application, Software.Vendor_Name_Normalized, Software.[Product/Expense Desc], Software.[Project Manager], Software.Mar FROM Software;"

Case "Apr"
DoCmd.OpenQuery "SELECT Software.BXG_Desc, Software.Application, Software.Vendor_Name_Normalized, Software.[Product/Expense Desc], Software.[Project Manager], Software.Apr FROM Software;"

Case "May"
DoCmd.OpenQuery "SELECT Software.BXG_Desc, Software.Application, Software.Vendor_Name_Normalized, Software.[Product/Expense Desc], Software.[Project Manager], Software.May FROM Software;"

Case "Jun"
DoCmd.OpenQuery "SELECT Software.BXG_Desc, Software.Application, Software.Vendor_Name_Normalized, Software.[Product/Expense Desc], Software.[Project Manager], Software.Jun FROM Software;"

Case "Jul"
DoCmd.OpenQuery "SELECT Software.BXG_Desc, Software.Application, Software.Vendor_Name_Normalized, Software.[Product/Expense Desc], Software.[Project Manager], Software.Jul FROM Software;"

Case "Aug"
DoCmd.OpenQuery "SELECT Software.BXG_Desc, Software.Application, Software.Vendor_Name_Normalized, Software.[Product/Expense Desc], Software.[Project Manager], Software.Aug FROM Software;"

Case "Sep"
DoCmd.OpenQuery "SELECT Software.BXG_Desc, Software.Application, Software.Vendor_Name_Normalized, Software.[Product/Expense Desc], Software.[Project Manager], Software.Sep FROM Software;"

Case "Oct"

DoCmd.OpenQuery "SELECT Software.BXG_Desc, Software.Application, Software.Vendor_Name_Normalized, Software.[Product/Expense Desc], Software.[Project Manager], Software.Oct FROM Software;"

Case "Nov"
DoCmd.OpenQuery "SELECT Software.BXG_Desc, Software.Application, Software.Vendor_Name_Normalized, Software.[Product/Expense Desc], Software.[Project Manager], Software.Nov FROM Software;"

Case "Dec"
DoCmd.OpenQuery "SELECT Software.BXG_Desc, Software.Application, Software.Vendor_Name_Normalized, Software.[Product/Expense Desc], Software.[Project Manager], Software.Dec FROM Software;"

Case Else
MsgBox " Didnt find a match"

End Select
End Sub
 
My first suggest would be to take:

SELECT Software.BXG_Desc, Software.Application, Software.Vendor_Name_Normalized, Software.[Product/Expense Desc], Software.[Project Manager], Software.Mar FROM Software;

and paste it in the query builder sql view and see if it will execute.

:)
ken
 
docmd.openquery will open a query saved in your db. You are passing an SQL statement instead of a query name.
 
So he should be doing a RunSQL instead, right?

???
ken
 
Ken.. i works perfectly fine..i had checked it before i started of with code. Is there something that needs to be done with the Object ???
 
See Keith's suggestion -

And it may work better with something like:

Code:
Private Sub View_Click()

	Dim StrMon, StrQry As String

	StrMon = [Forms]![Month]![MonthCMB].[Value]

	MsgBox StrMon

	StrQry = "SELECT Software.BXG_Desc," & _
			 "Software.Application, " & _
			 "Software.Vendor_Name_Normalized," & _
			 "Software.[Product/Expense Desc]," & _
			 "Software.[Project Manager]," & _
			 "Sum(Software." & StrMon & ") AS SumOf" & StrMon & " " & _
			 "FROM Software " & _
		 "GROUP BY Software.BXG_Desc," & _
			 "Software.Application," & _
			 " Software.Vendor_Name_Normalized," & _
			 "Software.[Product/Expense Desc]," & _
			 "Software.[Project Manager];"

	DoCmd.RunSql StrQry


End Sub


???
ken
 
Keith and Ken,
I am getting the attached error message when i try with the suggested code.
 

Attachments

  • VBA  err.JPG
    VBA err.JPG
    14.6 KB · Views: 137
try the below code. Ken I modified your code to create a saved query definition to the db with the SQL statement.

Code:
Private Sub View_Click()
    Dim qryDef As New DAO.QueryDef

    Dim StrMon, StrQry As String

    StrMon = [Forms]![Month]![MonthCMB].[Value]

    MsgBox StrMon

    StrQry = "SELECT Software.BXG_Desc," & _
             "Software.Application, " & _
             "Software.Vendor_Name_Normalized," & _
             "Software.[Product/Expense Desc]," & _
             "Software.[Project Manager]," & _
             "Sum(Software." & StrMon & ") AS SumOf" & StrMon & " " & _
             "FROM Software " & _
         "GROUP BY Software.BXG_Desc," & _
             "Software.Application," & _
             " Software.Vendor_Name_Normalized," & _
             "Software.[Product/Expense Desc]," & _
             "Software.[Project Manager];"

    qryDef.SQL = StrQry
    
    qryDef.Name = "qryExample"
    
    qryDef.Close
    DoCmd.OpenQuery "qryExample"


End Sub
 
Keith,
its giving me an err message Microsoft Access cannot find the object 'qryExample'. Any thing that i need to do other than this query. Appreciate all your help on this...
 
Sorry for asking a first type question after the fact; Are you wanting to open the table in datasheet view?

(fyi - my RunSQL did not work because it will only execute action queries... )
 
Try the below code, this should work for you.


Code:
Private Sub View_Click()
    Dim qryDef As DAO.QueryDef

    Dim StrMon, StrQry As String

    StrMon =[Forms]![Month]![MonthCMB].[Value]

    MsgBox StrMon

    StrQry = "SELECT Software.BXG_Desc," & _
             "Software.Application, " & _
             "Software.Vendor_Name_Normalized," & _
             "Software.[Product/Expense Desc]," & _
             "Software.[Project Manager]," & _
             "Sum(Software." & StrMon & ") AS SumOf" & StrMon & " " & _
             "FROM Software " & _
         "GROUP BY Software.BXG_Desc," & _
             "Software.Application," & _
             " Software.Vendor_Name_Normalized," & _
             "Software.[Product/Expense Desc]," & _
             "Software.[Project Manager];"

    Set qryDef = CurrentDb.CreateQueryDef("qryExample", StrQry)
    qryDef.Close
    DoCmd.OpenQuery "qryExample"


End Sub
 
Thanks a lot Keith.. this worked out perfect for me.. and i got to know were is was making mistake...

Thanks for your suggestions Ken.
 

Users who are viewing this thread

Back
Top Bottom