If Then Message Box Issue 1

[cpsqlrwn]

This code I have below is not doing what I want it to do. When the first message box response is no it does cancel properly. But when the message box response is yes it exits without running the rest of the code. The execution doesn't proceed to ProcessReceipt and the rest of the code is not run. The record is immediately saved with no additional user input. The second message box never appears. If I remove the Exit Sub line after End If all the code runs regardless of the response to the first message box. It just seems as though my GOTo line is being ignored. I must be missing something. Can someone please help me with this? Thank you.

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Form_BeforeUpdate

Dim ZeroResponse As Integer
If Me![TotalCostReceived].Value = 0 Then
ZeroResponse = MsgBox("You have entered Total Cost Received of ZERO!" _
& vbCrLf & vbCrLf & "Are you sure this is correct?", vbYesNo _
+ vbQuestion + vbDefaultButton2, "Zero Cost Entered")
If ZeroResponse = vbNo Then Cancel = True
Else
GoTo ProcessReceipt
End If
Exit Sub

ProcessReceipt:

Dim ItemCost As Double

ItemCost = Me!TotalCostReceived.Value / Me!QuantityReceived.Value
Me!UnitCost.Value = ItemCost

Dim UnitRec As String

UnitRec = UnitReceived
Me!ItemUnitReceived = UnitRec

Dim Response As Integer

Response = MsgBox("Save this receipt or Cancel?", vbOKCancel + vbQuestion _
+ vbDefaultButton1, "Process Receipt")

If Response = vbCancel Then Cancel = True
Exit Sub

Exit_Form_BeforeUpdate:
Exit Sub

Err_Form_BeforeUpdate:
MsgBox Err.Description
Resume Exit_Form_BeforeUpdate

End Sub

 

[Remou]

There is quite a long thread on why GoTo should not be used:
What's So Bad About Using GoTo
thread705-1328162

It is a good idea to use [ignore]

Code here

[/ignore] TGML tags (see below) to mark your code.

Putting all the 'Dims' in one chunk can make life easier.

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Form_BeforeUpdate

Dim ZeroResponse As Integer

If Me![TotalCostReceived].Value = 0 Then
    ZeroResponse = MsgBox("You have entered Total Cost Received of ZERO!" _
    & vbCrLf & vbCrLf & "Are you sure this is correct?", vbYesNo _
    + vbQuestion + vbDefaultButton2, "Zero Cost Entered")
    If ZeroResponse = vbNo Then
       Cancel = True
       Exit Sub
    End If
End If
        
Dim ItemCost As Double

ItemCost = Me!TotalCostReceived.Value / Me!QuantityReceived.Value
Me!UnitCost.Value = ItemCost

Dim UnitRec As String

UnitRec = UnitReceived
Me!ItemUnitReceived = UnitRec

Dim Response As Integer

Response = MsgBox("Save this receipt or Cancel?", vbOKCancel + vbQuestion _
+ vbDefaultButton1, "Process Receipt")

If Response = vbCancel Then
   'How about an undo?
   Cancel = True
   Exit Sub
End If
   
Exit_Form_BeforeUpdate:
    Exit Sub

Err_Form_BeforeUpdate:
    MsgBox Err.Description
    Resume Exit_Form_BeforeUpdate

End Sub

 

[cpsqlrwn]

Thank you Remou!

If I could ask you another question. I like to have numerical processing type fields default to zero and I have noticed that the Before Update event does not occur if the field has not changed. For example I default a quantity received field to zero (for appearances only) and I want a message box to appear if the value is zero because the value actually needs to be a non zero number. If I attach that message box code to the Before Update event on the control, and the user simply leaves the zero in the field, the code does not run. As an alternative I have been using the On Exit event which works whether the control has been modified or not. In your opinion, is this an acceptable approach or should I be using another method with the Before Update event which would cause it to run regardless?

 

[Remou]

Before Update will not run when a control is altered programmatically. A 'Save' button is probably the most suitable. You will find code in the FAQs that forces the user to click a button to close the form:
KISS way to force users to click a command button to exit a form regardless of menu or toolbar options
faq702-2071

 

[PHV]

A good practice (IMHO) is to enforce ALL the constraints in the BeforeUpdate event procedure of the form, playing with the SetFocus method and the Cancel parameter.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886