Datediff in VBA for unbound text box

[Neobeowulf]

Team,

I have an unbound text box that calculates how many days an item has been sitting with a reviewer.

=DateDiff("d",[A/O Date],Date())

Right now I have the formula in the control source block and it works, but you have to close the form then reopen it for the number to pop up.

What I want to do is use this formula in the afterupdate block so when the date is first put in, the block updates without having to close/open the form. When I put the formula as is into the afterupdate, it no longer works, nothing shows up at all.

I'm not good with VBA at all, but what would I need to do to get this same formula to work as afterupdate? Also, would that be the best approach?

 

[pr2-eugin]

Sorry your definition is a bit unclear..
* Which fields after update property have you placed the code?
* what is the code that you currently have in the after update event?
* Do you want to pop up the DateDiff value after you enter the date in the field??

 

[boblarson]

Right now I have the formula in the control source block and it works, but you have to close the form then reopen it for the number to pop up.

You shouldn't need to close the form and reopen for the number to work. All it should take is for you to move to another control on the form from the control bound to A/O Date.

 

[Neobeowulf]

Boblarson - I thought the same thing, but it doesn't work like that. Nothing pops up in the datediff box until I close the form then reopen it. It has the correct info, it just doesn't update once the initial date box is filled in.

Pr2-eugin:
1: I'm not sure what you mean. The unbound text box is supposed to calculate off a date I put in another box (the A/O Date). So when I put in a date in the A/O Date, my datediff box should pop up with a number of days (like 8 or 23). It does that fine...if i open and close the form. I want it to work without having to open/close. Sorry if this doesn't help explain.

2: I don't know how to translate =DateDiff("d",[A/O Date],Date()) into VBA language. That's what I need help with.

3: If you mean like a pop-up window like a message box, no. I just want my datediff box to populate once I put in my A/O Date.

I searched tons of datediff threads before posting, but I couldn't find quite what I was looking for in VBA. I tried messing around with birthday calculations since it looks similar to me, but that was a deadend too. I'm a rook.

 

[pr2-eugin]

I think you have the code in the AfterUpdate property of the Form.. (which is not good).

You should have it in the after update property of the text box.. (A/O Date)

If you click the text box in design mode and browse through the different events associated you will find the after update event..
* click on the three dots (...) at the end of the event
* you will have a pop up come to you,
* choose the last option ,
* and you will land up on the VBA editor..
then you just copy the code into that..
something along the lines of..

Private Sub A/O_Date_AfterUpdate()
    [COLOR="Blue"]theUnboundTextBoxBName[/COLOR] = DateDiff("d",[A/O Date],Date())
End Sub

blue bits needs to change.

 

[Neobeowulf]

Okay problem solved.

Pr2-Eugin, thanks for the code it worked perfectly. The problem was I was putting the formula into my unbound text box in the after update.

What I NEEDED to do was put that same formula into my after update of my A/O Date box. /facepalm Reading the code you gave me made me realize that.

:banghead: