Understanding afterupdate

[MAM8433]

Hi, all:
I hope you can help this VBA newbie. It's probably rooted in my lack of understanding and experience with Access events.



I realize that I should not store a calculated variable such as mileage cost (miles * rate) in my cost table. So I am storing Mileage and MIRate (with a default value).

My form, based on an all-fields query of my cost table, defaults to datasheet view. I would like to display a message box to appear immediately after the user enters mileage so the user can confirm the calculated mileage costs. So I created an afterupdate event:

Private Sub Mileage_AfterUpdate()
MsgBox prompt:="Mileage costs = $ " & Mileage * MIRate
End Sub

Nothing happens. It won't popup on before update or on change What must I do to get the msgbox to appear? Thanks!

[Bob Fitz]

Hi

Not sure if this will work.

Private Sub Mileage_AfterUpdate()
MsgBox prompt:="Mileage costs = $ " & Me.Mileage * Me.MIRate
End Sub

[MAM8433]

Thanks, Bob It's a matter of getting the message box to pop open. [I did fix as you recommended and it still won't display.]

[jzwp11]

I'm not sure about your syntax; perhaps something like the following. Once you have the verification or not, you have to do something with it (I assume). In the code below, if the user clicks OK, the code just exits, if they click cancel, it undoes the record, so they have to start over.

Private Sub miles_AfterUpdate()
Dim holdresp As Long
holdresp = MsgBox("Mileage Cost= $" & Me.miles * Me.MIRate, vbOKCancel, "Verify Cost")
If holdresp = 1 Then
    Exit Sub
Else
    Me.Undo
End If

End Sub

[MAM8433]

Hi, jzwp11:

I don't think I need a message box function, when a simple message statement will do. Once the user clicks okay, he/she should return to the datasheet and move on or reconcile. Still, I need to get the box to pop up.

[nicknameoscar]

Put a Debug.print "AfterUpdate ran" in your code and see if "AfterUpdate ran" shows up in your Immediate window in the VBA IDE. This will tell you if the AfterUpdate event is firing.

If it doesn't fire it might mean your database is not stored in a Access recognized safe location. Do you see the "Security Warning: Certain content in the database has been disabled" message?

Can you attach the database so users here can look at it?

[jzwp11]

Whether or not you need the code to do what I showed. Did you try it to see if the message prompt showed up (in my test database it did)? If it did not show up, then you should probably follow nicknameoscar guidance.

[Bob Fitz]

Hi

If the AfterUpdate event does not fire you could check that [Event Procedure] has been selected in the AfterUpdate event property sheet.

[MAM8433]

You all are terrfic!

Nicknameoscar nailed the problem. I have not established a trusted location for the database (but let me assure you that I will do that next). This morning I must have neglected to properly handle the warning message. Truly, a head-slapping I could have had a V-8 moment. Thank you for teaching me what to consider should this EVER happen again. Cheers!