Bound Calculated Field Not Displaying in Form View (Control Source: FullBreakdown)

[yrstruly]

I've added a calculated field named FullBreakdown to the form's Record Source SQL.

SELECT N.[Select], N.Nr, N.Anno, N.OrderDate, SP.PlantType AS Fruit, N.PlantIDScion AS SCult, SP.Ipno AS SIpno, SP.Clone AS SClone, SP.Phyto AS SPhyto, IIf(      Not IsNull(SP.CommercialAgent), SP.CommercialAgent,
      IIf(
        Not IsNull(SP.Partnership), SP.Partnership,
        IIf(
          Not IsNull(SP.NucliusResponse), SP.NucliusResponse,
          IIf(Not IsNull(SP.Origin), SP.Origin, "O Gaats geen Agent")
        )
      )
    ) AS Agent, N.PlantIDRoot AS RCult, RP.Ipno AS RIpno, RP.Clone AS RClone, RP.Phyto AS RPhyto, N.OrderAmount, N.Allocated, N.Nursery, N.OrderBy, N.Gebied, N.sRL2Pay, N.rRL2Pay, N.TreePrice, Round(
      N.TreePrice 
      + IIf(N.sRL2Pay, 1, 0)
      + IIf(N.rRL2Pay, N.TreePrice * 0.20, 0)
    ,2) AS FinalPricePerTree, Round(
      N.DeliveryQuantity
      * (
          N.TreePrice
        + IIf(N.sRL2Pay, 1, 0)
        + IIf(N.rRL2Pay, N.TreePrice * 0.20, 0)
      )
    ,2) AS TotalLinePrice, "R" & Format(N.TreePrice, '0.00')
      & " + sR" & Format(IIf(N.sRL2Pay, 1, 0), '0.00')
      & " + r%" & Format(IIf(N.rRL2Pay, 20, 0), '0.00') AS FullBreakdown, N.OrderSize, N.CertifiedType, N.DeliveryNo, N.DeliveryDate, N.DeliveryQuantity, N.DeliveryType, N.PayableTo, IIf(N.sRL2Pay, 1, 0) AS sRoyLev, IIf(N.rRL2Pay, N.TreePrice * 0.20, 0) AS rRoyLev
FROM (TblNursOrders AS N INNER JOIN Plants AS SP ON N.PlantIDScion = SP.PlantID) INNER JOIN Plants AS RP ON N.PlantIDRoot  = RP.PlantID
WHERE N.Nr   = [Forms]![frmNursOrder]![Nr]
    AND N.Anno = [Forms]![frmNursOrder]![Anno]
ORDER BY SP.Clone, N.PlantIDRoot, N.OrderAmount DESC;

This is confirmed to be in the form's Record Source, and the query runs fine when opened independently. However, when I try to add the FullBreakdown field to the form:

• I manually added a textbox
• Set its Control Source to FullBreakdown (exactly)
• But in Form View, the textbox displays blank (no error, just empty)
• Field List shows “No fields available”, unless I click “Show All Tables”
• I'm confident the field exists and is typed correctly
  
  The field does not appear on the sub form when i exit design view.

[davegri]

Try making the fullBreakdown textbox taller, or give it a vertical scrollbar.

Sql formattted for readability:

SELECT N.[select],
       N.nr,
       N.anno,
       N.orderdate,
       SP.planttype
       AS Fruit,
       N.plantidscion
       AS SCult,
       SP.ipno
       AS SIpno,
       SP.clone
       AS SClone,
       SP.phyto
       AS SPhyto,
       Iif(NOT Isnull(SP.commercialagent), SP.commercialagent,
       Iif(NOT Isnull(SP.partnership), SP.partnership, Iif(NOT
       Isnull(SP.nucliusresponse), SP.nucliusresponse,
       Iif(NOT
       Isnull(SP.origin), SP.origin,
       "o gaats geen agent"))))
       AS Agent,
       N.plantidroot
       AS RCult,
       RP.ipno
       AS RIpno,
       RP.clone
       AS RClone,
       RP.phyto
       AS RPhyto,
       N.orderamount,
       N.allocated,
       N.nursery,
       N.orderby,
       N.gebied,
       N.srl2pay,
       N.rrl2pay,
       N.treeprice,
       Round(N.treeprice + Iif(N.srl2pay, 1, 0)
             + Iif(N.rrl2pay, N.treeprice * 0.20, 0), 2)
       AS FinalPricePerTree,
       Round(N.deliveryquantity * ( N.treeprice + Iif(N.srl2pay, 1, 0)
                                    + Iif(N.rrl2pay, N.treeprice * 0.20, 0) ), 2
       ) AS
       TotalLinePrice,
       "r" & Format(N.treeprice, '0.00') & "+ sr" &
       Format(Iif(N.srl2pay, 1, 0), '0.00') & "+ r%" &
       Format(Iif(N.rrl2pay, 20, 0), '0.00')
       AS FullBreakdown,
       N.ordersize,
       N.certifiedtype,
       N.deliveryno,
       N.deliverydate,
       N.deliveryquantity,
       N.deliverytype,
       N.payableto,
       Iif(N.srl2pay, 1, 0)
       AS sRoyLev,
       Iif(N.rrl2pay, N.treeprice * 0.20, 0)
       AS rRoyLev
FROM   (tblnursorders AS N
        INNER JOIN plants AS SP
                ON N.plantidscion = SP.plantid)
       INNER JOIN plants AS RP
               ON N.plantidroot = RP.plantid
WHERE  N.nr = [forms] ! [frmnursorder] ! [nr]
       AND N.anno = [forms] ! [frmnursorder] ! [anno]
ORDER  BY SP.clone,
          N.plantidroot,
          N.orderamount DESC;

[June7]

Unrelated to issue but could eliminate second 2 Format function calls

"r" & Format(N.treeprice, "0.00") & "+ sr" &
      IIf(N.srl2pay, "1.00", "0.00") & "+ r%" &
      IIf(N.rrl2pay, "20.00", "0.00")

[yrstruly]

Its a subform and its for display purpose only. Still i dont understand why i cant add new field to it.

[davegri]

Forms don't have fields. They have controls. In your case, it's a textbox control.
The reason you show "No Fields Available" is because the subform has no recordsource defined.
If you want to show a field from the parent's recordsource in the subform textbox, add a Form_Current event to the subform and have it get the textbox value from the parent recordsource. in the example below the subform textbox is named txtFullBreakDown:

Private Sub Form_Current()
    txtFullBreakDown = Me.Parent.FullBreakDown
End Sub

[yrstruly]

Thank you for your assistance. I decided since the subform is for display prpose only. I created a query using the combined code above and implemented a button that will show the user the particular subform selected details with the full calculations.
Trouble is, im working on another developers work, with no documentation left behind.