Referring a control on subform not working

Question

Hi,

I have the following Record source behind my subform SalesOrderSubform:

SELECT tblSalesOrderDetails.SalesOrderID, tblSalesOrderDetails.InventoryCode, tblInventory.InventoryName, tblSalesOrderDetails.Quantity, tblSalesOrderDetails.Discount, tblSalesOrderDetails.Rate, Nz(([Quantity]*[Rate])*(1-[Discount]),0) AS ExtendedPrice
FROM tblInventory INNER JOIN tblSalesOrderDetails ON tblInventory.InventoryCode = tblSalesOrderDetails.InventoryCode
ORDER BY tblSalesOrderDetails.SalesOrderID;

on the main form I am referring the control as:

=[SalesOrderSubform].[Form]![GrandTotal]

on the subform GrandTotal comes as Sum([ExtendedPrice])

but it gives me #Error what to do?

Answer 1

Hello Abdul.

First, verify that the sum is displayed correctly on the subform. If so, verify the name of the GrandTotal control, if all is correct, verify the name of the cotrol in the main form that holds the subform. This should be SalesOrderSubform for the reference in the main form to work.

If all is correct but still doesn't work, open the VB Editor and goto Tools, References. Correct all entries for "Missing" libraries.

--
Regards,
Wolfgang

Answer 2

All the things are OK there is no missing libraries too

Answer 3

Could you tell me little bit more about the error as error code, error message etc.

Answer 4

Just not anything appear except #Error Value, the error is occurring from when I added tblInventory in the record source of the subform

Answer 5

did you check your sql command outside the from as ordinary query? 

Answer 6

Abdul Shakeel wrote:

I have the following Record source behind my subform SalesOrderSubform:

SELECT tblSalesOrderDetails.SalesOrderID,
tblSalesOrderDetails.InventoryCode, tblInventory.InventoryName,
tblSalesOrderDetails.Quantity, tblSalesOrderDetails.Discount,
tblSalesOrderDetails.Rate, Nz(([Quantity]*[Rate])*(1-[Discount]),0) AS
ExtendedPrice FROM tblInventory INNER JOIN tblSalesOrderDetails ON
tblInventory.InventoryCode = tblSalesOrderDetails.InventoryCode ORDER BY
tblSalesOrderDetails.SalesOrderID;

on the main form I am referring the control as:

=[SalesOrderSubform].[Form]![GrandTotal]

Is SalesOrderSubform the form name or the name of the subform control (or
both)?

on the subform GrandTotal comes as Sum([ExtendedPrice])

It has to be

=Sum([ExtendedPrice])

Mind the equal sign.

but it gives me #Error what to do?

Try
=[SalesOrderSubform]![GrandTotal]

where [SalesOrderSubform] is the name of the subform control.

---

Peter Doering [MVP Access]

Answer 7

Dear Peter

1. SalesOrderSubform is the name for both subform & the control on the subform

2. The "=" is there I just forget to write

3. =[SalesOrderSubform]![GrandTotal] not works

 

 

Answer 8

Abdul,

if that does not work still, then try disambiguating the subform name and the subform control name and assure that you're pointing at the subform control in your =[SalesOrderSubform]!... entry  (rename the subform control to something unique).

 

---

Mark Burns, MCAD, MCP
Sr. Microsoft Access Analyst/Developer
Manager LinkedIn.Com community: Professional Microsoft Access Developers Network (PMADN)

Answer 9

Abdul Shakeel wrote:

1. SalesOrderSubform is the name for both subform & the control on the subform

As Mark writes, use a different control name, e.g. SalesOrderSubformCtrl

2. The "=" is there I just forget to write

Ok.

3. =[SalesOrderSubform]![GrandTotal] not works

It works for me. Does [GrandTotal] show the correct total?

After you've renamed the control the control source should read:

=[SalesOrderSubformCtrl]![GrandTotal]

---

Peter Doering [MVP Access]