Refresh Subform_1 After Updating Subform_2?

UNC_Access

Registered User.
Local time
Today, 00:54
Joined
Oct 24, 2012
Messages
42
Hi!

0. My Goal

My goal is to refresh one subform after updating data in another subform.​


1. The Situation

A. I have two tables:

Table_1: a multiple-record, multi-field table used to enter detailed data

Table_2: a one-record, multi-field table that is used to enter summary data

- Relationship: The sum of one field in Table_1 should match a single data point in Table_2
- Note that the tables are not linked in any way (they should not be linked as they come from different sources)​

B. I have one query:

Query_1: a two-field query of Table_1:
- 1st Field - "Total Amount": Sum of a Table_1 field

- 2nd Field - "Variance":[Sum of a Table_1 field] - [Dlookup of a single data point in Table_2)​

C. I have three forms:

Form_0: a form which holds Subform_1 (aka Form_1) and Subform_2 (aka Form_2)

Form_1 (aka Subform_1): a form of Query_1

Form_2 (aka Subform_2): a simple form that is linked to Table_2


2. Question:

When I modify data in Table_1 via Subform_1, I need Query_1 in Subform_2 to update automatically.

How should I do this?

I'm thinking I may need to use VBA or SQL, but I honestly wouldn't even know where to enter or copy/paste the code.​


3. Things I Have Tried

I have already tried to associate a requery macro to all events in all of the forms (Form_0, Form_1, and Form_2) - nothing worked.

I have already tried to associate a refresh macro to all events in all of the forms (Form_0, Form_1, and Form_2) - nothing worked.
Any help is welcome!​


4. Possible Solutions

A. I think Subform.requery might work, but I'm not sure where to go to write the code, or what code should be written exactly.

B. I think FireStrike's answer in the following link may be correct, but again, I don't know where to copy/paste his code: http://www.access-programmers.co.uk/forums/showthread.php?t=111250&highlight=requery


Thanks in advance!
 
Let me give that a try pbaldy!

Thanks!
 
No problemo!
 
Okay, I tried it and I can't get it to work. I don't think I'm doing it right.

This is what I did:

01. I opened up Form_2 (not the main Form_0) in Design View
02. I selected the appropriate Form_2 field (Field_A)
- This field, once updated, should change the Query_1 "Variance" field​
03. I opened that Field_A's Properties
04. I went to the Event tab
05. I selected the "On Change" property
06. I typed "[Event Procedure]"
07. I clicked the "..." to bring me to VBA
08. I typed the following VBA

Code:
Private Sub Statement_[COLOR="RoyalBlue"]Field_1[/COLOR]_Change()
Forms!Form_0.Variance.[COLOR="Red"]Form_1[/COLOR].Requery
End Sub

09. I saved and opened Form_0 in Form View
10. I changed the data in the appropriate field to see if the refresh would work
11. I got the following error:

Runtime Error 2465
Application-defined or object-defined error

It is probably obvious what I'm doing wrong, but I am clueless.

Any thoughts?

Thanks!
 
Doesn't look like you've followed the syntax from the link or that I gave you. Try

Forms!Form_0.Form_1.Requery
 
Okay, I re-performed the same steps, except for #8, I put:

Code:
Private Sub Statement_[COLOR="RoyalBlue"]Field_A[/COLOR]_Change()
Forms!Form_0.[COLOR="Red"]Form_1[/COLOR].Requery
End Sub

For step #11, I did not get an error, but Field_A did not refresh.

Am I putting the code in the correct place?

Appreciate the help!

FYI, when you mentioned this:

Forms!MainFormName.SubformControlName.Form.Requery

I wasn't sure what the SubformControlName was. I was thinking it was the field that needed to be refreshed (i.e. the "Variance" field in Query_1).
 
The code would need to be behind the textbox the user is physically changing. Also, you'd want the after update rather than the change event, which fires with every keystroke. Can you post the db here?
 
Okay, I had to clear the data.

Form_0 = frm_user_interface (the first form that you see)

Form_1 = actually 4 Forms (I wanted to keep my question simple):
-frm_total_debits
-frm_total_credits
-frm_end_balance
-frm_total_cleared_warrants

They all act in the same way, so if we get one, we can apply that logic to all four.

My goal is to get the "Variance" fields to update when the "Enter Statement Data" fields are updated.

For example, if you change the Total Debits from $3 to $2, the "$-" should change to "($1)". The only way I can get it to work is by closing the form and then reopening it.

Thanks!
 
Last edited:
This worked in the after update event of the Begin Balance textbox:

Forms!frm_user_interface.frm_calculated_ending_balance.Requery

If you want all 4 subforms to update with a change to any of the Enter Statement Data inputs, I'd create a function that did the requerying, and call it from the appropriate places. That way you only have the requery code in one place.
 

Users who are viewing this thread

Back
Top Bottom