Me.Undo

[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

 

[Marshall Barton]

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

 

[briank]

Marsh, Your code worked great.

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