Auto update field in a table Access 2007

Tintoman51
Tintoman51 used Ask the Experts™
on
Hi experts
My data base contains 3 tables we will call them "IntoStock", "OutOfStock" and "CurrentStock"
IntoStock contains records of all items ordered for stock, OutOfStock contains records of everything taken from stock and booked to a job.
I want to automaically update the current stock quantitly when anything is booked in or out of stock. I have tried the update query method using DSum where the item descriptions match, however the error "Operation Must contain an updateable query" comes up so I guess the syntax is wrong or even the whole principal of what I am trying to do.
Please assist me!
Thank you
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
Generally you should not store data in this fashion, at least for a typical stock/inventory system. Instead, you'd store all transactions in a single table (using a positive transaction for a stock intake, and a negative transaction for a stock outtake) and then calculate your current quantity based on that table (and, perhaps, other tables if need be). Also, storing the "current" quantity is a very bad idea, since it must be consistent with at least 2 other tables (in your case, anyway) - and that's not likely to happen.

Here's Allen Browne's excellent example of a proper inventory system:
http://allenbrowne.com/AppInventory.html
Top Expert 2009

Commented:
I agree.  With very rare exceptions, queries should be used for calculated values.

Author

Commented:
I'm obliged for the replies
So now I have a single table which contains all the inbound stock recorde and all the outbound stock records.
I can query the the inbound and outbound totals by product code or description and filter them further to "inbound" or "outbound".
I have the total results displayed on my form but how on earth can I subract the outbound figure from the inbound?
if I try to set the total data control source to inbound - outbound nothing happens unless I click on both the inbound and outbound controls.
I am lost!
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
How did you store your transactions in your table? If you stored those as positive and negative values, then a simple DSum would work to get your Total:

DSum("YourQuantityField", "YourTransactionTable", "WhereClauseIfNeeded")

If you did NOT do this, then you may need more complex summations. If you included a field where you can define a transaction as "inbound" or "outbound", then you will find this much more difficult to work with.

Author

Commented:
I see the sense in that
I would like the users of this database to be able to type in the quantity they draw from stock without having to put put the "-" sign in every time. Can you tell me how to make the Quantity a negative by default?
Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
You can use code to change it:

Me.SomeField = 0 - Me.SomeField

This could cause so UI issues (users typically don't like for data they entered to be changed), so you may consider have your Quantity textbox unbound, and use the AfterUpdate event of that textbox to write to your underlying field:

Sub txQuantity_AfterUpdate()
  Me!YourDatabaseField = 0 - Me.txQuantity
End Sub

You also must account for those times when a user MIGHT enter a negative quantity:

If Me.YourDatabaseField < 0 Then
  Me!YourDatabaseField = Me.txQuantity
Else
  Me!YourDatabaseField = 0 - My.txQuantity
End IF
 

Author

Commented:
Superb! all working thank you

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial