Forum Discussion
Northwind example DB - auto lookup for prices from product table
Hi there,
I am playing around with building a database by looking in the Northwind example DB. I am trying to understand how prices of the Products are mapped into the Order Details once you select the individual product when creating an order. The table design just tells me there is a lookup on the Product ID, but nothing that I can see that links the price, and the Relationships tells me the same thing. Any guidance would be appreciated. Thanks
It is pretty opaque, isn't it? I have trouble understanding why anyone went to so much trouble to make it so obscure. But enough editorializing.
The After Update event of the Combo box where you select a product for the order details in an order is this (the last time I looked):
Private Sub ProductID_AfterUpdate()
'Initialize price and discount for each product change
If Not IsNull(Me![ProductID]) Then
Me![Quantity] = 0
Me.Quantity.Locked = False
Me![UnitPrice] = GetListPrice(Me![ProductID])
Me![Discount] = 0
Me![OrderDetailStatusID] = None_OrderItemStatus
'Empty Product records mean user wants to delete line item
Else
eh.TryToRunCommand acCmdDeleteRecord
End If
End SubI highlighted the line which calls a procedure, GetListPrice, which in turn looks up the current list price for the product.
That procedure is a Function which inexplicably calls yet another function called DLookupNumberWraper:
Function GetListPrice(lProductID As Long) As Currency
GetListPrice = DLookupNumberWrapper("[ListPrice]", "Products", "[ProductID] = " & lProductID)
End FunctionAs nearly as I can tell, that wrapper function is showing off, but I wasn't there when it was created, so that's just a cynical guess on my part.
Anyway, what the wrapper function does is (finally) use DLookup to get the ListPrice from the Products table which corresponds to the ProductID selected back in the form.
All of that could have been done in a single line in the original After Update event!
In my way of thinking this is about two layers of code too deep to be practical, but for the time being there's not much to be done about it.
3 Replies
It is pretty opaque, isn't it? I have trouble understanding why anyone went to so much trouble to make it so obscure. But enough editorializing.
The After Update event of the Combo box where you select a product for the order details in an order is this (the last time I looked):
Private Sub ProductID_AfterUpdate()
'Initialize price and discount for each product change
If Not IsNull(Me![ProductID]) Then
Me![Quantity] = 0
Me.Quantity.Locked = False
Me![UnitPrice] = GetListPrice(Me![ProductID])
Me![Discount] = 0
Me![OrderDetailStatusID] = None_OrderItemStatus
'Empty Product records mean user wants to delete line item
Else
eh.TryToRunCommand acCmdDeleteRecord
End If
End SubI highlighted the line which calls a procedure, GetListPrice, which in turn looks up the current list price for the product.
That procedure is a Function which inexplicably calls yet another function called DLookupNumberWraper:
Function GetListPrice(lProductID As Long) As Currency
GetListPrice = DLookupNumberWrapper("[ListPrice]", "Products", "[ProductID] = " & lProductID)
End FunctionAs nearly as I can tell, that wrapper function is showing off, but I wasn't there when it was created, so that's just a cynical guess on my part.
Anyway, what the wrapper function does is (finally) use DLookup to get the ListPrice from the Products table which corresponds to the ProductID selected back in the form.
All of that could have been done in a single line in the original After Update event!
In my way of thinking this is about two layers of code too deep to be practical, but for the time being there's not much to be done about it.
- Thanks George, much more complicated than I thought. I would have assumed there is an inbuilt function in Access to return a different field within the same record if the reference to that record is made.
What would the entire code be if you were to re-do it?There are two possible approaches, but for sure that wrapper function is totally redundant. I would also not implement the GetListPrice() function unless I needed to get the list price that in multiple places in different forms or reports. We'd have to review the entire accdb to be sure if that would be useful or not.
In it's simplest version, this ought to work.
Private Sub ProductID_AfterUpdate()
'Initialize price and discount for each product change
If Not IsNull(Me![ProductID]) Then
Me![Quantity] = 0
Me.Quantity.Locked = FalseMe![UnitPrice] = GetListPrice(Me![ProductID])Me!UnitPrice = DLookup("[ListPrice]", "Products", "[ProductID] = " & Me![ProductID])
Me![Discount] = 0
Me![OrderDetailStatusID] = None_OrderItemStatus
'Empty Product records mean user wants to delete line item
Else
eh.TryToRunCommand acCmdDeleteRecord
End If
End Sub
