The Units in Hand and Units on Order figures are computed in the form by
reference to text box controls in the subform's footer which aggregate the
values from the column in its underlying Inventory Transactions table . As
the subform is in datasheet view you don't see these control in the subform,
only this in the parent form which reference them.
When you switch the form to datasheet view the two Units in hand and Units
on Order columns can no longer 'see' the subform, so can't compute the values
until you open the subdatasheet.
You can if you wish produce a summary of the products current stock
positions with a query such as that below; just paste it into a new query in
SQL view:
SELECT ProductName, ProductDescription,
ReorderLevel, LeadTime,
(SELECT
NZ(SUM(UnitsReceived),0) -
NZ(SUM(UnitsSold),0) -
NZ(SUM(UnitsShrinkage),0)
FROM [Inventory Transactions]
WHERE ProductID =
ProductID)
AS [Units In Hand],
(SELECT
NZ(SUM(UnitsOrdered),0) -
NZ(SUM(UnitsReceived),0)
FROM [Inventory Transactions]
WHERE ProductID =
ProductID)
AS [Units On Order]
FROM Products
ORDER BY ProductName;
In the query the two subqueries replicate the two computed controls on the
products form. You could if you wish create a new form, in datasheet or
continuous form view, based on this query and amend the switchboard form
(using the built in switchboard manager) so you can open it. You won't be
able to change the data in this query, however, as it includes aggregated
values. You can only change these by changing the data in the underlying
tables from which the values are computed, which you can do either in the
Products form and its subform, or by switching it to datasheet view and
opening the subdatsheets.
If you want to change the data in datasheet view then you can join the two
tables in a query such as this:
SELECT ProductName, ProductDescription,
TransactionDate, PurchaseOrderID,
TransactionDescription, [Inventory Transactions].UnitPrice,
UnitsOrdered, UnitsReceived, UnitsSold, UnitsShrinkage
FROM Products INNER JOIN [Inventory Transactions]
ON [Inventory Transactions].ProductID = Products.ProductID
ORDER BY ProductName, TransactionDate;
This will return multiple rows per product, one per inventory transaction
per product, rather than one row per product as with the first query showing
the summary aggregated data.
Ken Sheridan
Stafford, England
