Before Update

[Joxk via AccessMonster.com]

I have the following code in the Before Update even of a form which I have
take from the threads on this site:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If MsgBox("Do you wish to save and proceed to the supplementary questions?
", vbYesNo) = vbNo Then
Cancel = True
Me.Undo
MsgBox "Entery has been cancelled", vbOKOnly + vbInformation
DoCmd.SetWarnings False

End If

End Sub

What I wish to do is amend it so that if the user clicks “No†on the message
box the records aren’t saved and the form simply closes. However if the user
clicks “Yes†I would like the record saved, the form to close and a new form
open.

I have tried creating a macro that launches the next form when the inital one
closes, however the next form opens whether the user click "yes" or "No"

I’m not so good with code, so any help is really appreciated

 

[BruceM]

I assume you clicked a button (or something) to go to the other form. When
you did so, you forced a save of the current form's record. That is
triggered the Before Update event. Once that event had run, the form went
ahead and opened. You canceled the update, not the opening of the other
form. In the command button on the first form (frmFirstForm) to go to the
other form you could have code to enter the form (be sure to use your actual
form name):

Private Sub cmdOpenForm_Click()

On Error GoTo ProcErr

DoCmd.OpenForm "frmSupplemental"

ProcExit:
Exit Sub

ProcErr:
If Err.Number <> 2501 Then
MsgBox "Error #" & Err.Number & ", " & Err.Description & " -
cmdOpenForm"
End If
Resume ProcExit

End Sub

Then, in the Open event for frmSupplemental:

Private Sub Form_Open(Cancel As Integer)

Dim strMsg as String
strMsg = "Do you wish to save and proceed to the supplementary questions?"

If MsgBox(strMsg, vbYesNo) = vbNo Then
Forms!frmSupplemental.Undo
Cancel = True
End If

End Sub

The error handling in the first sub to trap Error 2501 is to prevent a
(pretty much useless in my experience) message telling you that the Open
Form action has been cancelled.

I'm not sure the point of setting warnings to false in your code. It's
something that generally should be done temporarily (within a single sub or
function, for instance), if at all.

 

[Joxk via AccessMonster.com]

Hi Bruce

Thanks for your reply, your solution worked a treat.

FYI, I set warninngs to false as it was the only way I could think of to
prevent the message saying that the record could not be saved at this time.

Thanks again for your help.

 

[BruceM]

It would probably be better to trap the error message as I showed, but if
you are going to set warnings to false you also need to set them back to
true. Typically they would be set to false at the beginning of the sub or
of a section of code within the sub, then back to true at the end of the sub
or the section of code. Setting warnings to false will continue to apply
after the sub is complete, while error trapping will only apply to the
event.