Display msg when value chgs to zero.

[Dennis]

Hi,

I'm running Access via XP Office Pro under Windows 7.

Background -------------------

We are working on creating a cash receipt form. Checks are group into
"batches" of 20 checks. Someone adds up the checks using a calcuator. The
total from the calculator is called the "batch control total".

The cash receipts form has a form and a sub-form. The allows the user to
enter the batch number, batch date, control total amount (from manually run
caculator tape), and batch comment. The sub-form enables the user to enter
the individual checks. The control total amount's text control is called
txtCtlTotal.

In the footing of the sub-form, I calculate the total of the checks entered
by using a text box control called txtFFTotCash. The control's source is =
Sum(ChkAmt)

On the form, I also have two additional text box controls that were not
mentioned above. The first one is call txtBatchTot and its source is the
txtFFTotCash control in the sub-form's footer. The second control is called
txtDif and its source is = txtCtlTotal – txtBatchTot.

The second control indicates if the batch is in balance or not. If the
batch control total (txtCtlTotal) – total of checks entered (txtFFTotCash or
txtBatchTot) = 0, then the batch is in balance. If the amount is other than
zero, then the batch is out of balance.

All of the above works just fine. Here is my question.



Question ---------------------------------------------------------

As long as the txtCtlTotal is NOT zero, I was to display the message “Out of
Balanceâ€. When the txtCtlTotal is set to zero, then I was the display to
disappear.

I’ve put code in txtDif’s After Update, On Dirty, an On Change events to
make the message disappear (visible = False), but the events are not
triggered when the value of txtDif changes.

How do I get the Out of Balance message to disappear when the value of
txtDif changes to zero?

Thanks,

Dennis

 

[Tom van Stiphout]

On Fri, 21 May 2010 19:40:05 -0700, Dennis

When you change the value of a control, its AfterUpdate event fires.
You can confirm that by setting a breakpoint in the procedure.

The only reason I can think of this would not occur is if somehow
(e.g. cut control, paste it) the event procedure is "disconnected"
from the event. Design the form, select the control, select the
properties window > Events tab, and click on the ... button to the
right of the event to reestablish the link.

-Tom.
Microsoft Access MVP

 

[Dennis]

Tom

Your comment: When you change the value of a control, its AfterUpdate event
fires. You can confirm that by setting a breakpoint in the procedure.

Response: I thought the same thing. But when I put break points in the
After Update Event code, it never broke. I also tried the On Change and On
Dirty events for my txtDif control text box. None of those fired as Access
changed the value.

Remember, these are ALL calculated fields by Access. I never change then,
Access changes that values as I enter data into the sub-form.


Your comment: The only reason I can think of this would not occur is if
somehow (e.g. cut control, paste it) the event procedure is "disconnected"
from the event. Design the form, select the control, select the properties
window Events tab, and click on the ... button to the right of the event to
reestablish the link.


Response: I tried that. As a matter of fact, I have a break on the After
Update, Change, and On Dirty. None of that fire as I watch the values change
in the control bot.

Dennis

 

[John W. Vinson]

Response: I thought the same thing. But when I put break points in the
After Update Event code, it never broke. I also tried the On Change and On
Dirty events for my txtDif control text box. None of those fired as Access
changed the value.

That is correct: programmatic changes to a control do not fire its events,
only actual user input. You'll need to use the afterupdate or other
appropriate event of the *manually entered* controls which underlie the
calculation, or some form event.

 

[Dennis]

John,

I'm entering the data in a subform called frmCash_sfCheck. The amount field
is called txtChkAmt. The procedure name that checks the batch balance is
called Check_Batch_Balance and resides in the form frmCash.

I understand that I need to modify the txtChkAmt_AfterUpdate event in the
sub-form frmCash_sfCheck to call the Check_Bacth_Balance in the frmCash form.

How do I do that?
Do I need to make the Check_Batch_Balance public and call it from the
sub-form or is there a way to call a procedure in the form from the sub-form
without making the form's procedure public?


Thanks

Dennis

 

[Dennis]

John,

I got my answer - Call me.parent.Check_Batch_Balance.

 

[Dennis]

All,

Every one was correct in what they said. But there was still a timing issues.

Per John Winston, I modified my code check the batch balance each time I
updated a record. That worked, but then a timing problem popped up.

The batch balance amount is a figure calculated entirely by Access. That
may take a second or two to calculate. I put the call to the
Check_Batch_Balance procedure in the form’s After_Update event.
Unfortunately, at the time the Check_Batch_Balance procedure ran, Access had
not updated the Batch Balance. So the I put a call into the On Entry event
of the first field on the row and Access still had not updated the total
after the current record had been updated and Access set focus to the first
field on the next row.

My solution was to remove the Check_Batch_Balance procedure, remove all
references to it and then change the Control Souce of the txtOutOfBalance
text box.

Originally, the Control Source of the txtOutOfBalance text box was “=Out of
Balance†and I relied on my VBA code to make the box visible and not visible.
After deleting all of my code for this purpose, I change the Control Source
to “=IIf([txtDif]=0,"","Out of Balance")â€.

When I made this change, it worked perfectly. Granted, there was still the
timing issue, but Access automatically took care of that. When it finally
gets around to updating my batch balance (txtDif), it also updates the
txtOutOfBalance text box since it is dependent on txtDif. Note, when I say
finally I mean 1 to 3 seconds.

There is also a timing issue if there are a lot of checks in the batch. It
takes a second or two to load all of the checks in the continuous form and
calculates the batch total and the batch difference. This approach also
fixes that timing issue.


Dennis