Display msg when value chgs to zero.

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

0
Utf
5/22/2010 2:40:05 AM
access.formscoding 7493 articles. 0 followers. Follow

6 Replies
816 Views

Similar Articles

[PageSpeed] 57

On Fri, 21 May 2010 19:40:05 -0700, Dennis
<Dennis@discussions.microsoft.com> wrote:

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


>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
0
Tom
5/22/2010 5:50:56 AM
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
0
Utf
5/22/2010 6:40:01 AM
On Fri, 21 May 2010 23:40:01 -0700, Dennis <Dennis@discussions.microsoft.com>
wrote:

>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.
-- 

             John W. Vinson [MVP]
0
John
5/22/2010 2:25:48 PM
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 W. Vinson" wrote:

> On Fri, 21 May 2010 23:40:01 -0700, Dennis <Dennis@discussions.microsoft.com>
> wrote:
> 
> >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.
> -- 
> 
>              John W. Vinson [MVP]
> .
> 
0
Utf
5/22/2010 3:23:01 PM
John,

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


-- 
Dennis


0
Utf
5/22/2010 9:26:01 PM
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

0
Utf
5/22/2010 11:45:01 PM
Reply:

Similar Artilces:

Return a cell refernce based on a value
I have a value in worksheet A which is "x". In worksheet B column A have a list of values i.e. a,b,c,d...x,y,z and in column B I hav values relating to those in coumn A i.e. column A row 1 value is "a column B row 1 value is "apple", column A row 2 value is "b" and colum B row 2 value is "balloon", how do I get teh first worksheet to rea the value in one cell, find it in column A of the other worksheet an give back the value on teh same row in column B? Thank -- Message posted from http://www.ExcelForum.com Hi use the VLOOKUP function =VLOOKU...

Dual Displays
I want to connect my Sharp Aquos flat screen TV to my computer, and see my desktop on both the TV and computer monitor. Windows Vista Home Premium. HP Pavilion a1610n computer. Galaxy GeForce 8400 GS graphics card, outputs: 1-VGA, 1-DVI, and 1-SVGA. There is no HDMI port on the card. I connected a VGA cable between the motherboard "on-board" VGA port and the TV, figuring that I could use that output to work with the TV. The computer monitor is plugged into the GeForce graphics card. Tried the Desktop "Display Setting". It says, the number 2 monitor is "N...

Find cell which returned maximum value in formula
-- De De, try this, =CELL("address",INDEX(A:A,MATCH(MAX(A:A),A:A,0))) will show the first one if there are more than one -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "De" <De@discussions.microsoft.com> wrote in message news:A17DFFDA-BE05-463C-B68D-65FE235E6098@microsoft.com... > > -- > De Thanks, Paul, but I found this hard to follow. Excel kept returning that I had disabled macros but yo...

Folder could not be displayed
I am running Windows XP and Outlook Express 6 just fine then one day my husband does something to my computer and his email account won't work. My email is working just fine but in his he cannot open his inbox. It says folder could not be displayed, and when you send and receive it gives you this error message: "An unknown error has occured. Account: 'incoming.verizon.net'. Server:'incoming.verizon.net'.Protocol:POP3, port: 110, Secure(SSL): No, error number: 0x800C013B" If anyone could help me with this I would greatly appreciate it. Thank you T...

Display a message box "this entry is a duplicate"
How can I display a message box on a form if the shipment number is a duplicate? I need a message box to pop up when user attempts to tab out of the field. Thank you. Something like: Private Sub txtShipNum_BeforeUpdate(Cancel As Integer) If DCount("*", "MyTable", "ShipNum = '" & Me.txtShipNum & "'") > 0 Then Msgbox Me.txtShipNum & " already exists." Cancel = True End If End Sub This assumes that your text box is named txtShipNum, that the name of the field in table MyTable is ShipNum...

How to create a 2 columns and 2 lines graph with non-related value
Hi, I am trying to create a graph with 2 columns and 2 lines. I am able to get the 2 colums ($000 value) and one line easily ($000 value), however the 4th data series is completely out of range (it's a percentage value ranging between 8 and 10%). I can see the line completely flat on the bottom axis but would like to be able to move it up so it kind of "hangs over" the columns. Any ideas that do not involve complexed programming? Thanks, Double click on the line, and on the Axis tab of the Format Series dialog, select Secondary. - Jon ------- Jon Peltier, Microsoft Exc...

how to display values in 3 cells into one cell
Anyone can help me how do I display the 3 separate different values in 3 cells into just one single cell ? thanks so much in advance. Either there was a typo or there's an echo in here. That line should have been: = A1 & " " & B1 & " " & C1 "Bradley Dawson" <bradleydawson@earthlink.net> wrote in message news:l464b.2224$Lk5.2093@newsread3.news.pas.earthlink.net... > In the target cell, type: > > = A1 & B1 & C1 > > where A1, B1, C1 are the cell addresses that you want to concatenate. > > If these have number...

How to select & display distinct values
Hello, I need help with a form please. My form has two controls, the first being a combo box. This box should select the company name from a lookup table. The lookup table has only two companies in it, however, they each are repeated several time. Company 1 has about 10 rows, company 2 has 3 rows. I used the query builder and came up with the statement: "SELECT DISTINCT Lktbl_Role_Master.CompanyName FROM Lktbl_Role_Master;" When I run the statement directly from query view, it executes perfectly and returns exactly two rows (one for each company, in a datasheet view) ho...

Display Time in Outlook
The time setting in my Outlook is one hour behind my actual time. I cannot determine how to change the time. Outlook is the only item that is incorrect. Other display times in operating system are correct. Thanks, Jodi ...

non-zero solution
Hi, I do not know whether i can use excel in this or not. but I am trying to find the non-zero solution to the following equation: 0.6*exp(t*1.0986)-0.6=0.99994572*t but i always get a zero solution. How could i obtain the non-zero solution?I know that there is a non zero value of t satisfying the previous equation. Plz help, A.A.A A.A.A - Here's one way. Let A1 contain values of t, and initially enter 1 (or your choice of starting value) in A1. Let A2 contain the left side: =0.6*EXP(A1*1.0986)-0.6 Let A3 contain the right side: =0.99994572*A1 Let A4 contain the difference, which...

PK and FK Displayed
I am a complete Visio newbie, and cannot find the answer I need in books or online help. I am trying to create ER diagrams from scratch, and not connected to real tables, using VEA. I have added tables ('Entities') to the form, connected them with a Relationship connector, and, in the Database Properties window I've assigned the end points to a Primary Key on the left and a Foreign Key on the right. But the model shows the connector as running between the top colums on both sides, whereas I want the keys to be in that upper box and provide the connector's end points. Wh...

Formulas are displayed, results are not
Hello, For some reason, when I enter a formula into a cell and press enter, the formula just stays there. Anyone know what I can do to show the results? I tried pressing Cntrl + `, but that didn't work. FYI, when I cut and paste a column from a separate workbook, that column works like normal (i.e., the formulas produce results). Thanks for any suggestions! Hi Mike, Maybe your cell is preformatted as Text. Try formatting to general then erase your = and type it in again. HTH Martin "Mike C" <js2k111@yahoo.com> wrote in message news:f699bcd4-ad82-4a88-9759-5638c147b...

Pasting a value as an hyperlink
I have imported a list from a database of file paths to documents on our server, these data is currently formatted as 'General' Is there anyway of converting that value into a hyperlink to the said documents? If your list is the complete path and file name, then in an adjacent column you could just do: =3DHYPERLINK(A1) and copy this down. If you wish to you could fix the values and then you would not need column A. Hope this helps. Pete On Apr 20, 4:14=A0pm, The Rook <TheR...@discussions.microsoft.com> wrote: > I have imported a list from a data...

Stop displaying a line
I have a line graph/chart and it gets its values using a formula and the values of another column. However this poses a problem because if there is nothing in the targetted column the formula returns 0 cause the line to drop to the bottom, instead of casuing the line to just end until a value is added to the target column. How can I do this? Change your formula to use NA() if the cell is empty. For example: =IF(A1="",NA(),A1) To hide the resulting #N/A errors on the worksheet, you can use conditional formatting. There are instructions here: http://www.contextures.com/xlC...

A question about Console window(display or not display)
I've redirected the (stdin stdout stderr) of a console window process I spawned to a pipe. And I want to display the redirected console window, the question is: If I display it(set Create_new_CONSOLE in createprocess and some flags in startupinfo), I cannot use the redirected way of pipe, which means if i do not display, i can use the pipe communicating with the child process. Does it mean that as long as I disply the console window, windows will change stdin stdout to "screen&keyboard" instead of my pipe? That's right. If you redirect stdin or stdout then you preemp...

unattached textboxes disappear when a msgbox is displayed on a tab control
I'm using Access 2003. I have a form with a tab control on it. I was having problems with labels flashing and I followed the suggesting from Allen Browne - http://allenbrowne.com/ser-46.html. The flashing went away but now when a msgbox is displayed the unattached textboxes that I'm using as labels disappear. They come back as soon as the msgbox is removed from the screen. I've searched google looking for an answer but have come up short. Does anyone know why I'm experiencing this behavior and how to make it go away? Thanks, Rick That *is* strange. Have you tr...

assigning value to a letter (Excel 97)
Hi, At my place of work we have a roster. To indicate what day you are working a letter is put in a cell for that particular day e.g O = Operational Day (11 hours) X = Normal Day (8.5 hours) A = Admin day (8.5 hours).. an so on... Your weekly hours are totaled up at the end of the week (Sat) depending on what you worked (as above). What I would like to do is assign a value to whatever letter is in the cell and in another cell somewhere in the worksheet total up the hours for that week. Sadly I have no idea how to do this. This is useful so you can work out your projected hours to ...

does Excel support multiple display monitors?
Hello All Excel Experts How do I move chart windows to multiple display montors?? (one computer runnng three monitors... Excel Charts will not move off the primary monitor. Your advice is EXTREMELY appreciated thanks. Denni ...

Display or not display series
I have a graph with four series but some of the series may be blank, i.e. the series name is <blank series>. Is there a way to not display these series on the graph and/or legend if they are blank, and display them if they are without deleting the series? Thanks! Clay Create a chart from all the data Select the data on which the chart is based. Choose Data>Filter>AutoFilter In the heading cell for the series name column, select (NonBlanks) from the dropdown list The series with blank name cells will be hidden in the chart. Clay wrote: > I have a graph with four serie...

Displaying an equation
I have an equation setup that is referencing a few other cells that have equations in them. I want to have a cell that displays this equation with the answers that are calculated from these other cells displayed as values in the new equation. Any help would be greatly appreciated. Thanks Check out http://www.mvps.org/dmcritchie/excel/formula.htm#getformula In article <E4143E2B-360A-4B43-955F-73BF8EC60694@microsoft.com>, "hoising8" <hoising8@discussions.microsoft.com> wrote: > I have an equation setup that is referencing a few other cells that have > eq...

Data Value in a Chart
By default when you enable the data label in a 3-D Bar Chart, the data label starts in the middle of the bar chart. Is there any way to change the label to be out of the bar chart? This problem happens only with 3-D Bar Charts. Thanks Joe Joe - If you double click on the labels, and click on the Alignment tab, there is no Label Position option as there is for non-3D charts. You can select and move each label individually with the mouse, or use a VBA solution, like the Move Labels feature of Rob Bovey's Chart Labeler addin (free at http://appspro.com). But you can't specify an...

Serialisation
A general query Im serialising a person object with the following member variables Class Person Private _forenames As String Private _surname As String Private _dob As Date Private _marital_status As MaritalStatus 'Public Get and Set Properties here for each of the above ' I'll not detail them all to save space but Ill show attributes <XmlElementAttribute(Elementname:="forename")> _ <XmlElementAttribute(Elementname:="surname")> _ <XmlElementAttribute(Elementname:="dob"...

New Email does not display
When new email arrives you don't know it. You don't know you have new mail until you click on an existing email in your inbox. Any ideas? It's a setting. But... 1. You don't tell us the program you're using. 2. We can't guess if it's Outlook or Outlook Express. 3. This is the Excel newsgroup; you're in the wrong place. :) ******************* ~Anne Troy www.OfficeArticles.com "Travis" <Travis@discussions.microsoft.com> wrote in message news:D864BC01-7D98-48DA-A132-614D02E45963@microsoft.com... > When new email arrives you don't know ...

diable command button based on field value
I have a command button which I would like to disable based on the following rules: If A = 1 then button is disabled If B = 2 then button is enabled Where you put the code depends on how the form works. If this needs to be checked for each record, use the form current event: If A = 1 Then Me.Button.Enabled = False ElseIf B = 2 Then Me.Button.Enabled = True End If But, what if A is not 1 and B is not 2? -- Dave Hargis, Microsoft Access MVP "barrynichols@gmail.com" wrote: > I have a command button which I would like to disable based on the >...

Display 4 weeks of data
Hello all, I am trying to create a query in which data is displayed for the current month beginning with the first day of the week, first week of the month and ending with the last day of the week, last week of the month. In example: Today is Friday June 1, 2007 but the first day of the week would be Sunday, May 27, 2007. I would like the query to show data between 5/27/2007 through 6/30/2007 -OR- 4/29/2007 through 6/2/2007 for the previous month... Does anyone have any ideas? First day of the current month is DateSerial(Year(Date()), Month(Date()), 1) Last day of the current month...