Autoupdate feilds

[DaveRussell]

Hey everybody!

I've got an access database that i'm working on here, and i've run into a wall...

the program is an interest calculator, pretty much... it takes withdrawals and desposits, and every month it compounds at a given interest rate.

i've got a form in continuous form mode, and the recordsource set to display the transaction records of a particular client - whether it be a deposit, withdrawal, or if the principle has compounded, the compoud amount.

what i'd like to be able to do, is this:
on this form, when the user updates the first record, it will automatically update the rest of the records. eg:

the first transaction record is a deposit of 500, with an ending balance of 1000. The second is a record that represents the principle earning 2.5% interest, so, $25 deposited, and a balance of $1025. If the user goes back and changes the first record to a beginning deposit to $500, it will update the initial balance, then the compounded interest deposit, then the final balance.

If anyone has any idea of which direction i should go with this, it'd be really appreciated.

thanks
Dave.

 

[willir]

Hello Dave
(Can you open the pod bay doors...) ;-)

First, have you considered purchasing a third party software for accounting? There are several well respected and fairly inexpensive applications - QuickBooks, PeachTree, AccPac. These applications are accepted with respect and integrity. You would have to have some very special needs to justify the time and expense of redesinging the "wheel".

You might want to review...
Fundamentals of Relational Database Design by Paul Litwin
Download document
Read on-line (HTML)

The third level of Normalization states not to store calculated values. Having said that, I under stand your need to store calculate values.

And Access does not make it easy to calculate net differences between previous and current reading.

Although you can calculate the previous and net values using a nested SELECT statment, my preference is to use an array.

You can even use this approach to re-work interest calculations. For example, you now calculate interest on $500 instead of $1000.

Arrays have a limitation in the amount of memory available for use but it is very fast and effecient.

You have to realize...
- you have user transactions (deposits, withdrawals)
- you have system transactions (interest, fees)
- the total is based on the transactions

The array can be populated by the transactions, inserting the system transactions, and calculating the the balance by referencing the previous balance and the current transaction.

Now, another suggestion. As time marches on, the number of transactions increases. Any recalcuations becomes more and more awkward. ...And eventually, you want to get rid of transactions to free up space.

One way to reduce the time spent on caclulations, and preserve the total after deleting transactions is to use a balance file for year + period. When a period / year is closed, you can reference the balance file for the total instead of recalcuating totals and subtotals.

Lastly, I would be concerned about your comment about changing the openning balance -- I am sure you just gave this as an example -- preserving the integrity of transactions, etc is extremely important when working with money. Security is paramount.

Anyway, good luck...
Richard

 

[DaveRussell]

Richard!

I wish i could just go buy a program. let me get into it a bit more:

basically what i've got is an online MySQL database that is being written to via ODBC. the Access Database pulls information off of that database which is linked into msaccess for my clients. pretty much the wheel's already rolling, reinvented. it's still kinda shaky, i agree, but it's working so far. I've worked on it a bit more, and although i'm sure almost anyone here could put something a whole lot simpler together to get it to do the same thing (and by all means, go for it ) it's almost working... here's the code

Dim rstCustomer As DAO.Recordset
Set rstCustomer = CurrentDb.OpenRecordset("SELECT * FROM lclCustomerInfo WHERE AcctID = " & Forms!fSearch!lbSearchResults & ";")

Dim Conn As ADODB.Connection
Dim rstTrans As ADODB.Recordset

Set Conn = New ADODB.Connection
Conn.Open CurrentProject.Connection

Set rstTrans = New ADODB.Recordset

rstTrans.CursorLocation = adUseClient
rstTrans.Open "SELECT * blah blah", Conn, adOpenForwardOnly, adLockBatchOptimistic

Set rstTrans.ActiveConnection = Nothing

Conn.Close


Dim interest As Double
Dim beforeDate As Date
Dim beforeType As String
Dim principle, amount, balance As Currency
Dim theDay, daily As Integer

rstTrans.MoveFirst

principle = rstTrans.Fields("ahBalance")
amount = rstTrans.Fields("ahTransAmt")

Do Until rstTrans.EOF
    
     amount = principle * rstCustomer![ciInterestMonthly]
     With rstTrans
         .Fields("ahTransAmt") = amount
         .Fields("ahBalance") = principle + amount
     End With

    principle = rstTrans.Fields("ahBalance")
    beforeDate = rstTrans.Fields("ahTransDate")
    beforeType = rstTrans.Fields("ahTransType")
    
    rstTrans.MoveNext
    If rstTrans.EOF = False Then
        amount = rstTrans.Fields("ahTransAmt")
    End If
Loop



Set Conn = New ADODB.Connection
Conn.Open CurrentProject.Connection
rstTrans.ActiveConnection = Conn
rstTrans.UpdateBatch

rstTrans.Close
Conn.Close
Set rstTrans = Nothing
Set Conn = Nothing

Me.Requery

now the error that i'm getting is this:
"row could not be located for updating"

anyone ever encounter anything like this?

 

[DaveRussell]

I might add that i call this as a function right after i add a new record to the database...

 

[tziviak2]

did you try saving the record first?
ex.
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

 

[DaveRussell]

tziviak2,

I gave it a shot, first putting it after i added a new record, then just before i ran the .UpdateBatch statement. still getting an error am i putting it in the right spot?

Thanks -Dave.

 

[Zion7]

DaveRussell,
Bear with me here, could this be the culprit?

If rstTrans.EOF = False Then
amount = rstTrans.Fields("ahTransAmt")
End If

...if rstTrans.EOF = False, then how can you extract a value from the recordset...amount = rstTrans.Fields...

Maybe....

If rstTrans.EOF = False Then
rstTrans.MovePrevious
amount = rstTrans.Fields("ahTransAmt")
rstTrans.MoveNext
End If



Just a thought?

Good Luck!