By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,871 Members | 2,464 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,871 IT Pros & Developers. It's quick & easy.

Requery problem

P: 76
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
Oct 30 '06 #1
Share this Question
Share on Google+
2 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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
Oct 30 '06 #2

ebs57
P: 18
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()
  2.  
  3.     Dim idPrefix As String
  4.     idPrefix = "ABCDE"          ' Fixed ID Field
  5.  
  6.     With Me
  7.         If IsNull(.TID) Then
  8.             .TID = idPrefix & Form!ID
  9.         End If
  10.     End With
  11.  
  12. End Sub
  13.  
Here is the code that DOES work:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.  
  3.     Dim idPrefix As String
  4.     idPrefix = "ABCDE"          ' Fixed ID Field
  5.  
  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
  12.  
  13. End Sub
  14.  
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.