Triple subform in Tab Control requery question

Question

I’m working with Access 2007 and I have a tricky issue I can’t seem to resolve.

I’ve got a form frmEnterBids that is bound to a table tblBids.

The form has a couple of text box controls and a Tab Control.

The tab control has 6 tabs each containing a subform having Default View = Datasheet.

Each subform is bound to a separate table each containing a BidID field.

The Link Master/Child Fields for each subform on each tab is BidID.

And all works well.

The tricky issue is that tab 5 contains a subform that contains a subform that contains a subform.

Yes, a three layer subform!

 

The Relationship between the three layers

subform 1.  sfrmAVTechSupport – View/Datasheet - Link Master/Child Fields is BidID.

subform 2.  sfrmAVRooms – View/Datasheet - Link Master/Child Fields is ID_AV.

subform 3.  frmEnterAVDetail – View/Single Form - Link Master/Child Fields is ID_Rm.

 

This creates a nice interface where a + to the left of the record lets a user add child records. 

 

In subform2 I have a text box bound to a TotRmRate field from table, tblAVRoomEquipDetail, populated by frmEnterAVDetail.

Subform2, sfrmAVRooms, has the following Record Source:

SELECT tblAVRooms.*, tblAVRoomEquipDetail.TotRmRate

FROM tblAVRoomEquipDetail INNER JOIN tblAVRooms ON tblAVRoomEquipDetail.ID_Rm = tblAVRooms.ID_Rm;

 

The issue I’m having is when a new record is added with child records, the total is not refreshed after the – is clicked to close subform3.  If I press F5 the Total appears.

 

I tried the following Requery on the lost focus event of subform 3.  frmEnterAVDetail but nothing happens.

Private Sub Form_LostFocus()

Forms!frmEnterBid.sfrmAVRooms.Form.Requery

End Sub

 

Where am I going wrong?

Answer 1

To get a value to recalculate, you can use Me.Realc or Forms!FormName.Recalc.

I would use the After Update event to run the recalc code.

You should also check that you have the correct syntax to refer to a control on another subform when you have nested subforms. Here is a link:

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

and for tab controls

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

 

---

Jeanette Cunningham (Access MVP) Pakenham, Victoria Australia

Answer 2

Thanks for the reply Jeanette.  So what you are telling me is that .Recalc is the equivalent of pressing the F5 key on the keyboard.  Correct?  So when I enter data in subform 3.  frmEnterAVDetail and I what to .Recalc subform 2.  sfrmAVRooms I should use:

 

Me.Parent.Recalc

In the AfterUpdate event of my subform 3.  frmEnterAVDetail

 

I’ll give that a try.