Before update condition

[MKuria]

NEw to VBA
How do I enter the following condition in BeforeUpdate

Rule – If > [Initial Move Scheduled Pickup Date] > [Service Start Date] And
< [Service Completion Date] then user must enter data in [service Notes]

I would be entering the condition under service notes (before update)
Private Sub Service_Notes_BeforeUpdate(Cancel As Integer)
If (Me. Initial Move Scheduled Pickup Date >Me.Service Start Date And
<Me.Service Completion Date) Then
MsgBox "Date Entered is not within Service dates – Enter Reason",
vbCritical
Cancel = True
End If
End Sub
This is not working.

 

[Dirk Goldgar]

NEw to VBA
How do I enter the following condition in BeforeUpdate

Rule – If > [Initial Move Scheduled Pickup Date] > [Service Start Date]
And
< [Service Completion Date] then user must enter data in [service Notes]

I would be entering the condition under service notes (before update)
Private Sub Service_Notes_BeforeUpdate(Cancel As Integer)
If (Me. Initial Move Scheduled Pickup Date >Me.Service Start Date And
<Me.Service Completion Date) Then
MsgBox "Date Entered is not within Service dates – Enter Reason",
vbCritical
Cancel = True
End If
End Sub
This is not working.

Your If statement is incorrect (you can't stack criteria quite like that),
and also I think you're using the wrong event. You would want to do this in
the BeforeUpdate event of the *form*, not of the [Service Notes] text box,
because the BeforeUpdate event of the text box will only fire when someone
does in fact enter something in the text box. And also, you should use
brackets to "quote" the control names that have spaces in them. Try this
code:

'----- start of revised code -----
Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me![Service Notes]) Then

If (Me![Initial Move Scheduled Pickup Date] _

= Me![Service Start Date]) _

And (Me![Initial Move Scheduled Pickup Date] _
<= Me![Service Completion Date]) _
Then

Cancel = True

MsgBox _
"Date Entered is not within Service dates – Enter Reason", _
vbCritical,
"Reason Required"

Me![Service Notes].SetFocus

End If
End If

End Sub
'----- end of revised code -----

I adjusted your date conditions to "<=" and ">=", thinking they made more
sense. That said, I'm not sure about your logic. Your message will be
displayed when the pickup date is between the service start and completion
dates, but your message says the date is *not* within the service dates.
What condition do you really want to catch?

Once this is ironed out, you may also want to put a a similar test on the
AfterUpdate event of [Initial Move Scheduled Pickup Date], so that the check
is made immediately when the pickup date is entered.