Solved using dsum in update statement.

[Ashisht76]

1) Table 1 : TblOrder,
2) Table 2 : SubTblGreyFabricOrder,
3) Common field : "OrderID" in TblOrder and "OrderNumber" in SubTblGreyFabricOrder.
4) TblOrder has field : GreyPOQuantity which is in one to many relation with GreyPurchaseQualtity of Table SubTblGreyFabricOrder.
5) I want sum of GreyPurchaseQualtity and updated in GreyPOQuantity.
6) i have update statement that works with dummy number (5555 as of now) in stead of having dsum formula which is as below.
DoCmd.RunSQL "UPDATE TblOrder SET TblOrder.GreyPOQuantity = 5555 where TblOrder.OrderID = " & Forms![SubFrm_SubTblGreyFabricOrder]![OrderNumber]
7) I also have dsum formula that does the job
var = DSum("[GreyPurchaseQuantity]", "SubTblGreyFabricOrder", "[OrderNumber] = " & Forms!SubFrm_SubTblGreyFabricOrder!OrderNumber)
Debug.Print var
8 ) but when i put dsum in update statement it does not work.
DoCmd.RunSQL "UPDATE TblOrder SET TblOrder.GreyPOQuantity = DSum("[GreyPurchaseQuantity]", "SubTblGreyFabricOrder", "[OrderNumber] = " & Forms!SubFrm_SubTblGreyFabricOrder!OrderNumber) where TblOrder.OrderID = " & Forms![SubFrm_SubTblGreyFabricOrder]![OrderNumber]

Please help, i have tried everything.

 

[oleronesoftwares]

8 ) but when i put dsum in update statement it does not work.
DoCmd.RunSQL "UPDATE TblOrder SET TblOrder.GreyPOQuantity = DSum("[GreyPurchaseQuantity]", "SubTblGreyFabricOrder", "[OrderNumber] = " & Forms!SubFrm_SubTblGreyFabricOrder!OrderNumber) where TblOrder.OrderID = " & Forms![SubFrm_SubTblGreyFabricOrder]![OrderNumber]

Does it give an error message or it just doesn't update?

 

[Ashisht76]

Does it give an error message or it just doesn't update?

syntax error, also forgot to mention that this update is on after update event of from of table SubTblGreyFabricOrder and catches the OrderNumber field.

 

[SHANEMAC51]

dim sq,sw,
sw= DSum("[GreyPurchaseQuantity]","SubTblGreyFabricOrder","[OrderNumber] =" & Forms!SubFrm_SubTblGreyFabricOrder!OrderNumber)


sq= "UPDATE TblOrder 
sq=sq & " SET TblOrder.GreyPOQuantity =" & sw


DoCmd.RunSQL sq

 

[Ashisht76]

dim sq,sw,
sw= DSum("[GreyPurchaseQuantity]","SubTblGreyFabricOrder","[OrderNumber] =" & Forms!SubFrm_SubTblGreyFabricOrder!OrderNumber)


sq= "UPDATE TblOrder
sq=sq & " SET TblOrder.GreyPOQuantity =" & sw


DoCmd.RunSQL sq

Its working big Thanks! I have to change it a bit to include where condition in update statement. For anybodies interest I am putting it down here.

Dim sq, sw, su
su = "where TblOrder.OrderID = " & Forms![SubFrm_SubTblGreyFabricOrder]![OrderNumber]
sw = DSum("[GreyPurchaseQuantity]", "SubTblGreyFabricOrder", "[OrderNumber] =" & Forms!SubFrm_SubTblGreyFabricOrder!OrderNumber)


sq = "UPDATE TblOrder "
sq = sq & " SET TblOrder.GreyPOQuantity =" & sw & " " & su


DoCmd.RunSQL sq

 

[Ashisht76]

The reason your original query doesn't work is because it violates normal forms and so isn't implemented by the database engine. It is almost always wrong to store calculated values. Calculations should normally be performed at the point in time where you need to display the data.

You might want to do some reading on Normalization.

Thanks! I understood that I should try to use query to get calculated values and use those queries whenever I need them. And I am also curious above reading about Normalization, I will find any material available on that subject however I will be highly obliged if you can provide any link on subject.

The primary idea of updating table here was to remove access queries from database as it is very confusing to have lot of queries such that at some point it becomes difficult to understand why a particular query exist and what it does!

I am in process to get all my queries in vba codes now and as I am not very good in making SQL statement I am coming across hurdles.

Once again thanks for taking out time for my help.

 

[SHANEMAC51]

Right now I am in the process of getting all my queries in vba codes, and since I am not very good at creating SQL statements, I am running into obstacles.

in any database there is an ARCHIVIST tool (tab - Working with databases), with which you can output the texts of all queries to a WORD file