Using AfterUpdate event on a control in one subform to requery a control on another subform

1
votes

I have an Access 2010 database set up with using the native Navigation Form interface feature.

Using this very helpful table, I've been able to use vba on multiple ocassions to reference forms throughout my database. However I seem to be having difficulty in this one instance.

On the Navigation form I have a number of main tabs. Clicking on one of the main tabs brings up a number of sub-tabs that have various forms attached to them.

On one of these sub-tabs, I have a main form (main) attached to it. There is a subform (subform1) within that main form containing a control text box (control1). Within subform1 there is another form (subform2) containing an unbound text box (control2) displaying a value that is calculated using a TempVar.

I want to use the AfterUpdate event on control1 to requery subform2 in order to update the calculation in control2, or simply requery control2.

Using Refresh and/or Requery on the AfterUpdate event for control1 does not do anything to control2.

Currently I have a button on subform2 that users can click which refreshes the form and updates the value in control2. The user can also hit F5 and refresh all the forms with the same result.

My goal is to take away the manual aspect and have the AfterUpdate event for control1 to update control2.

Any help would be much appreciated as I haven't been able to find anything online that works and I've been kicking tires at the problem for over a month now.

**** UPDATE **** I did not correctly describe the layout of my forms. subform1 and subform2 are actually both nested under main. subform2 is NOT nested under subform1 as I originally described above.

1
ms-accessvba
As I understand Navigation form, only one subform is active and available at a time. Review this discussion accessforums.net/showthread.php?t=32053. I use a Tab control instead. – June7
I agree a tab control is the way to go here. If you avoid it because of the lower performance, review this thread. If performance is not an issue, a tab control is just more flexible. – Erik A
Thanks to both of you for the quick replies. I've explored the link you provided and attempted a few suggestions to no avail. RE: Tab control, unfortunately I'm very far into the project and don't want to have to migrate everything into a tab control interface. I would have to re-test everything as well. This particular issue just isn't worth it...I also like the look of the Navigation Form. That being said if anyone has anything further to add please do so. I will be away for a few days so my non-response is not indicative of anyone's input. I'll explore June's link further upon my return. – Hal Jam
Have done a little more digging into navigation controls, and the entire subform should requery as you switch tabs (tested in Access 2016, only the active subform is loaded, and gets unloaded as soon as you navigate to another sub. Thus requerying a control on another sub should not be necessary. Can you specify the exact issue? – Erik A
Sorry I didn't get back earlier. I had some more pressing matters upon my return. @Erik, you are correct that the subform should requery when switching tabs, however I would like the control2 to requery when control1 is updated. Switching tabs is more work than my current solution. I'm guessing that I wouldn't have the issue if my description of the problem was initially correct. I have updated my question as I realized I posted inaccurate information. This update might make more sense. My apologies. – Hal Jam

1 Answers

0
votes

Wow. Now I feel like an idiot. Tackling my problem again with the update I provided above allowed me to solve it.

All I needed to do was requery subform2 by referencing it this way:

[Forms]![HOME]![NavigationSubform].[Form]![subform2].[Form].Requery

Where "HOME" is the name of my Navigation Form.

For anyone with the same issue that hasn't renamed their Navigation Form I believe the syntax would be:

[Forms]![Navigation Form]![NavigationSubform].[Form]![subform2].[Form].Requery

Thank you to @Erik von Asmuth and @June7 for your patience and help.