Subform vs main form calculation

[fadone]

I have a subform on the main form with one to many relationship.I just want one of the field on the subform to update the corresponding field on the mainform,as follows:
Field names; On the main form : Total Amount Paid
On the subform:Amount Paid
Every time user enters Amount Paid on the subform,it should add up to the Total Amount Paid on the main form...


Help please!!!!!!!!!!

[matthewspatrick]

I have a subform on the main form with one to many relationship.I just want one of the field on the subform to update the corresponding field on the mainform,as follows:
Field names; On the main form : Total Amount Paid
On the subform:Amount Paid
Every time user enters Amount Paid on the subform,it should add up to the Total Amount Paid on the main form...
Help please!!!!!!!!!!

Try using an AfterUpdate event sub on the subform's AmountPaid control that forces the main form to requery:

Private Sub MyControl_AfterUpdate()

    Forms("MainForm").Requery

End Sub

[Mxcsquared]

Hey I'm having the same problem and I tried to figure out what to do with this code but I have no idea what... I have "paid" in the subform and then a "Total amount paid" on the form but I can't seem to get anything to work. I'm fairly new at this advanced access stuff and was wondering if you could help me out with fairly easy-to-understand information? I really do appreciate this

Thanks so much
--Mike

[Jim]

if the field on the main form is a bound field (not Calculated) then in the subform AfterUpdate put the following:
forms(me.parent.name)![Total Amount Paid]=forms(me.parent.name)![Total Amount Paid] + me![Amount Paid]

if is calculated then put:
me.refresh

[Mxcsquared]

*sigh* I must be an idiot but where do I put it? In the Control source of the "Cost" (My true values are "Cost" for the subform and "Total Cost" for the main form) And if so, what is the exact? I tried your method but, after taking out the = sign so that it works in the first place it simple gives me #Name?

Thanks for helping me out!

[Jim]

Is Total Cost a bound field? That is, is it in a table?

[Mxcsquared]

Not at the moment. It is simply supposed to hold the equation that will add up the costs of all services.

[Jim]

Can you send me the mdb and I will see if I can gvie you a solution?

[Mxcsquared]

Absolutely! I didn't know where you want it sent so I simply put it in a accessible folder on my server... here's the address:

http://216.203.126.38/db/


Thanks so much for taking the time to do this Jim!!
--Mike

[Jim]

Give me a email address and I will send you the modified database.

[fadone]

Thanks Jim it worked fine though one drawback with that solution was it keeps adding the amount to the mainform even if you just corrected the same figure on the subform...eg when you correct 5000 for 15000 it will add 5000 + 15000 instead of putting only the corrected amount ie 15000.
any idea????

[Mxcsquared]

mxcsquared@gmail.com

Thanks ^_^!!

[Jim]

I emailed the revised mdb. It was hard for me to tell exactly what you were trying to do. I took a guess so see if that is what you were looking for. You really need the Customer ID in the Receipt table.

[Jim]

fadone
put the following statement at on the top, right after to last Option statement and before the first Pirvate Sub statement:
Dim holdCost as long '(or maybe Currency depending on how you defined it)

Then on Got Focus event for Amount Paid:
holdCost=Me![Amount Paid]

Then in the After Update event for Amount Paid add to what I gave you before:
IF holdcost <> me![Amount Paid] and holdcost > 0 then
Me(parent.name)![Total Amount Paid]=Me(parent.name)![Total Amount Paid] - holdcost
End If

As you can see we just subtract hold cost if it was changed and not new.
Now you may have a problem if you are selecting differernt customers on the main form. You will need to add code to make Holdcost=0 when you change customers>

All of that said, I would nomally do this with a continuous subform that list all the Paid Amounts . Then I would put Total Amount Paid below form footer and put it's Control Source to =Sum([Amount Paid]).
I hope this helps!

[fadone]

Jim,
Thanks very much for your time and consideration.Your
previous solution worked out perfectly.

But please I am having troubles in designing some database tables
in the following scenario;
The company in question has invested in buying SHARES from
another companies.These SHARES might be bought several times
depend on the financial and market
position.....Thereafter,the DIVIDENDS from those shares can
be awarded accordingly (in most cases two times a year,but
not very specific).In the mean time,the company is able to
SALE its shares in the outside market as and when required.
My concern here is 'what are the relatioships exists between
SHARES , DIVIDENDS AND SALES?????so that i can design an
accurate te database????