Doing Math in Forms

  • Thread starter Thread starter RichardY
  • Start date Start date
R

RichardY

In my database I have three forms:

1. Purchase Form, to record each instance we purchase inventory for
unit and the quantity purchased.

2. Sell Form, to record each instance a unit is sold and the quantit
sold.

3. Unit Form, to record the details of the unit. This form has
a one-to-many relationship with the Purchase Form and Sell Form usin
the Unit field.

I have a field in my Sell Form called "Units Remaining" that I wante
to automatically generate the amount of products we have left. That'
where I am stuck. I tried playing with the Default Value for th
"Units Remaining" field with this expression:

=[Forms]![Purchase]![Units Purchased]-[Units Taken]

but it didn't work. Please help
 
Hmmm.
Does your db have three tables corresponding to the forms?
I might do it differently. Have a Product table, describing and identifying
the "Unit" with a unique Unit ID.
Second, have an Inventory Transactions Table, since purchases and sales are
really just transactions - one in and one out.
The Inv Trans table would have a foreign key(UnitID) to the Product table,
and also have date, unitsreceived, unitsremoved fields.
Then create a form for Products(or Units) and on that form a subform for
InvTransactions. From there it is relatively easy to show balance of each
product.
If you have separate Sales and purchase forms, the subform on the Products
table could be updated with a query.

HTH
Damon
 
Damon said:
HMMM.
DOES YOUR DB HAVE THREE TABLES CORRESPONDING TO THE FORMS?
I MIGHT DO IT DIFFERENTLY. HAVE A PRODUCT TABLE, DESCRIBING AND
IDENTIFYING
THE \"UNIT\" WITH A UNIQUE UNIT ID.
SECOND, HAVE AN INVENTORY TRANSACTIONS TABLE, SINCE PURCHASES AND
SALES ARE
REALLY JUST TRANSACTIONS - ONE IN AND ONE OUT.
THE INV TRANS TABLE WOULD HAVE A FOREIGN KEY(UNITID) TO THE PRODUCT
TABLE,
AND ALSO HAVE DATE, UNITSRECEIVED, UNITSREMOVED FIELDS.
THEN CREATE A FORM FOR PRODUCTS(OR UNITS) AND ON THAT FORM A SUBFORM
FOR
INVTRANSACTIONS. FROM THERE IT IS RELATIVELY EASY TO SHOW BALANCE OF
EACH
PRODUCT.
IF YOU HAVE SEPARATE SALES AND PURCHASE FORMS, THE SUBFORM ON THE
PRODUCTS
TABLE COULD BE UPDATED WITH A QUERY.

HTH
DAMON
Click to expand...

Damon,

Thanks for the tips. I redesigned my tables in accordance to your
suggestions. I have a products form w/ an Inventory Transaction
subform, which shows the Product ID, Date, Unitsreceived and
Unitspurchased fields. But how do I show the balance either in the
Inventory Transaction subform or in the Products Form?

Thanks for clarifying,

Richard
 
Put a textbox on the subform footer that
performs your sums and add this expression as the control source:
=Sum(nz([UnitsReceived])-nz([UnitsRemoved]))
On the main form, create a textbox and add the
following expression:

=[subformname].Form!textboxname

This refers to the textbox on your subform footer.
 
Back
Top