Calculated fields on subform not populating until mouse hovers over them

I have an Access 2003 data (I know, I know) that has a from with a continuous subform. The subform has a couple textboxes whose controlsource is an IIF based on the contents of another control on the subform. These textboxes also have conditional formatting (to colour the boxes red or green depending on values).

When the main form opens, none of the IIF fields in the subform get populated. Also, I notice that Access says "Calculating..." in the lower, left corner.

So far, I have found three ways to populate the fields (and get the "Calculating..." to go away):

1. If I hover my mouse over the first one, it populates. If I repeat this for the first six records, after I've done the sixth one, the IIF fields on all the records in the subform populate (even if there are 100 of them).

2. If I put a break in the code and then immediately continue execution once I hit the break, the subform shows with all the IIF fields populated.

3. Remove the conditional formatting.

Option 3 is the easiest, but the user really wants the conditional formatting. Any suggestions?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
First thing I would to is replace the IIF in the ControlSource and put the IIF function in a computed column in the query which is the RecordSource for the subform.

I believe this will resolve both of the problems.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jrmcanada2Author Commented:
Thank you! That worked perfectly!
Dale FyeOwner, Developing Solutions LLCCommented:
glad i could help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.