SQL and VBA

[cimabue]

I’m working with access 2003 and I have a command button that runs a query called "Test" and opens it as a Pivot (graphic):


Private Sub Comando196_Click ()
On Error GoTo Err_Comando196_Click
Dim stDocName As String
stDocName = "Test"
DoCmd.OpenQuery stDocName, acViewPivotChart, acEdit
Exit_Comando196_Click:
exit Sub
Err_Comando196_Click:
MsgBox Err.Description
Resume Exit_Comando196_Click
end Sub

The Query (Test) is :
SELECT Annata.

, Annata.Prodotto, Annata.Costo, Annata.Data
FROM Annata;


[/SIZE][/FONT][/COLOR][COLOR=#333333][FONT=Arial][SIZE=3]It works fine[/SIZE][/FONT][/COLOR][COLOR=#333333][FONT=Arial][SIZE=3].
I want to put in the instructions of the command button the SQL query.
Can anyone help me?
thanks [/SIZE][/FONT][/COLOR]
[COLOR=#333333][FONT=Arial][SIZE=3]Sergio[/SIZE][/FONT][/COLOR]</SPAN>

 

[John Big Booty]

It would look like;

Private Sub Comando196_Click ()
On Error GoTo Err_Comando196_Click

Dim stSQL As String

stSQL = "SELECT Annata.Code, Annata.Prodotto, Annata.Costo, Annata.Data " & _
"FROM Annata;"

DoCmd.[URL="http://msdn.microsoft.com/en-us/library/ff194626.aspx"]RunSQL[/URL] stSQL

Exit_Comando196_Click:
exit Sub

Err_Comando196_Click:
MsgBox Err.Description
Resume Exit_Comando196_Click

end Sub

 

[cimabue]

I tried and I have This Error:
A RunSQL action requires an argument consisting of an SQL statement...

If I remember ... runsql is not correct with SELECT ...but only with UPDATE or INSERT

 

[John Big Booty]

Why not use the Select query/SQL as the Control Source for a form and open the form to view the results?

 

[cimabue]

Excuse for my poor english..
I tried your suggestion but I cannot open form and results as.... ViewPivotChart.

I used your suggestion in all my reports and I insert SQL in record origin so to delete every queries.
Now I have one table,
only one query and
many reports.

I have to delete the last query but it is the only one to show the results as ViewPivotChart and I cannot able to find a solution
Ciao
Sergio

 

[cimabue]

Ops... my mistake,
Your suggestion is right.
Thanks.
Sergio