Solved using dsum in update statement. (1 Viewer)

[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

 

[Pat Hartman]

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.

 

[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

 

[Pat Hartman]

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.

Why? If you don't know how to write SQL statements, use the QBE. That is what it is for. I've been writing SQL since the early 80's. I think I know how to write SQL. That's more than 40 years and I use the QBE. It is a real time saver.

Embedding your SQL in your VBA is not an advanced method. It is a personal preference. Having spent 30 writing embedded SQL in COBOL, I don't need the practice. Why would I ever want to type and format all my queries in code when I have a tool that will build them using point and click. Call me crazy or lazy but I use the QBE and querydefs for probably 90% of the queries I write. Sometimes the QBE is awkward and so I switch to SQL view. As an SQL editor, the QBE leaves a lot to be desired so people who are used to better SQL editors turn up their noses at the QBE and never even consider the speed at which you can build a query with the graphic interface using point and click.

Querydefs are much easier to test than embedded SQL. You don't have to run the code. You just open the query. The QBE catches your typos as you make them, not at runtime. An additional benefit is the ability to stack queries. So I build basic queries that perform calculations and/or concatenations and save them. Then I can join to those partial queries to make use of what I already have. The nice thing about this is that I can change one base query and affect a bunch of other queries that depend on it. I don't have to search through code modules to find all the queries I need to modify when I make a change.

PS, it's vba code not codes. Codes are lists of values like zip codes and area codes and ICD10 codes. Code is what you write in a computer language such as VBA or C or whatever.