So, once you have entered the 3 prices for each product into the related
table, when you create an order for the customer you want to have the
software look up the current price to charge this client for the product,
based on their customer level?
Presumably you have a main form for the order, and a subform for the order
details (line items) - something like the Orders form in the Northwind
sample database that installs with Access. The main form will have a
CustomerID combo. Set up the combo's RowSource so it includes the
CustomerLevelID field, e.g.:
SELECT CustomerID, CustomerName, CustomerLevelID
FROM Customers ORDER BY CustomerName;
Don't forget to increase the combo's Column Count to 3.
Now in the AfterUpdate event procedure of the ProductID in the subform, you
can DLookup() the price in the ProductPrice table, based on the product and
the 3rd column of the CustomerID combo on the main form.
The AfterUpdate code will be something like this:
Dim strWhere As String
Dim varResult As Variant
varResult = Me.Parent!CustomerID.Column(2)
If IsNumeric(varResult) AND Not IsNull(Me.ProductID) Then
strWhere = "(ProductID = " & Me.ProductID & _
") AND (CustomerLevelID = " & varResult & ")"
varResult = DLookup("Price", "ProductPrice", strWhere)
If Not IsNull(varResult) Then
Me.Price = varResult
End If
End If
If that's a completely new idea, open the Orders Subform in Northwind, and
look at the code in the ProductID combo's event. It doesn't handle the bit
about differential pricing so it doesn't look at the customer combo on the
main form, but the idea of lookup is the same.
Alternatively, here's a description of DLookup():
http://allenbrowne.com/casu-07.html
Hi Allen,
It seems to be working fine -- you are a life saver. Can you also, tell
[quoted text clipped - 26 lines]
