Dlookup issues!

[Mikkel]

Hi
Not being an expert, I have taken Northwind's approach to Dlookup, which works fine for BLPEuPrice, see below.
I have tried the same for a different field in another table (Tbl_ProdCat4) but it returns always the last row value for the field InvFactor from Tbl_ProdCat4.
I have a few one to many tables each providing a level of product categorisation or hierarchy and at the highest level 4, each category has a different invoice factor.
Please help, I am lost at this point...

Sub ProdID_AfterUpdate()

On Error GoTo Err_ProdID_AfterUpdate

Dim strFilter As String
Dim StrFilter2 As String

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

' Look up product's unit price and assign it to UnitPrice control.
Me!OrDetInvFactor = DLookup("[InvFactor]", "Tbl_ProdCat4", StrFilter2)
Me!BLPEuPrice = DLookup("BLPEu", "Tbl_Prod_Cong", strFilter)

Exit_ProdID_AfterUpdate:
Exit Sub

Err_ProdID_AfterUpdate:
MsgBox Err.Description
Resume Exit_ProdID_AfterUpdate

End Sub

 

[jal]

What steps have you taken to debug your code? At the very least you need to do something like:

msgBox(strFilter2)

to verify that the correct filter is in place.

If so, then run the query in sql View. A DLookup is basically a SELECT.


SELECT [InvFactor]
FROM Tbl_ProdCat4
WHERE StrFilter2



Well, substitute the actual value for strFilter2 and then paste it into Sql View, and then run the query. If it's not returning what you need, then you have misdesigned your filter string.

 

[Mikkel]

Thanks Jal, I have made some progress but still not quite there. As you recommended I have mastered the Immediate tool, I have plugged values into the DLookup criteria etc. All seems to work fine. Thanks!
But still don't not know how to get the criteria to choose the correct row value of ProdCat4 based on the relationship that ProdCat4 has to the Products table. I would think somwhere the relationships between the tables must paly a role. How would I write the criteria code to do this?
Mike

 

[jal]

I don't really understand your tables or your situation. You suggest "levels of categorization." Ok let's say the lowest level is the actual item sold:

ProductID
Hershey bar
Scotch Tape
Casey's Motor oil

And then the next table categorizes those items:


Product2ID Product1ID
Candies Hershey_Bar
Adhesive_Tapes Scotch_tape
Automotive_Supplies Casey's_ motor_oil



And the next table would further categorize those items


Product 3ID Product2ID Product1ID
Supermarket_Items Candies Hershey_bar
Supermarket_Items AdhesiveTapes Scotch_Tape
Supermarket_Items Automotive Supplies Casey's_Motor_oil





This last table is called tbl_Product3ID. I don't see the problem. If you specify:

SELCECT Inv_Factor
FROM tbl_Product3ID
WHERE Product1ID = "Hershey bar"

Then, whichever row has "Hershey bar", should be returned, whether that row be first or last. You complain that "it always returns the last row." But is that the row with Hershey bar? If so, it is returning the correct row, so what's the problem?

 

[jal]

By the way, I also don't understand why so many tables are needed. When I look at that last table, it seems to include all the information from the lower-level tables, so I would hazard a guess that maybe the lower-level tables are unnecessary.

But I suppose the problem is that I just can't understand your situation. Sorry.

 

[Mikkel]

Jal
Your hierarchy breakdown with the 'Hershey bars' etc. was spot-on. Up to the point where you said 'what's the problem?', there was no problem. The problem came after where for example, I could not type Hershey bars or other products into the code everytime I change product in order Detail Table.
In my case, each highest level grouping of products have their own Invoice factor, and this invoice factor was to be selected dependent on the product selected in the Order Detail table, without having to type the value or 'hershey bar' into the code.
I managed to solve it somehow... In the Query of the Order Detail table I included the InvoiceFactor from the other table, and then did a Dlookup of this field via the ProdID of the Form Orders, inserting the value into the Orders Detail table. (To store the value historically).

Quite difficult to explain, hope you understand what I am saying. My method is probably wrong and their are probably better ways to achieve this. Your advice was definitely key.. Thanks!
Mike

 

[jal]

Thanks for the kind words, they are very encouraging, although realistically I didn't contribute much here. Glad you figured it out.