Macro to confirm data entry

[Kathy]

I hope I'm not wearing out my welcome here. I've had some great answers to
questions and now I have another.

I want to add an "Add record" and "Cancel entry" button on a form so that
once data is entered into the form, the user must select one of the two:
either add the record or cancel adding it. We want to prevent users from
accidently adding records they didn't intend to. Once again, a search on
this site mentioned some coding to do this, but I don't understand the
coding. Is there an option with a macro in the "After Update" or "Before
Update" areas?

 

[Steve Schapel]

Kathy,

Access automatically saves a record when you move to another record, or
close the from, so the 'Add record' button could use a Close action or a
GoToRecord action to trigger the save, and navigate at the same time.
If you want to explicitly force the save, you can use a
RunCommand/SaveRecord action in your macro.

Be aware, though, that if you still have Navigation Buttons or Close
button on the form, the user can use these, and the record will be saved
without the use of your 'Add record' button.

As for 'Cancel entry', the macro will use a RunCommand/Undo action.

 

[bob]

If you are willing to try code (I know this is a macro group), this very
simple code, on the form, will do exactly what you want:

'below from Access Advisor of March 2005 page 12
Private Sub Form_BeforeUpdate(Cancel As Integer)
'Confirm w/ user that record should be updated
Dim strTemp As String
If Me.Dirty = True Then
strTemp = "Data has changed. Save Changes?"
If MsgBox(strTemp, vbYesNo + vbQuestion, _
"Please Confirm") = vbNo Then
'User has chosen not to save data:
Me.Undo
End If
End If
End Sub

Bob Galway
www.bbg-enterprises.com

 

[Kathy]

I'm willing to try it (although, I'll just be cutting and pasting and not
understanding what I'md doing). I need you to tell me what to put where. I
see the code, but does it start with "Private Sub Form.." or somewhere else?
And I think I understood that I'd put this code in as an expression on the
"Before Update" line in the properties of the form. Is that right?

 

[Steve Schapel]

Kathy,

If you are going to go down this track, the code Bob supplied has an
error in it. Actually a couple of errors. Also, note that it goes away
from your original concept of 'Add' and 'Cancel' buttons, so you would
not have these buttons at all.

In the design view of the form, enter [Event Procedure] in the Before
Update property of the form. Then click the little button on the right
with the ellipsis (...) icon. This should open the Visual Basic Editor
window, with the cursor already placed between the lines of code:
Private Sub Form_BeforeUpdate(Cancel As Integer)

End Sub

So, just type in between, so it looks like this...

Private Sub Form_BeforeUpdate(Cancel As Integer)
If MsgBox("Save Changes?", vbYesNo + vbQuestion, "Confirm") = vbNo Then
Cancel = True
Me.Undo
End If
End Sub

 

[MikeJohnB]

Beware, Steves sound solution has wrapped the text due to the page width on
here, the "= vbno then" should be on the same line as the if statment unless
I'm mistaken. Also you could put Steve's solution into a button's on click
event to take you back where you wanted to be.

HTH

Kathy,

If you are going to go down this track, the code Bob supplied has an
error in it. Actually a couple of errors. Also, note that it goes away
from your original concept of 'Add' and 'Cancel' buttons, so you would
not have these buttons at all.

In the design view of the form, enter [Event Procedure] in the Before
Update property of the form. Then click the little button on the right
with the ellipsis (...) icon. This should open the Visual Basic Editor
window, with the cursor already placed between the lines of code:
Private Sub Form_BeforeUpdate(Cancel As Integer)

End Sub

So, just type in between, so it looks like this...

Private Sub Form_BeforeUpdate(Cancel As Integer)
If MsgBox("Save Changes?", vbYesNo + vbQuestion, "Confirm") = vbNo Then
Cancel = True
Me.Undo
End If
End Sub

--
Steve Schapel, Microsoft Access MVP

I'm willing to try it (although, I'll just be cutting and pasting and not
understanding what I'md doing). I need you to tell me what to put where. I
see the code, but does it start with "Private Sub Form.." or somewhere else?
And I think I understood that I'd put this code in as an expression on the
"Before Update" line in the properties of the form. Is that right?

 

[Steve Schapel]

Well, if you are using the Click event of a button, the code would
simply be like this...

Private Sub YourCancelButton_Click()
Me.Undo
End Sub

Thanks, MikeJohnB, for your comment about the word wrapping, which will
vary from one person to another, depending on newsreader used and/or
monitor resolution.