DLookup You Cancelled Previous Operation

[BJNK]

Ok I have a very simple DLookup function that I cannot figure out! I have an order detail subform in my order form and I just want the selected product to update the SalePrice field to the Price in the Product table.

I took the code directly out of the Northwinds database and changed the code to fit my fields:

Private Sub Product_ID_AfterUpdate()
On Error GoTo Err_Product_ID_AfterUpdate

    Dim strFilter As String
    
    ' Evaluate filter before it's passed to DLookup function.
    strFilter = "Product_ID = " & Me!Product_ID
    
    ' Look up product's unit price and assign it to UnitPrice control.
    Me!SalePrice = DLookup("[Price]", "Product", strFilter)

Exit_Product_ID_AfterUpdate:
    Exit Sub

Err_Product_ID_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_Product_ID_AfterUpdate

End Sub

With that code I get the error "You Cancelled the previous operation" If I remove the strFilter from the DLookup the error goes away, but it returns a value of $0.00.

Your thoughts are appreciated! Thank you!

 

[PHV]

Is Product_ID defined as numeric in the Product table ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BJNK]

No it is not. They are text. I see where your going with this, but I dont know the solution Thanks for your help!

 

[PHV]

strFilter = "Product_ID=[!]'[/!]" & Me!Product_ID [!]& "'"[/!]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BJNK]

Hmmm doesnt work.. The products do contain numbers ie: E8809

So now the error is Syntax error in string in query expression 'Product_ID = "Product_ID='E8809"

Sorry I am clueless with VBA Syntax.. trying to learn but its a slow process.

 

[PHV]

What is your actual code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BJNK]

Private Sub Product_ID_AfterUpdate()
On Error GoTo Err_Product_ID_AfterUpdate

    Dim strFilter As String
    
    ' Evaluate filter before it's passed to DLookup function.
    strFilter = "Product_ID = ""Product_ID='" & Me!Product_ID & "'"

    
    ' Look up product's unit price and assign it to UnitPrice control.
    Me!SalePrice = DLookup("[Price]", "Product", strFilter)

Exit_Product_ID_AfterUpdate:
    Exit Sub

Err_Product_ID_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_Product_ID_AfterUpdate

End Sub

 

[PHV]

Why have you modified what I suggested you for strFilter ?

 

[BJNK]

Because its been a long week and Im an rtard. Thank you very much for your help!

 

[rolandam]

Private Sub Product_ID_AfterUpdate()
On Error GoTo Err_Product_ID_AfterUpdate

Dim strFilter As String

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

' Look up product's unit price and assign it to UnitPrice control.
Me!SalePrice = DLookup("[Price]", "[Product]",strFilter)

Exit_Product_ID_AfterUpdate:
Exit Sub

Err_Product_ID_AfterUpdate:
MsgBox Err.Description
Resume Exit_Product_ID_AfterUpdate

End Sub