Can I update another table using events?

[jendendav]

Is it possible to take the number of units entered into a data entry form based on one table and in events write code to update the stock on hand field of another table? Would the event be "after update" and how complicated is the code? The table the form is based on is strictly to record transactions.

 

[Guest_imported]

several easy ways but something like this in the code for the after update event may be the easiest

dim strsql as string
strsql = "update stocktbl set [stocktbl].[onhand] = [onhand]- " & [forms]![unitsform]![unitsused] & " where stocktbl.keyfield = " & [forms]![unitsform]![keyfield]
docmd.runsql(strsql)
you would need to place the names of your fields in the proper places for the above code to work
you could create an update query using the builder in the qbe grid to help with syntax then use the event to run the query

 

[Guest_imported]

another solution is to use an update query

 

[Guest_imported]

sorry, echo, wake up puerto

 

[jendendav]

Thanks imlost2. Now some questions.
Using your language strsql is the update query, yes?
Is unitsform based on the update query? Because mine is based on the transactions table. This meant that it had fields from the transactions table only. But the code you sent me looks as though it references a field from the products table (stocktbl). The key field for my products table is the basis of the relationship between the two tables. Or am I reading too much into the word "keyfield"? I'm understanding this to say you cannot directly get to the products table. You must use the data entry form to run the query to update stocktbl while the data entry form is updating the transactions table. Have I made a complete muddle of this?

 

[denisemathieu]

i'm using the update query to do an update to a table from a form. but when it runs, it prompts the user like an update query, 'you are updating...do you want to update.... i want it to just update the record and not tell the user what's going on.