vbOKCancel - Cancel doesn't work! 3

[dpav29]

I posted this previously and got a lot of help, but I've changed the code so much and the thread is so messy I thought it deserved a re-post for this final point:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim response As Integer
response = MsgBox("Are you sure you want to save this record?", vbOKCancel)
Exit Sub
If response = 2 Then
Cancel = True
Me.Notes.SetFocus


End If
End Sub

Clicking Cancel actually performs exactly as when you click save! It saves the record and moves to a new blank record. I should mention that the save button has a macro behind it that is "go to record, new". The theory is, the vbOKCancel should fire before the macro runs and cancel should stop the macro. Should there be some code added to the above If statement that stops the new record macro?

 

[jimmythegeek]

Try this:

If MsgBox("Are you sure you want to save this record?", vbOKCancel + vbQuestion, " Save Record?&quot = vbCancel Then
Cancel = True
Me.Notes.SetFocus
End If

This should cancel adding the record and set focus to the notes field. If you actually want to exit the record or form, then do a Me.Undo instead (this will take you to the next record).

If MsgBox("Are you sure you want to save this record?", vbOKCancel + vbQuestion, " Continue?&quot = vbCancel Then
Me.Undo
Me.Notes.SetFocus
End If

(this will exit the form)

If MsgBox("Are you sure you want to save this record?", vbOKCancel + vbQuestion, " Continue?&quot = vbCancel Then
Me.Undo
DoCmd.Close
End If Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[dpav29]

That works if I simply "tab" out of the record. But, if I click my save button which fires the "add new record" macro, it still gets caught up in the macro command. Is there I way I could add a command to stop the macro as well when the cancel button is clicked?

Maybe something like DoCmd.CancelAction "mcrNewCase"

or maybe the save button shouldn't fire a macro at all. . . the client want's this save button, but maybe there's a way for it to just mimick the tab button instead???

 

[jimmythegeek]

Why is a macro necessary? Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[dpav29]

Is there another way you know of to click a command button to go to a new record?

 

[mph1]

To use a command button to go to a new record, use the following code in the On Click event of the command button (you should erase any other code that is underlying)

DoCmd.OpenForm "form Name",acNornal
Docmd.GoToRecord acDataForm, "form name",acNewRec
DoCmd.GoToControl "text box name" 'this is to set the focus

HTH

Maurie

 

[dpav29]

That works, but it still conflicts with my "before update" logic which is the vbOKCancel problem:

If MsgBox("Are you sure you want to save this record?", vbOKCancel + vbQuestion, " Save Record?&quot = vbCancel Then
Cancel = True
Me.Notes.SetFocus

End If

So, if you cancel, instead of setting focus back to the current record, it's trying to run the code you just gave me and I get the runtime error "can't go to specified record".

I think I need to add something that will stop it if the cancel button is clicked in the before update. What do you think?

 

[jebry]

Hi!

Put the logic in the command button click:

If MsgBox("Are you sure you want to save this record?", vbYesNo + vbQuestion, " Save Record?&quot = vbYes Then
DoCmd.RunCommand acCmdRecordsGoToNew
Else
Me.Undo
End If

hth
Jeff Bridgham
bridgham@purdue.edu

 

[dpav29]

jebry,

THAT'S THE TICKET!!!!!!!

One last thing. . . since I'm now not using the before update event, how can I stop the user from simply "tabbing" through the save button to the new record?

 

[jebry]

Hi!

The method that comes to my mind first is to add a form level variable, frmSave as a boolean. In the Current event of the form set frmSave = False. Adjust the code in the click event of your save button to:

If MsgBox("Are you sure you want to save this record?", vbYesNo + vbQuestion, " Save Record?&quot = vbYes Then
frmSave = True
DoCmd.RunCommand acCmdRecordsGoToNew
Else
Me.Undo
End If

Then, in the before update event procedure use:

If frmSave = False Then
Call MsgBox("You must use the Save Button to save a record and the exit button to exit the form&quot
Cancel = -1
End If

Note, you will also need a button to exit which uses the following code:

frmSave = True
DoCmd.Close acForm, Me.Name

hth
Jeff Bridgham
bridgham@purdue.edu

 

[dpav29]

DONE!!!!! Finally!

Thanks a million Jeff, Jim & Maurie. . . .you are true friends!

 

[dpav29]

Wait, I spoke too soon!

If you click save/OK, you get the "you must click the save button. . " before update event

When I click ok, I get a runtime error!

 

[mph1]

Dave,
I re-read the posts and can appreciate your frustrations, but am not sure exactly what you have done. If you are using the mentioned code, there will be no reason to have the macro attached. I am not sure, but that could be causing a conflict. Hopefully some else can be more specific. I use macros a great deal, but when I convert the macro to code, I erase the macro. I also check the code of everything to make sure there are no references.

Good Luck
Maurie

 

[jebry]

Hi!

Post the final version of all the code you are using (just cut and paste will be good). If we can look at all you have done, we may be able to determine how they are interacting to give you those errors.

Jeff Bridgham
bridgham@purdue.edu

 

[dpav29]

I just checked, and the macro's are gone. So now it's just the current:

Private Sub Form_Current()
frmSave = False
End Sub

Then the "on click" event of the save button:


If MsgBox("Are you sure you want to save this record?", vbYesNo + vbQuestion, " Save Record?&quot = vbYes Then
frmSave = True
DoCmd.RunCommand acCmdRecordsGoToNew
Else
Me.Notes.SetFocus 'put the focus on the last field


End If

End Sub

Then, the before update:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If frmSave = False Then
Call MsgBox("You must use the Save Button to save a record and the close button to exit the form.&quot
Cancel = -1
End If

End Sub

Somehow, the "true" command is not changing the current state to "frmSave = True" or. . .may I need an "else" statement in the before update command?/

 

[dpav29]

I think the error is on the

"on click" event of the save button:

DoCmd.RunCommand acCmdRecordsGoToNew

 

[mph1]

Dave,
According to your code, you have a sub-form

Private Sub Form_Current()
frmSave = False
End Sub

Is that correct? You are in the correct ball park, just not on the correct base. When you open this form, determine exactly what you want to occur. Also, what buttons do you want. SAVE, DELETE, SWITCHBOARD, NEW RECORD ect. I think what you are trying to accompolish can be done with one button OnClick. If what you are trying to do is much more complex, start with small steps, then work your way to an end. I have found (the hard way) thinking what I had was good and trying to fix it was not the best.

Hope this helps.
Maurie

 

[dpav29]

No, there's no sub form. It's just the one Main form giving me trouble. That's referring to the On_Current event of the Form. . ."Private Sub" Form On_Current.

I just need the Save and Close Buttons.

 

[mph1]

Using the SAVE button, use the OnClick event. This will cause the dialog box to appear. If yes is clicked, then save and go to next record. By clicking Yes, the record is saved. If no is clicked, either undo entire entry or just leave as is and let the user make a correction, until they check yes (you will have to decide which alternative). For the close button, all you are really doing is closing the current form and going to something else.

DoCmd.OpenForm "formname",acnormal ' new form
DoCmd.Close acForm, "formanme ' current form

Make sure you use close the current form by code, last.

If it is another input form, use the code I posted before.
The following is a copy of code I used:
************************************************************
Private Sub Save_Click()
Dim strMsg As String
strMsg = "Data may have changed."
strMsg = strMsg & " Do you wish to save the changes?"
strMsg = strMsg & " Click Yes to Save and go to a new record!"
strMsg = strMsg & " Click No and none of the changes will be saved and you will stay on the current record!"
If MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?&quot = vbYes Then
DoCmd.GoToRecord acForm, "F MemList", acNewRec
DoCmd.GoToControl "FirstName"
Else
DoCmd.RunCommand acCmdUndo
End If
End Sub
************************************************************
As you will notice, I chose the route NOT to undo.

HTH
Maurie

 

[dpav29]

Thanks so much for sticking with me on this!!

But, I believe the problem is on the "before update" event of the form. This was needed because without it the user could just "tab" right through the save button with no warnings or anthing.

That is what's causing the problem . . a conflict between that and the "on click" of the save button. If I remove the "before update" event, everything works fine.. . I'd love to, but the purpose of the save button is then defeated!