Which Event to Use?

[ChronicFear]

I'm back with another validation question. I've read the descriptions of when different events fire, but I'm confused as to why this one is messing up my form.

I have two txtboxes: DateReceived and DateCompleted. The data may be changed at any time. If information has been changed/added/removed in either box, I would like to first make sure both boxes have dates in them, then run a function to calculate the time between the two. Also if, originally there was a date in both boxes, but it was deleted from DateCompleted, i want to clear the field that shows the difference between the two.

My code currently looks like this

Private Sub DateReceived_Exit(Cancel As Integer)

    If IsNull([DateReceived]) Then
        MsgBox "Please enter date received", vbOKOnly, "Incomplete Information"
        Me.DateReceived.SetFocus
    ElseIf IsNull([DateCompleted]) Then
        Form_frmTransactions![CompletionTime].Value = ""
    Else
        BusinessDays [DateReceived], [DateCompleted]
    End If

End Sub

What confuses me is that if placed in some events, access likes the code, but fires it at the wrong time. In other events, I get errors saying access cant find the object. If the syntax is right in one, why is it wrong in the other?

Thanks,
CF

 

[Rich]

Use the BeforeUpdate event of the form

 

[RuralGuy]

You would set Cancel = True to keep the focus on the current control in the Exit event, not Me.DateReceived.SetFocus. As a good coding practice, I would validate the entry in the BeforeUpdate event and update the other controls in the AfterUpdate event.

 

[ajetrumpet]

Form_frmTransactions![CompletionTime].Value = ""[/quote]

Is CompletionTime on another form? If it's not, why did you not use "me.completiontime" instead here?

    Else
        BusinessDays [DateReceived], [DateCompleted]
    End If[/quote]

Are you assigning this "BusinessDays" function to anything, or is it just hanging out there in midair?

 

[ChronicFear]

RG - Thanks for the tips. I'll give that a try

ajetrumpet - BusinessDays puts its result in the correct field as part of that code, so I don't think I need to assign it to anything.