Did you try requery more than once? I've no idea if it will work or not but I had a similar problem with ACC2003: A control would not refresh with data that was extracted from its bound table and concatenated with a simple string.
What I wanted was the form to display a unique identifier. When the form opened and closed the identifier worked; when the form was opened and the user advanced to a new record, the identifier would return a null because the table data was somehow not loaded yet. The code was invoked from the form's OnCurrent event. Here is the code that did NOT work:
-
Private Sub Form_Current()
-
-
Dim idPrefix As String
-
idPrefix = "ABCDE" ' Fixed ID Field
-
-
With Me
-
If IsNull(.TID) Then
-
.TID = idPrefix & Form!ID
-
End If
-
End With
-
-
End Sub
-
Here is the code that DOES work:
-
Private Sub Form_Current()
-
-
Dim idPrefix As String
-
idPrefix = "ABCDE" ' Fixed ID Field
-
-
With Me
-
If IsNull(.TID) Then
-
.TID = "" ' dummy load to initialise control or refresh data???
-
.TID = idPrefix & Form!ID
-
End If
-
End With
-
-
End Sub
-
Perhaps a similar problem is occurring with your form. Maybe duplicating another .Requery is all you need. I was able to run the debugger and actually see the control finally return a valid number once I loaded my control twice!
I assure you I stumbled upon this quite by accident (like most of my coding) so I cannot explain why it works but I believe it has something to do with updating table data and synchronising it to the form.