MessageBox in combination with IF function

[suradj]

Dear Fiends,

I have a tbl called tblUsers:[Fields]: Userid, Name, HireDate.

I created a form with this fields.

Also i placed a textbox with controlsource: =DateDiff("d",[HireDate],Now()).

What i want:

If the text box value is greater then 100 a messagebox must must popup.


I tried the IF function but with no success.

Please i need your help.

See attachment

 

[vbaInet]

What about using Conditional Formatting instead of a msgbox? So what would happen is if it meets the criteria (i.e. > 100) then the textbox backcolor becomes red. Would that work for you?

 

[suradj]

Thats also possible. The messagebox would be better. Let me try with conditional formatting.

Thnxfor the new idea. I wil try this.

 

[SteveSchapel]

Suradj,

If the message box would be better, then do the message box. No problem. But you have to clarify when you want the message box to appear. This will happen in response to an event. I guess the most likely options would be the After Update event of the HireDate, or the Current event of the form.

You said you tried If statement with no success, but that is in fact what you need. The code would be something like this:

   If Me.HireDate + 100 < Date Then
      MsgBox "YourMessage"
   End If

 

[suradj]

Steve,

I tried this code on Afterupdate: NO success
I tried this code on doubleClick: With Success

Did i do something wrong? Same code, nothing els

If Me.HireDate + 100 < Date Then
MsgBox "YourMessage"
End If

 

[SteveSchapel]

Suradj,

The After Update event of what? If you mean the After Update event of the HireDate control, then this will only happen at the point where you enter some date in there, or edit the date in there. As I said before, you need to decide when you wnat this to be triggered, and only then will you be able to select the appropriate event(s) to put your code.

 

[suradj]

Steve,

Example: if i have 10 records and i navigate to record 9 and if textbox with controlsource: =DateDiff("d",[HireDate],Now()) = greater 100 a messagebox must be prompt.
The Hiredate is already has a date value.

So i want this to be triggerd if i am on a record and the textbox with controlsource: =DateDiff("d",[HireDate],Now()) = greater 100

 

[SteveSchapel]

Suradj,

Ok, well if you mean for the message box to be triggered when you navigate on the form to the record, then this is the Current event of the form. So put your code there.

As pointed out to you elsewhere, you should be using the Date() function instead of the Now() function.

But in any case, you are advised to not reference the value of a calculated control within your code. Certainly use the textbox with your DateDiff expression if you wish to display the value on your form. But for your code, you are advised to refer directly to your actual data. Therefore, use the code that I gave you before, rather than taking any notice of the calculation in the texbox.