Hvordan kan jeg lave en requery, så data også opdaters i formular

me.requery

In the sub forms after updated event

Docmd.RunSQL "UPDATE [Master] set SomeField = Dsum("DetailField", "DetailTable")"

Or something like that

And you may need to include the "WHERE" parameter in the dsum if the detail records are related (which they very likeley will be) to the main form record

Concerning the comment you placed in you rother question ..

OrderID, sum(price pr. orderline), .....

When you have calculated fields in a query it will make the data read only.

You can get around this by having a field in the main table like you suggest here, or you could try having an unbound field on the main form an deither set this in code or maybe using th eexpression builder (right click in the Control source property and then choose build)

So if I have an unbound field in the main form how do I set the value in code? I have tried the expression builder but it doens't work. I have tried something like

= sum(detail-form.field)

but I just get an error.

It simportant that the syntax is correct.

I use this and it works
=DSum("[Antal]";"[OrdreLinier]";"[OrdreID] = " & [OrdreID])

Back to the Docmd.RunSQL "UPDATE [Master] set SomeField = Dsum("DetailField", "DetailTable")"

I'm getting closer, but have problems with the where ....

I guess it should be something like "[detailfield] = ????" but should it be the masterforms field? I am in the subform on an after_update.

Your updateing the master table so you have to use a field in the master table. The master and detail tables are related so it will be (normally) the primary key field in the main table.



..;"OrderID = " & [OrderID])

THis bit is the field from the detail form which I also assume has the saem name as the primary key in the main order table.
& [OrderID])

Oh yes syntax is important :-)

So now I have a string like this:

Txt = "UPDATE [Order] set OrderSum = DSum("[Antal]";"[OrdreLinier]";"[OrdreID] = " & [OrdreID])

but there's a problem whit the ".
Can you help me (again)?

If you can send the dB to me its maybe easier for me to see the problem.

ekspertenATsanthell.dk

AT = @

You could also try

Txt = "UPDATE [Order] set OrderSum = " & DSum("[Antal]";"[OrdreLinier]";"[OrdreID] = " & [OrdreID])

and you should still look at the open questions you have, otherwise some users wont be prepared tohelp you in future.

have you got it working?

I have tried to close the questions, but some don't have this 'accept' so how do I close?

I think the problem is in the string itself. When I type it exactly as

Txt = "UPDATE [Order] set OrderSum = DSum("[Antal]";"[OrdreLinier]";"[OrdreID] = " & [OrdreID])

it is not accepted as a string. It is wrong use of "? perhaps ' instead?

So no, it's not working :(

You only need to close when your finished with the question, either when youhave received an anwer whichworks, or you find a solution yourself, or maybe when no answers have been helpeful. In this case you can just place an answer yourself and accpet it, then you get your points back.

Also if users have only placed a comment then you cant accept it, you need to get an anwer first. If your happy with any of the comments then you can get the user to place an answer, or again you can place on yourself to accept.

Can you send me your dB?

14/01-2008 18:27:19

Thanks, hope you could use the answer.

Yes, part of the problem is solved and then ... a new one ;-)

So now I can update, but it seems the update is only in the database, not on screen. Can I make a requery/refersh??? on the master formula from the after-update in the subformula?

You can try something like this (not tested) just after the SQL update

Me.parent.requery

I have tried and it doesn't work :( I have a query with the orderID and sum(orderlines) so I can check what happens.

I run the DoCmd.RunSQL "update ... DSUM(...)" but it doesn't save the update. (In Oracle I would use either af 'post' or a 'commit')

The new value in the subformula IS updated OK.

I have used this code in another database. There the fun is, that all! resords are updated, as if the where-clause was empty. So lots of fun ;-)

You said 15/01-2008 18:27:17
"So now I can update, but it seems the update is only in the database, not on screen."

Is the dB updated?
Do you get an error or doesnt it update the form?

I don't get an error, and the field is not updated. The field in the subformula is updated.

Ie. OrderLines.Price is updated but Orders.TotalPrice is not.

"..and the field is not updated."

Thge field on the form or in the dB?

Can you send me your dB?

14/01-2008 18:27:19