Requery Sub-Form on Tab After Updating other Sub_Form 1

[dmon000]

I have a form which has 4 tabs each with it's own subform. I change the value of a field on tab 3, then click on tab 4. On tab 4 there is a calculated field that is dependent on the value of the field on tab 3. But it order to see the updated value on the field in tab 4 I have to close the form and reopen.

I've tried requery commands in several of the tab events and several of the subform events to no avail. No error messages.

What am I doing wrong? or what should I be doing differently?

THANKS !!

 

[TheAceMan1]

How are ya dmon000 . . .

You need to [blue]ReCalc[/blue] the subform on [blue]Tab4[/blue]:

[blue]   Forms![[purple][B][I]MainFormName[/I][/B][/purple]]![[purple][B][I]Tab4SubFormName[/I][/B][/purple]].Form.[b]ReCalc[/b][/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[dmon000]

Hi ACEMAN.

Thanks for the input, which makes perfectly logical sense. But for some reason I cant get that line of code to make a difference in the value of the calculated field on tab 4. Again, no error messages.

It must be a matter of putting it in the proper event. I've tried the after change and after update event of field on tab 3 to call a recalc of all of tab 4; and alternately a recalc of just the field on tab 4.

Also tried this code in the onchange event of the tab:
Forms!fNP_100a_NP_NEW!fNP_100e_ACCT_NEW.Form.Recalc
to no avail.

Am I putting the code in the wrong place, or is there some other property that needs to be changed before this code can work? I'm mystified. It shouldn't be this hard.
THANKS !!

 

[TheAceMan1]

dmon000 . . .
Sorry to get back so late. Digging into this I realized it would be a bit more than I thought. So I put it on the back burner until I could give it its due focus.

I setup a simulation of what you have and received worse results than you state in your origination of this thread. Although the unbound textbox on Tab4 was finally updated, A [blue]racing condition occurs[/blue], as if all the records on tab3 were parsed, then tab4. I've flagged this for [blue]research[/blue] as its completely unacceptable, and I've never run into this before.

The biggest problem (using proper syntax) is opening of the mainform:

[blue]Be Aware: When you open a mainform with subforms, the mainform opens from the innermost subform level out to the mainform which opens last.[/blue]

This means your subforms open in some unknown order first (usually in the order you inserted the subforms), then the mainform. The problem here is that [blue]code you need to reference the subform on Tab4 via the subform on Tab3, requires referencing the mainform, [purple]which is not yet open![/purple][/blue] . . . [blue]Can you see it![/blue]

So to fix this, [blue]were going to flag wether the mainform is open or not[/blue], as an indication of what to do in our code in the tab3 subform. Where going to let the mainform do the updating On Open (no way out), and let the tab3 subform thru its [blue]On Current[/blue] event and the [blue]AfterUpdate[/blue] event of the edited field, perform the updates of the tab4 unbound textbox there after.

Be aware: in the above paragraph, I'm assuming in my use of the tab3 [blue]On Current[/blue] event, you do desire the update to reflect the current tab3 record as well! . . . as you navigate record to record!

So lets do this:
[ol][li]Save tab3 subform under a new name so you can come back to square one if necessary.[/li]
[li]Delete any code involved in updating the tab4 unbound textbox. Be sure of this, as [blue]we don't need any extraneous code screwing things up![/blue][/li]
[li]In the [blue]declaration section[/blue] of a module in the modules window, copy/paste the following public variable:

[blue]Public MainFormIsOpen As Boolean[/blue]

[/li]
[li]In the [blue]On Load[/blue] event of the mainform, copy/paste the following:

[blue]   Dim ctlSrc As Control, ctlDes As Control
   
   Set ctlSrc = [[purple][B][I]tAB3subFormName[/I][/B][/purple]].Form![[purple][b][B][I]TextboxName[/I][/B][/purple]]
   Set ctlDes = [[purple][B][I]tAB4subFormName[/I][/B][/purple]].Form![[purple][B][I]UnboundTextboxName[/I][/B][/purple]]
   
   MainFormIsOpen = True
   ctlDes = ctlSrc
   
   Set ctlSrc = Nothing
   Set ctlDes = Nothing[/blue]

[/li]
[li]In the mainforms [blue]On Close[/blue] event, copy/paste the following:

[blue]   MainFormIsOpen = False[/blue]

[/li]
[li]Finally, in the tab3 subforms [blue]On Current [/blue] event & the Tab3textbox [blue]AfterUpdate[/blue] event, copy/paste the following:

[blue]   If MainFormIsOpen Then
      Me.Parent![[purple][B][I]tAB4subFormName[/I][/B][/purple]].Form![[purple][B][I]UnboundTextboxName[/I][/B][/purple]] = Me![[purple][b][B][I]Tab3TextboxName[/I][/B][/purple]] End If[/blue]

[/li][/ol]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[TheAceMan1]

Woops!

In step4 and step6 of my prior post, remove [purple][/purple] where you see it!

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886