BeforeUpdate Cancel event

[IfButOnly]

Would appreciate it if someone can help me with this problem. On the BeforeUpdate event on my form, I am checking that required fields are entered and giving the user the option to go back and re-enter or Close the form (modal, PopUp).
__________________________________
If the response to the msgbox is NO (close and don't save), I get the error message:-

Run-time error '2501':
The Close action was canceled.
(highlights the DoCmd.Close command)
____________________________________
If the response to the msgbox is Yes (go back and re-enter), I get the following message:

….You can't save this record at this time.
…Microsoft Access may have encountered any error while trying to save a record…

I have tried to hide this with the SetWarnings command, but to no avail.
___________________________________

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Dim enoughinfo As Boolean
enoughinfo = True
' Check we have all required fields
If Len(Me.Surname) < 1 And Len(Me.Firstname) < 1 Then enoughinfo = False
If IsNull(Me.Street) Or IsNull(Me.Suburb) Then enoughinfo = False

If Not (enoughinfo) Then
Msg = "You must provide Client Surname or Firstname," & vbCrLf etc, etc
Title = "NOT ENOUGH INFORMATION ENTERED " ' Define title.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
' DoCmd.SetWarnings False
Cancel = True
If Response = vbNo Then DoCmd.Close
If Response = vbYes Then Me.Surname.SetFocus
' DoCmd.SetWarnings True
GoTo ExitUpdate:
End If

_________________________

Any help appreciated.

 

[Rich]

If Response = vbNo Then DoCmd.Close
Else
Surname.SetFocus

 

[IfButOnly]

Rich, tried your suggestion but both problems remain.

Any other thoughts appreciated.

 

[Jack Cowley]

Try this....

If Not (enoughinfo) Then
Msg = "You must provide Client Surname or Firstname," & vbCrLf etc, etc

Title = "NOT ENOUGH INFORMATION ENTERED " ' Define title.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)

If Response = vbNo Then
Me.Undo
Cancel = True
DoCmd.Close
Else
Me.Surname.SetFocus
End if

End If

GoTo ExitUpdate:

hth,
Jack

 

[IfButOnly]

Jack, tried your suggestion -

Still had the same problem when No selected.

If Yes selected, closed the form.

Any other thoughts appreciated..

 

[Jack Cowley]

If you have a Close Button on the form with DoCmd.Close in it then that is probably your problem. In the Before Update event uses error trapping something like this in the code I posted:

Private Sub Form_BeforeUpdate(Cancel As Integer)

On Error Goto ErrorHandler

All your code here

ErrorExit:
Exit Sub

ErrorHandler:

If Err.Number = 2105 The Resume ErrorExit

MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ErrorExit

End Sub

I hope that does it for you...

Jack