Bank Statement type form

[rick roberts]

mine is a general question im trying to create somethig simillar to a bank statement that includes a debit credit and balance column my problem starts when i look towards passing the previous line's old balance to become the new lines new balance - i manage it roughly with some code -- but its very unstable eg if i move up to previous rows calculatios are made that i dont want - ive looked for database templates with this function in mind but cant seem to find anything

 

[Banana]

I'm sure code isn't required for that sort of thing. You'd just make a table of transactions (both credit and debit), then on the form, you would have something looking like a sheet (but i wouldn't use datasheet view for reasons below) where you allow users to edit the values in credit/debit field, both which are bound to the same table, then have the running balance calculate from the last credit/debit + previous running balance.

This is recursive, so if you go to the very first transaction and change the value, everything will be updated.

The only downside is that if you're looking at very large number of transaction that recuring to the very first transaction may not be very practical, at which point may be helpful to create a checkpoint of running balance every a given period. Look at inventory management templates for examples on how they calculate stocks, which is also recursive but has functionality built to limit the recursive to not bog down the database.

HTH.

 

[GaryPanic]

I would have thought this was do-able
what you need is a credit field and a debit field on your tables and also an outstanding
right if you are going down this route and using a bank statement style approach - once a transaction has beeen done - credit or debit then they should be locked and no alteration allowed - if you have ever had a statement thats wrong (I have) they don't repalce the statement the do a correction and issue a new statement (On my occasion the bank knew about the **** up and let me know about it- it was quite a few 000 adrift )

then prior to runing a statement run hit an update qry to work out the balance of the previous qtr/month/period and have this in outstanding or even after you run the statement - to update the outstanding field (might be better if called balance) this would then give you a rolling statement - and the ability to have a carry on from last statement

this should be quite easy to do

run report - easy
update table to sum of credit +debit = balance
next time you run report
first item on report balance (or whatever it is called) and then credit debits _ I presume it will be between date range and date range

now this does not help if you need to do historic statements

these are just my ideas and not looked at throughly - their is bound to be errors in this



but it might lead you to alternative solutions

 

[GaryPanic]

might want to explain a bit more about what you are trying to achive
and how big the record sets might be - if we are looking at mid hundred or thousands of records

are these bills
if so you could have settled dates tie to invoices
then it would give you more flexibility

 

[rick roberts]

im not sure i explained myself very well - im not looking to update final balances but to have a form that looks like a bank statement

Date---Debit--Credit--Balance
------------------------------
1/1----------------- 1000.00
2/1-----50 --------- 950.00
3/1-----------50----1000.00
4/1----500-----------500.00

i understand that the fields must be locked once they have lost focus so as not to recalculate which might help the situation but i still feel that it is very unstable and id hate to make a start and find that a click to a previous box causes recalculations
if i dont use code to create a
previous balance (for the row above)-Debit+Credit=New Balance how do i do it from DLast or something?