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?

 

[mph1]

In the properties of the SAVE button go to Tab Stop and change to No (default is Yes), you can also do the same on the Close button. By doing this, the user must click the SAVE button or Close button and will not be able to tab through. After, doing this, delete the Before Update.

Ummm, now I see what you mean. I never thought of this. All my forms open to a new record (blank). By changing the tab Stops the user will not be able to activate the button with the tab key or enter key only. I tried using the following code on the last field of a form:
***************************************************
Private Sub size_Exit(Cancel As Integer)
MsgBox " you for got to save the record"
DoCmd.GoToControl "Acct#"
End Sub
****************************************************
There may be another way, but can't think of anything at the moment.

Maurie

 

[dpav29]

Well, I have tab stops on all fields (another client requirement). I don't mind taking it off the save button if that will work, but then if the user hits tab at the previous object, it'll still tab to the next record!

 

[mph1]

Try using the same code in the BeforeUpdate of the form and delete it from Exit of last field.

********************************************************
Private Sub Form_BeforeUpdate(Cancel As Integer)
MsgBox " you for got to save the record, click Save Button or something like this"
DoCmd.GoToControl "Acct#"
End Sub
*******************************************************
Just tried it and seems to work. Once a form is opened, new record, added some data, at end the message box appeared and returned me back to the first field Acct# and did not move to a new record. I hope this works.

 

[dpav29]

OK Everyone. . . .thanks once again for sticking with me on this. . .I'm new to VBA and wouldn't have been able to get though this without your help. I love you man!

Anyway, here's what I did and I think it solved everything:

Put a hidden field as the last tab stop on the form. I took the before update command off the form and put it on the "got focus" event of the hidden field. So now, if someone tabs past the save button, the "on focus event" kicks them to the "you must press the save button" warning.

With the before update- form removed, I don't have the conflict!!!! There's probably a more "programatically correct" way to do it, but. . I'm tired! And, I'm sure you are all tired of me!

Love words!

 

[mph1]

Word of Caution. I just realized you can go from record to record by using the PageUP and PageDown keys, but forget how to disable them. As you see, there is more than one way to accompolishe the same goal.

Glad the db is working.

 

[jebry]

Hi!

A couple of things. First, did you declare frmSave in the general declarations section of your form? Second, a more elegant way of handling this may be to set the forms AllowAdditions property to false, turn your save button into an add new record button and use the following code:

In the Click event

Me.AllowAdditons = True
DoCmd.RunCommand acCmdRecordsGoToNew

In the BeforeUpdate event

If MsgBox("Are you sure you want to save the record?", vbYesNo + vbQuestion, "Save Record?&quot = vbnNo Then
Me.Undo
DoCmd.RunCommand acCmdRecordsGoToPrevious
Me.AllowAdditions = False
End If

In the AfterUpdate event

Me.AllowAdditions = False

Now they will need to click the button each time they want to add a record but they won't be able to just tab through.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[dpav29]

Yes, I was afraid my method was a bit crude, but "whatever it takes"!

Now that the pressure is off, I can introduce your code more carefully. Thanks again for all your help!