After Update, On Change, On Exit, On Lost Focus

[jastek]

I want a calculation for "textbox1 (called from a module function) to execute when the value in "textbox2" is entered or changed. Which event for "textbox2" is the best one to call the function; After Update, On Change, On Exit, On Lost Focus?

Is there a disadvantage to making all of these events call the function?

 

[Mile-O]

Calculations, typically, don't need an event on a form as you can put the expression in another textbox's ControlSource and it will calculate dynamically.

 

[jastek]

My calculation is a lengthy if, elseif statement, which I have made a function in a global module. When I call the function from the after update event of "textbox2", it runs OK. When I set the ControlSource of "textbox1" to the function, I get an error "You can't assign a value to this object".

Maybe I am going about this whole thing the wrong way. . . .

 

[Mile-O]

You just put:

=MyFunction()

in the ControlSource of the textbox. No need to set it with code.

 

[jastek]

That is what I did when I got the error.

 

[Mile-O]

Post your function.

 

[jastek]

Here is the function I have:

Option Compare Database
Option Explicit
Public Function CountDaysToSchedule()

'If not work is not completed and not scheduled and not started, then count days from initialized date to today'
If IsNull(Forms!Input!dtFinished) Or Forms!Input!dtFinished = "" And IsNull(Forms!Input!dtScheduled) Or Forms!Input!dtScheduled = "" And IsNull(Forms!Input!dtWorkStarted) Or Forms!Input!dtWorkStarted = "" Then
Forms!Input!DaysToSchedule = DateDiff("d", Forms!Input!dtInitialized, Now)

ElseIf Not IsNull(Forms!Input!dtScheduled) Or Forms!Input!dtScheduled <> "" Then
Forms!Input!DaysToSchedule = DateDiff("d", Forms!Input!dtInitialized, Forms!Input!dtScheduled)
End If

End Function

 

[Pat Hartman]

Be careful when combining AND and OR operators in the same conditional. Your condition is NOT being evaluated properly because you have not used parentheses to specify the order of presidence. It is being evaluated as:

If IsNull(Forms!Input!dtFinished) Or (Forms!Input!dtFinished = "" And IsNull(Forms!Input!dtScheduled)) Or (Forms!Input!dtScheduled = "" And IsNull(Forms!Input!dtWorkStarted)) Or Forms!Input!dtWorkStarted = "" Then

And I suspect that what you want is:
If (IsNull(Forms!Input!dtFinished) Or Forms!Input!dtFinished = "") And (IsNull(Forms!Input!dtScheduled) Or Forms!Input!dtScheduled = "") And (IsNull(Forms!Input!dtWorkStarted) Or Forms!Input!dtWorkStarted = "") Then

 

[jastek]

Pat - Thanks. You were right about the parenthesis.

I still have the issue with getting the "you can't assign a value to this object" error.

Anyone ??

 

[kousotsu]

English Police

Pat, I'm surprised at you: "presidence?" Though I suppose an item with higher PRECEDENCE would preside over others of descending rank, it's not even really a word.

Just a random attack of the Spelling and Grammar Police. ^_^

 

[Pat Hartman]

E's and I's are commonly interchanged when typing. The characters are typed by the same finger on opposite hands. So, depending on whether you're leaning left or right, you (or at least I) may interchange them randomly. Or, it could just be a plain ol' spelling error I do know how to spell separate though