×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Formula Help

## Formula Help

(OP)
I need to write a formula to do the following:-

Field 1 = Qty in Stock
Field 2 = Qty Branch Ordered.

I need the qty in the Qty in Stock field to go down when a branch orders the stock from Qty Branch Ordered.

Hope the above makes sense, I am new to this.

Thanks

Catherine

### RE: Formula Help

The "simple" answer is that you can write a macro that runs when the Qty Branch Ordered is entered, and use a SET command to change the Qty in Stock. Like this:

SET Qty in Stock = Qty in Stock - Qty Branch Ordered

The problem you will find is that someone enters 100 and meant to enter 10, and your macro is likely to subtract the 100 and then subtract the 10 making a mess of your Qty in Stock.

A better method is to have the Qty in Stock be dynamically calculated for you. Instead of having a field "Qty in Stock" you could have a database to contain the "stock received" records. Adding up (using a SSum()in a calculated field) all the amounts in this new database gives you the Total Received. Then add up all the Qty Branch Ordered and that is the Total Ordered. Then your Qty In Stock = Total Received - Total Ordered, and the error I described above will not occur.

Note that on a form view, the Total Received should have a summary option of "All records in the received database" and Total Ordered would have "All records in the ordered database".

This is how professional order entry systems, like mine, work!

Sue Sloan
www.odpro.com

### RE: Formula Help

(OP)
Dear Sue,

Thanks for the advice.  It's been a great help.

Catherine

### RE: Formula Help

(OP)
Dear Sue,

I was wondering if you could please answer one more question for me.  I have tried your first option for deducting the stock by creating the macro you suggested but instead of deducting the stock, it creates a new record which ends up - the figure the branch has ordered.  The original stock stays the same.  How do I get the macro to find the correct stock figure to deduct.  Is there a formula where it can marry up the item name if it equals the same??

Thanks

Catherine

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!