subform vs form error

[Guest]

this works fine in my prototype form:

Private Sub ProductID_AfterUpdate()
Me.UnitPrice = DLookup("[UnitPrice]", "PeriodicalsTable", "[ProductID] =
Forms![OrderDetails subform]![ProductID]")
End Sub

but when I have this form used as a subform inside another....the
AfterUpdate throws the runtime error 2001 'You have canceled this operation'

ps - went to Forms![OrderDetails subform]![ProductID] because Me.ProductID
wouldn't work despite every possible variation attempted with "'"& not
sure if this is relevant to the Form vs subform issue here but just thought I
would throw that in.....

 

[Baz]

Not entirely clear to me what is where in this scenario, but I'd guess that
this is maybe what you want (if ProductId is numeric):

Private Sub ProductID_AfterUpdate()
Me.UnitPrice = DLookup("[UnitPrice]", "PeriodicalsTable", "[ProductID]
= " & [ProductID])
End Sub

Or this, if ProductID is text:

Private Sub ProductID_AfterUpdate()
Me.UnitPrice = DLookup("[UnitPrice]", "PeriodicalsTable", "[ProductID]
= """ & [ProductID] & """")
End Sub

 

[John Vinson]

this works fine in my prototype form:

Private Sub ProductID_AfterUpdate()
Me.UnitPrice = DLookup("[UnitPrice]", "PeriodicalsTable", "[ProductID] =
Forms![OrderDetails subform]![ProductID]")
End Sub

but when I have this form used as a subform inside another....the
AfterUpdate throws the runtime error 2001 'You have canceled this operation'

ps - went to Forms![OrderDetails subform]![ProductID] because Me.ProductID
wouldn't work despite every possible variation attempted with "'"& not
sure if this is relevant to the Form vs subform issue here but just thought I
would throw that in.....

It is indeed a subform issue.

A Subform is NOT open in its own right, and is not part of the Forms!
collection. You must refer to it via the name of the main form, and
the Name of the subform control on that main form; the subform
*control* is the box containing the subform, and may or may not have
the same name as the form within it. Try

Me.UnitPrice = DLookup("[UnitPrice]", "PeriodicalsTable", "[ProductID]
= Forms![Yourmainformname]![OrderDetails subform].Form![ProductID]")


John W. Vinson[MVP]