on hand quantity

[segurarl]

To get the 'on hand' quantity I'm using a 'Dsum' [received]-[sold] from the
Detail table.

My products' form (from the Product table, not from the Detail table) has a
sub-form with the results from de 'Dsum'.

The problems is the form is "very slow" now.

Is there a better technique to show the 'on-hand' quantity on the form?

Thank you!

 

[Allen Browne]

If this is a continuous form or datasheet (i.e. showing lots of products at
once) and you need it to be editable, it will be slow.

You can try writing your own DSum() replacement function based on a query
statement. It will probably be faster than DSum(), but will still be slow.

If you are using DSum() twice (once for the 'received' and once for the
'sold'), you could might double the speed by using a single call:
=DSum("[received]-[sold]", "Detail", "ProductID = " & Nz([ProductID],0))

If you don't mind a read-only result, using a subquery will be much more
efficient. Assuming a query based on the Product table only, type an
expression like this into a fresh column in the Field row in query design:
OnHand: (SELECT Sum([received]-[sold]) FROM Detail
WHERE Detail.ProductID = Product.ProductID)

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066