Me.Undo

B

briank

Hello. I have a subform that I want to limited how data is inserted. If the
condition is not met then I would like to clear out any of the data attempted
to be inputted. Currently the user can enter data, see the msgbox and proceed
to click enter and the subform will accept the data. Not really what I want.
Any thoughts?

Column 1 (Grant2_Notes) is a comment field.
Column 2 (Grant2_Goal) is a numerical field.

The user can enter data in Column 2 only if there is text in Column1.

Private Sub Grant2_Goal_BeforeUpdate(Cancel As Integer)
If IsNull(Me.Grant2_Notes) Then
MsgBox "<message>", vbOKOnly
Me.Undo
End If
End Sub
 
M

Marshall Barton

briank said:
Hello. I have a subform that I want to limited how data is inserted. If the
condition is not met then I would like to clear out any of the data attempted
to be inputted. Currently the user can enter data, see the msgbox and proceed
to click enter and the subform will accept the data. Not really what I want.
Any thoughts?

Column 1 (Grant2_Notes) is a comment field.
Column 2 (Grant2_Goal) is a numerical field.

The user can enter data in Column 2 only if there is text in Column1.

Private Sub Grant2_Goal_BeforeUpdate(Cancel As Integer)
If IsNull(Me.Grant2_Notes) Then
MsgBox "<message>", vbOKOnly
Me.Undo
End If
End Sub


Me.Undo is overkill. It undoes all the entries on the form.
In you case there is only one other control and it is nu;;
so there's no need to undo it. Just undo the grant2 goal
control.

To prevent the value from being accepted, you need to set
the procedure's Cancel argument to True.

If IsNull(Me.Grant2_Notes) Then
MsgBox "<message>", vbOKOnly
Me.Grant2_Goal.Undo
Cancel = True
End If

In general, I think it almost always better to do that kind
of data validation in the ***form*** BeforeUpdate event and
cancel the record save if the data isn't correct. The code
here would be like:

If IsNull(Me.Grant2_Notes) Then
MsgBox ""You must provide a comment", vbOKOnly
Me.Grant2_Notes.SetFocus
Cancel = True
End If
 
B

briank

Marsh, Your code worked great.

Marshall Barton said:
Me.Undo is overkill. It undoes all the entries on the form.
In you case there is only one other control and it is nu;;
so there's no need to undo it. Just undo the grant2 goal
control.

To prevent the value from being accepted, you need to set
the procedure's Cancel argument to True.

If IsNull(Me.Grant2_Notes) Then
MsgBox "<message>", vbOKOnly
Me.Grant2_Goal.Undo
Cancel = True
End If

In general, I think it almost always better to do that kind
of data validation in the ***form*** BeforeUpdate event and
cancel the record save if the data isn't correct. The code
here would be like:

If IsNull(Me.Grant2_Notes) Then
MsgBox ""You must provide a comment", vbOKOnly
Me.Grant2_Notes.SetFocus
Cancel = True
End If
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top