|
 |
Introduction
This is a simple tool but very handy Access tool that you can use to estimate how your cash or budget is going to expend over time. I use it and it is pretty handy, especially if your deposits and debits are irregular.
To use the tool simply enter all of your credits and debits in the cash amount column along with the date that the transaction will occur. There is a place for a description of the transaction as well. For example, if you know your payroll each month, enter the amount ahead of time with the dates as negative amounts in the "cash" column. I estimate my expenses and other costs and enter those amounts as well. Now that I have my regular expenses entered I can instantly see the impact that a credit will have on my cash balance in the future, or see how long the money will last. Again, once the data is entered you can instantly get a look at the impact of a date change for a payment s going to affect your cash balance out in the future.
You can change any future amounts and edit dates then press button to recalculate the cash amount in your account and instantly get a preview/estimate of your cash flow into the future. Preparation: Create a table with these fields and attributes:
Id ......autonumber
Cash ....currency
Date ....Date/time
Balance..currency
Desc.....Text
Save the table as "cashflow"
Next create a form with "cashflow" as the record source. Use a continuous form and place all the fields from the cashflow table on the form. Place the field labels in the form header, place the fields in the detail section and place a button to click for the calculation also in the form header.
Use any descriptive text for the button and any labels you desire to fill out the form.
Select the form and make it active. Then set these values on the Data tab:
Record source = cashflow
Order by = cashflow.date
Allow filters = yes
Allow deletions = yes
Allow additions = yes
Data entry = no
Enter this code on the Event tab "On Click" Event on the button you created to perform the calculations.
Dim sql1 As String
Dim db1 As DAO.Database
Dim rec2 As DAO.Recordset
Dim amnt As Currency
Dim bal As Currency, newbal As Currency
Dim cnt As Integer, counter As Integer
DoCmd.Requery
newbal = 0
Set db1 = CurrentDb()
sql1 = "SELECT cashflow.id, cashflow.Cash, cashflow.date, cashflow.balance, cashflow.desc FROM cashflow;"
Set rec2 = db1.OpenRecordset(sql1, dbOpenDynaset)
rec2.MoveLast
cnt = rec2.RecordCount
rec2.MoveFirst
counter = 0
DoCmd.GoToRecord , , acFirst
Do Until counter = cnt
newbal = newbal + Cash
Me.Balance.Value = newbal
counter = counter + 1
DoCmd.GoToRecord , , acNext
Loop
rec2.Close
Save the form and give it a try.
Notes: Enter debits as negative values in the amount column. Note: that as you proceed through the year, the fields get locked for past dates to preserve the data.
Thank you for your interest and I hope you find this article useful in your efforts to develop powerful applications for your users.
BioMation Systems, Inc is an Atlanta, Georgia based consulting company that develops custom database solutions that increase the efficiency of businesses around the world. BioMation's range of services can be found at www.biomationsystems.com
You can find help for Access at
http://www.accessdatabasehelp.com
http://www.accesshelpebook.com
http://www.biomationsystems.com/AccessTips.htm
Contact: Jon Watson
jonw@biomationsystems.com
Category: SoftwareMS Access Quick Cash Flow Calculator tip was written by:Author:Jon WatsonAdded: Sat, 20 Oct 2007 07:40:24 -0400 This Article Has Been Read 960 times View all Jon Watson's articlesAbout the Author: Jon Watson is the founder of Biomation Systems, Inc. With 26 years experience helping Fortune 500 companies with process improvement he formed BioMation to bring the same expertise to smaller companies that need the same improvements at an affordable price. |
More Articles About Software
Not What You Were Looking For?
 Moxie Drive Expressions
 Submit Articles
 Contact us
Add us to favorites
Hint - Hold "T" button down and right click to open Sphinn and Subbmitt in a new window. 
|