Before Update event

petern
StarLounger
Posts: 72
Joined: 10 Feb 2010, 15:45
Location: Kitchener, Ontario

Before Update event

Post by petern »

I have a form for estimates of giving in a church. For various reasons, we need to be able to enter the amount either as a weekly amount or a monthly amount. Once one is entered, the other will be automatically recalculated. For those instances where both already have data, I have a before update event to remind the user there is info in the opposite field and do they want to proceed with the change. Right now, if they say no, rather than having the field stay unchanged, the value is getting changed rather randomly. It seems to be going down to a single digit. Anyways, here is the code. Is there something wrong with this syntax or should it do what I describe (rather byzantinely) above?

Code: Select all

Private Sub WkAmt_BeforeUpdate(Cancel As Integer)
If Not IsNull(Me.MnthAmt) Then
    If MsgBox("You already have a monthly amount entered. Do you want to change it?", vbYesNo, "Change Pledge?") = vbYes Then
    Me.MnthAmt = Null
    Else
    Cancel = True
    End If
End If
The part that isn't working is Cancel = True the other side of the If statement works fine.
Peter N

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Before Update event

Post by HansV »

You state "Once one is entered, the other will be automatically recalculated". How does that happen?
Best wishes,
Hans

petern
StarLounger
Posts: 72
Joined: 10 Feb 2010, 15:45
Location: Kitchener, Ontario

Re: Before Update event

Post by petern »

I told you my description is byzantine! However, as I was writing a long reply I figured out what was missing. I added Me.Undo after the Cancel = true. Now it is doing what I need. As frequently happens, stepping back to describe the problem allowed me to think about it more clearly as opposed to the usual :brickwall:

Thanks, anyways.

Code: Select all

Private Sub WkAmt_BeforeUpdate(Cancel As Integer)
If Not IsNull(Me.MnthAmt) Then
    If MsgBox("You already have a monthly amount entered. Do you want to change it?", vbYesNo, "Change Pledge?") = vbYes Then
    Me.MnthAmt = Null
    Else
    Cancel = True
    Me.Undo
    End If
End If
Peter N

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Before Update event

Post by HansV »

petern wrote:As frequently happens, stepping back to describe the problem allowed me to think about it more clearly
Yep! That's very true for me too!
Best wishes,
Hans