Problem with update coding

[CP]

the following coding will only pick the first "unit price" in my "products"
table regardless of the productID - could someone kindly explain/breakdown
this coding for me so I can fix this, or does my problem lie elsewhere

Private Sub ProductID_AfterUpdate()
On Error GoTo Err_ProductID_AfterUpdate

Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "ProductID = " & Me!ProductID

' Look up product's unit price and assign it to UnitPrice control.
Me!UnitPrice = DLookup("UnitPrice", "Products", strFilter)

Exit_ProductID_AfterUpdate:
Exit Sub

Err_ProductID_AfterUpdate:
MsgBox Err.Description
Resume Exit_ProductID_AfterUpdate

End Sub

ProductID exists in both products and orderdetails tables as does the field
unitprice

 

[Damian S]

Hi CP,

If you have a control called ProductID and a field called ProductID, you can
end up with a circular reference and confuse Access when you say me!ProductID
- do you mean the control or the field from the datasource? Naturally you
want the control, so call your control something like txtProductID or
cboProductID and use me!cboProductID to reference it.

Your code *looks* correct, so I would guess that it's a circular reference
problem.

Hope this helps.

Damian.

 

[CP]

Ok I found the problem

Both the tables concerned did not have the unique key with the same name

One was called ProductID and other CHProductID in the test version I made

Once I made the unique keys have the same name it worked fine - thanks for
the help