vhart rowsource based on recordset

[diegomarino]

hi


this is my daily topic
the question today is:
i have a form based on a query in my vba.
then i have a chart in this form that is based on an union query based on the query that's the recordsource of the main form
i'm trying to set the rowsource of the chart to this union query based on the main form query. it obiouvsly do not work, that's the code

Private Sub Form_Load()   
   Dim Source As String
   Dim SourceRst As Recordset
    Source = "SELECT Trattative.IDtrattativa, Candidati.IDcandidato, Candidati.NomeCognome, Candidati.[RAL/Fatturato], Candidati.[penale economica], Trattative.[RAL richiesta], FormatPercent(([RAL richiesta]-[RAL/Fatturato])/[RAL/Fatturato],0) AS [Incremento  percentuale], Candidati.[corrispettivo patto], FormatCurrency([ral richiesta]*15/100,0) AS [Corr patto nuovo], [RAL richiesta]*5 AS [RAL 5 anni], [Penale economica]*2 AS [Penale * 2], [RAL 5 anni]+([corr patto nuovo]*5)+[Penale * 2] AS [RAL 5 anni + corr patto nuovo + Patto * 2], ([RAL 5 anni]+([corr patto nuovo]*5)+[Penale * 2])*0.4 AS [Costo INPS], [RAL 5 anni + corr patto nuovo + Patto * 2]+[Costo inps] AS [Costo totale], Candidati.[PTF trasferibile personale], Candidati.[Redditività ptf trasferibile], ([ptf trasferibile personale]*1000000)*[redditività ptf trasferibile]/100 AS [Mint annuo], [mint annuo]*5 AS [Mint 5 anni], Round([Costo totale]/[mint 5 anni],4) AS Costi, " & vbCrLf & _
   "Round(1-[costo totale]/[mint 5 anni],4) AS Ricavi, FormatNumber([Costo totale]/[mint annuo]*12,0) AS Payback " & vbCrLf & _
   "FROM Candidati INNER JOIN Trattative ON Candidati.IDcandidato = Trattative.CandidatoID " & vbCrLf & _
   "WHERE (((Trattative.IDtrattativa)= " & Me.OpenArgs & ")) " & vbCrLf & _
   "ORDER BY Candidati.NomeCognome, Trattative.[RAL richiesta] DESC;"
   Me.RecordSource = Source
   Set SourceRst = Me.RecordsetClone
   Me.AnalisiOperazioneGrafico.RowSource = "SELECT IDtrattativa, NomeCognome, [Ricavi] AS Valore, ""Redditività"" AS CostiRicavi FROM [SourceRst] UNION SELECT  IDtrattativa, NomeCognome, [costi], ""Costi"" FROM  [SourceRst];"
End Sub

[ranman256]

put the sql into a query.
you can add a title field in the query: Title: MyQry

then the code is:

Me.AnalisiOperazioneGrafico.RowSource = "qsMyQuery"


the sql for the query would need the full path to form objects:
forms!fMyForm!OpenArg

[diegomarino]

thanks very much
i'm trying the querydef method, cause i don't want query as objects in my database...too messy

Private Sub Form_Load()   Dim Db As DAO.Database
   Set Db = CurrentDb
   Dim newSQL As String
   Dim Qdf As DAO.QueryDef
    newSQL = "SELECT Trattative.IDtrattativa, Candidati.IDcandidato, Candidati.NomeCognome, Candidati.[RAL/Fatturato], Candidati.[penale economica], Trattative.[RAL richiesta], FormatPercent(([RAL richiesta]-[RAL/Fatturato])/[RAL/Fatturato],0) AS [Incremento percentuale], Candidati.[corrispettivo patto], FormatCurrency([ral richiesta]*15/100,0) AS [Corr patto nuovo], [RAL richiesta]*5 AS [RAL 5 anni], [Penale economica]*2 AS [Penale * 2], [RAL 5 anni]+([corr patto nuovo]*5)+[Penale * 2] AS [RAL 5 anni + corr patto nuovo + Patto * 2], ([RAL 5 anni]+([corr patto nuovo]*5)+[Penale * 2])*0.4 AS [Costo INPS], [RAL 5 anni + corr patto nuovo + Patto * 2]+[Costo inps] AS [Costo totale], Candidati.[PTF trasferibile personale], Candidati.[Redditività ptf trasferibile], ([ptf trasferibile personale]*1000000)*[redditività ptf trasferibile]/100 AS [Mint annuo], [mint annuo]*5 AS [Mint 5 anni], Round([Costo totale]/[mint 5 anni],4) AS Costi, " & vbCrLf & _
   "Round(1-[costo totale]/[mint 5 anni],4) AS Ricavi, FormatNumber([Costo totale]/[mint annuo]*12,0) AS Payback " & vbCrLf & _
   "FROM Candidati INNER JOIN Trattative ON Candidati.IDcandidato = Trattative.CandidatoID " & vbCrLf & _
   "WHERE (((Trattative.IDtrattativa)= " & Me.OpenArgs & ")) " & vbCrLf & _
   "ORDER BY Candidati.NomeCognome, Trattative.[RAL richiesta] DESC;"
   Set Qdf = Db.CreateQueryDef("TempAnOp", newSQL)
   DoCmd.OpenQuery "TempAnOp", acViewNormal, acReadOnly
End Sub

i try to open my temporary query but i have the errore "object already exists"

[diegomarino]

this is the complete code
work perfectly

Private Sub Form_Load()   Dim Db As DAO.Database
   Set Db = CurrentDb
   Dim newSql As String
   Dim qdf As DAO.QueryDef
    newSql = "SELECT Trattative.IDtrattativa, Candidati.IDcandidato, Candidati.NomeCognome, Candidati.[RAL/Fatturato], Candidati.[penale economica], Trattative.[RAL richiesta], FormatPercent(([RAL richiesta]-[RAL/Fatturato])/[RAL/Fatturato],0) AS [Incremento percentuale], Candidati.[corrispettivo patto], FormatCurrency([ral richiesta]*15/100,0) AS [Corr patto nuovo], [RAL richiesta]*5 AS [RAL 5 anni], [Penale economica]*2 AS [Penale * 2], [RAL 5 anni]+([corr patto nuovo]*5)+[Penale * 2] AS [RAL 5 anni + corr patto nuovo + Patto * 2], ([RAL 5 anni]+([corr patto nuovo]*5)+[Penale * 2])*0.4 AS [Costo INPS], [RAL 5 anni + corr patto nuovo + Patto * 2]+[Costo inps] AS [Costo totale], Candidati.[PTF trasferibile personale], Candidati.[Redditività ptf trasferibile], ([ptf trasferibile personale]*1000000)*[redditività ptf trasferibile]/100 AS [Mint annuo], [mint annuo]*5 AS [Mint 5 anni], Round([Costo totale]/[mint 5 anni],4) AS Costi, " & vbCrLf & _
   "Round(1-[costo totale]/[mint 5 anni],4) AS Ricavi, FormatNumber([Costo totale]/[mint annuo]*12,0) AS Payback " & vbCrLf & _
   "FROM Candidati INNER JOIN Trattative ON Candidati.IDcandidato = Trattative.CandidatoID " & vbCrLf & _
   "WHERE (((Trattative.IDtrattativa)= " & Me.OpenArgs & ")) " & vbCrLf & _
   "ORDER BY Candidati.NomeCognome, Trattative.[RAL richiesta] DESC;"
   Me.RecordSource = newSql
   For Each qdf In CurrentDb.QueryDefs
       If qdf.Name = "TempAnOp" Then
          CurrentDb.QueryDefs.Delete "TempAnOp"
          Exit For
       End If
   Next
   Set qdf = Db.CreateQueryDef("TempAnOp", newSql)
   DoCmd.OpenQuery "TempAnOp", acViewNormal, acReadOnly
   Me.AnalisiOperazioneGrafico.RowSource = "SELECT IDtrattativa, NomeCognome, [Ricavi] AS Valore, ""Redditività"" AS CostiRicavi FROM [TempAnOp] UNION SELECT  IDtrattativa, NomeCognome, [costi], ""Costi"" FROM  [TempAnOp];"
End Sub

if someone is interested i can give some explanation

however, thanks again for the wonderful support