Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Students Click Here

Formula Help

Formula Help

Formula Help

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.



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

RE: Formula Help

Dear Sue,

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


RE: Formula Help

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??



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.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close