update a record based on another updated record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database that I use to create purchase orders. This allows me to
track the prices I pay for items and see how those prices change throughout
the month. (food items). I use a form with a subform that has the individual
items.
Subform has a field of "product" from the product list table and "price" on
the POdetail table. I want to have the product table contain a field with the
last price paid that is updated from the "price" field in the POdetail field
for inventory purposes. How can I updated both fields when I enter the
information in only one?
 
In the subform's AfterUpdate event procedure put some code to update the
table of products, e.g.

Dim cmd As ADODB.Command
Dim strSQL As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strSQL = "UPDATE Products" & _
" SET UnitPrice = " & Me.Price & _
" WHERE Product = """ & Me.Product & """"
cmd.CommandText = strSQL
cmd.Execute

I've assumed that table of products is called Products, the price field in
it is called UnitPrice and that the Product column is text rather than a
numeric ID. If the Product control in your subform is bound to a numeric
field which refernces a numeric primary key of the Products table, e.g.
ProductID, then the SQL would be built like so, omitting the quotes
characters which are needed in the case of a text field:

strSQL = "UPDATE Products" & _
" SET UnitPrice = " & Me.Price & _
" WHERE Product = " & Me.Product

Ken Sheridan
Stafford, England
 
I knew it was going to be that complicated. I pasted your code with a few
changes for my table and field names and it worked perfectly. Thank you for
your help.
 
Back
Top