Using Formulas In Access

[Hayley78]

Hello!
I'm new to Access, and I'm trying to teach myself as I got along, But have run into a small problem. And was hoping some body could help, or point me into the right direction.
I am trying to find a way of using a type of formula between 2 forms (Tables), In one table/form, I want to be able to change a #, and then for it to do the sum and update it in another Table/form.
Am I barking up the wrong tree?? Can this even be done!??
Please help!

Kind regards
Hayley Taylor (IT @ Brands Hatch)

 

[KenReay]

Hi

If the value can be calclated, why store it?

Without knowing the detail of what you are trying to achieve, I would suspect that the answer is to use a query, and have the 'formula' as a calculated field within the query.

To make a calculated field you go into the query builder grid, set up the rest of your query, ie the table(s), join(s), criteria, and in a spare column, you put MyField:MyFormula whre MyField is a filed name you decide and formula is an expresion based on constants and/or columns in you table(s) so for example you might have Value:tblStock.qty * tblPriceList.Price

Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[Hayley78]

Ken Reay,

I have a table which shows total stock quantities, and another table which shows received stock. What I would like to do is add a button in the received stock form to that when clicked will update the total stock quantity table.

Hope that this explains a bit better

Thank you in advanced for your help.

Hayley Taylor
IT @ Brands Hatch

 

[KenReay]

Hi

You could do something like (in the on click event)

DoCmd.RunSQL "UPDATE tblStock INNER JOIN tblReceived ON tblStock.PartNo = tblReceived.PartNo SET tblStock.StockQty = [StockQty]+[QtyRec];"

You need to substitute your table and column names of course.

But to be fair, to do this correctly you need to think about your table structure, eg is the relationship between stock and Recieved 1 -> Many (yes I would think), in which case you need to qualify the above SQL to select only the specific received row you wish to use in the update.

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use