If Statement Problem

[Guest]

I have this code in the Before Update event on a form and only 1/2 of it is
working. This is what I want to do: 1) If the status =1, then clear the
value of the reviewer field (if any). 2)If the status = 1, then clear the
value of the Date of Review field (if any), and 3) If the status <> 1, then
make sure the reviewer field has a name in it or show a msg box.

Can anyone see if there is a problem with my code? I've tried it a few
different ways and it seems like only part of it will execute.

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.Status = 1 And IsNull(Me.Reviewer) = False Then
Me.Reviewer.Value = ""
ElseIf Me.Status = 1 And IsNull(Me.Date_of_Review) = False Then
Me.Date_of_Review.Value = ""
ElseIf Me.Status <> 1 And IsNull(Me.Reviewer) = True Then
MsgBox "Please enter your name in the Reviewer field", vbCritical
Cancel = True
End If
End Sub

THANKS!

 

[Guest]

Janet,

The second condition (the first ElseIf) will never be executed since when
the status is = 1 the first condition executes and then the complete If/End
If construct is exited.

May I suggest that in this case the use of a Select Case construct will make
things a little clearer.

Select Case Me.Status
Case 1
<do something>
Case Else
<do something else>
End Select

For the <do something else> it may be a little more robust to code

If Len(Trim(Nz(Me.Reviewer,""))) = 0 then
Msgbox <your message>
Cancel = True
Me.Reviewer.SetFocus
Exit Sub
End If

as it catches nulls, zero length strings and any string of only spaces.

Regards,

Rod

 

[Guest]

Sorry, talking a load of drivel again: forget what I said about the second
comparison not executing, I did not scan your code properly.

However my other comments still hold.

For the <do something> in my first post why not?

Me.Reviewer = ""
Me.Date_of_Review = Null

There's no harm in setting an already null field to null, etc.

I would question setting the date field to a zero length string if you have
declared the column on the table as Date/Time since such a type is actually
numeric and zero length strings have no place in numeric fields.

Rod

 

[Guest]

You could also write it using the "Not" operator:

If Me.Status = 1 And Not IsNull(Me.Reviewer) Then
... rest of the code

And here is another way to write your code:

'------beg code----------
Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.Status = 1 Then
'status = 1.....clear these fields
' if it is already Null.... so what?? ;-)
' clear it again
Me.Reviewer = Null
Me.Date_of_Review = Null
Else
' Me.Status <> 1
If IsNull(Me.Reviewer) Then
MsgBox "Please enter your name in the Reviewer field", vbCritical
Cancel = True
End If
End If

End Sub
'-----end code----------


(You don't need to specify ".Value" - it is the default property.)


I used to have time in a bottle, but then I spilled it. Now I have time on
my hands... (yep, too much time!) Tried to wash it off, but nothing worked.
Now it is time to go. You know how time flies if <SLAP>...


HTH

 

[Guest]

Thanks Rod For your help!!

Sorry, talking a load of drivel again: forget what I said about the second
comparison not executing, I did not scan your code properly.

However my other comments still hold.

For the <do something> in my first post why not?

Me.Reviewer = ""
Me.Date_of_Review = Null

There's no harm in setting an already null field to null, etc.

I would question setting the date field to a zero length string if you have
declared the column on the table as Date/Time since such a type is actually
numeric and zero length strings have no place in numeric fields.

Rod

 

[Guest]

Thanks Steve, this works great. janet

You could also write it using the "Not" operator:

If Me.Status = 1 And Not IsNull(Me.Reviewer) Then
... rest of the code

And here is another way to write your code:

'------beg code----------
Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.Status = 1 Then
'status = 1.....clear these fields
' if it is already Null.... so what?? ;-)
' clear it again
Me.Reviewer = Null
Me.Date_of_Review = Null
Else
' Me.Status <> 1
If IsNull(Me.Reviewer) Then
MsgBox "Please enter your name in the Reviewer field", vbCritical
Cancel = True
End If
End If

End Sub
'-----end code----------


(You don't need to specify ".Value" - it is the default property.)


I used to have time in a bottle, but then I spilled it. Now I have time on
my hands... (yep, too much time!) Tried to wash it off, but nothing worked.
Now it is time to go. You know how time flies if <SLAP>...


HTH

 

[Guest]

Excellent!!