Form Calculations 1

[sha123]

Hi There

I have 2 forms: 1)FrmTools 2)FrmToolBookOut
On Both forms there is a field called quantity!

If I Book out a tool on the FrmToolBookOut Form I need it to deduct it from FrmTools!


Please does anyone have the code to help me here?

 

[KenReay]

Hi

You do not hold data in forms, you hold it in tables, what you want to do is to update the table which conatins teh quantity of tools and on which frmTools is presumably based

Regards

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

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

UK

 

[sha123]

I store my info in tables, the names of the tables is the same as the forms!

But what is the code to deduct the out going quantity of tools from the tools in stock?

 

[KenReay]

Hi

First, there should be no need to deduct the quatity loaned out, you should be able to calculate the quantity 'onhand' from the data you have, using a query.

But assuming you insist on storing the data twice

base your issues form on a query joining the two tables

put both quantity columns on the form (the quantity on hand can be set visible = false if you do not want user to see it)

In the before update event on the form put

txtQuantityOnHand = txtQtyOnHand - txtQuantity

using your own control names of course

Alternativley you could keep FrmToolBookOut Form and in the after update event of the form execute an SQl update query, something like

Dim strSQL As String
strSQL = "UPDATE tblWhatever SET Quantity = Quantity + " & Me.Quantity & " WHERE StockCode = '" & Me.StockCode & "';"
DoCmd.RunSQL strSQL

again you need to subsitute your own control / column names, and if your stock code is anumber type ratehr than a string you do not need the ' surrounding it

Regards

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

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

UK

 

[sha123]

Thanks it worked like a bomb!