group by problem on form

  • Thread starter Thread starter kderaedt
  • Start date Start date
K

kderaedt

Hi,

If have a problem with my Continuous Forms and record source.

Record source:
SELECT Fac_ander.FactuurID,
Sum([fac_ander_detail.eenheidsprijs]*[fac_ander_detail.aantal]) AS bedrag,
Fac_ander.Betaald
FROM Fac_ander INNER JOIN Fac_ander_detail ON Fac_ander.FactuurID =
Fac_ander_detail.FactuurID
GROUP BY Fac_ander.FactuurID, Fac_ander.Betaald;

In this form I have the field 'bedrag' and the field 'betaald'. I want to
change the value of this in my
form. With the queries above this is not possible.

fac_ander is a table with a primary key factuurID.
In the fac_ander_detail table there is a also a factuurID, this are linked
(there are more entries of factuurID at the fac_ander_detail table).
The fac_ander.betaald is a Yes/No field.
The problem is that I can't change this fac_ander.betaald field?
Is there anyone that knows a solution for this problem?

Thanks

Karel
 
How can I make a public function? Are there examples available?

Thanks

Karel

Emilia Maxim said:
kderaedt said:
Record source:
SELECT Fac_ander.FactuurID,
Sum([fac_ander_detail.eenheidsprijs]*[fac_ander_detail.aantal]) AS bedrag,
Fac_ander.Betaald
FROM Fac_ander INNER JOIN Fac_ander_detail ON Fac_ander.FactuurID =
Fac_ander_detail.FactuurID
GROUP BY Fac_ander.FactuurID, Fac_ander.Betaald;
Click to expand...
fac_ander is a table with a primary key factuurID.
In the fac_ander_detail table there is a also a factuurID, this are linked
(there are more entries of factuurID at the fac_ander_detail table).
The fac_ander.betaald is a Yes/No field.
The problem is that I can't change this fac_ander.betaald field?
Is there anyone that knows a solution for this problem?
Click to expand...

Karel,

a query with GROUP BY is not updatable; it consolidates several table
rows to one according to the GROUP BY clause, so it would not know
which original table row to update.

You can use a normal SELECT statement based only on the table
fac_ander as Recordsource and use an unbound calculated text box to
show 'bedrag'. Because you need data from another table, best bet
would be to write a public function in the form's module returning the
'bedrag' and then write in the Controlsource of the text field:
=MyFunctionForBedrag()

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de
Click to expand...
 
Back
Top