Cannot get "if" statement to update text box

[chriscardwell06]

Let me start off by saying I am new to this so go easy on me. I have a form that I cannot get to act like I think it should I have a date (duedatetxt) that calculates (=DateAdd("w",14,[Approval Date])) off of an approval date (Approval date). Now I have a text box (text25) that I use as a status box and I want it to change based off the due date. It is just a quick status to say whether the item is "On Time" or "Late". I have tried several different versions of code that I have found online. This one to me should be dumbed down enough to work but it isn't. This should be really simple and is driving me nuts. Thanks in advance for any help.

Private Sub Form_Click()
If duedatetxt.Value < Date Then Text25.Value = "on time"
Else: If duedatetxt.ValidationRule > Date Then Text25.Value = "late"
End If
End Sub

 

[pbaldy]

Not sure about that event, nor using ValidationRule. Try this in the after update event of the duedatetxt control:

If duedatetxt.Value < Date Then 
  Text25.Value = "on time"
Else
  Text25.Value = "late"
End If

 

[chriscardwell06]

Sorry the validation rule was a mistype. I changed the code as you said and it will still not populate the box. I can put the code in the click event for the form but it will only update if I click the arrow that spans vertically down the side of the form.

 

[pbaldy]

Like I said, I would have it in the after update event of that control. The click event of the form is rarely used in my experience. I think it would also fire if you clicked in a blank area of the form, but the logical place is the after update event so it will fire automatically when the user changes data in that control.

 

[chriscardwell06]

I added it to the after update of the duedatetxt box and still no results. That is what is puzzling is the fact that it shoul be firing and it is not. That is what made me think there was something wrong with my code but the code we have put in is nothing complex and should work with no problems.

 

[RainLover]

Add a Message box or two into your code to see if the code is actually firing.

 

[missinglinq]

First off, Paul is exactly right about the Form_Click event seldom being used; when/how it fires is always a crap shoot.

But the AfterUpdate event of duedatetxt will never fire! That's because it is Calculated! Control events such as AfterUpdate only fire if data is physically entered, i.e. thru typing or pasting data into it. They will only fire when code is used to populate them if they are explicitly Called at the same time.

I assume that your project won't already be late when the data is first entered, but that you want its 'status' to appear anytime, down the road, that you access the Record.

If this is true, Paul's code will work in the Form_Current event, assuming that you're using a Single View Form, which is to say you're looking at one Record at a time.

If you're using a Continuous View or Datasheet View Form, you'll have to change your approach. I think you'd have to use the Control Source of the 'status' Textbox and use the IIf() function to assign the value there, based on the difference in the duedatetxt and the current Date.

Linq ;0)>



never fireBut this event, as I read your problem, doesn't belon

 

[chriscardwell06]

So I tried the code in the approval box, one that someone has to enter data in, and it fired. It seems that due to the fact that the duedatetxt box is populated by the software, it won't fire off the after update command. I would have thought that it would have updated anytime the duedatetxt box changed. But seeing how it is driven off the approval box it serves the same purpose. Thanks for the help.

 

[pbaldy]

Sorry, I missed that the textbox was calculated. Glad it's sorted out.

 

[missinglinq]

...But seeing how it is driven off the approval box it serves the same purpose. Thanks for the help.

Not sure that it is sorted out! Having it fire off of the 'approval date' will place the correct text in the 'status' box at that point in time, but if 'status' is bound, the status won't change when the date becomes 'late.' And if 'status' is unbound, all records it will reflect the 'status' of the last record entered.

I think it's got to be done as outlined in Post # 7.

 

[chriscardwell06]

That is correct Linq. That is exactly what I need it to do. I set it up that way and it now functions correctly. Thanks again for everyones help.

Chris

 

[missinglinq]

Glad we could help!

Linq ;0)>