subform in main form

[Revned]

i have a subform name [OrderSubform] under my main form with a Record
Source: qryOrder
with the following SQL from a query object

SELECT OderTable.OrderRef, OderTable.CustomerID, OderTable.[Item Code],
Item.Description, OderTable.Quantity, Item.Price, [Quantity]*[Price] AS
Amount, OderTable.[Date Order], OderTable.[Date required], OderTable.[Date
Deliver], OderTable.Remarks
FROM OderTable INNER JOIN Item ON OderTable.[Item Code] = Item.[Item Code];

when i enter the Item code in Item code column of my subform under my main
form it populate the item description in Description column and price to
Price column then I manually enters the qty in Qty column and the Amount
column calculates [Qauntity]* [Price] so it works will so far

but the problem is when I check and open the tblOrder where my query is
bound only the Item Code and the Price are populate in table but the
Description and the Amount are empty

i am using access 2003, pls. i just want to know what could be the best way
to handle this?

pls.if there is a need to reconstruct my SQL i will appreciate it.

thanks in advance

 

[Allen Browne]

Leave the Description and Amount out of your table.

You can easily create a query that calculates the Amount, and gets the
Description for matching the [Item Code]. If you actually store the Amount
in your table instead of having the computer calculate it, there is always
the chance that the wrong value will be stored.

For more info, see:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

 

[Revned]

Mr. Allen Browne, thank you very much it helps me a lot

and thank you for your time, i really appreciate

Leave the Description and Amount out of your table.

You can easily create a query that calculates the Amount, and gets the
Description for matching the [Item Code]. If you actually store the Amount
in your table instead of having the computer calculate it, there is always
the chance that the wrong value will be stored.

For more info, see:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

i have a subform name [OrderSubform] under my main form with a Record
Source: qryOrder
with the following SQL from a query object

SELECT OderTable.OrderRef, OderTable.CustomerID,
OderTable.[Item Code],
Item.Description, OderTable.Quantity, Item.Price,
[Quantity]*[Price] AS Amount, OderTable.[Date Order],
OderTable.[Date required], OderTable.[Date
Deliver], OderTable.Remarks
FROM OderTable INNER JOIN Item
ON OderTable.[Item Code] = Item.[Item Code];

when i enter the Item code in Item code column of my subform
under my main form it populate the item description in Description
column and price to Price column then I manually enters the qty in
Qty column and the Amount column calculates [Qauntity]* [Price]
so it works will so far

but the problem is when I check and open the tblOrder where my
query is bound only the Item Code and the Price are populate in table
but the Description and the Amount are empty

i am using access 2003, pls. i just want to know what could be the
best way to handle this?

pls.if there is a need to reconstruct my SQL i will appreciate it.