Dlookup for mutiple conditions - HELP!!!!!!!!

[resistme]

Hi,

I'm new to writing databases in Access (used to write basic stuff in Filemaker years ago) but can't seem to fathom Access lookup codes!

I am trying to build a form that looks up the unit price, royalty rate and haulage rate of a product and posts an entry into the table to store the unit price for future reference so I can calculate the total price based on quantity.

I started by using the sample database Northwind Trader.

However the unit price is dependant to three different conditions, (Product ID, Customer ID and Destination ID), not one in the example. Originally I copied the code from Northwind.

The royalty price is dependent on Product ID and Site Depot ID, and the haulage rate is dependent on the Destination ID.


The code on the from is:-

--------------

Private Sub Customer_ID_AfterUpdate()
'Initialize price for each product change
If Not IsNull(Me![Product ID]) And Not IsNull(Me![Customer ID]) And Not IsNull(Me![Destination ID]) Then
Me![Quantity] = 0
Me.Quantity.Locked = False
Me![Unit Price] = GetListPrice(Me![Product ID], [Customer ID], [Destination ID])

'Empty Customer records mean no customer price list
Else
Me![Unit Price] = 0
End If
End Sub

====

Private Sub Product_ID_AfterUpdate()
'Initialize royalty rate each product change
If Not IsNull(Me![Product ID]) And Not IsNull(Me![Site Depot ID]) Then
Me![Royalty Rate] = GetRoyaltyRate(Me![Product ID], [Site Depot ID])

Else
'Initialize price for each product change
If Not IsNull(Me![Product ID]) And Not IsNull(Me![Customer ID]) And Not IsNull(Me![Destination ID]) Then
Me![Unit Price] = GetListPrice(Me![Product ID], [Customer ID], [Destination ID])

'Empty Product records means no price or royalty rate
Else
Me![Unit Price] = 0
Me![Royalty Rate] = 0
End If
End If
End Sub

====

Private Sub Destination_ID_AfterUpdate()
'Initialize haulage rate for each destination change
If Not IsNull(Me![Destination ID]) Then
Me![Haulage Rate] = GetHaulageRate(Me![Destination ID])


Else
'Initialize price for each destination change
If Not IsNull(Me![Product ID]) And Not IsNull(Me![Customer ID]) And Not IsNull(Me![Destination ID]) Then
Me![Unit Price] = GetListPrice(Me![Product ID], [Customer ID], [Destination ID])


'Empty Destination means no haulage rate

Else

Me![Haulage Rate] = 0
End If
End Sub

--------------------------
Modules Code:


Function GetListPrice(ProductID As Long, CustomerID As Long, DestinationID As Long) As Currency
GetListPrice = DLookup("[Unit Price]", "Customer Price List", "[Product ID]= " & ProductID And "[Customer ID]=" & CustomerID And "[Destination ID]=" & DestinationID)
End Function

Function GetHaulageRate(DestinationID As Long) As Currency
GetHaulageRate = DLookup("[Haulage Rate]", "Haulage Rates", "[Destination ID]= " & DestinationID)
End Function

Function GetRoyaltyRate(ProductID As Long, SiteID As Long) As Currency
GetRoyaltyRate = DLookup("[Royalty Rate]", "Royalty Rates", "[Product ID]= " & ProductID)
End Function

-----------------


I've set the default values to be zero, but as soon as one of the conditional varables changes, I just get error messages, and nothing is updated.

HELP!


Function GetRoyaltyRate(ProductID As Long, SiteID As Long) As Currency
GetRoyaltyRate = DLookup("[Royalty Rate]", "Royalty Rates", "[Product ID]= " & ProductID)
End Function

 

[Atomic Shrimp]

There shouldn't be any need to look up a value, store it, then use it for calculation later - you can do this all in one go in a query...

 

[resistme]

The problem is that when the price or rates change, you won't have the old rates stored in the database and thus the query will spit out the wrong price for the product!

Hence you need to store the current price in the table, so that it doesn't update when you change any of the prices!

 

[boblarson]

Yes, in this case is it okay to store the data in the table. The problem with you lookups is that you are not putting things inside the quotes that need to be inside the quotes and not outside.

For example, this one:
GetListPrice = DLookup("[Unit Price]", "Customer Price List", "[Product ID]= " & ProductID And "[Customer ID]=" & CustomerID And "[Destination ID]=" & DestinationID)

Should be:
GetListPrice = DLookup("[Unit Price]", "Customer Price List", "[Product ID]= " & ProductID & " And [Customer ID]=" & CustomerID & " And [Destination ID]=" & DestinationID)