Links when not in subforms. 1

[Flarkey]

hey folks,

I have a problem that seems very simple, but I've been stuck for the last 3 days trying to figure out what to do.

I have a database of engineering equipment and components, with a form used to issue items out to people. There are two main tables:

a) record of equipment
b) records of transactions

The form which is used to issue the equipment uses table b) as a subform and has an "In stock" cell, which is cacultaed by the formula...

"=[Original Quantity]-[Inventory Transactions].[Form]![Sum]"

...where [Sum] is a running total of the transactions.

My Question is: How do I rewrite this formula if i want to remove the subform from the form and just have a linked table???

Thanks for yor help.

 

[Blanch]

Off the top of my head 3 solutions.

1) Use the DSum() function to return the value
e.g. =[Original Quantity] - DSum("Sum", "Inventory Transactions", "EquipmentID = " & [EquipmentID])

This domain function will search the "Inventory Transactions" table and Sum the "Sum" field using a criteria of "EquipmentID = " & [EquipmentID] of the form.

2) write a query that gives you the totals you need and write a procedure that updates your totals box based on the results.

3) WARNING! DISGUSTING HACK!
Create a listbox that rolls up all numbers that you may need to use on the form. If it can be used to display the details within the listbox on the form, then your done. If you need to reuse that data and are not pleased with the listbox appearance on the form, simply hide the listbox and reference the Column property (where n = the columns from 0 to n of the result set).

There is no wrong answer. I haven't actually explored the use of binding tables directly to forms (as subforms) - so knowing the "Correct" answer, if there is such athing, is unknown.

I hope this helps!

Jim

 

[Flarkey]

Cheers blanch!!

The formula you gave me worked a treat, with a few mods to get the syntax right.

I really really appreciate this!!! Thanks again!!

Flarkey in Belfast.