Datasheet and Code

[dbprogman]

I have a major project underway and have come across an issue that has me stumped. So any help would be gratefully accepted.



I am attempting to create a simple warning system for tasks associated with a project.

Form description
Form1
Combobox (User chooses project)
Refresh Button
Tab Control
Subform - Assigned Date - Time to Complete

Me.Time_to_Complete.Value = DateDiff("d", Me.Assigned_Date, Date)

Please refer to attached DB

Overview of Issue
Calculated fields wont update unless got focus

Description of Issue
As you will notice on the attached DB there is a form that has a subform on a tab control. Within this subform is a field called Time to Complete. The code behind the form directs this field to show the number of days passed from Assigned Date to current date. Then using conditional formatting change colour related to number of days elapsed. The code works well on individual records only, All records associated with a chosen project must update on open.

Required Outcome
How can I get code to run on all records in datasheet view, when form opened?

[RuralGuy]

I'll start by saying that what I downloaded works as you want without any changes. It your system completely up to date with Service Packs?

[dbprogman]

I have tried this on multiple PC's running a variety of OS's but all use MSACCESS 2007.

Making changes to the assigned date or system date and time will only be recalculated if the field assigned date recieves focus I need all records in datasheet view to reflect changes when opened etc..

The system is fully up to date with all updates from Microsoft.

It's only confusing when you don't understand.

[RuralGuy]

So how do you change the Assigned Date without putting the focus on that field? And while we're at it, why is the System Date being changed?

[dbprogman]

The system date changes each day obviously my ability to expalin my problem has caused a problem. To me what I am after is a simple procedure that records the number of days between a set date and todays date. The procedure works great if dealing with an individual record, but in datasheet view you see lots of records.
I have managed to achieve my goal by using an unbound textbox however because I need to store this information I have to tell access to store the results of my unbound textbox in a field called Time to Complete. Now I am back where I started.
I think I will give it away I honestly didnt expect such a simplistic request from Access to be so difficult. Thanks for trying to help and understand we might just throw this one in the too hard basket..

[RuralGuy]

Try calculating the Time_to_Complete field in the underlying query rather than in code in an event as you have it. It really need not be a real field at all, just a calculated alias.

[dbprogman]

I can get the the procedure to work like a charm as long as I am not dealing with a bound textbox so for sake of KISS I will leave it as a visual warning only and use a query to ascertain days for reporting. Hey! problem solved, not quite the way imagined it to be but netherless an outcome.

Once again thanks heaps for you persistance and help...

Cheers

[RuralGuy]

If your happy then I'm happy. You're welcome.