code problem with before update

[scott04]

I am trying to put code into a field to give it an error message and set
focus if the field is blank. The code that i am using is:
If Me.Requested_By = isnull Then
MsgBox "Please enter who requested this service request", vbCritical
Me!Requested_By.SetFocus
End Sub
Please let me know what isnt correct or a better way to do this? Thanks

 

[Rob Parker]

Your syntax is wrong. Try:
If IsNull(Me.Requested_By) Then
...

HTH,

Rob

 

[scott04]

Rob,
Thanks for your feedback as that makes sence. I tried using this revised
code:
Private Sub Requested_By_BeforeUpdate(Cancel As Integer)
If IsNull(Me.Requested_By) Then
MsgBox "ppdate is now requested", vbCritical
Me!Requested_By.SetFocus
End Sub
However when i enter a new record and leave that field blank the code does
not work. Maybe something else is wrong with it?

 

[Klatuu]

The Before Update can be canceled. YOu are also missing the End If. This is
how it should be:

Private Sub Requested_By_BeforeUpdate(Cancel As Integer)
If IsNull(Me.Requested_By) Then
MsgBox "ppdate is now requested", vbCritical
Cancel = True
End If
End Sub

If it is not working with new records and the above changes don't fix it,
then Requested_By has some value other than Null. You shoul set a breakpoint
on the first line of this sub and see what the value is.
You don't need to set the focus to this control. It already has it. When
you cancel the event, the control retains the focus.

 

[scott04]

Thanks for the feedback......would it be a better route to just put a
validation rule is not null in the design of my table or is code the better
way to go?

 

[Klatuu]

Wow, you can ask 6 people this question and get 7 different answers.
There are pros and cons for each.
My personal preference is to use code. I feel like I have better control.
Also, if you use the field level validation, you have to trap for the error
and deal with it programmatically any way if you want to avoid Access'
confusing messages that users never understand.
A reason to use the field level, however, is that it enforces consistency.
That is, if one developer does not check for it in one place, it leaves open
the possibility of an incorrect value being allowed.

 

[scott04]

Thanks Dave

Wow, you can ask 6 people this question and get 7 different answers.
There are pros and cons for each.
My personal preference is to use code. I feel like I have better control.
Also, if you use the field level validation, you have to trap for the error
and deal with it programmatically any way if you want to avoid Access'
confusing messages that users never understand.
A reason to use the field level, however, is that it enforces consistency.
That is, if one developer does not check for it in one place, it leaves open
the possibility of an incorrect value being allowed.

 

[Bob Quintal]

Rob,
Thanks for your feedback as that makes sence. I tried using this
revised code:
Private Sub Requested_By_BeforeUpdate(Cancel As Integer)
If IsNull(Me.Requested_By) Then
MsgBox "ppdate is now requested", vbCritical
Me!Requested_By.SetFocus
End Sub
However when i enter a new record and leave that field blank the
code does not work. Maybe something else is wrong with it?

The BeforeUpdate event of a control only fires if the value of the
control is changed. Since you leave the field blank it never
changes. Put the same code into the form's BeforeUpdate event.
It triggers if the value of any control has been changed by the
user.

 

[scott04]

Bob,
Thanks code now works!

The BeforeUpdate event of a control only fires if the value of the
control is changed. Since you leave the field blank it never
changes. Put the same code into the form's BeforeUpdate event.
It triggers if the value of any control has been changed by the
user.