altering Subform records via button

[flect]

Hi there!

I have a Form/Subform that i'm trying to add some buttons to-

The form links to tblProducts with all the product information and the subform links to tblStocktake and stores a quantity value for 3 stocktake locations and are linked together and working just fine.


What I want to do is add +(plus) and -(minus) buttons next to the stocktake fields on the subform to add or subtract 1 to the quantity.

So far I have created the buttons on the subform and have failed.

I have the button attached via VBA to a query -

the problem is the query updates ALL the records on tblStocktake to +1 - not just the one selected in the form/subform.

Private Sub plusbtne_Click()
    DoCmd.OpenQuery "plus1", acNormal, adedit
    Me.Form.Repaint
    Me.Form.Requery
End Sub

plus1

UPDATE tblstocktake SET tblstocktake.StockEnv = ([StockEnv]+1);

I hope this is something painfully simple?! I'm trying to teach myself and the learning curve is more like a right angle.

 

[KenHigg]

You're going to have to add a where clause to your sql statement that indicates which record to update. You should ahave a primary key field so it would look something like:

Where myPrimaryKeyFiled = forms!MyMainForm!mySubForm.form!myPrimaryKeyField

 

[flect]

Thanks for your help Ken but I just can't seem to get it working.

So basically - i want to be able to alter the values of [tblstocktake].[StockEnv], +/-1 with the buttons

So there's my [frmStocktake] with info (tblProducts) and [frmStocktake_subform] displaying the "quantity on hand" (tblstocktake) info.

So i've tried to work the WHERE clause into my SQL but i don't think i'm getting the syntax right.

UPDATE tblstocktake SET tblstocktake.StockEnv = ([StockEnv]+1)
WHERE product=forms!stocktake!tblstocktake_subform!stocknumber;

any ideas?

 

[flect]

Sorry to bump myself - but I still haven't solved this one...

Anyone able to help?