467,161 Members | 888 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,161 developers. It's quick & easy.

Requery problem

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,
Oct 30 '06 #1
  • viewed: 3218
2 Replies
Expert Mod 8TB
To requery a subform from its main form


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

Oct 30 '06 #2
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  3.     Dim idPrefix As String
  4.     idPrefix = "ABCDE"          ' Fixed ID Field
  6.     With Me
  7.         If IsNull(.TID) Then
  8.             .TID = idPrefix & Form!ID
  9.         End If
  10.     End With
  12. End Sub
Here is the code that DOES work:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  3.     Dim idPrefix As String
  4.     idPrefix = "ABCDE"          ' Fixed ID Field
  6.     With Me
  7.         If IsNull(.TID) Then
  8.            .TID = ""      ' dummy load to initialise control or refresh data???
  9.            .TID = idPrefix & Form!ID
  10.         End If
  11.     End With
  13. 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.
Jun 8 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by Tim Marshall | last post: by
2 posts views Thread by Gianluca_Venezia | last post: by
2 posts views Thread by F. Michael Miller | last post: by
1 post views Thread by ANDRESUK | last post: by
2 posts views Thread by Lyn | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.