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.
imlost2 (Visitor) Oct 26, 2000
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
jendendav (TechnicalUser) Oct 27, 2000
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?
This was posted last week but I got no answer to my second set of questions. Can anyone help?
imlost2 (Visitor) Oct 26, 2000
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
jendendav (TechnicalUser) Oct 27, 2000
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?
This was posted last week but I got no answer to my second set of questions. Can anyone help?
