Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Nov 2005
    Posts
    12

    Subform vs main form calculation

    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!!!!!!!!!!

  2. #2
    Join Date
    Dec 2005
    Location
    Wilmington, DE - USA
    Posts
    275

    Re: Subform vs main form calculation

    Quote Originally Posted by 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!!!!!!!!!!
    Try using an AfterUpdate event sub on the subform's AmountPaid control that forces the main form to requery:

    Code:
    Private Sub MyControl_AfterUpdate()
    
        Forms("MainForm").Requery
    
    End Sub

  3. #3
    Join Date
    Dec 2005
    Posts
    22
    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

  4. #4
    Join Date
    Dec 2005
    Posts
    9
    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

  5. #5
    Join Date
    Dec 2005
    Posts
    22
    *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!

  6. #6
    Join Date
    Dec 2005
    Posts
    9
    Is Total Cost a bound field? That is, is it in a table?

  7. #7
    Join Date
    Dec 2005
    Posts
    22
    Not at the moment. It is simply supposed to hold the equation that will add up the costs of all services.

  8. #8
    Join Date
    Dec 2005
    Posts
    9
    Can you send me the mdb and I will see if I can gvie you a solution?

  9. #9
    Join Date
    Dec 2005
    Posts
    22
    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

  10. #10
    Join Date
    Dec 2005
    Posts
    9
    Give me a email address and I will send you the modified database.

  11. #11
    Join Date
    Nov 2005
    Posts
    12

    it worked but.............

    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????

  12. #12
    Join Date
    Dec 2005
    Posts
    22
    mxcsquared@gmail.com

    Thanks ^_^!!

  13. #13
    Join Date
    Dec 2005
    Posts
    9
    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.

  14. #14
    Join Date
    Dec 2005
    Posts
    9
    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!

  15. #15
    Join Date
    Nov 2005
    Posts
    12

    APPRECIATION & SOME MORE HELP....

    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????

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 0
    Last Post: 08-17-2008, 12:19 PM
  2. Exporting main table AND subform
    By jenny28 in forum Import/Export Data
    Replies: 0
    Last Post: 05-10-2007, 11:40 AM
  3. Replies: 2
    Last Post: 04-11-2006, 08:40 AM
  4. Form with subform
    By grgerhard in forum Forms
    Replies: 0
    Last Post: 04-10-2006, 10:28 AM
  5. Replies: 1
    Last Post: 02-27-2006, 07:46 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums