Results 1 to 15 of 15
  1. #1
    Join Date
    Nov 2009
    Posts
    11

    Unanswered: Textbox Events Not Firing

    I have an Access 2003 database. One one of the forums, I have a textbox, called txtTotal, whose control source is set to add up all of the rest of the textboxes on the form (there are several).

    I have to ensure txtTotal never exceeds 100 (this is actually formatted as Percent, so I'm checking to see that 100% is not gone over)

    I figured it would be pretty easy by doing
    Code:
    Private Sub txtTotal_Change()
    If Val(txtTotal.Text) > 100 Then
       MsgBox "The total for the this metric can not exceed 100%.  Please make the necessary adjustments.", vbOKOnly + vbExclamation, "Total Exceeds 100"
       txtTotal.ForeColor = vbRed
       txtTotal.FontBold = True
    Else
       txtTotal.ForeColor = vbBlack
       txtTotal.FontBold = False
    End If
    End Sub
    But, much to my surpise the code never ran because the event did not fire when the total in the textbox changed. So, I moved the above code to the AfterUpdate event....that never fired either nor did the BeforeUpdate event.

    In each of these events I had a break set on the Private Sub line. But nothing happened on any occasion.

    I am pretty experienced in VB6 and VB.NET...not so much in VBA so I don't know what I'm missing here. Why aren't these events firing when I make a change to the totals textbox?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Do you change the value of txtTotal through the interface (Keyboard/Mouse) or is some VBA code used to perform the modifications?

    Changing the Value property of a control using code does not triggers the OnChange, BeforeUpdate, AfterUpdate events, you have to call the corresponding event handlers manually in your code.
    Have a nice day!

  3. #3
    Join Date
    Nov 2009
    Posts
    11
    Quote Originally Posted by Sinndho View Post
    Do you change the value of txtTotal through the interface (Keyboard/Mouse) or is some VBA code used to perform the modifications?
    The textbox is set to add up the totals of 23 other textboxes. The Totals textbox can not exceed 100. If it does, I need to immediately generate an error message, and undo what the user has just done. The Totals textbox can not be typed into directly. It updates based on what is done in the other textboxes.
    Quote Originally Posted by Sinndho View Post
    Changing the Value property of a control using code does not triggers the OnChange, BeforeUpdate, AfterUpdate events
    Yes...this is what I have found out and it is a behavior that I think is bizarre.

    <Rant>For years I've maintained that the VBA development group at Microsoft never spoke to the Visual Basic development group. There is no reason for these two platforms to be so alike in some respects and so different in others.</Rant>

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    As Sinndho said, populating Controls thru Code won't pop any Control-associated events. How/where are you doing your calculation? This is where you're going to have to do your Validation, either directly there or by explicitly Calling a Control-associated event that does the Validation.

    Linq ;0)>
    Last edited by Missinglinq; 09-29-11 at 10:31.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Nov 2009
    Posts
    11
    Quote Originally Posted by Missinglinq View Post
    How/where are you doing your calculation?
    Quote Originally Posted by Moe1950 View Post
    The textbox is set to add up the totals of 23 other textboxes.
    The totals textboxs' control source is bound to a calculation which adds all the other texboxes together. Example
    Code:
    =Textbox1+Textbox2+Textbox3+etc+etc+etc

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    First off, you're going to need to change your Calculation to allow for Fields that are empty, otherwise you'll pop an Error. You can do this using the Nz() Function:

    Code:
    = Nz([textbox1],0)+ Nz([textbox2],0)+ Nz([textbox3],0)
    Now, in the Form's Code Module, create a Validation Sub to check the totals, using your posted Code
    Code:
    Private Sub CheckTotal()
       If Val(txtTotal) > 100 Then
         MsgBox "The total for the this metric can not exceed 100%.  Please make the necessary adjustments.", vbOKOnly + vbExclamation, "Total Exceeds 100"
         txtTotal.ForeColor = vbRed
         txtTotal.FontBold = True
      Else
         txtTotal.ForeColor = vbBlack
         txtTotal.FontBold = False
      End If
      End Sub
    Notice that I changed txtTotal.Text to txtTotal, which is the same as txtTotal.Value. In Access VBA you can only refer to the Text Property when a Control actually has Focus, but the Value Property has no such restriction.

    Now, in the AfterUpdate event of each Control that is part of the Calculation, Call the Validation Sub
    Code:
    Private Sub Textbox1_AfterUpdate()
       Call CheckTotal
      End Sub
       
      Private Sub Textbox2_AfterUpdate()
       Call CheckTotal
      End Sub
       
      Private Sub Textbox3_AfterUpdate()
       Call CheckTotal
      End Sub
    Linq ;0)>
    Last edited by Missinglinq; 09-29-11 at 14:19.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    Nov 2009
    Posts
    11
    There should never been an empty field. The table is loaded from a mainframe download. All of the fields have a positive value or a 0.

    Of course, that is in theory. I believe I will go with your NZ suggestion, however...just in case.

    Anyway, why did you use the AfterUpdate? I need this check to be done before the update, and if the total exceeds 100, to not do the update.

    Example: A textbox contains 50% - all of the other textboxes, combined, total up to another 50% which equals 100. Now, a user comes along and changes the 50% in the first textbox to 60%. This would put the total a 110% which is not allowed, so in addition to the message box saying "No No you fool" I do not want the datatable updated with 60, but left at 50. Isn't that what, theoretically, BeforeUpdate and Cancel = True should do?

  8. #8
    Join Date
    Nov 2009
    Posts
    11
    Quote Originally Posted by Missinglinq View Post
    Notice that I changed txtTotal.Text to txtTotal, which is the same as txtTotal.Value. In Access VBA you can only refer to the Text Property when a Control actually has Focus, but the Value Property has no such restriction.
    Thanks for this...I wish I'd known this sooner. It would have saved a whole lot of TextBox.Setfocus coding.

  9. #9
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by Moe1950 View Post
    Of course, that is in theory. I believe I will go with your NZ suggestion, however...just in case.
    Probably best! It's good to get into the habit!
    Quote Originally Posted by Moe1950 View Post
    ...why did you use the AfterUpdate? I need this check to be done before the update, and if the total exceeds 100, to not do the update.
    The Control AfterUpdate fires as soon as you leave the Textbox, before the Record is updated, so if an entry in any of the Calculation's component Fields throws the total over 100 you'll know it immediately. I suppose you could use the Controls' BeforeUpdate events and use Cancel, but I tend to stay away
    from them because in some situations they can trip you up. Like many things in programming, it's just a personal preference.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if the data is coming from a third party then make the default value for the column 0, unless you actually want a null value.

    nulls do have their role in a db, but you need to understand what they represent and what their purpose is.

    Null means that there is either no known value for that intersection or that a value isn't known art present. although 0 can also mean there is no value for that intersection its not the same as null. null can at times tell more than 0, null could infer that a result is expected or is never going to be expected.

    ferisntance I had a mathmatician all excited because she'd found a bug in Access because her percentages were all to cock. the real reason was that she was using 0 as a default value instead of null so when using the domain average fucntions she was getting the 'wrong' answers ie a value of 0 was interpreted (correctly) as a mark of 0 and therefore that dragged the averages down. instead she should have used null to indicate there was no result for that particular intersection of her data.

    providing there is no reason why a null value should be sued I'd be tempted to set the default value to zero and do away with the associated problems of handling a null value
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Nov 2009
    Posts
    11
    Well, after all of this I discover the database is being updated with the change no matter where I'm putting the validation code.

    So now I decided to store all existing values in variables when the table opens and if a particular value causes the total to exceed 100, I'll still display the aforementioned messages, but then I will also, through code, repopulate the textbox with the content of the corresponding value saved at runtime.

    We will see how that works.

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    As I see it there a several sources of error here
    one is you are getting crap data. one way round that is to identify all such records usign an appropriate SQL query, thatr is fired up by some process. whetehr taht process is when a specific person or member of a usergroup opens the db / form or by a batch process which runs immediately after the import is up to you

    another source of error is if soemone edits the data
    as missingling has suggested they way round that is two fold
    one write a function which returns true or false if the sum of the columns = 100. then place a call to that function behind various events. the precise events is up to you, and how you want to handle this data error.
    I'd suggest in the forms on current record (so that it flags an error when the record changes)
    behind each controls on change event
    behind the forms on update event

    I wouldn't use a modal message box as those get seriously annoying if you are doing data entry. I'd change, say the background colour of the relevant controls to identify there is a problem, inhibit the update event (perhaps with a modal msgbox there though)
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Nov 2009
    Posts
    11
    This app will be used as a stand along Access database that will be run from a field reps' laptop.

    The fields reps will use this application to load metric scores based on 32 different categories which will be evaluted on a field visit to a physicians office. The results of these metrics will depend on whether the Healthcare orgainization I work for gives them some extra cash once a year.

    The tables used for this are preloaded. All fields have entries in the form of a percentage amount. Added all together, the percentages add up to 100%

    That is where they start.

    What can't happen in the field is for a rep to score one part so high it exceeds the overall total of 100.

    I know these reps...they have the computer savy of a pet rock.

    I'm trying my new method of saving the total when the form loads, and if an input into a textbox causes the total to exceed 100 I pop up a message, then write the original total back to the text box. The problem now is that I get this error message.
    Attached Thumbnails Attached Thumbnails BeforeUpdate.gif  

  14. #14
    Join Date
    Nov 2011
    Posts
    1

    Lightbulb Text box Change event not firing (apparently):

    Having beaten my head against the wall for some time due to the change event apparently not firing on some text boxes on a pair of subforms
    I gleaned the following, which may be of use to other amateurs:

    My example involved a pair of identical subforms sitting together on another subform of a main form (not altogether relevant to the problem)
    I wanted to compare text boxes between the two forms, and highlight one of the text boxes when the values differed - simple?

    The two subforms are "PdataEnt7" and "PdataEnt8", and the text box in each case is "Para71", when they differ I want to highlight the box on the second form in orange.

    my first attempt looked like this:

    PdataEnt7 Code:

    Private Sub Para71_Change()

    If (Me.Para71.Value <> Me.Parent.PdataEnt8.Form.Para71.Value) Then
    Me.Parent.PdataEnt8.Form.Para71.BackColor = 4227327
    Else
    Me.Parent.PdataEnt8.Form.Para71.BackColor = 16777215 '16777215 White 65280 Green
    End If

    End Sub

    PdataEnt8 Code:

    Private Sub Para71_Change()

    If (Me.Para71.Value <> Me.Parent.PdataEnt7.Form.Para71.Value) Then
    Me.Para71.BackColor = 4227327
    Else
    Me.Para71.BackColor = 16777215 '16777215 White 65280 Green
    End If

    End Sub

    This just didn't work, and I was convinced that the change event was not being triggered, but I was wrong!
    On investigation what I found was that when you start to change the text in one of the boxes, the change event fired but the 'If' routine wasn't working because the .value at that point doesn't reflect the change, whereas the .text does.
    So by changing "Me.Para71.Value <>" to "Me.Para71.Text <>", Bingo! What you mustn't do is to change the second half "Me.Parent.PdataEnt7.Form.Para71.Value" to the .text form, as that will throw an error as you can
    only reference .text for a control that currently has Focus.

    Note also that if you don't use the explicit form ie. "Me.Para71" instead of "Me.Para71.text", this is equivalent to "Me.Para71.Value", and won't work for that reason.


    This is the version that worked

    PdataEnt7

    Private Sub Para71_Change()

    If (Me.Para71.Text <> Me.Parent.PdataEnt8.Form.Para71.Value) Then
    Me.Parent.PdataEnt8.Form.Para71.BackColor = 4227327
    Else
    Me.Parent.PdataEnt8.Form.Para71.BackColor = 16777215 '16777215 White 65280 Green
    End If

    End Sub


    Private Sub Para71_Change()

    If (Me.Para71.Text <> Me.Parent.PdataEnt7.Form.Para71.Value) Then
    Me.Para71.BackColor = 4227327
    Else
    Me.Para71.BackColor = 16777215 '16777215 White 65280 Green
    End If

    End Sub

    ***********

    And so to the next challenge!

  15. #15
    Join Date
    Feb 2013
    Posts
    1

    Smile Thank you!

    So many times in our web forums I think I've found the answer to a problem, only to realize the answer isn't actually listed. Thank you so much for continuing in the forum and giving us the 'fix'; it was exactly what I've been looking for for WEEKS!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •