Calculated textbox control not showing value in Access 2007, but shows in 2003

Question

I have an Access form with a textbox that is uses as an expression as it's controlsource to calculate a value to display. While the below expression works fine in Access 2003, it doesn't display in Access 2007 automatically. Only when I click on the field, does the value display. What is does is it checks the database for a value based on other fields on the form. If its NULL, the field displays a "N/A". If it's not NULL, it displays the value found in the database.

Does anyone know what I might be doing wrong?

=IIf
(
    IsNull( DLookUp("SomeField","SomeTable","SomeField = Forms!frm_FormName!cboSomeCombobox")),
    "N/A",
    LookUp("SomeField","SomeTable","SomeField = Forms!frm_FormName!cboSomeCombobox")
)

Answer 1

Issues that are fixed in Access 2007 by the 2007 Microsoft Office suites Service Pack 1

On a Windows Vista-based computer, fields that are bound to time-intensive 
expressions are blank in Access 2007

Fields that are bound to time-intensive expressions are blank in Access 2007. 
These fields are empty until you click the text box or until you click anything
outside of Access 2007 if the following conditions are true:
The Control Source property of a text box uses the DLookup() function.
The query that is called by the DLookup() function references a control on 
a form.

http://support.microsoft.com/kb/942378

Answer 2

I've run into this before. Previously I found mentions of it on a couple of different Access VBA forums after searching with Google, but now I cannot find any of these hits.

I've been fiddling with this for hours today. I tried a variety of code bits in various events, but as any of us know from running into this problem, the Report View has vastly fewer event triggers than does Print Preview. However, I did find a workaround and a couple of important discoveries regarding this irritating bug:

1. The TextBox need not receive focus; simply accessing the .Value property is sufficient to give it the kick in the pants that it needs to properly display its contents. As such, the .SetFocus method isn't necessary.
2. The Section_Paint event is sufficient for this purpose.

The following bit of code worked for me. Of course, change the section and control names to match those on your own report.

Fair warning: This causes screen flicker on my system when the report is scrolled. I did attempt to disable screen updates with Application.Echo, but this only made the flicker worse. It seemed like each and every line of code added to the event, no matter what it was, made the flicker worse; it seems to be a timing issue:

Private Sub GrpGlacctFooter_Paint()
    Dim DummyVar As Variant
    'DummyVar can be reused for each control. We just need somewhere (anywhere) to
    'stuff each .Value. Simply accessing .Value in any way is enough to trigger the
    'TextBoxes to display their contents.
    DummyVar = txtAcctSubtotalMessage.Value
    DummyVar = txtAcctTotalSpent.Value
    DummyVar = txtAcctRemainder.Value
End Sub