Changing field in a table from form in Access

Question

Sorry if this is a newbie question, I'm a bit rusty in Access and even more rusty in Visual Basic!

I'm trying to find a way of changing a field in a table when a field in another (unrelated) form is changed. More specifically, I'm after a way of reducing the number in the table by whatever the value is in the form field (in a stock control way)

Does anybody know the best way to go about doing this?

Answer 1

The best way is probably an update query run via VBA, for example:

Dim db As Database
Dim strSQL as String

Set db=CurrentDB

strSQL="UPDATE Stock SET InStock=Instock-" & Me.OrderQty & " WHERE ID=" & Me.ID

db.Execute strSQL, dbFailOnError

MsgBox "Updated " & db.RecordsAffected & " record(s)"