Subform Question (1 Viewer)

[Lissa]

I'm kind of confused... can someone help me?
I have a main form (frmPROPOSALSetup) with 6 subforms. The first subform (subfrmPROPOSALSetup) contains several fields that contain total labor hours (for each defined labor category). The second subform (subfrmWBS) contains a workbreakdown structure that has a summary of total labor hours (in the form footer) for each labor category that essentially feeds the first subform the total labor hours. (LbrHrs1=Forms!frmProposalSetup!subFrmWBS.Form.SumHrs_LabCat1, LbrHrs2=Forms!frmProposalSetup!subFrmWBS.Form.SumHrs_LabCat2...etc)

I am trying to get the fields on the first subform to update as the user updates or changes the number of labor hours on the second subform.

I have tried adding Me.subfrmPROPOSALSetup.Form.Requery
to the After Update event of the SumHrs_LaborCat1 field on the second subform (subfrmWBS) and this doesn't appear to help at all - data on the first subform is still unchanged.

How can I requery the first subform after an update to the second subform?

 

[pbaldy]

Here's a good reference on the syntax:

http://www.mvps.org/access/forms/frm0031.htm

 

[KenHigg]

When referencing the subform you need to back all the back up to the main form:

forms.frmPROPOSALSetup.subfrmPROPOSALSetup.Form.Requery

 

[Lissa]

Close but not there yet

Thanks for your replies!

I tried putting
forms.frmPROPOSALSetup.subfrmPROPOSALSetup.Form.Requery
in the After Update event of the SumHrs_LabCat1 field on the second subform but that didn't do anything either.

Then I tried putting the same line of code in the After Update event of the entire subform (subfrmWBS) and that seems to be updating the first form, to SumHrs_LabCat1's previous value. If I open the form and SumHrs_LabCat1 equals 25 and I changed it to 15 - nothing happens. But if I change the value to 10, it changes to 15.
I'm guessing it's a timing issue of when the update is fired? I'm not sure...

 

[KenHigg]

Something is telling me you're pushing the limits of the whole form/subform thing. Six is quite a few to try and manage.

So the second subform has an hours field that gets updated. And then you want another subforms data to be refreshed. Is the subform that does all the total stuff tied to the same table as the second subform?

 

[Lissa]

6 forms pushes the limit?

To provide more background info... this is a database for storing contract proposals. There is a proposal table that tracks the basic proposal information such as the customer the proposal is submitted to, proposal title, proposal creation date, etc. This table's primary key (ProposalID) is a foreign key to a LABOR table and a WBS table. The Labor table stores labor categories and their corresponding rates. The WBS table stores the task and labor hours spent on each defined task.

The main form is driven by the PROPOSAL TABLE.
The first subform, tied to the main form via the ProposalID, contains horizontal listing of the labor categories, labor rates... the labor hours is obtained form a second subform (subfrmWBS) that contains task entries with labor hours applied then summarized. The second subform is also tied to the main form via ProposalID. The fields from both subforms are not stored in the tables since they are calculated values.

The remaining subforms are all forms based on tables that are linked to the PROPOSAL table via the ProposalID foreign key. But I'm not concerned with those tables at the moment.

__________________

 

[KenHigg]

I don't think you're pushing any limits per se, just trying to overdesign things and make a one form fits all. Without seeing the entire thing it's going to be hard to figure out where the problem(s) are. Can you post a sample database?

 

[Lissa]

Sample DB

Here it is with sample data... the main form is frmProposalSetup
and when you change an hour value in section C, I was trying to get
labor hours in section B to update...

Oops - the size exceeds the limit - I'll try to compress it or something..

 

[KenHigg]

I may have to bug out before I get to look at it - Hopefully someone else will help if I have to pass.

 

[Lissa]

Okay.. thanks for trying.

 

[Lissa]

Help - if anyone is out there...

I was having trouble uploading my sample db since it was over the upload limit - but I have finally gotten my sample db small enough to upload.

Based on Ken's reply, he thought I might be overdesigning with 6 subforms on a main form. I've added a tab control and added the some of the subforms to the main form. I had to delete several forms to make it small enough to post.... but anyhow again... my problem is that I want to update the total labor hours shown on the LABOR tab if the user changes the hours on the WBS tab.

Is there anyone that can help me figure this out? I just feel like I'm so close to a solution...

Thanks.