Results 1 to 10 of 10
  1. #1
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160

    How To Get Invoice Details Total In Form Field, Access 2013

    Hi Guys,

    I am trying to make a simple Invoicing database. I have tblSale (Sales Table) & tblSaleDetails (Sale Details table). I am using a form to view, Update & new data in the table.

    Now I would like save the total value of invoice amount (from Sales Details table for current active record) to be stored in the field of InvoiceAmt of the tblSale.

    Hope I am able to explain my question. Also attaching my access file for reference.

    Thanks and Regards


    Deepak Gupta
    Attached Files Attached Files

  2. #2
    gcbeldar is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    May 2017
    Posts
    14
    Is it possible to convert your database to access 2007 ?

  3. #3
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Are you sure you want to save invoice totals? It's generally considered bad practice to store calculations. Primarily, you have to consider that if a figure used in the calculation changes you might find the old data reflects new values when it should not. If you correct a price for a line item, the stored table value will be wrong for that line - unless you design to enforce a new calculation. For all the trouble you can get yourself into, it's just better to perform calculations in queries and/or forms only.

    EDIT:
    are you using calculated fields or data macros in your db? I think nobody with version 2007 can open a few of your tables.
    also, to help with deciding on storing calculations, suggest you read http://allenbrowne.com/casu-14.html
    Last edited by Micron; 11-25-2017 at 10:08 PM. Reason: added info
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    gcbeldar is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    May 2017
    Posts
    14
    Your are right, "Invoice Totals should not be stored. they are to be calculated at the time of Printing / Entry". Even if you want to store, is better to store in the sales(master table, which stores, Invoice No, Date, Customer Details etc.,).

  5. #5
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160
    Dear gcbeldar,

    I am using MS Access 2013. I want to save the value of Invoice total in master only in my attached DB.

    I agree that we don't need to save the calculated fields, but i am trying to do this, because in future I have to generate some reports based on the invoice data, where I only need invoice total and no further need for invoice details. Just wan't to avoid future queries being more complex. Also I might need to round of the invoice total figure and then perform rest of the future calculations.

    could you please help me show how it can be done.

    Thanks and Regards
    Deepak Gupta

  6. #6
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Add this code to form tblSale:

    Code:
    Option Compare Database
    Option Explicit
    
    
    Private Sub Form_Current()
        InvoiceAmt = DSum("Amount", "tblSaleDetail", "Sale_FK=" & ID)
    End Sub

  7. #7
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160
    Thanks will try and revert back.

  8. #8
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160
    Thanks davegri,

    worked like a charm.

    Regards
    Deepak Gupta

  9. #9
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Happy to help. Good luck with the rest of your project.

  10. #10
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 09-23-2017, 08:16 AM
  2. Sub total and total on invoice form
    By knh2r in forum Access
    Replies: 3
    Last Post: 07-13-2016, 02:09 PM
  3. Replies: 2
    Last Post: 08-22-2014, 05:16 PM
  4. Replies: 30
    Last Post: 07-03-2014, 01:22 PM
  5. Vendor ID, Last Update Date, Invoice Details - Query
    By AppsDeveloper in forum Queries
    Replies: 1
    Last Post: 03-14-2012, 04:43 PM

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