Requery problem

[jpatchak]

I am experiencing two distinct problems with the Requery method in Access 2003.

Problem 1: I have a series of text boxes on my form. The control sources of these text boxes are used in calculations in a query. The results of the query are displayed in a subform (continuous form) on my main form. I want the text boxes in the subform to update after any of the text boxes on the main form are updated. I have tried to requery the subform on each textbox's After Update event. However, the requeried results do not display unless I shift the focus to the subform. This is truly bizarre behavior. Any ideas? I have a temporary fix in place where I refresh the database on the changes in the mainform text boxes. However, I am afraid that as my DB grows, this get slow.

Problem 2: The main form's After Update event does not seem to fire unless the focus is shifted to one of its subforms. This is causing a big pain in that I have to write code for each control's After Update event instead of just having the code in the form's After Update event. We are calculating health insurance rates here, so the queries are quite complicated and rely on several checkboxes, combos, and text boxes across several tabs in a tab strip. Any ideas on this one?

Thanks in advance,
jpatchak

[MMcCarthy]

To requery a subform from its main form

Me.SubformName.Form.Requery

To requery a subform from a form other than its main form

Forms!MainFormName!SubFormName.Form.Requery

[ebs57]

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.