Calculations

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello - I am making a form (with subforms) to enter daily batch/deposit
information. I put a Close button on the form, which I would like to do a
"check" before it closes the form. There are three fields included:
TotalDeposit, which is the Sum of CashAmt+CheckAmt
TotalBatch, which is keyed in manually
TotalVariance, which is the sum of all the variance amounts (Continuous Form)
I want Close button to do TotalDeposit - BatchAmt - Total Variance if it
doesn't = 0 then a msgbox with "Doesn't balance" comes up. What is the best
way to do this? Should I use the calculated fields or each individual field?
What Event should I use? I pretty lost on this one, so the more help I get
the better. Thanks so much!!
 
form Unload event
---

Hi Katelin

use the form Unload event, which can be cancelled if you do not want to
allow the form to close

if you have a Close button, you can do this:

'~~~~~~~~~
on error resume next
doCmd.Close acForm, me.name
'~~~~~~~~~

if the Unload event is cancelled, the form will stay open

'~~~~~~~~~
Private Sub Form_Unload(Cancel As Integer)
dim mBalance as currency
mBalance = me.TotalDeposit - me.BatchAmt - me.TotalVariance
if mBalance <> 0 then
msgbox mBalance & " Does not balance",,"Cannot close form"
CANCEL = true
end if
End Sub
'~~~~~~~~~

use calculated fields to show calculations

for totalling calculations, you can do this in the form footer. For
instance:

Name --> TotalDeposit
controlSource --> =Sum(nz(CashAmt,0) + nz(CheckAmt,0))

Name --> TotalVariance
controlSource --> =Sum(nz(VarianceField,0))

if Variance is an equation, you will need to repeat the equation in the
sum control

to keep the sum and other calculations updated, save the form each time
CashAmt or CheckAmt or whatever field(s) goes into Variance is changed.
On the AfterUpdate event of each control:

me.dirty = false


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
I can't get it to work. I think it has something to do with the names of the
calculated fields. I made the fields like you said to below, with the
name/control source. What does the "nz" and ",0" do to the equation? I could
get it to work in the totaldeposit field but not in the totalvariance field,
which is not an equation. Also, what exactly does "Me." mean? Is it the path
to the field? I think crazy things are happening with my database. I have 4
subforms nested on the main form and all of their names have changed. I had
frmdd.... for every form, and now all the names are missing the dd. Is my
database about to crash? Thank you so much for all your help.
 
NZ, what is Me, Reserved words, AutoCorrect, Compile
---


Hi Katelin,

"What does the "nz" and ",0" do to the equation? "

from Help

'~~~~~~~~~~~~~~~~~~~~~~~~~~``
Nz Function

You can use the Nz function to return zero, a zero-length string ("
"), or another specified value when a Variant is Null. For example, you
can use this function to convert a Null value to another value and
prevent it from propagating through an expression.

Syntax

Nz(variant[, valueifnull])

The Nz function has the following arguments.

Argument

variant
A variable of data type Variant.

valueifnull
Optional (unless used in a query).
A Variant that supplies a value to be returned if the variant
argument is Null. This argument enables you to return a value other than
zero or a zero-length string.

Note If you use the Nz function in an expression in a query without
using the valueifnull argument, the results will be a zero-length string
in the fields that contain null values.


'~~~~~~~~~~~~~~~~~~~~~~~~~~``

the thing to keep in mind when using Nz is what it will return if you
don't the specify the second, optional, argument.

If you are using Nz on a field, it will be the data type of that field
-- 0 for numbers (including dates), and an empty string for text or memo

Unbound textbox / combobox / listbox controls on a form are assumed to
have TEXT in them... so an empty string will be returned if nothing is
specified. And, if you specify something, it doesn't have to be 0 or ""

you could do this:

NZ(..., "no commision is found in the table for this employee")

If you do not specify the optional argument -->
If the expression is bound to a text field, an empty string will be
returned if the field is null. If the expression is bound to a numeric
field, 0 will be returned if the field is null.

it is a good idea to wrap return values from dLookup, etc, in NZ in case
no match was found

.... a good "rule-of-thumb" is to specify the optional arguments (even
though it is not necessary) and do this:

= Nz(DLookup("[Commission]", "Employees", "[EmpID] = " & nz([EmpID],0)),0 )

notice how NZ is used twice -- once to make sure the criteria will be
evaluated, and another time around everything in case dLookup didn't
return a value...

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`
"Also, what exactly does "Me." mean?"

when you are in the code behind a form or report, Me referes to the form
or report itself. When you type Me. in code, Access will prompt you
with a list of choices so it makes coding easier to use it. Me is not
usually necessary.

It also makes things more clear. If you have Me.Total in code, you know
it is referring to a control named Me. If you just use Total, it could
mean a variable too.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`
"...all of their names have changed"

do you mean Names or Controlsource? The ControlSource is what shows up
inside the control itself. To see the name, you can turn on the
Property sheet and look.

the only way the name can get changed is by you.

The controlSource can get changed if you change what it is based on if
you have AutoCorrect on (Tools, Options, General tab). you should, by
the way, disable this.

Failures caused by Name AutoCorrect
http://allenbrowne.com/bug-03.html

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`

"I think it has something to do with the names "

you can check what you are using here:

Problem names and reserved words in Access, Allen Browne
http://www.allenbrowne.com/AppIssueBadWord.html

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code or references, your should always compile
before executing.

from the menu in a module window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`

"...but not in the totalvariance field"

What is the source for your variance control? What is the NAME of the
control with variance in it?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`

to help you understand Properties in Access a bit better, send me an
email and request my 30-page Word document on Access Basics (for
Programming) -- it doesn't cover VBA, but prepares you for it because it
covers essentials in Access.

Be sure to put "Access Basics" in the subject line so that I see your
message...




Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
Back
Top