Problem with Subform update based on another subform. 1

[LD1010]

Thank you for reading my post.
I am tracking Customers, their contracts with us and each of the services that make up each contract.

The form structure I'm using is a main form with 10 sub forms.

Main form - "frmCustomerResEnter" - RS is "tblCustomers", Pk "CustomerID"

1st Sub Form - "sfrmContracts" - RS is "tblContracts", Pk "ContractID", Fk "CustomerID"

2nd Sub Form - "sfrmServiceItems" - RS is "tblServiceItems", Pk "ServiceItemID", Fk's "ContractID" and "ServiceTypeItemID"

3rd Sub Form - "sfrmHavcItems" - RS is tblHvacItems, Pk "HavcItemID", Fk "ServiceTypeItemID" (a composite key of "ServiceItemID" & "SerivceTypeID")
(There are 7 of the above sub forms, each for a different type of service. Each becomes visible based on the value entered in "cboServiceItemID"

10th Sub Form - "sfrmContServicesSum", RS - "qryContServicesSum" (This sub form displays the total accumlated cost for each of the
different services that make up the contract.)

What I'm trying do (unsucssefuly so far) is update the values shown in "sfrmContServicesSum" on the after update event of the "cost" control of "sfrmHavcItems". (And each of the other sub forms.)
I have added a cmd button on the "sfrmServiceItems" to refresh the form data. "DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70" This does update the data in "sfrmContServicesSum" but I would like the update to happen each time a cost is entered for a new item.

I tried "Me.Parent.sfrmServiceItems.Form.Requery" on the after update event of "cost" on "sfrmHavcItems" but that doesn't seem to work. Not sure if I'm using the wrong event, the worng syntax or both.

Any help would be much appreciated.

 

[MajP]

I would build a procedure and put it in a standard module. Not a form module. This is untested, but the general idea.

public sub requeryAll(frm as access.form)
  dim ctl as access.control
  for each ctl in frm.controls
    if ctl.controltype = acsubform then
     ctl.form.requery  
    end if
  next ctl
end sub

Call this from a subform/s event/s
requeryAll(me.parent)

This will requery all subforms on the parent form.

 

[LD1010]

Thank you very much MajP.
I need to leave the office now but I'll give suggestion a try on Monday. Have a great weekend.

 

[joebb3]

MajP,

Wouldn't another possible fix be to reapply the record source of sfrmContServicesSum OnUpdate of sfrmHavcItems?

Kind of forced a requery?

OnUpdate of the "Cost" Control:
Me!sfrmContServicesSum.RecordSource = "qryContServicesSum"

Its worked for me in the past.
Just asking.
Joe

 

[joebb3]

Opps.. Didn't see the Sub form to sub form thing...

You'd have to use the fully qualified path:

forms!mainform!sfrmContServicesSum.RecordSource = "qryContServicesSum"


Yes?

 

[MajP]

I was actually focusing on ensuring that the 10 other subforms requery. If they are linked that is not a problem because they will requery with the main. But if not linked you may have to force the requery.

However, like you said, changing the recordsource also forces a requery.

 

[boblarson]

But since you have to refer to the subform CONTROL in the syntax (not the name of the subform itself, unless they share the exact same name) it should be:

Forms!YourMainForm.SubformControlName.Form.RecordSource = ...

And from the subform itself it is just

Me.Requery

or for the parent (to ensure all)

Me.Parent.Requery

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[boblarson]

I meant RECORD SOURCE not Requery. Eeek - I wish you could edit threads after posting.

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[MajP]

Bob,

or for the parent (to ensure all)
Me.Parent.Requery

I do not believe that is correct. If the subforms are linked to the main form (or cascaded) then yes, if they are not linked then requerying the main form does not requery all subforms. Thus the code I provided.

 

[boblarson]

MajP:

Yes, you are correct about UNLINKED subforms. However, I didn't see any mention of the OP's subforms not being linked so I was going off of their post but I suppose I should have qualified it with that they need to be linked for it to work.

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com