update a record based on another updated record

[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?

 

[Guest]

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

 

[Guest]

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.