SQL DataSource error...

[ItIsHardToProgram]

I have the following Query running a Form. Deproj is the subform of Dépense, the childlink is FDnumero and the parentlink is No_F_Dep

SELECT Dépense.*, Sum(DepProj.soustotal) AS Tototal FROM Dépense INNER JOIN DepProj ON Dépense.no_f_dep=DepProj.FDNumero;

Apparantly when I try running this query it does not detect the Fields in Dépense..... I can't see whats wrong in my query, could some one try to enlighten me please?

Thank you all.

 

[PHV]

You can't use an aggregate function (Sum) and * in the same SELECT clause.
Furthermore you need a GROUP BY clause.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ItIsHardToProgram]

Is what I want to achieve possible without going through a recordset?

 

[PHV]

Is what I want to achieve possible
What do you want to achieve ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ItIsHardToProgram]

I have a subform That has Many dates with different bills, I want to sum up all the bills of that subform to 1 txtbox in my main form.

 

[PHV]

One way is to create a textbox in the Footer section of the subform with a ControlSource like this:
=Sum([name of amount field in the underlaying query])

Then create a textbox in the mainform bound to the above textbox.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ItIsHardToProgram]

Right I guess that would be an easy way, maybe not the best but quite reliable, Thank you PHV.

 

[ItIsHardToProgram]

Access gives me an error message when I try to send the value of the footer txtbox to a txtbox in my main form.....

Saying you can't assign the current value to the txtbox, the txtbox is unbounded in my main form and goes like this:

Me.Parent.Form![Stotal] = Forms![DepProjSF]![Sommation].Value

Ive tried every single syntax and it still won't work....

I can't even assign "sommation" (which is my footer txtbox)
to a string in vb......

Very frustrating indeed

 

[ItIsHardToProgram]

Oh I forgot mentioning my subform was a Datasheet.

 

[ItIsHardToProgram]

I got rid of the error and fixed the problem but a problem still remains, When I try to update my txtbox in my main form, it seems that The Sum is only calculated if I add a new record on top of the other one.....

 

[ItIsHardToProgram]

Nevermind refresh did the job

 

[ItIsHardToProgram]

Or not, My total txtbox is always 1 record late...

 

[ItIsHardToProgram]

I have tried using this with no result:

Private sub Sommation_afterupdate()
    While Me.Parent.Form![Stotal] <> Me.[Sommation]
        Refresh
        Me.Parent.Form![Stotal] = Me.[Sommation]
    Wend
End sub

 

[PHV]

In the Current and AfterUpdate event procedures of the subform:
Me![Sommation].Recalc
Me.Parent.Form![Stotal] = Me![Sommation]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ItIsHardToProgram]

Private Sub Form_Current()
[highlight]Me![Sommation].Recalc[/highlight]
Me.Parent.Form![Stotal] = Me![Sommation]
End Sub

Error msg: Méthode non géré par cette objet erreur 438

Means that the object can't Recalc....

 

[PHV]

OOps, sorry, hit submit too fast.
Private Sub Form_Current()
Me.Parent.Form![Stotal] = Me![Sommation]
End Sub

Private Sub Form_AfterUpdate()
Me.Recalc
Me.Parent.Form![Stotal] = Me![Sommation]
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ItIsHardToProgram]

It worked like a charm.

Thanks, your the best, even a programmer I knew could not pass the problem without a recordset.

 

[ItIsHardToProgram]

A new problem as appeared.... I have now put my subtotal cell as the addition of the other cells. Since I have done that (changed directly in the source of the cell) It won't clone itself on the main form... how is that possible....


?????

 

[ItIsHardToProgram]

I have isolated where the problem was, the problem is Sommation does not calculate the sum of SousTotal for the current record..... it does not calculate it at all....

 

[ItIsHardToProgram]

I have tried passing through VB still with no results......