Checkbook like Running Balance HELP!~

[akrshaw]

Hey!~

I need a little help, I am trying to come up with a budget database I have found the following code built into the Balance Field on my Form:

Private Sub Balance_BeforeUpdate(Cancel As Integer)
BankStatement![Balance] = Nz(Bank Statement![Deposits],) + Nz(Bank Statement![Withdrawls],)
End Sub

Below you can see the first time I put the formula into the field it worked. Now when I add transactions...it does nothing.

Any help is GREATLY appreciated!~


Withdrawls Deposits Balance
$1,718.00 $1,554.29
$288.99 $1,265.30
$46.06 $1,219.24
$27.51 $1,191.73
$25.00 $1,216.73
$741.00 $475.73
$19.98 $455.75
$55.00 $400.75
$100.35 $300.40
$90.00 $210.40
$40.00 $170.40
$38.18 $132.22
$30.85 $101.37
$21.00 $80.37
$600.00
$29.00
$29.77
$33.60
$36.26
$57.75
$125.00
$70.36
$146.83
$29.73

 

[Golom]

I assume that "BankStatement" refers to some recordset that contains a computed sum for deposits and withdrawls ... is that correct?

If so, what does the SQL look like that is pulling that data from the database?

I also assume that the code that you have presented has some typos

Private Sub Balance_BeforeUpdate(Cancel As Integer)
[red]BankStatement[/red]![Balance] = Nz([red]Bank Statement[/red]![Deposits],) [red][b]+[/b][/red] Nz(Bank Statement![Withdrawls],)
End Sub

Are [red]BankStatement[/red] and [Red]Bank[/red][COLOR=white yellow] [/color][red]Statement[/red] intended to refer to the same thing?

Shouldn't you be subtracting withdrawls ... not adding them?

 

[akrshaw]

Bank Statements is the Table where the information is pulled into the form.

There is no query that filters the information from the table to the form.

You are correct, Bank Statements refers to the table, but the form also has the same name.

The posting on here where I found the Code stated that subtracting was not needed.

 

[Golom]

Let me explain what I see as the issue which (I hope) will relieve some of my confusion.

There are two ways to do this.

First Way

- Start with an opening balance

- Add up the Deposits and Withdrawls

- Add the sum of the Deposits to the opening balance and subtract the sum of the Withdrawls (or Add if negative)

Second Way

- For any given transaction, find the balance from the preceding transaction.

- If the transaction is a deposit, add it to the previous balance

- If it is a withdrawl subtract it (or add a negative).

Both ways are equivalent of course but the first depends on working with totals whereas the second can be applied at the individual transaction level without redoing the "sums from day 1" calculation.

Your calculation says

Balance = Deposits + Withdrawls

and, using either of the above methods, it would be

Balance = [red]PreviousBalance + [/red]Deposits + Withdrawls

Nowhere do I see anything corresponding to a previous balance (i.e. it must be zero) so I'm obviously missing some vital piece of information about your process.

 

[akrshaw]

Sorry for the confusion, at this point I'm having a hard time determining which way is up, on something that should not be stumping me so much (grin)

The second scenario you refer to above is what I am trying to accomplish, but I cannot find where anything here shows how to access the previous balance to utilize it in the next record.

 

[Golom]

OK. That's why I asked about the SQL.

I infer that your table must contain some field that can be used to determine the transaction ordering ... for example a datetime stamp or an autonumber field.

Making the assumption that it is a DateTime field and that you are appending a new transaction (i.e. you are not modifying an existing one.)

Private Sub Balance_BeforeUpdate(Cancel As Integer)
Dim rs As DAO.Recordset
Dim SQL As String
Dim PrevBalance As Currency
SQL = "Select Balance From BankStatement " & _
      "Where BankStatement.TransDateTime = " & _
      "      (Select MAX(TransDateTime) From BankStatement)"
Set rs = CurrentDb.Openrecordset(SQL)
PrevBalance = IIF(IsNull(rs![Balance]), 0, rs![Balance])
BankStatement![Balance] = [red]PrevBalance + [/red]Nz(Bank Statement![Deposits]) + Nz(Bank Statement![Withdrawls])
Set rs = Nothing
End Sub

 

[akrshaw]

When I use this I am getting the error:

Compile Error
Expected: Link Seperator or )

With the below in red highlighted:

Bank Statements![Balance] = PrevBalance + Nz(Bank Statements![Deposits]) + Nz(Bank Statements![Withdrawls])

 

[Golom]

Names that contain spaces must be enclosed in square brackets

BankStatement![Balance] = PrevBalance + Nz([red][[/red]Bank Statement[red]][/red]![Deposits]) + Nz([red][[/red]Bank Statement[red]][/red]![Withdrawls])

 

[jsteph]

...I know this sounds minor but when you present this app to users...make sure you spell Withdraw[green]a[/green]l correctly...
--Jim