Reference Subform control within the subform in Ms Access

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc used Ask the Experts™
Hi people There!

I having problems assigning a bound control within a sub form called sfrmCostofSales subform] see the photo below . I want a control called stdcost to be updated by un bound control called txtperformance which carries special calculation for cost of sales , I tried the VBA code below , but nothing is working:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.StdCost = Forms![frmCustomerInvoice]![sfrmCostofSales subform].Form![txtPerformance]
End Sub

The parent form is called frmCustomerInvoice , could it be I'm using a wrong event.

Instead of entering calculation of cost of sales per unit manually I want this control called stdCost which is bound to the table to be update by this unbound control within the datasheete subform called sfrmCostofSales subform] which calculate the correct cost of sales

Kindly help!



Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018
This should work - but [sfrmCostofSales subform] must be name of the subform control, not the subform itself:

Private Sub Form_BeforeInsert(Cancel As Integer)

    Me!StdCost.Value = Me![sfrmCostofSales subform].Form![txtPerformance].Value

End Sub

Open in new window

Fabrice LambertConsulting
Distinguished Expert 2017

In your main form, there is a control holding the subform, and this  control have a Form property giving you access to the held form.
So basically, the syntax is:

Open in new window

Side note:
Access give the sub form control holder's name the same name as the sub form (ehence the confusion), but you can name it whatever you like.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial