#ERROR on a Form (1 Viewer)

[HillTJ]

Hi, I have a form containing a linked subform & a linked Sub-Sub form within it. All works well. Basically, it's for receipting goods ordered where one item can be receipted in over multiple deliveries. All Good. However, on the Sub-Sub form I have an unbound control in the footer to sum the Quantity Received. That is sum([Quantity_Received]). I then use this sum on the main form in another unbound control =Nz([Forms]![FRMPO]![FRMPoDetails subform].[Form]![FRMPOReceipts subform].[Form]![Sum_Qty_Received],0). This works too, except where the Sub-Sub form has not been opened (I mean clicked on the expand "+" beside the line item on the Sub form) where a #Error is returned by the unbound control on the main form. If you're still with me, how do I prevent this occurrence? Appreciate any help.

 

[arnelgp]

what is the Master/Child links of Subform.
also the Master/Child links of Sub-Subform.
maybe you can directly use =DSum() on the unbound textbox instead of =Nz(Forms!....)

 

[HillTJ]

arnelgp,

Subform
Source Object = FRMPODetails Subform
Link Master Fields = POID (Which stands for Purchase Order ID)
Link Child Fields = POID

Sub-Sub Form
Source Object = FRMPOReceipts Subform
Link Master Fields = PO UniqueID
Link Child Fields = Receipt ID

Hope this is what you mean. Cheers

 

[arnelgp]

like I said, just replace your =Forms![…] with this:

=Nz(DSum("qtyReceivedFieldName", "tableWhereToGetTheQty", "[Receipt ID]=" & [Forms]![FRMPO]![FRMPoDetails subform].[Form]![PO UniqueID]), 0)

if the ID's are string:

=Nz(DSum("qtyReceivedFieldName", "tableWhereToGetTheQty", "[Receipt ID]=" & Chr(34) & [Forms]![FRMPO]![FRMPoDetails subform].[Form]![PO UniqueID] & Chr(34)),  0)

 

[HillTJ]

Arnelgp, big thanks, will give it a go. How/where do you people learn this stuff? Amazes me!

 

[HillTJ]

arnelgp, The following line works in eliminating the #Error but does not update when I select a different line on the Subform & select the linked sub-sub form. Any ideas, I think we are close to getting it right. Thanks..

 

[arnelgp]

add a code to the first subforn Tequerying the textbox.

Private Sub sub1_Current()
Me.Parent!theTextboxOnMainform.Requery
End Sub

 

[HillTJ]

Arnelgp, works like a charm!. Really appreciate it. Will study up on this code so that i understand it to use elsewhere. How do i learn this stuff?. Frustrates me being a numby.

 

[arnelgp]

you should read some books/article on vba especially om access. there are lots on the net.