Error 35, Subform DLookUp Solution (1 Viewer)

[Solved] Error 35, Subform DLookUp Solution

I am stuck on something that's probably easy for one of you to fix. Here's the problem. On my subform, I have 2 controls. One combo box that displays a list of product codes and product names. Beside it is the BaseCaseCost.

I need the after update property of Product Code, to look at the code and go find the BaseCaseCost from a query called, Base Case Price.

Private Sub Product_Code_AfterUpdate()
'Initialize price for each product change

If Not IsNull(Me![Product Code]) Then

Me![BaseCaseCost] = GetBaseCaseCost(Me![Product Code])

'Empty Product records mean user wants to delete lien item
Else
eh.TryToRunCommand acCmdDeleteRecord

End If
End Sub

Then Function GetBaseCaseCost(Product Code As Long) As Currency

GetBaseCaseCost = DLookupNumberWrapper("[BaseCaseCost]", "Base Case Price", "[Product Code] = " & Product Code)

End Function


I borrowed the from Northwind but I'm obviously changed it improperly. I get Error 35. I'm happy to get this another way. Any other suggestions?

Not sure that I understand your need correctly, but maybe something like:

Hi Bob,

This problem is in the subform of the Order Details form. I want the product name (Product ID is stored value) to appear in a drop down box and then the subform needs to go and get the base case cost. This is a calculated field in a query. All the solutions I see have the price or cost right in the products table. Since mine is calculated is in a different query. Here is a new price of code that I was trying.

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 base case cost from the Base Case Price query and assign it to BaseCaseCost control.

Me![BaseCaseCost] = DLookup("BaseCaseCost", "Base Case Price", strFilter)

Exit_Product_ID_AfterUpdate:
Exit Sub

Err_Product_ID_AfterUpdate:
MsgBox Err.Description

Resume Exit_Product_ID_AfterUpdate

End Sub

This code worked. I realized I had a problem with multiple values at the table level. I fixed that and it worked!

How you store Price could lead to problems with historical data.
You might want to look at this.

Thank you Bob Fitz and jdraw for some different ideas. I have more to build in this database so I'm sure I'll be back there.