DLOOKUP error 1

[malbreht]

Hi!

I want to do a lookup on tblProduct before entering the price of the product in tblPurchaseProduct.

tblPurchaseProduct ([u]PurhaseProduct_ID[/u], Purchase_ID, Product_ID, [b]Price[/b], Quantity, Cancel)
tblProduct ([u]Product_ID[/u], Name, [b]Price[/b])

The names in my form are the same - Product_ID and Price.

I copied the code from Nortwind db, but an error occurs - red text (actually yellow).

Private Sub 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.
    [COLOR=red]Me!Price = DLookup("Price", "tblProduct", strFilter)[/color]

Exit_Product_ID_AfterUpdate:
    Exit Sub

End Sub

Does anybody know, what could be the problem?

Tnx, mare

 

[JerryKlmns]

DLookup returns NULL if the value was not found!

And do provide the error that comes up

 

[malbreht]

Oh, sorry: the error window displays:

run-time error '3464':

Data type mismatch in criteria expression.

Tnx!

 

[JerryKlmns]

If it is a Text data type ...
strFilter = "Product_ID = '" & Me!Product_ID & "'

 

[malbreht]

Hey!

If I delete:

Dim strFilter As String

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

and erase strFilter from the red row, it does start to lookup the price, but for each product returns only the price of the first product (ID=1), which is wrong...

Any clues?

Thank you very much!

 

[malbreht]

Your suggestion returns the same - everytime just the price for the first product.

Anybody?

 

[JerryKlmns]

And this?

Me!Price = DLookup("Price", "tblProduct", "Product_ID=" & Me!Product_ID)

 

[malbreht]

Thank you very much, Jerry - but it still doesn't work. The line is still yellow or it returns the wrong price (the first one).

Product_ID is text, if I haven't written yet.

Thanks a lot!

 

[JerryKlmns]

You just did!

Me!Price = DLookup("Price", "tblProduct", "Product_ID='" & Me!Product_ID & "'")

 

[malbreht]

Oh, man! I love you!

It works! Many, many thanks and sorry for not describing the type of the field.

Mare

 

[JerryKlmns]

You 're welcomed, Mare!