Trigger event when text box value changes (1 Viewer)

[lorveney]

Hi Guys,

Apologies if this has been asked before …. I have not been able to find an answer for it in the forums.

I have a form with in excess of fifty text boxes on it (actual & forecast spend by month, subcategorised by operational and capital spend, with totalling to give total forecasted spend for the year, plus several categorys of budget subtotalled to give total budget for year). The form also has a RAG (‘Red/Amber/Green) control on it to indicate the current state of spend against budget.

I cannot use the standard conditional formatting for the RAG control because there is also a fourth state (Blue) to indicate a project is closed. The conditional formatting is therefore done in VBA.

Is there a way I can trigger an event (or is there an event I can use?) so that when a value is changed in one of the fifty or so text boxes, the VBA code to set the RAG will be run? I currently have the code on the Form_Current event (or to be more precise a call to a sub which sets the RAG in the Form_Current event). This works if you move to another record and back again but is not triggered simply by changing a value in one of the fields and pressing Enter.

I realise I could attach an event to each of the fifty or so text boxes to achieve this but was looking for a quicker, more elegant, solution first.

Many thanks in advance.

 

[Dennisk]

there is an on Change event for each control

 

[lorveney]

Yes, I realise that Dennisk, as I said "I realise I could attach an event to each of the fifty or so text boxes to achieve this but was looking for a quicker, more elegant, solution first.
"

 

[Rabbie]

You could try using the 'OnDirty' event for the form. This occurs when you change any of the control contents. Would be worth a little experimentation.

 

[lorveney]

Unfortunately, the Form_Dirty event fires before the new value in the text box is fully input (and therefore before the totals have been recalculated), plus it will only fire the first time a field is changed, not on subsequent changes to that field or any other field on the form.

 

[missinglinq]

Barring code on all 50 controls, I think you need to run your code to determine RAG's status on a regular, timed interval which, curiously enough, entails using the Timer Interval! This code should give you an idea. You may have to adjust the Timer Interval. As indicated, it fires 1 second per every 1000 units in the Interval.

Private Sub Form_Load()
  Me.TimerInterval = 10000 'cycles 10 seconds (1 second per 1000)
End Sub

Private Sub Form_Timer()
  'Code goes here to set RAG goes here
End Sub

I've never used the Timer for something like this, but it should do the job. It should be noted that running the Timer constantly adds a load to your processor, so say the "gurus," but with the superfast boxes most people are running today, I don't think you'll notice any drop in performance.

Good luck!

 

[lorveney]

Decided to go with adding an AfterUpdate event to all 61 textbox controls.

Actually, didn't take too long by cutting and pasting between VBA and Word.

Seems to work ok anyway.

Thanks for your help

 

[missinglinq]

As painful as it was, I think that was the right approach!

Linq