calculation box

[Bickertk]

I have set up a table for a small stock contol. In the table I have the following: Initial stock - New stock In - Stock distributed - Rolling Stock.

I want the rolling stock to recalculate itself everytime new books are ordered or others are distributed. I can get it to do this by using the following code but not every time a new record is entered.

=[Initial Stock]+[New Stock]-[Distributed]

This only works on the first input. When I enter a new record Iniitial stock and new stock start from Zero.

Can anyone help me sort this out please

 

[KenReay]

Hi

First - if you can calculate teh stock with the formula "=[Initial Stock]+[New Stock]-[Distributed]" - why store it in the table, why not just calculate it on the fly in a query?

But if you must do this, where do you have the code?, I would say you should have it in the Before Update Event of the form used to input Stock Movements

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Bickertk]

Hi Ken

Thanks for the information. I have to keep a record of what I get in and also what I distriute an to who.

As you state I could keep the rolling stock figure on the fly. But I am not sure how to write the code for this as the ins and outs would be in a table.

Can you help further

 

[KenReay]

Hi

To calculate the RollingStock 'on the fly' you create a query based on your table, as well as the columns of the table, you also include a 'calculated' column in the query so

RollingStock: [Initial Stock]+[New Stock]-[Distributed]

Use the query in place of the table, and it is as if you have teh RollingStock as a 'permenant column' without actually having its stored.

May I also advise you against using column names with embedded spaces?, better to use something like InitialStock, better still to have a naming convention so that control and/or data type is used as a prefix, so, say Initial Stock is a numeric long type it would be named lngInitialStock


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Bickertk]

Thanks for you help ken