Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    mshad is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Location
    Pakistan
    Posts
    9

    Calculation between mainform and subform

    Hi there,


    I am a rookie in ms access. Somehow I have created a an installment database but now I am stuck in calculating between mainform and subform based on two different tables related in one-to-many relationship. Now I want to show the [TotalPrice] value from mainform in first row of [Previous] in the subform and after subtracting [Amount] from that I want to bring the [Balance] in next row of the [Previous] and than continue with that until balance is zero.
    Attached Thumbnails Attached Thumbnails msAccess1.JPG   msAccess.JPG  

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Can you upload a small sample of your db? You don't show how the main form and subform are linked (propertyid, customerid, projectid). You need to use dlookups or queries to get you the "previous" BalPay to update the Previous field. You could do that in the after update event of the PAmount field - I would change the order and bring that before the Previous so once you enter the PDate and PAmount you now have all the data you need to update the Previous and BalPay for the current record.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    mshad is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Location
    Pakistan
    Posts
    9
    First of all thanks for the reply and helping me out. Here is my db relationship chart. All the IDs are auto generated.

    Click image for larger version. 

Name:	Capture.JPG 
Views:	34 
Size:	42.7 KB 
ID:	41041

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Would you be able to upload a small sample of your db with just a few "dummy" records?

    Storing the Previous and BalPay into each receipt record is not a good idea and generally frowned upon as it can lead to data inconsistencies. Imagine you have 10 receipts for one property and realize that you entered the wrong PAmount in Receipt#3. Once you correct that you need to go and fix all subsequent receipts. The Previous and Balance can and should be calculated as needed in a query (for the subform you can use two unbound controls to display them).

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    mshad is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Location
    Pakistan
    Posts
    9
    Thank you for your interest in solving my issue. I am uploading my db with some dummy records please look into it. EmaratBuilder.zip. I do understand your concern but we don't give system receipts to our customers, even we don't print them its just for our own record that's why I want to populate the [Previous] field automatically (which is I am typing manually now). Storing previous and balance in each record is our requirement.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Subform using a value from main form to perform calcs is usually backwards. More common to take a summation from subform and display on main form.

    The type of 'balance' calcs you describe are best done on report, not form. This is a very common topic.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    mshad is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Location
    Pakistan
    Posts
    9
    Unfortunately the people I am working for don't understand these things. So I have to make forms as per their requirement.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    It will be rather difficult. Calculating dynamically with nested query will likely result in a non-editable dataset which is useless on a data entry form. Might be able to do something with domain aggregate function DSum() in textbox on form. This will allow data entry/edit. I agree with Gicu that saving this data to table is a very bad idea. However, if you must, real trick is figuring out which event(s) to put code into and handling situations Gicu already described.

    For a start, consider following code:
    Code:
    Option Compare Database
    Option Explicit
    
    
    Private Sub Form_Current()
    If Me.NewRecord Then
        With Me.RecordsetClone
            If .EOF Then
                Me.Previous = Me.Parent.TotalPrice
            Else
                .MoveLast
                Me.Previous = !BalPay
            End If
        End With
    End If
    End Sub
    
    

    Note the Option Explicit at top. Every module header should have this. This can be set in VBA editor properties as default.



    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    mshad is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Location
    Pakistan
    Posts
    9
    I have tried that now when I open installment form it opens a vb window and says compile error.

    Click image for larger version. 

Name:	Capture.JPG 
Views:	23 
Size:	108.1 KB 
ID:	41064

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I tested this in your db and it worked.

    Code goes behind Reciept subform not main form. BTW, Reciept is a misspelling.


    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    mshad is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Location
    Pakistan
    Posts
    9
    Thank you very much for helping me out, it did worked. But the problem which Gicu indicated is still there (If i make a mistake I will have manually correct all the other receipts) which I thought he was talking about the print receipts but now I realize that he was talking about the system. Any ways thank you so much for your help again. God bless you, have good day or night which ever is in your part of the world.

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Can you upload what you have now so I could have a look, you can add code in the AfterUpdate event of the PAmount to run an update query and reset all the Previous/BalPay fields for the related receipts.

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I did say would be rather difficult. Also said suggested code was just for a start. As Gicu indicates, will need more code to handle situations of deleted record or changed amount. Or make sure those situations don't ever happen (ha! good luck with that!). The more 'user friendly', the more code.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    mshad is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Location
    Pakistan
    Posts
    9
    Emarat Builder.zip
    Now whenever I open a record or enter in a new row it automatically creates a new ReceiptID and stores a blank record. Not in the uploaded file but I have also tried with "PAmount" after update event but still the same problem is occurring.

  15. #15
    mshad is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Location
    Pakistan
    Posts
    9
    Quote Originally Posted by June7 View Post
    I did say would be rather difficult. Also said suggested code was just for a start. As Gicu indicates, will need more code to handle situations of deleted record or changed amount. Or make sure those situations don't ever happen (ha! good luck with that!). The more 'user friendly', the more code.
    Yes indeed but anyway thank you very much for your help.

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

Similar Threads

  1. Replies: 1
    Last Post: 11-07-2012, 05:31 PM
  2. Replies: 4
    Last Post: 03-30-2012, 01:47 AM
  3. Replies: 1
    Last Post: 11-29-2011, 01:37 AM
  4. Link between subform and mainform
    By crowegreg in forum Forms
    Replies: 2
    Last Post: 08-24-2011, 07:05 AM
  5. MainForm/SubForm question
    By 10 Gauge in forum Forms
    Replies: 5
    Last Post: 03-04-2011, 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