Update Main Form and SubForm into Table

[Mehvan]

Hello Everyone

I have SaleInvoice MainForm related with SubForm , and I have another table its name is Details , I want when I update data in MainForm and SubForm , and then click on "cmdButton" automatically update data in table name Details


I made code INSERT INTO , but Update isn't work

This is INSERT INTO :

 If DLookup("[id]", "details") = [SaleID] Then    Exit Sub
    
    Else
    DoCmd.RunSQL "INSERT INTO Details ( ID, sDate, CustomerName, ItemsName, Qy, Price, Total )SELECT Sale.SaleID, Sale.SaleDate,     Sale.CustName, Sub.Items, Sub.Qy, Sub.Price, Sub.Total FROM Sale INNER JOIN Sub ON Sale.SaleID = Sub.SubID"

 End If

UPDATE :

    DoCmd.SetWarnings False        CurrentDb.Execute "UPDATE  Details " & _
        "SET ID =" & Me.SaleID & _
        ", sDate = #" & Me.SaleDate & "#" & _
        ", CustomerName ='" & Me.CustName & "'" & _
        ", ItemsName ='" & Me.Sub!Items & "'" & _
        ", Qy = " & Me.Sub!Qy & _
        ", Price =" & Me.Sub!Price & _
        ", Total =" & Me.Sub!Total & _
        "WHERE ID = " & Me.SaleID


    DoCmd.SetWarnings True

Can someone help me , please ?

[June7]

Why do you need to use INSERT INTO? Are your forms not bound to tables?

[Mehvan]

related with another table
I want INSERT ( Sale , Purchase , Return Purchase , Return Sale , Income ) INTO Details Table
I want to Make " Customer account statement " , When one Customer come to me and want his account statement , I will give him Details Table

[Minty]

But you have all that information already stored in your order tables, simply query them from the original source, don't duplicate data.

[Mehvan]

you're right , but it's difficult I be able to create query all these 5 Invoice in one query .

[Minty]

It shouldn't be, simply group by the customers unique account number?
What difficulty are you having with querying the data?

[Mehvan]

I want to create that Table the same this below picture



This is a small example , I want to do it by using INSERT And UPDATE
Can you correct my UPDATE code , please ?

[Minty]

You are heading for a world of pain trying to store your information in this fashion.
The rolling amount must be calculated in a query or a report and not stored, as there is no way to guarantee the amount is correct if you make or correct a mistake.

By calculating it from your source data you ensure it is always accurate.
Copying it in this way and format is against all good database design advice.

Good luck with your project.

[GinaWhipp]

Hmm, no what you want is a Running Sum query (Google that) that will be as you pictured above.

[June7]

Easiest to calculate running sum on report. Textbox on report has RunningSum property.

[GinaWhipp]

@June,

Not sure that would work in this case as it's more like a check register. The math has to happen across before down.

[June7]

It can be done. RunningSum on expression. =Nz([Qy])*Nz([Price])-Nz([IncomeAmount])

Shouldn't that last value be 80?

[Mehvan]

My English isn't very good , because of that maybe you didn't understand What I need to do

I want to create Customer Account : for example
if someone come to me and he wants his account , and told me , I want to give me , my account , What I bought in this year with ItemsName as appear in photo

when he bought something such as : SaleInvoice ( SaleID , Date , CustName , ItemName , Qy , Price , Total , Net ) , when He return something I want automatically appear under the same Table (SaleID , Date , CustName , ItemName , Qy , Price , Total , Net) just will say , this is return and automatically decreases of his account
I mean anything related with that Customer appear in one page ( Sale , Return , Stock and Income ) with them details .

I know to create Calculate
I will use the same this code , I used in another project

Total: (select(Sum( [SumOfFirstTotal] ) + Sum( [SumOfSaleTotal] ) - Sum( [SumOfReturnTotal] ) - Sum( [SumOfComeTotal] ) - Sum( [SumOfSaleCash] )) from [Q_CustomersAccounts] where C1.[AccountDate] >= [AccountDate] and C1.[CustName] = [CustName] )

Just I want to correct my code in Update
INSERT INTO , work without problem , but I have a problem with UPDATE

When I change something in Invoice and click on Update button , change it .

[June7]

UPDATE action using an aggregate query as source - won't work, period.

If you can calculate the totals for update then you can calculate whenever needed.

Saving aggregate data (also called dependent data - data that is calculated from other data) is normally not advisable. Review http://allenbrowne.com/AppInventory.html

[Mehvan]

Can you correct this code , please ?

    DoCmd.SetWarnings False        CurrentDb.Execute "UPDATE  Details " & _
        "SET ID =" & Me.SaleID & "" & _
        ", sDate = #" & Me.SaleDate & "#" & _
        ", CustomerName ='" & Me.CustName & "'" & _
        ", ItemsName ='" & Me.Sub!Items & "'" & _
        ", Qy = " & Me.Sub!Qy & "" & _
        ", Price =" & Me.Sub!Price & "" & _
        ", Total =" & Me.Sub!Total & "" & _
        ",FROM Sale INNER JOIN Sub ON Sale.SaleID = Sub.SubID"


    DoCmd.SetWarnings True