Nov 9 2009, 04:15 AM
Hello Access Crew
Im using access 2007 and I have 2 forms,
One is accounts payable which lists my customers names and Total Owing
The other is payable accounts lists All the separate invoices for one customer account
My question is how Do I get the accounts payable total to reflect the totals of Payable Accounts
1 Customer Name………………….Ron Total Owing 2000.00
2 Customer Name,,,,,,,,,,,,,,,,,Crystal Total Owing 1000.00
1 Ron Invoice 1 1000.00
2 Ron Invoice 2 1000.00
In the form Accounts Payable Im trying to get the total to adjust to whatever Expense Total for Ron is if Im adding or deleting his purchase Orders
So if I delete Invoice 2 from this example Im left with 1000.00. in payable accounts. Id like the adjusted total to display in the total owing box for ron in accounts payable and for each customer I add to accounts payable.
Ive attatched an example to make it easier
Nov 9 2009, 05:41 AM
Your layout for the forms was all wrong
What you needed was a Main Form to display the Customer as a Single Form
with the SubForm as a Continuous Form
Some of your field names were not reauired ie TotalwithSalesTax as this is a calculated field and should be calculated on the Fly
See the attached modified Db
It allows you to select the Customer using the ComboBox in the Form Header
You can then add Invoices to the Subform as they occur
The Subform displays the Outstanding Balance that you asked for
Come back with any questions
Nov 9 2009, 06:33 AM
Mike thats fantastic
Thank you very much...I really like what you have come up with.
You guys are amazing on here.....In half an hour I get a solution
I really wanted something along the lines of what you have done. Dont know how I can repay you.
Im really excited with what you have given me....brilliant
Nov 9 2009, 06:43 AM
Glad to help
Do you understand the process with the calculations on the Form?
Nov 9 2009, 03:24 PM
Yes I looked at the code from what you have created and understand you have used calculated fields. And you used formulas referring to the subform and then formulas that add the calculated controls. Im relatively new at this but your
help is invaluable as now I can look at the controls and analyse how you did them.
If you download easyas accounting which is an access database with a 30 day free trial from www.easyasaccountingsoftware.com
there is an accounts payable entry screen. I wanted to achieve something similar to what that program has done.The guy that designed this program is brilliant. Its a little bit different to what you have given me but I really like what you are expressing.
Nov 9 2009, 03:54 PM
Mike I want to use the subform idea that you made in accounts payable in this invoice. Could you possibly give me an example of a subform to go in it which adds the totals and tax for each customer and a percentage box like you showed in the accounts payable
If you could do this I will forever be in your debt
Nov 9 2009, 06:08 PM
See the attached
I have included the Invoice Db within the Accounts Payable
The Form that opens at startup allows you to select a Company (You current have a lot of redundant data in this table??)
The Subform allows you to enter details of the Invoice
The Subform withinh the SubForm allows you to add a Number of Invoice Items
The Totals are calculated similar to the previous Accounts Payable
Hope this helps?
Nov 9 2009, 08:05 PM
Mike thats the biggest help I have ever received from a forum
Cant thank you enough
You have gained me hours of progress and I can study the way you have done this
I cant express how thankful I am
Nov 10 2009, 03:39 AM
Glad to help
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here