Is it possible to... (1 Viewer)

[Lissa]

Is it possible to have a subform update it's contents based on user changes made to another subform? I've been trying for days to get the control on the subform to update but I can't get it to update correctly. Someone even suggested I may be "pushing the limits" of MS Access by overdesigning my main form. Maybe... but I don't think so. What do you think?

I am posting my db. Note that I have deleted some subforms in order to reduce the size to meet uploading limits.

If you look at the main form (frmProposalSetup), it has a tab control with several pages. On the WBS page, if I change the hours under PM (Program Manager) at the "Kickoff Meeting" task, I want to be able to click over to the LABOR page and see the corresponding Labor Hours under PM reflect the change in hours.
At one point I have tried adding forms.frmPROPOSALSetup.subfrmPROPOSALSetup.Form.Requery to the After Update event of the hours field on the subform that is suppose to drive the update (subfrmWBS) but that didn't work.

Does anyone have any other ideas? I doesn't seem like something that would "push the limits" of Access...
I appreciate anyone's input.
Thanks!

 

[Lissa]

Unhappy.

I have been on this same problem for many days now. I'm trying to help myself since I haven't gotten any input from anyone...
But in trying to help myself find any solution.... I found something but I'm still not satisfied with it.
On the tabbed control I ended up adding a Refresh button that the user will have to manually click to update the form. I'm unhappy with this because I still believe it's possible do this with out having the user to manually click on the refresh button.

Is there any way to basically use this
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
or this DoCmd.RunCommand acCmdRefresh
from an After Update Event on a different subform?
I want to send a SHIFT + F9 command from one subform to another subform - instead of having the user have to click the refresh button on the second form.
I tried the Requery method and that didn't do anything

I'm so frustrated

 

[Mike375]

It appears to be a "saving/update" problem

If you change the number on WBS and then close and reopen the change is reflected in labor. Or if you change WBS then tab to Labour and just sort A to Z it updates.

I don't use Tabbed forms but what you have there appears to be similar to opening all forms and doing SelectObject to bring the appropriate from forward. However, when that form is brought forward the record will not have updated.

 

[Mike375]

I just put a macro on the main form to do Requery and that changed it after I tabbed from WBS to Labour and then ran the macro (clicking on label I put it on). So if you have Requery run when you clicked Labour that might get you there.

 

[wiklendt]

also, make you are referencing your subforms correctly, this was happening to me until i found the correct convention. also, make sure you only use Me if it really is on the form that code is placed in! i have made that mistake before too ;-)

[FONT=&quot]Me.MySubformContainerName.Form.MyControl[/FONT]

[FONT=&quot]or if you need to refer to another form[/FONT]

  [FONT=&quot]Forms!YourMainFormName.YourSubformContainerName.Form.YourControlName [/FONT]

(i think this was from a comprehensive tutorial put together by bob larson)

 

[Lissa]

Thanks for your input...

It's the Aussies to the rescue I appreciate your ideas. I've tried incorporating your suggestions but still nothing works I've tried different variations and I've been searching the forum for the past 2 hour still looking for possible alternative but I'm beginning to think the only possible way is to have that stupid refresh button on the subform and have the user manually refresh the form. I'm just so disappointed that it can't be done programmatically.....???

 

[wiklendt]

try putting the code in the after_update section of the form...?

 

[Lissa]

Hi wiklendt -
Well I've tried putting code in the after update event of the form - that didn't work. I also tried putting it in the several other events of the form with no avail. I'm going to have to give up on this for today since it it past 11pm here. I will continue trying tomorrow.
Thanks for trying to help! I sure do appreciate it!

 

[poporacer]

I haven't had a chance to look into it in detail, but there are a couple of things I see might be the problem. You are not requerying subfrmwbs, so your total does not update, therefore your query for subfrmProposal does not update...and then in the LaborCategory1_Hrs_AfterUpdate() run a requery on the form subfrmProposal...It is late so I will look at it later...hopefully this gets you closer

 

[Banana]

Lissa, can't look at the database at this computer, but the question should be: Is the AfterUpdate event even firing at all?

If you don't know how to tell- open up Visual Basic Editor and go to the subform's AfterUpdate event, then click on the gray margin to the left to get a red dot to create a breakpoint. Once set, you want to try and editing the values in the subform.

If it does fires, step through the code and see what it does; check the variables if has any, and in the Immediate Windows (the tiny windows to below of the VBE), check the status such as whether forms are dirty or whatever.

Report back with the details; it will help us know exactly why it's not being updated at all.

 

[georgedwilkinson]

I got your refresh to work in the main form's tab control change event:

Private Sub TabCtl85_Change()
    'MsgBox "Tab change"
    Forms.frmPROPOSALSetup.subfrmPROPOSALSetup.Form.Requery
End Sub

Please verify.

 

[gemma-the-husky]

if you change details in one form, affecting the underlying database, then another form may not immediately show the update, I think.

Instead of requery, which repositions the data cusor at the top of the record set, use refresh which does just that - refreshes the underlying data in case it has changed.

so as george says

in the afterupdate event for a field, force the associated subform to refresh - now if you are changing the value of the field through code, rather than a direct keyboard entry, then the afterupdate event will not fire - so you need to force the refresh in the code module.

 

[Lissa]

I'm a happy camper now!!

First - thanks to everybody who replied. I learned some new debugging tips and some facts about requery vs refresh!

I tried georgedwilkinson's code snippet at that worked like a charm! Thank you! Sometimes I get so focused on doing something one way and then someone sheds light on a better solution!! That's why I love this forum!

Thanks!!!

 

[georgedwilkinson]

First - thanks to everybody who replied. I learned some new debugging tips and some facts about requery vs refresh!

I tried georgedwilkinson's code snippet at that worked like a charm! Thank you! Sometimes I get so focused on doing something one way and then someone sheds light on a better solution!! That's why I love this forum!

Thanks!!!

Glad we could help! You'll notice the MsgBox in my code. I put a MsgBox in every event to track this down, if that helps you in the future.