Results 1 to 2 of 2
2004-02-09, 11:55 #1
- Join Date
- Feb 2003
- Thanked 0 Times in 0 Posts
Updating Field from a textbox on a form (Access 2000)
I have three tables STOCK, RESTOCK, USED.
And one form with two subforms (FMSTOCK, SUBFMRESTOCK and SUBFMUSED). The form is based on the STOCK table, and the two subforms are based on RESTOCK, and USED.
I have a textbox in FMSTOCK called TOTAL which subtracts the total in SUBFMUSED from the total in SUBFMRESTOCK, to show me how many of the item I have left.
This all works fine.
Unfortunately I cannot work out how to get the number shown in the textbox TOTAL to replace the amount of stock shown in the STOCK table (need to do this to make it easy to run a reorder report) when I add records to either RESTOCK, or USED. I cannot link the textbox TOTAL direct using the control source, as the control source is set to subtract the totals from the textbox's in the two subforms ( ie =[subrestock]-[subtaken] )
I have tried to use an update query, which is run through a macro via the onchange, and on update options in the properties of the TOTAL textbox on the FMSTOCK form. Nothing happens. I have tried to add a textbox which uses the field AMOUNT from the STOCK table as the control source, which would then update to the TOTAL textbox when the TOTAL textbox changes. Again nothing happens.
2004-02-09, 15:06 #2
- Join Date
- Mar 2002
- Thanked 28 Times in 28 Posts
Re: Updating Field from a textbox on a form (Access 2000)
In general, storing a calculated value in a table is not a good idea. From your description, it seems to me that it wouldn't be too difficult to create a query that calculates TOTAL on the fly; you could use this query for the reorder report.
However, if you really need to store the value, I would bind the TOTAL text box to the corresponding field in the STOCK table. Update TOTAL in the After Update event of both subforms, something like Me.Parent.TOTAL = Me.Parent.[SubRESTOCK] - Me.Parent.[SubTAKEN] (the situation is not quite clear to me, so you may have to adapt this.)