Events Not Firing

[Moe1950]

I have an Access 2003 database. On one of the forms, 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

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?

[GolferGuy]

Sadly, VBA only looks at events that happen when the USER does something, that when something happens to an object (text box, button, etc.) So, even though there is a change that happens in this text box, no event will fire in Access. But, I'm assuming that some change is happening, by the USER, in the text boxes that are being summed into this Total text box.
Therefore, you can write a function that can be called when any of the other text boxes are changed. (Remember, the USER using the UI, is how the Change event happens.) BTW, I would suggest using the BeforeUpdate event, and if the total goes over 100% because of the value input, your program can put up an error on the spot, and if you set CANCEL = True, then the update to that text box (the individual text box) will be cancelled.
You could also use the AfterUpdate event, and let the user know there was an error somewhere, because the error could have been in an earlier text box. That is hard to know (which text box is causing the error) when you are dealing with an overall sum.
Hope this helps.

[Moe1950]

Sadly, VBA only looks at events that happen when the USER does something, that when something happens to an object (text box, button, etc.) So, even though there is a change that happens in this text box, no event will fire in Access.

I just knew I wasn't going to like VBA.

I'm assuming that some change is happening, by the USER, in the text boxes that are being summed into this Total text box.
Therefore, you can write a function that can be called when any of the other text boxes are changed. (Remember, the USER using the UI, is how the Change event happens.) BTW, I would suggest using the BeforeUpdate event, and if the total goes over 100% because of the value input, your program can put up an error on the spot, and if you set CANCEL = True, then the update to that text box (the individual text box) will be cancelled.

This sounds like the best approach for this situation.

Thank you GolferGuy....

[Moe1950]

I put the cart before the horse...I added the following code to the before update event

Private Sub txtPCMH_Capabilities_BeforeUpdate(Cancel As Integer)
'txtTotal.SetFocus
If Val(txtTotal) > 100 Then
   MsgBox "The proposed change causes the total for the Family Metrics to exceed 100%. _
   Please adjust your entry.", vbOKOnly + vbExclamation, "Can Not Exceed 100%"
   Cancel = True
End If
End Sub

Then I ran it and change the percentage in this textbox (txtPCMH_Capabilities) to a value I knew would exceed the 100% ceiling total for all texboxes. Nothing happend...my message box didn't pop up and the change I made didn't roll back. The only thing that happened was txtTotal changed from 100 to 110.

Now what am I doing wrong?

[Moe1950]

Ok, I got the thing to throw an error for over 100%. The values are stored as decimals 0.01 etc. So, even though the textbox displayed "100%" the actual value is 1.0 not 100. Once I fixed that, I get my error if the total of all boxes exceeds 100%

What isn't working, however, is Canceling the update. I'm using the BeforeUpdate event but it looks like it is updating anyway. That event doesn't fire until I tab off of the textbox which does not sound like "Before"Update at all. The update does happen no matter what it seems.

I do have cancel set to True

'example
Private Sub PCMH_Capabilities_BeforeUpdate(Cancel As Integer)
If Val(txtTotal) > 1 Then
   Cancel = True
   MsgBox sFamMsg, vbOKOnly + vbExclamation, "Can Not Exceed 100%"
   End If
End Sub

[GolferGuy]

BeforeUpdate is truly (in Access terms) before the update is committed to the database. What it sounds like you were expecting was a DuringChange which Access, IMHO, does not handle very well. That was a generic event, but Access does not have a DuringChange event as such. As I remember, the Change event will fire after every keystroke in the textbox. I consider that not an "easy" thing to handle, but if you wanted, you could watch and respond to every keystroke. But the before update should, as the user leaves the textbox, display the message AND cancel the update to the database. The cursor should also remain in the textbox waiting for the user to fix the problem they just "caused." Remember, the update Access is talking about is to the database, not the update to the screen. When I say what "should" happen, I mean that is what, as I remember, should be happen as Access does its thing, not what you should have to program into the things that happen.

[Moe1950]

I wound up storing the values of all textboxes, individually, in individual variables when the form opens.

It, during runtime, someone changes a value causing the total to exceed 100 I overwrite what the user did with the original value, thus resetting the total back to 100.

I do that after popping up an annoying message telling them they can not exceed 100%...thus far that seems to be working (but then I'm the one doing the testing...we will see what happens when it gets into the users hands. )

[GolferGuy]

While you are in the BeforeUpdate event handler, you have available to you the "OldValue." To get that value, use the textbox name like:
TextBox1.OldValue. This is a read only property, but it is available to you. When you issue the Cancel = True, you can also reset the original value within the text box: TextBox1 = TextBox1.OldValue
I'm not suggesting you undo what you have, but maybe for next time (if there ever is a next time with Access for you.)

[Moe1950]

you have available to you the "OldValue." To get that value, use the textbox name like:
TextBox1.OldValue.

You have no idea how much simpler this little bitty tidbit has just made my life.

GolferGuy you are on my Christmas Card list!!!

[GolferGuy]

Will there be a large check in that card? )
Moe, Thanks for the feedback, I'm just glad I can help. It always feels good.
Vic
PS: I'm taking a VB.net class right now so I can learn how the "big boys" write programs. VBA does have its limits.

[Moe1950]

VB.NET I know...I've been writing that since 2003...VB6 and before I know...been doing that since VB3 back in 1994.

VBA...not so much. Very first time I've ever had to deal with an actual VBA project. I've been doing visual programming for 17 years and this VBA thing has gotten me feeling like a freakin' noob!

[GolferGuy]

I've been doing VBA in Access, some in Excel and Word, since 1993. And I sure know what a freakin' noob feels like in VB.net. I have a GREAT teacher, and he has explained objects very, very well. I'm learning to really like classes (real ones, not like the ones in Access) and looking forward to "mastering" them.

[Moe1950]

At the risk of being yelled at by a moderator for "chit chatting", I will say one final thing and that is once you understand OOP and the concept of classes and how they actually work, figuring out how to use the wealth of classes available to you through the framework is pretty easy.

Thanks again...I'm sure I'll be back with other VBA questions so don't go too far.

[Allan Murphy]

Moe

At work we had a similar scenario. The total % had to be 100 so we used the After update of each text box then called a sub procedure to display the total in another text box

Sub calculate_tot_per()
On Error GoTo Err_calculate_tot_per

Me!tot_per = CDec(Me!in_5_1 + Me!in_5_2 + Me!in_5_3 + Me!in_5_4 + Me!in_5_5 + Me!in_5_6 + Me!in_5_7 + Me!in_5_8 + Me!in_5_9)
Me!balance_per = 100 - Me!tot_per

Exit_calculate_tot_per:
Exit Sub

Err_calculate_tot_per:
MsgBox Err.Description
Resume Exit_calculate_tot_per

End Sub

We used a button for the user to close and save the record if the total was not 100% then an error message was displayed. From memory we used CDEC as the total at times was not 100 but 99.999 etc but the figures on the screen totalled 100.

The text field were numeric Fixed two decimal places.

Here is a snippet of the code that was used when the user selected the Close button

strmsg = "Select Yes to save your the changes and exit" & Space(20) & _
vbCrLf & " " & _
vbCrLf & "Select No to exit without saving the changes" & _
vbCrLf & " " & _
vbCrLf & "Select Cancel to return to the screen"

msg_title = "Save changes" & Space(20)

Response = MsgBox(strmsg, vbYesNoCancel + vbDefaultButton3 + vbExclamation, msg_title)

Select Case Response

Case vbYes
If Me!tot_per = 100 Then ' save the record
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.Close acForm, "form name"
Else
strmsg = "Percentages changed do not add up to 100%." & Space(20) & _
vbCrLf & "No changes made. " & _
vbCrLf & "You will have to start again. "
msg_title = "Save Modules changes" & Space(20)
Response = MsgBox(strmsg, vbYes + vbDefaultButton1 + vbExclamation, msg_title)
DoCmd.Close acForm, "form name"
End If
Case vbNo
DoCmd.Close acForm, "form name"

End Select

Hope this helps you